/// <summary>
/// 获取post预览列表
/// </summary>
/// <param name="customSQL">自定义SQL</param>
/// <param name="treatedTags">经处理过的标签</param>
/// <param name="site">图站</param>
/// <param name="column">按此列排序</param>
/// <param name="ratingArray">分级</param>
/// <returns></returns>
public static DataTable SelectPostPreviewList(string customSQL, string treatedTags, Site site, PostOrderBy column, Rating[] ratingArray)
{
string selectColumns = string.Format("file_md5,{0}", column.ToString());
string ratingIN = string.Join(",", ratingArray.Select(s => string.Format("'{0}'", s.ToString().Substring(0, 1).ToLower())));
string tagsMATCH = string.IsNullOrWhiteSpace(treatedTags) ? string.Empty : string.Format(" AND id IN (SELECT docid FROM posts_fts4 WHERE tags MATCH '{0}')", treatedTags);
if (string.IsNullOrWhiteSpace(customSQL) == false)
{
customSQL = string.Format(" AND ({0})", string.Join(" AND ", customSQL.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)));
}
string commandText = string.Format("SELECT {0} FROM posts WHERE rating IN ({1}){2}{3}", selectColumns, ratingIN, tagsMATCH, customSQL);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText);
try
{
DataTable dt = SQLiteHelper.ExecuteDataTable(command);
return(dt);
}
catch
{
return(null);
}
}
/// <summary>
/// 获取偏好MD5列表
/// </summary>
/// <param name="mode">模式</param>
/// <param name="preferenceArray">需获取的偏好</param>
/// <returns></returns>
public static DataTable SelectPreferMD5(Mode mode, params Prefer[] selectPreferArray)
{
string commandText = string.Format("SELECT file_md5 FROM {0} WHERE prefer IN ({1})", mode.ToString(), string.Join(",", selectPreferArray.Select(s => s.ToString("d"))));
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.prefer, commandText);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 更新工程信息,成功返回true，不成功返回false
/// </summary>
/// <param name="pro_id"></param>
/// <returns></returns>
public bool UpdateInfo(int pro_Id)
{
string sqlUpdate = "update tb_projinfo set pro_name = '" + ProjectInfo.Pro_Name +
"',pro_type='" + ProjectInfo.Pro_Type +
"',con_province='" + ProjectInfo.Con_Province +
"',con_city='" + ProjectInfo.Con_City +
"',unit='" + ProjectInfo.Unit +
"',con_unit='" + ProjectInfo.Con_Unit +
"',sup_unit='" + ProjectInfo.Sup_Unit +
"',con_area='" + ProjectInfo.Con_Area +
"',con_height=" + ProjectInfo.Con_Height +
",des_unit='" + ProjectInfo.Des_Unit +
"' where pro_id = " + pro_Id;
SQLiteConnection conn = _connBase.connectToDatabase();
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sqlUpdate);
int n = SQLiteHelper.ExecuteNonQuery(cmd);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 函数直接从BLL中的实体类中获取数据并保存，成功返回工程ID值，失败返回-1
/// </summary>
/// <returns></returns>
public int AddInfo()
{
//拼凑Sql语句的方法无法存null串，只能存入空字符串
string sqlInsertInfo = "insert into tb_projinfo(pro_name,pro_type,con_province,con_city,unit,con_unit,sup_unit,con_area,con_height,des_unit)"
+ " values('" + ProjectInfo.Pro_Name + "','" + ProjectInfo.Pro_Type + "','" + ProjectInfo.Con_Province + "','" + ProjectInfo.Con_City + "','" + ProjectInfo.Unit + "','" + ProjectInfo.Sup_Unit + "','" + ProjectInfo.Sup_Unit + "','" + ProjectInfo.Con_Area + "','" + ProjectInfo.Con_Height + "','" + ProjectInfo.Des_Unit + "')";
int isIn = -1;
SQLiteConnection conn = null;
try
{
conn = _connBase.connectToDatabase();
//执行插入语句，同时Sqlite自动生成一个ID值
isIn = SQLiteHelper.ExecuteNonQuery(conn, sqlInsertInfo);
}
catch { }
//插入成功则找到ID值
if (isIn == 1)
{
//查询最大的工程ID
string sqlFindLastId = "select max(pro_id) from tb_projinfo";
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sqlFindLastId);
DataTable dt = SQLiteHelper.ExecuteDataset(cmd).Tables[0];
return(int.Parse(dt.Rows[dt.Rows.Count - 1][0].ToString()));
}
else
{
return(-1);//不成功返回负1
}
}
/// <summary>
/// 获取MD5的偏好
/// </summary>
/// <param name="mode">模式</param>
/// <param name="file_md5"></param>
/// <returns></returns>
public static DataTable SelectMD5Prefer(Mode mode, string file_md5)
{
string commandText = string.Format("SELECT prefer FROM {0} WHERE file_md5 = @file_md5", mode.ToString());
SQLiteParameter param = new SQLiteParameter("@file_md5", file_md5);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.prefer, commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取Pool中posts预览列表
/// </summary>
/// <param name="pool_id">pool的id</param>
/// <param name="site">图站</param>
/// <returns></returns>
public static DataTable SelectPoolPostsPreviewList(int pool_id, Site site)
{
string commandText = string.Format("SELECT file_md5,sequence FROM pool_posts WHERE pool_id=@pool_id");
SQLiteParameter param = new SQLiteParameter("@pool_id", pool_id);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取Pool详情
/// </summary>
/// <param name="id"></param>
/// <param name="site">图站</param>
/// <returns></returns>
public static DataTable SelectPoolDetail(int id, Site site)
{
string commandText = "SELECT * FROM pools WHERE id=@id";
SQLiteParameter param = new SQLiteParameter("@id", id);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取post详情
/// </summary>
/// <param name="file_md5"></param>
/// <param name="site"></param>
/// <returns></returns>
public static DataTable SelectPostDetail(string file_md5, Site site)
{
string commandText = "SELECT * FROM posts WHERE file_md5 = @file_md5";
SQLiteParameter param = new SQLiteParameter("@file_md5", file_md5);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取预览图
/// </summary>
/// <param name="md5Group">按首位分组的md5</param>
/// <returns></returns>
public static DataTable SelectPreviewImage(IGrouping <string, string> md5Group)
{
string md5IN = string.Join(",", md5Group.Select(s => string.Format("'{0}'", s)));
string commandText = string.Format("SELECT file_md5,preview FROM _{0} WHERE file_md5 IN ({1})", md5Group.Key, md5IN);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.preview, commandText);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 为图片详情查询某图片的标签
/// </summary>
/// <param name="multiCRC32">多个CRC32标签</param>
/// <returns></returns>
public static DataTable SelectTagsForDetail(string multiCRC32, Site site)
{
string tagCRC32IN = string.Join(",", multiCRC32.Split(' ').Select(s => string.Format("'{0}'", s)));
string commandText = string.Format("SELECT name,type,post_count FROM tags WHERE crc32 IN ({0}) ORDER BY type,name", tagCRC32IN);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 获取Pool预览列表
/// </summary>
/// <param name="inputName">用户输入的名称</param>
/// <param name="site">图站</param>
/// <param name="column">按此列排序</param>
/// <param name="ratingArray">分级</param>
/// <returns></returns>
public static DataTable SelectPoolPreviewList(string inputName, Site site, PoolOrderBy column, Rating[] ratingArray)
{
string selectColumns = string.Format("id,name,file_md5,{0}", column.ToString());
string ratingIN = string.Join(",", ratingArray.Select(s => string.Format("'{0}'", s.ToString().Substring(0, 1).ToLower())));
string commandText = string.Format("SELECT {0} FROM pools WHERE name LIKE ('%'||@inputName||'%') AND rating IN ({1})", selectColumns, ratingIN);
SQLiteParameter param = new SQLiteParameter("@inputName", inputName);
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, param);
return(SQLiteHelper.ExecuteDataTable(command));
}
/// <summary>
/// 为推荐查询匹配图片标签
/// </summary>
/// <param name="incompleteTag">不完整的标签</param>
/// <param name="limit">返回数量</param>
/// <returns></returns>
public static DataTable SelectTagsForRecommend(string incompleteTag, Site site, int limit = 5)
{
//搜索 （标签*） 和 （*_标签*）
string commandText = @"SELECT name,type,post_count FROM tags WHERE name LIKE (@incompleteTag||'%') OR name LIKE ('%\_'||@incompleteTag||'%') ESCAPE '\' ORDER BY post_count DESC LIMIT @limit";
SQLiteParameter[] paramArray =
{
new SQLiteParameter("@incompleteTag", incompleteTag),
new SQLiteParameter("@limit", limit),
};
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.GetConn(site), commandText, paramArray);
return(SQLiteHelper.ExecuteDataTable(command));
}
public bool DeleteLog(string sto_path)
{
string sqlDeleteLog = "delete from tb_projlog where sto_path ='" + sto_path + "'";
SQLiteConnection conn = con.connectToDatabase();
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sqlDeleteLog);
int m = SQLiteHelper.ExecuteNonQuery(cmd);
if (m == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdateLog(int pro_Id, string pro_Name, string sto_Path, string rec_Time)
{
string sqlUpdateLog = "update tb_projlog set pro_name='" + pro_Name + "',sto_path='" + sto_Path + "',rec_time='" + rec_Time + "'"
+ "where pro_id =" + pro_Id;
SQLiteConnection conn = con.connectToDatabase();
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sqlUpdateLog);
int n = SQLiteHelper.ExecuteNonQuery(cmd);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
public bool UpdatePara(int pro_Id)
{
// ScaffoldPara.Con_Layers =66;
//ScaffoldPara.Sca_Type = "yyy";
string sqlUpdate = "update tb_scaffoldPara set sca_type= '" + ScaffoldPara.Sca_Type +
"',con_layers=" + ScaffoldPara.Con_Layers +
",act_layers=" + ScaffoldPara.Act_Layers +
",soil_types='" + ScaffoldPara.Soil_Types +
"',rough_level='" + ScaffoldPara.Rough_Level +
"',cha_value=" + ScaffoldPara.Cha_Value +
",pad_area=" + ScaffoldPara.Pad_Area +
",anchor_style='" + ScaffoldPara.Anchor_Style +
"',anchor_type='" + ScaffoldPara.Anchor_Type +
"',anchor_model='" + ScaffoldPara.Anchor_Model +
"',anchor_connect='" + ScaffoldPara.Anchor_Connect +
"',sca_situation='" + ScaffoldPara.Sca_Situation +
"',bui_status='" + ScaffoldPara.Bui_Status +
"',bui_distance ='" + ScaffoldPara.Bui_Distance +
"',per_brace=" + ScaffoldPara.Per_Brace +
",per_level=" + ScaffoldPara.Per_Level +
",per_set=" + ScaffoldPara.Per_Set +
",la=" + ScaffoldPara.La +
",lb=" + ScaffoldPara.Lb +
",h=" + ScaffoldPara.H +
",fast_num=" + ScaffoldPara.Fast_Num +
",fitting_model='" + ScaffoldPara.Fitting_Model +
"',step_num=" + ScaffoldPara.Step_Num +
" where pro_id =" + pro_Id;
SQLiteConnection conn = con.connectToDatabase();
SQLiteCommand cmd = SQLiteHelper.CreateCommand(conn, sqlUpdate);
int n = SQLiteHelper.ExecuteNonQuery(cmd);
if (n == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新偏好信息
/// </summary>
/// <param name="mode">模式</param>
/// <param name="file_md5">图片MD5</param>
/// <param name="prefer"></param>
/// <returns></returns>
public static int UpdatePrefer(Mode mode, string file_md5, Prefer prefer)
{
string commandText = string.Empty;
List <SQLiteParameter> paramList = new List <SQLiteParameter>();
if (prefer == Prefer.Normal)
{
commandText = string.Format("DELETE FROM {0} WHERE file_md5 = @file_md5", mode.ToString());
paramList.Add(new SQLiteParameter("@file_md5", file_md5));
}
else
{
commandText = string.Format("DELETE FROM {0} WHERE file_md5 = @file_md5;INSERT INTO {0} VALUES (@file_md5,@prefer)", mode.ToString());
paramList.Add(new SQLiteParameter("@file_md5", file_md5));
paramList.Add(new SQLiteParameter("@prefer", prefer.ToString("d")));
}
SQLiteCommand command = SQLiteHelper.CreateCommand(DBConn.prefer, commandText, paramList.ToArray());
return(SQLiteHelper.ExecuteNonQuery(command));
}