/// <summary>
/// 生成追番
/// </summary>
/// <param name="PHBN">配合表年</param>
/// <returns></returns>
public string GenerateZF(string PHBN)
{
string strSql = "select ifnull(max(zf),'0') + 1 as zf from FMD070 where PHBN ='" + PHBN + "';";
string zf = DbHelperMySql.GetSingle(strSql).ToString();
return(zf.PadLeft(3, '0'));
}
/// <summary>
/// 得到材料过期日
/// </summary>
/// <param name="CLBH">材料编号</param>
/// <param name="HLRY">混炼日期</param>
/// <returns></returns>
public DateTime GetYXTS(string CLBH, string HLRY)
{
string strSql = "select DATE_ADD('" + HLRY + "',INTERVAL YXTS DAY) from fmd050 where CLBH='" + CLBH + "'";
string YXTS = DbHelperMySql.GetSingle(strSql).ToString();
return(Convert.ToDateTime(YXTS));
}
/// <summary>
/// 根据用户名返回上一次登录记录
/// </summary>
public Model.manager_log GetModel(string user_name, int top_num, string action_type)
{
int rows = GetCount("user_name='" + user_name + "'");
if (top_num == 1)
{
rows = 2;
}
if (rows > 1)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id from (select top " + top_num + " id from " + databaseprefix + "manager_log");
strSql.Append(" where user_name=@user_name and action_type=@action_type order by id desc) as T ");
strSql.Append(" order by id asc");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@action_type", MySqlDbType.VarChar, 100)
};
parameters[0].Value = user_name;
parameters[1].Value = action_type;
object obj = DbHelperMySql.GetSingle(strSql.ToString(), parameters);
if (obj != null)
{
return(GetModel(Convert.ToInt32(obj)));
}
}
return(null);
}
/// <summary>
/// 获得Tags查询分页数据(搜索用到)
/// </summary>
public DataSet ArticleSearch(int site_id, string tags, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
//查询站点频道列表
DataTable dt = new DAL.MySql.site_channel(databaseprefix).GetList("site_id=" + site_id).Tables[0];
if (dt.Rows.Count > 0)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from (");
for (int i = 0; i < dt.Rows.Count; i++)
{
strSql.Append("select id,site_id,A.channel_id,call_index,title,zhaiyao,add_time,img_url");
strSql.Append(" from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dt.Rows[i]["name"].ToString() + " as A INNER JOIN (");
strSql.Append("select R.channel_id,R.article_id");
strSql.Append(" from dt_article_tags_relation as R INNER JOIN dt_article_tags as S ON R.tag_id=S.id and S.title='" + tags + "'");
strSql.Append(") as T ON A.channel_id=T.channel_id and A.id=T.article_id");
strSql.Append(" where datediff(d,add_time,getdate())>=0");
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
if (i < (dt.Rows.Count - 1))
{
strSql.Append(" UNION ALL ");//合并频道数据表
}
}
strSql.Append(") as temp_article");
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
recordCount = 0;
return(new DataSet());
}
/// <summary>
/// 得到比例合计
/// </summary>
/// <param name="CLBH">材料编号</param>
/// <returns></returns>
public double GetSumBL(string CLBH)
{
string strSql = "select ifnull(sum(BL),1) as BL from FMD070 where CLBH='" + CLBH + "'";
string bl = DbHelperMySql.GetSingle(strSql).ToString();
return(Convert.ToDouble(bl));
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Model.article_attribute_field model = GetModel(id);//取得扩展字段实体
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
//删除所关联的频道数据表相关列
DataTable dt = new DAL.MySql.site_channel(databaseprefix).GetFieldList(conn, trans, id).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//检查有无该频道数据表和列
int rowsCount = Convert.ToInt32(DbHelperMySql.GetSingle(conn, trans, "select count(1) from syscolumns where id=object_id('" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "') and name='" + model.name + "'"));
if (rowsCount > 0)
{
//删除频道数据表一列
DbHelperMySql.ExecuteSql(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + " drop column " + model.name);
}
}
}
//删除频道关联字段表
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "site_channel_field");
strSql1.Append(" where field_id=@field_id");
MySqlParameter[] parameters1 =
{
new MySqlParameter("@field_id", MySqlDbType.Int32, 4)
};
parameters1[0].Value = id;
DbHelperMySql.ExecuteSql(conn, trans, strSql1.ToString(), parameters1);
//删除扩展字段主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article_attribute_field");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = id;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
trans.Commit();//提交事务
}
catch
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
/// <summary>
/// 返回父节点的ID
/// </summary>
public int GetParentId(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 parent_id from " + databaseprefix + "article_category");
strSql.Append(" where id=" + id);
return(Convert.ToInt32(DbHelperMySql.GetSingle(strSql.ToString())));
}
/// <summary>
/// 返回商品库存数量
/// </summary>
public int GetStockQuantity(string channel_name, int article_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 stock_quantity ");
strSql.Append(" from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where id=" + article_id);
return(Convert.ToInt32(DbHelperMySql.GetSingle(strSql.ToString())));
}
public int GetSingle(string sqlClause)
{
var result =
_providerConfig.DbProvider == DbProvider.MySql
? DbHelperMySql.GetSingle(_providerConfig.DbConnectionString, sqlClause)
: DbHelperSql.GetSingle(_providerConfig.DbConnectionString, sqlClause);
return(TypeParse.StrToInt(result));
}
/// <summary>
///
/// </summary>
/// <param name="sqlClause"></param>
/// <returns></returns>
public object GetSingleObject(string sqlClause)
{
var result =
_providerConfig.DbProvider == DbProvider.MySql
? DbHelperMySql.GetSingle(_providerConfig.DbConnectionString, sqlClause)
: DbHelperSql.GetSingle(_providerConfig.DbConnectionString, sqlClause);
return(result);
}
/// <summary>
/// 快捷添加系统默认导航
/// </summary>
public int Add(string parent_name, string nav_name, string title, string link_url, int sort_id, int channel_id, string action_type)
{
//先根据名称查询该父ID
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("select top 1 id from " + databaseprefix + "navigation");
strSql1.Append(" where name=@parent_name");
MySqlParameter[] parameters1 =
{
new MySqlParameter("@parent_name", MySqlDbType.VarChar, 50)
};
parameters1[0].Value = parent_name;
object obj1 = DbHelperMySql.GetSingle(strSql1.ToString(), parameters1);
if (obj1 == null)
{
return(0);
}
int parent_id = Convert.ToInt32(obj1);
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "navigation(");
strSql.Append("parent_id,channel_id,nav_type,name,title,link_url,sort_id,action_type,is_lock,is_sys)");
strSql.Append(" values (");
strSql.Append("@parent_id,@channel_id,@nav_type,@name,@title,@link_url,@sort_id,@action_type,@is_lock,@is_sys)");
strSql.Append(";select @@IDENTITY");
MySqlParameter[] parameters =
{
new MySqlParameter("@parent_id", MySqlDbType.Int32, 4),
new MySqlParameter("@channel_id", MySqlDbType.Int32, 4),
new MySqlParameter("@nav_type", MySqlDbType.VarChar, 50),
new MySqlParameter("@name", MySqlDbType.VarChar, 50),
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@link_url", MySqlDbType.VarChar, 255),
new MySqlParameter("@sort_id", MySqlDbType.Int32, 4),
new MySqlParameter("@action_type", MySqlDbType.VarChar, 500),
new MySqlParameter("@is_lock", MySqlDbType.TinyText, 1),
new MySqlParameter("@is_sys", MySqlDbType.TinyText, 1)
};
parameters[0].Value = parent_id;
parameters[1].Value = channel_id;
parameters[2].Value = DTEnums.NavigationEnum.System.ToString();
parameters[3].Value = nav_name;
parameters[4].Value = title;
parameters[5].Value = link_url;
parameters[6].Value = sort_id;
parameters[7].Value = action_type;
parameters[8].Value = 0;
parameters[9].Value = 1;
object obj2 = DbHelperMySql.GetSingle(strSql.ToString(), parameters);
return(Convert.ToInt32(obj2));
}
/// <summary>
/// 返回数据数
/// </summary>
public int GetCount(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) as H from " + databaseprefix + "orders ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Convert.ToInt32(DbHelperMySql.GetSingle(strSql.ToString())));
}
/// <summary>
/// 返回数据总数
/// </summary>
public int GetCount(string channel_name, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) as H ");
strSql.Append(" from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(Convert.ToInt32(DbHelperMySql.GetSingle(strSql.ToString())));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + "user_point_log");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select *,(select count(0) from " + databaseprefix + "article_tags_relation where tag_id=" + databaseprefix + "article_tags.id) as count FROM " + databaseprefix + "article_tags");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.sites model)
{
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder(); //数据字段
StringBuilder str2 = new StringBuilder(); //数据参数
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <MySqlParameter> paras = new List <MySqlParameter>();
strSql.Append("insert into " + databaseprefix + "sites(");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null)
{
str1.Append(pi.Name + ","); //拼接字段
str2.Append("@" + pi.Name + ","); //声明参数
paras.Add(new MySqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值
}
}
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(") values (");
strSql.Append(str2.ToString().Trim(','));
strSql.Append(") ");
strSql.Append(";select @@IDENTITY;");
object obj = DbHelperMySql.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());//带事务
model.id = Convert.ToInt32(obj);
//添加站点导航菜单
new DAL.MySql.navigation(databaseprefix).Add(conn, trans, "sys_contents", "channel_" + model.build_path, model.title, "", model.sort_id, 0, "Show");
trans.Commit();//提交事务
}
catch
{
trans.Rollback();//回滚事务
return(0);
}
}
}
return(model.id);
}
/// <summary>
/// 返回站点对应的导航ID
/// </summary>
public int GetSiteNavId(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select N.id from " + databaseprefix + "navigation as N," + databaseprefix + "sites as S");
strSql.Append(" where 'channel_'+S.build_path=N.name and S.id=" + id);
object obj = DbHelperMySql.GetSingle(strSql.ToString());
if (obj != null)
{
return(Convert.ToInt32(obj));
}
return(0);
}
/// <summary>
/// 返回类别名称
/// </summary>
public string GetTitle(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 title from " + databaseprefix + "article_category");
strSql.Append(" where id=" + id);
string title = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(title))
{
return(string.Empty);
}
return(title);
}
/// <summary>
/// 获取总下载次数
/// </summary>
public int GetCountNum(int article_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select sum(down_num) from " + databaseprefix + "article_attach");
strSql.Append(" where article_id=" + article_id);
string str = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(str))
{
return(0);
}
return(Convert.ToInt32(str));
}
/// <summary>
/// 获取阅读次数
/// </summary>
public int GetClick(string channel_name, int article_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 click from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where id=" + article_id);
string str = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(str))
{
return(0);
}
return(Convert.ToInt32(str));
}
/// <summary>
/// 获取会员组折扣
/// </summary>
public int GetDiscount(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select discount from " + databaseprefix + "user_groups");
strSql.Append(" where id=" + id + " limit 1");
string str = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(str))
{
return(0);
}
return(Convert.ToInt32(str));
}
/// <summary>
/// 根据用户名取得Salt
/// </summary>
public string GetSalt(string user_name)
{
//尝试用户名取得Salt
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 salt from " + databaseprefix + "users");
strSql.Append(" where user_name=@user_name");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100)
};
parameters[0].Value = user_name;
string salt = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString(), parameters));
if (!string.IsNullOrEmpty(salt))
{
return(salt);
}
//尝试用手机号取得Salt
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("select top 1 salt from " + databaseprefix + "users");
strSql1.Append(" where mobile=@mobile");
MySqlParameter[] parameters1 =
{
new MySqlParameter("@mobile", MySqlDbType.VarChar, 20)
};
parameters1[0].Value = user_name;
salt = Convert.ToString(DbHelperMySql.GetSingle(strSql1.ToString(), parameters1));
if (!string.IsNullOrEmpty(salt))
{
return(salt);
}
//尝试用邮箱取得Salt
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("select top 1 salt from " + databaseprefix + "users");
strSql2.Append(" where email=@email");
MySqlParameter[] parameters2 =
{
new MySqlParameter("@email", MySqlDbType.VarChar, 50)
};
parameters2[0].Value = user_name;
salt = Convert.ToString(DbHelperMySql.GetSingle(strSql2.ToString(), parameters2));
if (!string.IsNullOrEmpty(salt))
{
return(salt);
}
return(string.Empty);
}
/// <summary>
/// 返回标题名称
/// </summary>
public string GetTitle(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select title from " + databaseprefix + "payment");
strSql.Append(" where id=" + id + " limit 1");
string title = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(title))
{
return("-");
}
return(title);
}
/// <summary>
/// 得到最大ID
/// </summary>
private int GetMaxId(MySqlConnection conn, MySqlTransaction trans)
{
string strSql = "select id from " + databaseprefix + "payment order by id desc limit 1";
object obj = DbHelperMySql.GetSingle(conn, trans, strSql);
if (obj == null)
{
return(0);
}
else
{
return(int.Parse(obj.ToString()));
}
}
/// <summary>
/// 返回信息封面图
/// </summary>
public string GetImgUrl(string channel_name, int article_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 img_url from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where id=" + article_id);
string imgsrc = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString()));
if (string.IsNullOrEmpty(imgsrc))
{
return(string.Empty);
}
return(imgsrc);
}
/// <summary>
/// 返回频道名称
/// </summary>
public string GetChannelName(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 name from " + databaseprefix + "site_channel");
strSql.Append(" where id=" + id);
object obj = DbHelperMySql.GetSingle(strSql.ToString());
if (obj != null)
{
return(Convert.ToString(obj));
}
return(string.Empty);
}
public int Count(string tableName, string whereClause)
{
tableName = tableName.Replace("'", "''");
if (!string.IsNullOrEmpty(whereClause) && !whereClause.TrimStart().ToLower().StartsWith("where "))
{
whereClause = " where " + whereClause;
}
string sqlClause = string.Format("SELECT Count(*) FROM {0} {1}", tableName, whereClause);
var result =
_providerConfig.DbProvider == DbProvider.MySql
? DbHelperMySql.GetSingle(_providerConfig.DbConnectionString, sqlClause)
: DbHelperSql.GetSingle(_providerConfig.DbConnectionString, sqlClause);
return(TypeParse.StrToInt(result));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM(");
strSql.Append("select S.*,A.title as ptitle,A.img_url,A.remark,A.api_path,A.is_lock");
strSql.Append(" from " + databaseprefix + "oauth_app as A INNER JOIN " + databaseprefix + "site_oauth as S ON A.id=S.oauth_id");
strSql.Append(") as temp_oauth");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 根据导航的名称查询其ID
/// </summary>
public int GetNavId(string nav_name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id from " + databaseprefix + "navigation");
strSql.Append(" where name=@nav_name");
MySqlParameter[] parameters =
{
new MySqlParameter("@nav_name", MySqlDbType.VarChar, 50)
};
parameters[0].Value = nav_name;
string str = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString(), parameters));
return(Utils.StrToInt(str, 0));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM(");
strSql.Append("select S.*,P.img_url,P.title as ptitle,P.remark,P.type,P.poundage_type,P.poundage_amount,P.redirect_url,P.return_url,P.notify_url,P.is_lock");
strSql.Append(" from " + databaseprefix + "payment as P INNER JOIN " + databaseprefix + "site_payment as S ON P.id=S.payment_id");
strSql.Append(") as temp_payment");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}