/// <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 = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
/// <param name="pageSize">分页数量</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="strWhere">条件</param>
/// <param name="filedOrder">排序</param>
/// <param name="recordCount">返回数据总数</param>
/// <returns>DataTable</returns>
public DataSet ArticleList(string channel_name, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
if ("" != strWhere.Trim())
{
strSql.Append(" where " + strWhere);
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 获得查询分页数据
/// </summary>
public List <Model.user_point_log> 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 = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(GetModelList(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
// <summary>
/// 获得规格数据列表
/// </summary>
public DataSet GetList(int Top, int ruleId, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,account_id,rule_id,title,content,link_url,img_url,media_id,media_url,meida_hd_url,sort_id,add_time");
strSql.Append(" FROM " + databaseprefix + "weixin_request_content where rule_id=" + ruleId);
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
strSql.Append(" order by sort_id asc,id desc");
return(ReadDataBase.QueryFillDataSet(strSql.ToString()));
}
/// <summary>
/// 返回网点ID
/// </summary>
public int GetSiteId(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select site_id from " + databaseprefix + "site_channel where id=@0 ");
object obj = ReadDataBase.ExecuteScalar <object>(strSql.ToString(), id);
if (null != obj)
{
return(Convert.ToInt32(obj));
}
return(0);
}
/// <summary>
/// 返回站点名称
/// </summary>
public string GetTitle(string build_path)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 title from " + databaseprefix + "sites");
strSql.Append(" where build_path=@0");
string title = ReadDataBase.ExecuteScalar <string>(strSql.ToString(), build_path);
if (!string.IsNullOrEmpty(title))
{
return(title);
}
return(string.Empty);
}
/// <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 = ReadDataBase.ExecuteScalar <object>(strSql.ToString());
if (obj != null)
{
return(Convert.ToInt32(obj));
}
return(0);
}
/// <summary>
/// 将对象转换为实体
/// </summary>
public Model.orders DataRowToModel(DataRow row)
{
Model.orders model = new Model.orders();
if (row != null)
{
#region 主表信息======================
//利用反射获得属性的所有公共属性
Type modelType = model.GetType();
for (int i = 0; i < row.Table.Columns.Count; i++)
{
//查找实体是否存在列表相同的公共属性
PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
if (proInfo != null && row[i] != DBNull.Value)
{
proInfo.SetValue(model, row[i], null);//用索引值设置属性值
}
}
#endregion
#region 子表信息======================
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("select * from " + databaseprefix + "order_goods");
strSql1.Append(" where order_id=" + model.id);
DataTable dt1 = ReadDataBase.QueryFillDataSet(strSql1.ToString()).Tables[0];
if (dt1.Rows.Count > 0)
{
int rowsCount = dt1.Rows.Count;
List <Model.order_goods> models = new List <Model.order_goods>();
Model.order_goods modelt;
for (int n = 0; n < rowsCount; n++)
{
modelt = new Model.order_goods();
Type modeltType = modelt.GetType();
for (int i = 0; i < dt1.Rows[n].Table.Columns.Count; i++)
{
PropertyInfo proInfo = modeltType.GetProperty(dt1.Rows[n].Table.Columns[i].ColumnName);
if (proInfo != null && dt1.Rows[n][i] != DBNull.Value)
{
proInfo.SetValue(modelt, dt1.Rows[n][i], null);
}
}
models.Add(modelt);
}
model.order_goods = models;
}
#endregion
}
return(model);
}
/// <summary>
/// 根据订单号获取支付方式ID
/// </summary>
public int GetPaymentId(string order_no)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 payment_id from " + databaseprefix + "orders");
strSql.Append(" where order_no=@0");
object obj = ReadDataBase.ExecuteScalar <int>(strSql.ToString(), order_no);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
return(0);
}
/// <summary>
/// 获得Tags查询分页数据(搜索用到)
/// </summary>
public DataSet ArticleSearch(string channel_name, string tags, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select id,channel_id,call_index,title,zhaiyao as remark,tags,add_time,click,img_url,user_name,category_id,update_time from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where status=0 and datediff(d,add_time,getdate())>=0 and id in(select article_id from " + databaseprefix + "article_tags_relation ");
strSql.Append(" where tag_id=(select id from " + databaseprefix + "article_tags where title='" + tags + "'))");
if (!string.IsNullOrEmpty(channel_name))
{
strSql.Append(" and channel_id=(select id from " + databaseprefix + "site_channel where [name]='" + channel_name + "')");
}
recordCount = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 返回信息内容
/// </summary>
public string GetContent(string channel_name, int article_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 content from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name);
strSql.Append(" where id=" + article_id);
string content = ReadDataBase.ExecuteScalar <string>(strSql.ToString());
if (string.IsNullOrEmpty(content))
{
return(string.Empty);
}
return(content);
}
/// <summary>
/// 返回规则的第一条标题
/// </summary>
public string GetTitle(int rule_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 title from " + databaseprefix + "weixin_request_content");
strSql.Append(" where rule_id=@0");
string title = ReadDataBase.ExecuteScalar <string>(strSql.ToString());
if (!string.IsNullOrEmpty(title))
{
return(title);
}
return(string.Empty);
}
/// <summary>
/// 获取单个附件下载次数
/// </summary>
public int GetDownNum(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 down_num from " + databaseprefix + "article_attach");
strSql.Append(" where id=" + id);
string str = ReadDataBase.ExecuteScalar <string>(strSql.ToString());
if (string.IsNullOrEmpty(str))
{
return(0);
}
return(Convert.ToInt32(str));
}
/// <summary>
/// 返回标题名称
/// </summary>
public string GetTitle(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 title from " + databaseprefix + "site_payment");
strSql.Append(" where id=" + id);
string title = ReadDataBase.ExecuteScalar <string>(strSql.ToString());
if (!string.IsNullOrEmpty(title))
{
return(title);
}
return(string.Empty);
}
/// <summary>
/// 返回规则的第一条内容
/// </summary>
public string GetContent(int rule_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 content from " + databaseprefix + "weixin_request_content");
strSql.Append(" where rule_id=@0");
object obj = ReadDataBase.ExecuteScalar <string>(strSql.ToString(), rule_id);
if (obj != null)
{
return(obj.ToString());
}
return(string.Empty);
}
/// <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 = ReadDataBase.ExecuteScalar <object>(strSql.ToString());
if (obj != null)
{
return(Convert.ToString(obj));
}
return(string.Empty);
}
/// <summary>
/// 获得频道对应的数据
/// </summary>
public DataSet GetList(int channel_id, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select A.* ");
strSql.Append(" FROM " + databaseprefix + "article_attribute_field as A INNER JOIN " + databaseprefix + "site_channel_field as S ON A.id=S.field_id");
strSql.Append(" where S.channel_id=" + channel_id);
if (strWhere.Trim() != "")
{
strSql.Append(" and " + strWhere);
}
strSql.Append(" order by A.is_sys desc,A.sort_id asc,A.id desc");
return(ReadDataBase.QueryFillDataSet(strSql.ToString()));
}
/// <summary>
/// 获取父类下的所有子类ID(含自己本身)
/// </summary>
public string GetIds(string nav_name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id from " + databaseprefix + "navigation");
strSql.Append(" where name=@0");
object obj = ReadDataBase.ExecuteScalar <string>(strSql.ToString(), nav_name);
if (obj != null)
{
return(GetIds(Convert.ToInt32(obj)));
}
return(string.Empty);
}
/// <summary>
/// 根据用户名取得Salt
/// </summary>
public string GetSalt(string user_name)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 salt from " + databaseprefix + "manager");
strSql.Append(" where user_name=@0");
string salt = ReadDataBase.ExecuteScalar <string>(strSql.ToString(), user_name);
if (string.IsNullOrEmpty(salt))
{
return(string.Empty);
}
return(salt);
}
/// <summary>
/// 获取总下载次数
/// </summary>
public int GetCountNum(int channel_id, int article_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select sum(down_num) from " + databaseprefix + "article_attach");
strSql.Append(" where channel_id=" + channel_id + " and article_id=" + article_id);
string str = ReadDataBase.ExecuteScalar <string>(strSql.ToString());
if (string.IsNullOrEmpty(str))
{
return(0);
}
return(Convert.ToInt32(str));
}
/// <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 = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 是否存在记录
/// </summary>
/// <param name="sqlwhere">条件</param>
/// <returns>Users</returns>
public Model.users GetModel(string sqlwhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 * from " + databaseprefix + "users ");
if (string.IsNullOrEmpty(sqlwhere))
{
strSql.Append(" where 1=1");
}
else
{
strSql.Append(" where " + sqlwhere + "");
}
return(ReadDataBase.Query <Model.users>(strSql.ToString()).FirstOrDefault());
}
/// <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 = ReadDataBase.ExecuteScalar <int>(PagingHelper.CreateCountingSql(strSql.ToString()));
return(ReadDataBase.QueryFillDataSet(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder)));
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Model.article_attribute_field model = GetModel(id);//取得扩展字段实体
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//删除所关联的频道数据表相关列
DataTable dt = new DAL.site_channel(databaseprefix).GetFieldList(conn, trans, id).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//检查有无该频道数据表和列
int rowsCount = ReadDataBase.ExecuteScalar <int>(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)
{
//删除频道数据表一列
WriteDataBase.Execute(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=@0");
WriteDataBase.Execute(conn, trans, strSql1.ToString(), id);
//删除扩展字段主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article_attribute_field");
strSql.Append(" where id=@0");
WriteDataBase.Execute(conn, trans, strSql.ToString(), id);
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
private async void Button_Click(object sender, RoutedEventArgs e)
{
var write = new DataBase();
var powiadomienieSMS = new PowiadomienieSm();
List <int> listValidationErrors = new List <int>();
listValidationErrors.Add(Validation.GetErrors(TextNumerTelefonuDN).Count);
listValidationErrors.Add(Validation.GetErrors(TextMiastoDN).Count);
listValidationErrors.Add(Validation.GetErrors(TextStanDN).Count);
var collections = listValidationErrors.Where(x => x == 0).ToList();
if ((collections.Count == listValidationErrors.Count) && (ListBoxMiejsc.SelectedItem.ToString().Any() == true))
{
var ViewModel = (RegistrationViewModel)DataContext;
powiadomienieSMS.NumerTelefonu = ViewModel.NumerTelefonuDN;
powiadomienieSMS.Miasto = ViewModel.MistoDN;
powiadomienieSMS.StanZagrozenia = ViewModel.StanZagrożeniaDN;
var readDataBase = new ReadDataBase();
bool resoultResarhNumber = await readDataBase.BaseGetPhoneKey(powiadomienieSMS.NumerTelefonu);
powiadomienieSMS.Miejscowosc = ListBoxMiejsc.SelectedItem.ToString();
bool resoultResarhLocality = await readDataBase.BaseGetMeasureResreahef(powiadomienieSMS.Miejscowosc);
if (resoultResarhNumber == true)
{
StringBuilder builder = new StringBuilder();
builder.AppendLine($"W bazie znajduję się już numer: {powiadomienieSMS.NumerTelefonu}");
MessageBox.Show(builder.ToString());
}
else if (resoultResarhLocality)
{
await write.WriteDataBaseNumerTelefonu(powiadomienieSMS);
MessageBox.Show("Dodano numer do bazy ;-)");
}
}
else
{
MessageBox.Show("Wypełnij wszystkie pola oraz zaznacz miejscowość!");
}
}
/// <summary>
/// 获取该表的所有实体对象
/// </summary>
/// <returns></returns>
public List <T> GetAll()
{
try
{
return(ReadDataBase.Query <T>(Sql.Builder.Select("*").From(TableName)).ToList <T>());
}
catch (Exception ex)
{
log.Info("GetAll:" + ex);
}
finally
{
ReadDataBase.CloseSharedConnection();
}
return(null);
}
/// <summary>
/// 根据ID获取记录
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T Get(object id)
{
try
{
return(ReadDataBase.SingleOrDefault <T>(Sql.Builder.Select("*").From(TableName).Where(PrimaryKey + "=@0", id.ToString().Trim().Trim('\''))));
}
catch (Exception ex)
{
log.Info("Get:" + ex);
}
finally
{
ReadDataBase.CloseSharedConnection();
}
return(null);
}
/// <summary>
/// 分页Fetch机制
/// </summary>
/// <returns></returns>
public List <T> GetFetchPage(int pageIndex, int pageSize)
{
try
{
return(ReadDataBase.Fetch <T>(pageIndex, pageSize, Sql.Builder.Select("*").From(TableName)));
}
catch (Exception ex)
{
log.Info("GetFetchPage(pageIndex,pageSize)" + ex);
}
finally
{
ReadDataBase.CloseSharedConnection();
}
return(null);
}
/// <summary>
/// 根据频道名称获取总记录数
/// </summary>
public int ArticleCount(string channel_name, int category_id, string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) 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);
}
return(ReadDataBase.ExecuteScalar <int>(strSql.ToString()));
}
/// <summary>
/// 类别统计数量
/// </summary>
/// <param name="channel_id">频道ID</param>
/// <param name="category_id">类别ID</param>
/// <returns></returns>
public int GetCount(string channel_name, int category_id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channel_name + " s join " + databaseprefix + "article_category a on a.id =s.category_id where a.class_list like '%," + category_id + ",%' and s.status=0");
object obj = ReadDataBase.ExecuteScalar <object>(strSql.ToString());
if (null != obj)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
