/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.fmd090 GetModel(int ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" ID,SX,XX,GC,JSQF,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" from fmd090 ");
strSql.Append(" where ID=" + ID + "");
Model.fmd090 model = new Model.fmd090();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.ffd050 GetModel(string CKZLBH)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" CKZLBH,CKRQ,CLBH,PM,PH,KHBH,ZF,DJ,ZSSLHJ,ZSXS,ZSDDS,ZSXDS,CKZLXDZ,CKDDZ,CKWCS,WCQF,BZ,DMQF,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM,CKZLRQ ");
strSql.Append(" from ffd050 ");
strSql.Append(" where CKZLBH='" + CKZLBH + "' ");
Model.ffd050 model = new Model.ffd050();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/*
*/
#endregion Method
#region MethodEx
/// <summary>
/// 获得绑定CboBox的出库指令数据
/// </summary>
/// <returns></returns>
public DataTable getAllCKZLBH_CBOX(string CKZLRQ, string WCQF)
{
DataTable dtResult = new DataTable();
StringBuilder strSQL = new StringBuilder();
strSQL.Append("select CKZLBH ZSMC, CKZLBH MCKEY from FFD050 ");
strSQL.Append("where 1=1 ");
if (!string.IsNullOrEmpty(CKZLRQ))
{
strSQL.Append(" and CKZLRQ='" + CKZLRQ + "' ");
}
if (!string.IsNullOrEmpty(WCQF))
{
strSQL.Append(" and WCQF='" + WCQF + "' ");
}
strSQL.Append("order by WCQF desc ; ");
dtResult = DbHelperMySql.Query(strSQL.ToString()).Tables[0];
return(dtResult);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.fmd070 GetModel(string CLBH, string PHBBH)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" CLBH,PHBBH,ZDRQ,ZT,BL,SYBH,PZRBH,BZ,PHBN,ZF,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM,XS ");
strSql.Append(" from fmd070 ");
strSql.Append(" where CLBH='" + CLBH + "' and PHBBH='" + PHBBH + "' ");
Model.fmd070 model = new Model.fmd070();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.fmd100 GetModel(string KHBH, string PMGC, string PHGC)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" KHBH,PMGC,PHGC,PMKH,PHKH,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" from fmd100 ");
strSql.Append(" where KHBH='" + KHBH + "' and PMGC='" + PMGC + "' and PHGC='" + PHGC + "' ");
Model.fmd100 model = new Model.fmd100();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.message GetModel(string MSGCD)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" MSGCD,MSG,RLZBH,RLR,RLSJ,RLDMM ");
strSql.Append(" from message ");
strSql.Append(" where MSGCD='" + MSGCD + "' ");
Model.message model = new Model.message();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.ffd010 GetModel(long ID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" ID,RKQF,LHRQ,HLPCLOTNO,JPRQ,CLBH,PM,PH,HGSL,BLSL,XDSLBZ,YRKID,RKZF,YLHR,LHBGZNO,LHBGRQ,DMQF,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" from ffd010 ");
strSql.Append(" where ID=" + ID + "");
Model.ffd010 model = new Model.ffd010();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.fmd000 GetModel(string GLBH, string MCKEY)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" GLBH,MCKEY,ZSMC,BZ,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" from fmd000 ");
strSql.Append(" where GLBH='" + GLBH + "' and MCKEY='" + MCKEY + "' ");
Model.fmd000 model = new Model.fmd000();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
public DataTable GetPc25Js02(string strCkrq, string strkhbh, string strClbh, string strPm, string strPh)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select HLPCLOTNO from ffd010 where ID in( ");
strSql.Append(" select distinct ID from ffd020 where ckzlbh in ");
strSql.Append(" ( ");
strSql.Append(" select a.CKZLBH CKZLBH from ffd050 a ");
strSql.Append(" where a.WCQF = '1' ");
strSql.Append(" and a.CKRQ = '" + strCkrq + "' ");
strSql.Append(" and a.KHBH = '" + strkhbh + "' ");
strSql.Append(" and a.CLBH = '" + strClbh + "' ");
strSql.Append(" and a.PM = '" + strPm + "' ");
strSql.Append(" and a.PH = '" + strPh + "' ");
strSql.Append(" group by a.CLBH,a.PM,a.PH,a.CKZLBH ");
strSql.Append(" ) ");
strSql.Append(" ) ");
return(DbHelperMySql.Query(strSql.ToString()).Tables[0]);
}
public DataSet GetDaTaForWinSubKey_Value(string TB_NAME, string strKEY, string strVALUE)
{
StringBuilder StrSQL = new StringBuilder();
StrSQL.Append(" SELECT KHBH as strKEY,KHMC as strVALUE from " + TB_NAME + " a ");
StrSQL.Append(" WHERE 1=1 ");
if (!string.IsNullOrEmpty(strVALUE))
{//社员名称
StrSQL.Append(" AND a.KHMC LIKE '" + strVALUE + "%" + "' ");
}
if (!string.IsNullOrEmpty(strKEY))
{//社员编号
StrSQL.Append(" AND a.KHBH = '" + strKEY.ToString().Trim() + "' ");
}
StrSQL.Append("ORDER BY A.KHBH");
return(DbHelperMySql.Query(StrSQL.ToString()));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.fmd010 GetModel(string SYBH)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" SYBH,SYMC,BMBH,ZWBH,XB,SR,ZZ,SFZ,SJH,EMAIL,ZT,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" from fmd010 ");
strSql.Append(" where SYBH='" + SYBH + "' ");
Model.fmd010 model = new Model.fmd010();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 根据用户名取得Salt
/// </summary>
public string GetSalt(string user_name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select salt from " + databaseprefix + "manager");
strSql.Append(" where user_name=@user_name");
strSql.Append(" limit 1");
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("");
}
return(salt);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.fmd040 GetModel(string YCLBHBS)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" YCLBHBS,YCLBHGHS,YCLLX,ZT,YXTS,JJ,MS,ZXKC,ZDKC,JLDW,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" from fmd040 ");
strSql.Append(" where YCLBHBS='" + YCLBHBS + "' ");
Model.fmd040 model = new Model.fmd040();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 执行插件SQL语句
/// </summary>
public bool ExeSqlStr(string dirPath, string xPath)
{
bool result = true;
List <string> ls = ReadChildNodesValue(dirPath + DTKeys.FILE_PLUGIN_XML_CONFING, xPath);
if (ls != null)
{
ArrayList al = new ArrayList();
foreach (string str in ls)
{
al.Add(str);
}
if (!DbHelperMySql.ExecuteSqlTran(al))
{
result = false;
}
}
return(result);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.fmd080 GetModel(string PHBBH, string YCLBH)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append(" PHBBH,YCLBH,JLDW,ZL,GC,XSS,BZ,XS,RLZBH,RLR,RLSJ,RLDMM,GXZBH,GXR,GXSJ,GXDMM ");
strSql.Append(" from fmd080 ");
strSql.Append(" where PHBBH='" + PHBBH + "' and YCLBH='" + YCLBH + "' ");
Model.fmd080 model = new Model.fmd080();
DataSet ds = DbHelperMySql.Query(strSql.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 返回站点名称
/// </summary>
public string GetTitle(string build_path)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select title from " + databaseprefix + "channel_site");
strSql.Append(" where build_path=@build_path");
strSql.Append(" limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@build_path", MySqlDbType.VarChar, 100)
};
parameters[0].Value = build_path;
string title = Convert.ToString(DbHelperMySql.GetSingle(strSql.ToString(), parameters));
if (string.IsNullOrEmpty(title))
{
return("");
}
return(title);
}
/// <summary>
/// 增加一条数据，带事务
/// </summary>
public bool Add(MySqlConnection conn, MySqlTransaction trans, Model.user_group_price model, int channel_id, int article_id)
{
StringBuilder strSql = new StringBuilder(); //SQL字符串
StringBuilder str1 = new StringBuilder(); //数据库字段
StringBuilder str2 = new StringBuilder(); //声明参数
PropertyInfo[] pros = model.GetType().GetProperties();
List <MySqlParameter> paras = new List <MySqlParameter>();
strSql.Append("insert into " + databaseprefix + "user_group_price(");
foreach (PropertyInfo pi in pros)
{
if (!pi.Name.Equals("id"))
{
if (pi.GetValue(model, null) != null)
{
str1.Append(pi.Name + ",");
str2.Append("@" + pi.Name + ",");
switch (pi.Name)
{
case "channel_id":
paras.Add(new MySqlParameter("@" + pi.Name, channel_id));
break;
case "article_id":
paras.Add(new MySqlParameter("@" + pi.Name, article_id));
break;
default:
paras.Add(new MySqlParameter("@" + pi.Name, pi.GetValue(model, null)));
break;
}
}
}
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(") values (");
strSql.Append(str2.ToString().Trim(','));
strSql.Append(")");
return(DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray()) > 0);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.sms_template model)
{
int newId;
using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + databaseprefix + "sms_template(");
strSql.Append("title,call_index,content,is_sys)");
strSql.Append(" values (");
strSql.Append("@title,@call_index,@content,@is_sys)");
MySqlParameter[] parameters =
{
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@call_index", MySqlDbType.VarChar, 50),
new MySqlParameter("@content", MySqlDbType.LongText),
new MySqlParameter("@is_sys", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.title;
parameters[1].Value = model.call_index;
parameters[2].Value = model.content;
parameters[3].Value = model.is_sys;
DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
/// <summary>
/// 根据频道名称及规格查询分页数据
/// </summary>
public DataSet ArticleList(string channel_name, int category_id, Dictionary <string, string> dicSpecIds, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
string specWhere = string.Empty;
foreach (KeyValuePair <string, string> kv in dicSpecIds)
{
if (Utils.StrToInt(kv.Value, 0) > 0)
{
if (!string.IsNullOrEmpty(specWhere))
{
specWhere += "and ";
}
specWhere += "B.spec_ids like '%," + kv.Value + ",%'";
}
}
if (!string.IsNullOrEmpty(specWhere))
{
specWhere = " and (" + specWhere + ")";
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select * FROM " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where datediff(d,add_time,getdate())>=0");
if (category_id > 0)
{
strSql.Append(" and category_id in(select id from " + databaseprefix + "article_category where class_list like '%," + category_id + ",%')");
}
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
if (!string.IsNullOrEmpty(specWhere))
{
strSql.Append(" and id in(");
strSql.Append("select A.id from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name + " as A," + databaseprefix + "article_goods as B");
strSql.Append(" where A.channel_id=B.channel_id and A.id=B.article_id " + specWhere);
strSql.Append(" group by A.id)");
}
recordCount = Convert.ToInt32(DbHelperMySql.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
return(DbHelperMySql.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.user_oauth model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "user_oauth set ");
strSql.Append("user_id=@user_id,");
strSql.Append("user_name=@user_name,");
strSql.Append("oauth_name=@oauth_name,");
strSql.Append("oauth_access_token=@oauth_access_token,");
strSql.Append("oauth_openid=@oauth_openid,");
strSql.Append("add_time=@add_time");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_id", MySqlDbType.Int32, 4),
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@oauth_name", MySqlDbType.VarChar, 50),
new MySqlParameter("@oauth_access_token", MySqlDbType.VarChar, 500),
new MySqlParameter("@oauth_openid", MySqlDbType.VarChar, 255),
new MySqlParameter("@add_time", MySqlDbType.Date),
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = model.user_id;
parameters[1].Value = model.user_name;
parameters[2].Value = model.oauth_name;
parameters[3].Value = model.oauth_access_token;
parameters[4].Value = model.oauth_openid;
parameters[5].Value = model.add_time;
parameters[6].Value = model.id;
int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article_attribute_field model)
{
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 + "article_attribute_field(");
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(strSql.ToString(), paras.ToArray());
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Hashtable sqllist = new Hashtable();
//List<CommandInfo> sqllist = new List<CommandInfo>();
//删除站点OAtuh应用
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "site_oauth");
strSql1.Append(" where oauth_id=@oauth_id");
MySqlParameter[] parameters1 =
{
new MySqlParameter("@oauth_id", MySqlDbType.Int32, 4)
};
parameters1[0].Value = id;
//CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1);
sqllist.Add(strSql1.ToString(), parameters1);
//删除主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "oauth_app ");
strSql.Append(" where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = id;
//cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(strSql.ToString(), parameters);
bool result = DbHelperMySql.ExecuteSqlTran(sqllist);
if (result)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 取得所有类别列表
/// </summary>
/// <param name="parent_id">父ID</param>
/// <param name="channel_id">频道ID</param>
/// <returns></returns>
public DataTable GetList(int parent_id, int channel_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,channel_id,title,call_index,parent_id,class_list,class_layer,sort_id,link_url,img_url,content,seo_title,seo_keywords,seo_description");
strSql.Append(" from " + databaseprefix + "article_category");
strSql.Append(" where channel_id=" + channel_id + " order by sort_id asc,id desc");
DataSet ds = DbHelperMySql.Query(strSql.ToString());
DataTable oldData = ds.Tables[0] as DataTable;
if (oldData == null)
{
return(null);
}
//复制结构
DataTable newData = oldData.Clone();
//调用迭代组合成DAGATABLE
GetChilds(oldData, newData, parent_id, channel_id);
return(newData);
}
/// <summary>
/// 获取扩展字段对称值
/// </summary>
public Dictionary <string, string> GetFields(int channel_id, int article_id, string strWhere)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
DataTable dt = GetList(channel_id, strWhere).Tables[0];
if (dt.Rows.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
sb.Append(dr["name"].ToString() + ",");
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select " + Utils.DelLastComma(sb.ToString()) + " from " + databaseprefix + "article_attribute_value ");
strSql.Append(" where article_id=@article_id ");
strSql.Append(" limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@article_id", MySqlDbType.Int32, 4)
};
parameters[0].Value = article_id;
DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (ds.Tables[0].Rows[0][dr["name"].ToString()] != null)
{
dic.Add(dr["name"].ToString(), ds.Tables[0].Rows[0][dr["name"].ToString()].ToString());
}
else
{
dic.Add(dr["name"].ToString(), "");
}
}
}
}
return(dic);
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Hashtable sqllist = new Hashtable();
//删除订单商品
//List<CommandInfo> sqllist = new List<CommandInfo>();
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("delete from " + databaseprefix + "order_goods where order_id=@order_id");
MySqlParameter[] parameters2 =
{
new MySqlParameter("@order_id", MySqlDbType.Int32, 4)
};
parameters2[0].Value = id;
//CommandInfo cmd = new CommandInfo(strSql2.ToString(), parameters2);
sqllist.Add(strSql2.ToString(), parameters2);
//删除订单主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "orders where id=@id");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = id;
//cmd = new CommandInfo(strSql.ToString(), parameters);
sqllist.Add(strSql.ToString(), parameters);
//return DbHelperMySql.ExecuteSqlTran(sqllist) > 0;
bool result = DbHelperMySql.ExecuteSqlTran(sqllist);
if (result)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 权限查找对应权限(表头)
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetProject_SelectQX(string DMQF)
{
StringBuilder strSql = new StringBuilder();
if (DMQF.Equals("0") == true)
{
strSql.Append("select a.M_ID,a.M_NAME,b.P_NAME,b.P_PorName from MenuPro a ");
strSql.Append("left join Project b on a.M_ID = b.M_P_ID ");
strSql.Append("where a.DMQF ='" + DMQF + "' and IFNULL(P_NAME,'') <>''");
strSql.Append("order by b.M_P_ID,b.P_order ");
}
if (DMQF.Equals("1") == true)
{
strSql.Append(" select a.M_ID,a.M_NAME P_NAME from MenuPro a ");
strSql.Append("where a.DMQF ='" + DMQF + "' and IFNULL(M_NAME,'') <>'' ");
}
return(DbHelperMySql.Query(strSql.ToString()).Tables[0]);
}
/// <summary>
/// 获取支付平台实体
/// </summary>
public Model.payment GetPaymentModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 P.* FROM " + databaseprefix + "payment as P INNER JOIN " + databaseprefix + "site_payment as S ON P.id=S.payment_id");
strSql.Append(" and S.id=@id ");
MySqlParameter[] parameters =
{
new MySqlParameter("@id", MySqlDbType.Int32, 4)
};
parameters[0].Value = id;
DataTable dt = DbHelperMySql.Query(strSql.ToString(), parameters).Tables[0];
if (dt.Rows.Count > 0)
{
return(new DAL.MySql.payment(databaseprefix).DataRowToModel(dt.Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 修改子节点的ID列表及深度（自身迭代）
/// </summary>
/// <param name="parent_id"></param>
private void UpdateChilds(MySqlConnection conn, MySqlTransaction trans, int parent_id)
{
//查找父节点信息
Model.article_category model = GetModel(conn, trans, parent_id);
if (model != null)
{
//查找子节点
string strSql = "select id from " + databaseprefix + "article_category where parent_id=" + parent_id;
DataSet ds = DbHelperMySql.Query(conn, trans, strSql); //带事务
foreach (DataRow dr in ds.Tables[0].Rows)
{
//修改子节点的ID列表及深度
int id = int.Parse(dr["id"].ToString());
string class_list = model.class_list + id + ",";
int class_layer = model.class_layer + 1;
DbHelperMySql.ExecuteSql(conn, trans, "update " + databaseprefix + "article_category set class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + id); //带事务
//调用自身迭代
this.UpdateChilds(conn, trans, id); //带事务
}
}
}
private void fillSPD()
{
try
{
//"" = cboGlMc.SelectedValue.ToString();
//_modelFMD030.KHBH = txtDZ.Text.Trim();
this.fspdMc.ActiveSheet.Rows.Count = 0;
string strWhere = " and SCQF='0'";
List <string> listFields = new List <string>();
listFields.Add(" ID,GYSMC,GYSSLMC,DZ,DH,LXR");
DataTable dtTable = DbHelperMySql.Query(DBHelper.getAllList(TableName, listFields, strWhere)).Tables[0];
if (dtTable.Rows.Count > 0)
{
for (int i = 0; i < dtTable.Rows.Count; i++)
{
this.fspdMc.ActiveSheet.Rows.Count++;
//往spread里填充数据
this.fspdMc.ActiveSheet.SetValue(i, 0, dtTable.Rows[i]["GYSMC"].ToString());
this.fspdMc.ActiveSheet.SetValue(i, 1, dtTable.Rows[i]["GYSSLMC"].ToString());
this.fspdMc.ActiveSheet.SetValue(i, 2, dtTable.Rows[i]["DZ"].ToString());
this.fspdMc.ActiveSheet.SetValue(i, 3, dtTable.Rows[i]["DH"].ToString());
this.fspdMc.ActiveSheet.SetValue(i, 4, dtTable.Rows[i]["LXR"].ToString());
this.fspdMc.ActiveSheet.SetValue(i, 5, dtTable.Rows[i]["ID"].ToString());
}
ComSpread.SpdSetFocus(fspdMc, 0, 0);
fspdMc.ActiveSheet.Rows[0].BackColor = Color.Lavender;
}
else
{
return;
}
}
catch (Exception ex)
{
ComForm.InsertErrLog(ex.Message, this.Text);
}
}
/// <summary>
/// 根据用户名得到一个对象实体
/// </summary>
/// <param name="datepart">日期格式,d(天)hh(小时)n(分钟)s秒</param>
public Model.user_code GetModel(string user_name, string code_type, string datepart)
{
switch (datepart)
{
case "d": datepart = "DAY"; break;
case "hh": datepart = "HOUR"; break;
case "n": datepart = "MINUTE"; break;
case "s": datepart = "SECOND"; break;
default: datepart = "DAY"; break;
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,user_id,user_name,type,str_code,count,status,user_ip,eff_time,add_time");
strSql.Append(" from " + databaseprefix + "user_code ");
strSql.Append(" where status=0 and TIMESTAMPDIFF('" + datepart + "',eff_time,now())<=0 and user_name=@user_name and type=@type");
strSql.Append(" limit 1");
MySqlParameter[] parameters =
{
new MySqlParameter("@user_name", MySqlDbType.VarChar, 100),
new MySqlParameter("@type", MySqlDbType.VarChar, 20)
};
parameters[0].Value = user_name;
parameters[1].Value = code_type;
DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
