/// <summary>
/// 分页功能
/// </summary>
/// <param name="tableName">表名，可以是多个表，最好用别名</param>
/// <param name="primarykey">主键，可以为空，但@order为空时该值不能为空</param>
/// <param name="fields">要取出的字段，可以是多个表的字段，可以为空，为空表示select * </param>
/// <param name="pageSize">每页记录数</param>
/// <param name="currentpage">当前页，表示第页</param>
/// <param name="filter">条件，可以为空，不用填where</param>
/// <param name="group">分组依据，可以为空，不用填group by</param>
/// <param name="order">排序，可以为空，为空默认按主键升序排列，不用填order by</param>
/// <param name="recnums">记录个数</param>
/// <param name="pagenums">页数</param>
/// <returns></returns>
public DataTable GetPagingInfo(string tableName, string primarykey, string fields, int pageSize, int currentpage, string filter, string group, string order, out int recnums, out int pagenums)
{
DataTable Dt = new DataTable("data");
try
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@tablenames", tableName),
new SqlParameter("@primarykey", primarykey),
new SqlParameter("@fields", fields),
new SqlParameter("@pagesize", pageSize),
new SqlParameter("@currentpage", currentpage),
new SqlParameter("@filter", filter),
new SqlParameter("@group", group),
new SqlParameter("@order", order),
new SqlParameter("@recnums", 0),
new SqlParameter("@pagenums", 0)
};
sqlParameters[8].Direction = ParameterDirection.Output;
sqlParameters[9].Direction = ParameterDirection.Output;
Dt = Obj.ExecuteDataTable("dbo.pPagingLarge", CommandType.StoredProcedure, sqlParameters);
recnums = Helper.StringToInt(sqlParameters[8].Value.ToString());
pagenums = Helper.StringToInt(sqlParameters[9].Value.ToString());
return(Dt);
}
catch (Exception ex)
{
ErrorLog.WriteErrorMessage(ex);
recnums = 0;
pagenums = 0;
return(Dt);
}
}
//获取门店预定列表
public DataTable GetReserveList(string rid, string resdate, string retime, string metcode, string restatus, string mobile, string name, string currentpage, string pagesize, string stocode, string dicid, string ttcode, ref string sumcount)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@rid", rid),
new SqlParameter("@stocode", stocode),
new SqlParameter("@resdate", resdate),
new SqlParameter("@retime", retime),
new SqlParameter("@metcode", metcode),
new SqlParameter("@restatus", restatus),
new SqlParameter("@mobile", mobile),
new SqlParameter("@name", name),
new SqlParameter("@currentpage", currentpage),
new SqlParameter("@pagesize", pagesize),
new SqlParameter("@dicid", dicid),
new SqlParameter("@ttcode", ttcode),
new SqlParameter("@sumcount", SqlDbType.VarChar,64, sumcount)
};
sqlParameters[12].Direction = ParameterDirection.Output;
dt = DBHelper.ExecuteDataTable("p_getreservelistbyform", CommandType.StoredProcedure, sqlParameters);
sumcount = sqlParameters[12].Value.ToString();
return(dt);
}
/// <summary>
/// 消息中心
/// </summary>
/// <param name="openid"></param>
/// <returns></returns>
public DataTable MyMesInfo(string openid)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@openid", openid)
};
return(DBHelper.ExecuteDataTable("p_mymesinfo", CommandType.StoredProcedure, sqlParameters));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(ref TB_DiscountSchemeEntity Entity)
{
DataSet dtStructure = DBHelper.ExecuteDataSet("select top 0 * from TB_DiscountScheme");
StringBuilder sql = new StringBuilder();
sql.Append(" declare @returnval int;");
sql.Append(" set @returnval=0;");
sql.Append("declare @PKCode varchar(32)");
sql.Append("exec [dbo].[p_GetbaseCode] @PKCode output");
sql.Append(" if exists(select 1 from TB_DiscountScheme where BusCode='" + Entity.BusCode + "' and StoCode='" + Entity.StoCode + "' and SchName='" + Entity.SchName + "') begin set @returnval=1; end ");
if (!string.IsNullOrEmpty(Entity.InsideCode))
{
sql.Append(" else if exists(select 1 from TB_DiscountScheme where BusCode='" + Entity.BusCode + "' and StoCode='" + Entity.StoCode + "' and InsideCode='" + Entity.InsideCode + "') begin set @returnval=1; end ");
}
sql.Append(" else begin BEGIN TRAN tan1");
Entity.CTime = System.DateTime.Now;
Entity.UTime = System.DateTime.Now;
List <string> lstExcludeFilds = new List <string>();
Dictionary <string, string> dicAttachFilds = new Dictionary <string, string>();
lstExcludeFilds.Clear();
lstExcludeFilds.Add("Id");
lstExcludeFilds.Add("PKCode");
lstExcludeFilds.Add("CTime");
lstExcludeFilds.Add("UTime");
dicAttachFilds.Clear();
dicAttachFilds.Add("PKCode", "@PKCode");
dicAttachFilds.Add("CTime", "getdate()");
dicAttachFilds.Add("UTime", "getdate()");
string InsertSql = EntityHelper.GenerateSqlByDE <TB_DiscountSchemeEntity>(dtStructure.Tables[0], Entity, lstExcludeFilds, dicAttachFilds, EntityHelper.eSqlType.insert);
sql.AppendFormat(" INSERT INTO TB_DiscountScheme " + InsertSql + " ;");
sql.AppendLine(" exec dbo.p_uploaddata_isSync '" + Entity.BusCode + "','" + Entity.StoCode + "','TB_DiscountScheme','PKCode',@PKCode,'add'");
#region 特殊折扣、
foreach (TR_DiscountSchemeRateEntity rate in Entity.DSRateList)
{
sql.Append("insert into [dbo].[TR_DiscountSchemeRate]([BusCode],[StoCode],[CCode],[CCname],[CTime],[TStatus],[Sort],[SchCode],[DiscountType],[DisTypeCode],[DisCode],[DisMetCode],[DiscountRate]) values('" + Entity.BusCode + "','" + Entity.StoCode + "','" + Entity.CCode
+ "','" + Entity.CCname + "','" + Entity.CTime + "','" + rate.TStatus + "','" + rate.Sort + "',@PKCode,'" + rate.DiscountType + "','" + rate.DisTypeCode + "','" + rate.DisCode + "','" + rate.DiscountRate + "');");
}
sql.AppendLine(" if(@@error=0) begin set @returnval=0; commit tran tan1; end else begin rollback tran tran1;set @returnval=1; end");
sql.AppendLine(" end");
sql.AppendLine(" select @returnval;");
DataTable dt = DBHelper.ExecuteDataTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(StringHelper.StringToInt(dt.Rows[0][0].ToString()));
}
else
{
return(2);
}
#endregion
}
/// <summary>
/// 检测名称是否存在
/// </summary>
/// <param name="id"></param>
/// <param name="Status"></param>
/// <returns></returns>
public int CheckRoleName(string id, string rolename)
{
int Result = 0;
DataTable dtCheck = DBHelper.ExecuteDataTable(string.Format("select * from TB_Roles where RoleName='{0}' and id<>{1} ", rolename, id));
if (dtCheck != null && dtCheck.Rows.Count > 0)
{
Result = 1;
}
dtCheck = null;
return(Result);
}
//预约
public DataTable AddReserve(string openid, string stocode, string rdate, string rtime, int usernum, string remark)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@openid", openid),
new SqlParameter("@stocode", stocode),
new SqlParameter("@rdate", rdate),
new SqlParameter("@rtime", rtime),
new SqlParameter("@usernum", usernum),
new SqlParameter("@remark", remark)
};
return(DBHelper.ExecuteDataTable("dbo.p_AddReserve", CommandType.StoredProcedure, sqlParameters));
}
/// <summary>
/// 菜品类别管理树节点
/// </summary>
/// <returns></returns>
public DataTable GetDisTypeTreeListInfo(string filter, string order)
{
string sql = "select PKKCode as pId,PKCode as id,TypeName,(case TStatus when '0' then '(无效)' else '(有效)' end) as TStatusName,StoCode,Sort,'' as StoName,dbo.fn_GetDisTypeParentName(PKKCode) as PPKName,TStatus from TB_DishType ";
if (!string.IsNullOrEmpty(filter))
{
sql += filter;
}
if (!string.IsNullOrEmpty(order))
{
sql += order;
}
return(DBHelper.ExecuteDataTable(sql));
}
//排队记录
public DataTable GetWaitList(string openid, string type, string currentpage, string pagesize, ref string sumcount)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@openid", openid),
new SqlParameter("@type", type),
new SqlParameter("@currentpage", currentpage),
new SqlParameter("@pagesize", pagesize),
new SqlParameter("@sumcount", SqlDbType.NVarChar,256, sumcount)
};
sqlParameters[4].Direction = ParameterDirection.Output;
dt = DBHelper.ExecuteDataTable("p_getwaitlist", CommandType.StoredProcedure, sqlParameters);
sumcount = sqlParameters[4].Value.ToString();
return(dt);
}
/// <summary>
/// 账单退款操作
/// </summary>
/// <param name="BusCode"></param>
/// <param name="StoCode"></param>
/// <param name="BillCode"></param>
/// <param name="CCode"></param>
/// <param name="CCname"></param>
/// <param name="mescode"></param>
/// <returns></returns>
public DataTable BillReturn(string BusCode, string StoCode, string BillCode, string CCode, string CCname, out string mescode)
{
mescode = "";
SqlParameter[] sqlParameters =
{
new SqlParameter("@BusCode", BusCode),
new SqlParameter("@StoCode", StoCode),
new SqlParameter("@BillCode", BillCode),
new SqlParameter("@CCode", CCode),
new SqlParameter("@CCname", CCname),
new SqlParameter("@mescode", SqlDbType.NVarChar,64, mescode)
};
sqlParameters[5].Direction = ParameterDirection.Output;
DataTable dt = DBHelper.ExecuteDataTable("dbo.p_TB_Bill_Return", CommandType.StoredProcedure, sqlParameters);
mescode = sqlParameters[5].Value.ToString();
if (mescode.Length > 0)
{
return(null);
}
else
{
return(dt);
}
}
/// <summary>
/// 绑定会员卡
/// </summary>
/// <param name="openid"></param>
/// <param name="cardcode">卡号</param>
/// <param name="mobile">手机号</param>
/// <param name="idno">身份证号</param>
/// <param name="paypassword">支付密码</param>
/// <param name="mes"></param>
public void BindMemCard(string openid, string cardcode, string mobile, string idno, string paypassword, ref string mescode)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@openid", openid),
new SqlParameter("@cardcode", cardcode),
new SqlParameter("@mobile", mobile),
new SqlParameter("@IDNO", idno),
new SqlParameter("@CardPassWord", paypassword),
new SqlParameter("@mes", SqlDbType.VarChar,64, mescode)
};
sqlParameters[5].Direction = ParameterDirection.Output;
DBHelper.ExecuteDataTable("p_checkbindmemcardtrue", CommandType.StoredProcedure, sqlParameters);
mescode = sqlParameters[5].Value.ToString();
}
/// <summary>
/// 找店
/// </summary>
/// <param name="pagesize"></param>
/// <param name="currentpage"></param>
/// <param name="citycode"></param>
/// <param name="shopcircle"></param>
/// <param name="keywords"></param>
/// <returns></returns>
public DataTable FindStore(string pagesize, string currentpage, string citycode, string shopcircle, string keywords, ref string sumcount)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@citycode", citycode),
new SqlParameter("@shopcircle", shopcircle),
new SqlParameter("@keywords", keywords),
new SqlParameter("@pagesize", pagesize),
new SqlParameter("@currentpage", currentpage),
new SqlParameter("@sumcount", SqlDbType.VarChar,64, sumcount)
};
sqlParameters[5].Direction = ParameterDirection.Output;
DataTable dt = DBHelper.ExecuteDataTable("p_FindStore", CommandType.StoredProcedure, sqlParameters);
sumcount = sqlParameters[5].Value.ToString();
return(dt);
}
/// <summary>
/// 根据门店编号及员工编号获取打赏信息
/// </summary>
/// <param name="empcode"></param>
/// <param name="stocode"></param>
/// <returns></returns>
public DataTable GetWXRewardInfo(string empcode, string stocode)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@empcode", empcode),
new SqlParameter("@stocode", stocode)
};
return(DBHelper.ExecuteDataTable("dbo.p_getwxrewardinfo", CommandType.StoredProcedure, sqlParameters));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(ref TB_UserRoleEntity Entity)
{
StringBuilder sql = new StringBuilder();
sql.Append(" declare @returnval int;");
sql.Append(" set @returnval=0;");
sql.Append("declare @BusCode varchar(16);");
sql.Append("declare @StoCode varchar(8);");
sql.Append("declare @RoleId varchar(1024);");
sql.Append("declare @UserId bigint;");
sql.Append("declare @RealName nvarchar(32);");
sql.Append("declare @EmpCode varchar(16);");
sql.Append(" set @BusCode='" + Entity.BusCode + "';");
sql.Append(" set @StoCode='" + Entity.StoCode + "';");
sql.Append(" set @RoleId='" + Entity.StrRoleId + "';");
sql.Append(" set @UserId=" + Entity.UserId + ";");
sql.Append(" set @RealName='" + Entity.RealName + "';");
sql.Append(" IF NOT EXISTS(Select 1 from [dbo].TB_UserRole Where userid=@UserId)");
sql.Append(" BEGIN ");
sql.Append(" BEGIN TRANSACTION tran1");
sql.Append(" exec dbo.p_uploaddata_isSync @BusCode,@StoCode,'TB_UserRole','userid',@UserId,'add';");
sql.Append(" insert into TB_UserRole ([buscode],[stocode],userid,roleid,realname,empcode,CTime)SELECT @BusCode,@StoCode,@UserId,col,@RealName,@EmpCode,getdate() FROM [dbo].[fn_StringSplit](@RoleId,',');");
sql.Append(" IF(@@ERROR=0) BEGIN COMMIT TRAN tran1;set @returnval=0;END ELSE BEGIN ROLLBACK TRAN tran1; set @returnval=1; END ");
sql.Append(" END");
sql.Append(" else ");
sql.Append(" BEGIN ");
sql.Append(" BEGIN TRANSACTION tran1");
sql.Append(" exec dbo.p_uploaddata_isSync @BusCode,@StoCode,'TB_UserRole','userid',@UserId,'update';");
sql.Append(" DELETE FROM TB_UserRole where userid=@UserId; delete TB_UserSigScheme where usercode=@UserId;");
sql.Append(" insert into TB_UserRole ([buscode],[stocode],userid,roleid,realname,empcode,CTime)SELECT @BusCode,@StoCode,@UserId,col,@RealName,@EmpCode,getdate() FROM [dbo].[fn_StringSplit](@RoleId,',');");
sql.Append("delete TB_UserDiscountScheme where usercode=@UserId;");
sql.Append(" IF(@@ERROR=0) BEGIN COMMIT TRAN tran1;set @returnval=0;END ELSE BEGIN ROLLBACK TRAN tran1; set @returnval=1; END ");
sql.Append(" END ");
sql.Append(" select @returnval;");
DataTable dt = DBHelper.ExecuteDataTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(StringHelper.StringToInt(dt.Rows[0][0].ToString()));
}
else
{
return(2);
}
}
/// <summary>
/// 根据商户编号获取所有员工,缓存用,仅缓存dicname
/// </summary>
/// <param name="BusCode"></param>
/// <returns></returns>
public DataTable GetBusCodeToDicts(string BusCode)
{
string where = string.Empty;
if (!string.IsNullOrEmpty(BusCode))
{
where = " and buscode='" + BusCode + "'";
}
string sql = "select dicname from ts_Dicts where status = 1 and pdicid = 28" + where + ";";
return(DBHelper.ExecuteDataTable(sql, CommandType.Text, null));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(ref TB_DishEntity Entity)
{
StringBuilder sql = new StringBuilder();
sql.Append(" declare @ID int;");
sql.Append(" declare @returnval int;");
sql.Append(" set @returnval=0;");
sql.Append("declare @DisCode varchar(32);");
sql.Append("declare @DisMethodCode varchar(32);");
sql.Append("exec [dbo].[p_GetbaseCode] @DisCode output;");
sql.Append(" if exists(select 1 from TB_Dish where StoCode='" + Entity.StoCode + "' and DisName='" + Entity.DisName + "') set @returnval=1;");
sql.Append("BEGIN TRANSACTION tan1");
string InsertSql = " values('{0}',getdate(),'1',@DisCode,'{1}','{2}','{3}','{4}','{5}',{6},{7},'{8}','{9}')";
InsertSql = string.Format(InsertSql, Entity.StoCode, Entity.DisName, Entity.OtherName, Entity.TypeCode, Entity.QuickCode, Entity.Unit, Entity.Price, Entity.CostPrice, Entity.QRCode, Entity.Descript);
sql.AppendFormat(" INSERT INTO TB_Dish " + InsertSql + " ;");
#region 菜品图片
if (!string.IsNullOrEmpty(Entity.ImageName))
{
string[] ins = Entity.ImageName.Split(',');
foreach (string name in ins)
{
sql.AppendFormat(" INSERT INTO TR_DishImage(DisCode,ImgUrl) values(@DisCode,'/uploads/UpDishImages/" + name + "');");
}
}
#endregion
sql.AppendLine(" if(@@error=0) begin set @returnval=0; commit TRANSACTION tan1; end else begin rollback TRANSACTION tran1;set @returnval=1; end");
sql.AppendLine(" select @returnval;");
DataTable dt = DBHelper.ExecuteDataTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
return(StringHelper.StringToInt(dt.Rows[0][0].ToString()));
}
else
{
return(2);
}
}
/// <summary>
/// 投诉建议列表
/// </summary>
/// <param name="openid"></param>
/// <param name="pagesize"></param>
/// <param name="currentpage"></param>
/// <param name="mescode"></param>
/// <returns></returns>
public DataTable GetComplaintsList(string openid, string pagesize, string currentpage, ref string sumcount)
{
SqlParameter[] sqlParameters =
{
new SqlParameter("@openid", openid),
new SqlParameter("@pagesize", pagesize),
new SqlParameter("@currentpage", currentpage),
new SqlParameter("@sumcount", SqlDbType.VarChar,64, sumcount)
};
sqlParameters[3].Direction = ParameterDirection.Output;
DataTable dt = DBHelper.ExecuteDataTable("p_ComplaintsList", CommandType.StoredProcedure, sqlParameters);
sumcount = sqlParameters[3].Value.ToString();
return(dt);
}
/// <summary>
/// 增加一条数据
/// </summary>
public DataTable Add(ref TB_BillCouponEntity Entity, ref string mescode)
{
intReturn = 0;
SqlParameter[] sqlParameters =
{
new SqlParameter("@Id", Entity.Id),
new SqlParameter("@BusCode", Entity.BusCode),
new SqlParameter("@StoCode", Entity.StoCode),
new SqlParameter("@CCode", Entity.CCode),
new SqlParameter("@CCname", Entity.CCname),
new SqlParameter("@TStatus", Entity.TStatus),
new SqlParameter("@BillCode", Entity.BillCode),
new SqlParameter("@CouponCode", Entity.CouponCode),
new SqlParameter("@CouponMoney", Entity.CouponMoney),
new SqlParameter("@MemberCardCode", Entity.MemberCardCode),
new SqlParameter("@RealPay", Entity.RealPay),
new SqlParameter("@VIMoney", Entity.VIMoney),
new SqlParameter("@Remark", Entity.Remark),
new SqlParameter("@UseType", Entity.UseType),
new SqlParameter("@ShiftCode", Entity.ShiftCode),
new SqlParameter("@CouponName", Entity.CouponName),
new SqlParameter("@McCode", Entity.McCode),
new SqlParameter("@mescode", SqlDbType.NVarChar,256, mescode),
new SqlParameter("@TicType", Entity.TicType),
new SqlParameter("@TicWay", Entity.TicWay)
};
sqlParameters[0].Direction = ParameterDirection.Output;
sqlParameters[17].Direction = ParameterDirection.Output;
DataTable dt = DBHelper.ExecuteDataTable("dbo.p_TB_BillCoupon_Add", CommandType.StoredProcedure, sqlParameters);
if (dt != null && dt.Rows.Count > 0)
{
Entity.Id = int.Parse(sqlParameters[0].Value.ToString());
}
return(dt);
}
/// <summary>
/// 按条件获取会员卡基本信息
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public DataTable GetMemcard(string where)
{
string sql = "SELECT *,mt.cname as typename FROM[dbo].[MemCard] m inner join[dbo].[memcardtype] mt on m.ctype = mt.mctcode " + where;
return(DBHelper.ExecuteDataTable(sql));
}
/// <summary>
/// 根据门店获取折扣方案
/// </summary>
/// <param name="BusCode">商户编号</param>
/// <param name="StoCode">门店编号</param>
/// <returns></returns>
public DataTable GetStoDisCountPackage(string BusCode, string StoCode)
{
string sql = "select *,'' as CardName from discountpackage where stocode='" + Helper.GetAppSettings("StoCode") + "' and buscode='" + BusCode + "' and status='1'";
return(DBHelper.ExecuteDataTable(sql));
}
