using System;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Drawing;
namespace DissData_cs{
	public class DissSql:Control,INamingContainer{
	string ConnectString;//连接字符串
		public DissSql(string ServerName,string UID,string Password,string DBName){
			ConnectString="server=" + ServerName + ";uid=" + UID + ";pwd=" + Password + ";database=" + DBName;
		}
		public DataTable GetDataTable(int cate,string StartDate,string EndDate){
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm = new SqlDataAdapter("select * from Article where (cate = " + cate + ") and (nowtime between CONVERT(DATETIME, '" + StartDate + " 00:00:00', 102) AND CONVERT(DATETIME, '" + EndDate + " 23:59:59', 102)) and (reply = 0) Order by ID desc",Source);
			Comm.Fill(DS,"Article");
			Comm.Fill(DS,"Article1");
			DS.Tables["Article1"].Rows.Clear();
			foreach(DataRow Row in DS.Tables["Article"].Rows){
				SqlDataAdapter Comm1 = new SqlDataAdapter("select * from Article where reply =" + Row["id"] + " Order by ID desc",Source);
				for(int p=0;p <= DS.Tables.Count-1;p++){
					if(DS.Tables[p].TableName=="SubArticle"){
						DS.Tables[p].Rows.Clear();
						break;
					}
				}
				Comm1.Fill(DS,"SubArticle");
				DataRow po = DS.Tables["Article1"].NewRow();
				for(int i = 0;i <= DS.Tables["Article"].Columns.Count-1;i++){
					po[i]=Row[i].ToString();
				}
				DS.Tables["Article1"].Rows.Add(po);
				if(DS.Tables["SubArticle"].Rows.Count > 0){
					foreach(DataRow ko in DS.Tables["SubArticle"].Rows){
						DataRow po1 = DS.Tables["Article1"].NewRow();
						for(int u=0;u<=DS.Tables["Article"].Columns.Count-1;u++){
							if(DS.Tables["Article"].Columns[u].ColumnName=="subject"){
								string LK = "　" + ko[u].ToString();
								po1[u]=LK;
							}
							else{
								po1[u]=ko[u].ToString();
							}
						}
						DS.Tables["Article1"].Rows.Add(po1);
					}
				}
			}
			return DS.Tables["Article1"];
		}
		public DataTable GetAllCate(){
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm = new SqlDataAdapter("select * from cate Order by ID",Source);
			Comm.Fill(DS,"category");
			return DS.Tables["category"];
		}
		public Boolean SaveToArticle(int NowID,int Reply,string Subj,string Cname,string Body ,int cate,string uid){
		try{
			Body=Body.Replace("\r","<br>");
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm = new SqlDataAdapter("select * from Article",Source);
			Comm.Fill(DS,"Article");
			DataRow Row;
			Row=DS.Tables["Article"].NewRow();
			Row["id"]=NowID;
			Row["reply"]=Reply;
			Row["cate"]=cate;
			Row["subject"]=Subj;
			Row["send"]=Cname;
			Row["body"]=Body;
			Row["UserID"]=uid;
			Row["nowtime"]=DateTime.Now;
			DS.Tables["Article"].Rows.Add(Row);
			SqlCommandBuilder custCB = new SqlCommandBuilder(Comm);
			Comm.Update(DS,"Article");
			SqlDataAdapter Comm1 = new SqlDataAdapter("select * from Cate where id=" + cate,Source);
			Comm1.Fill(DS,"Cate");
			DS.Tables["Cate"].Rows[0]["nowtime"]=DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
			int v=Convert.ToInt32(DS.Tables["Cate"].Rows[0]["total"].ToString());
			DS.Tables["Cate"].Rows[0]["total"]=v+1;
			SqlCommandBuilder custCB1 = new SqlCommandBuilder(Comm1);
			Comm1.Update(DS,"Cate");
			return true;
		}
		catch{
			return false;
		}
		}
		public DataRow GetArticleRow(int CID){
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm = new SqlDataAdapter("select * from Article where id =" + CID,Source);
			Comm.Fill(DS,"ArticleRow");
			return DS.Tables["ArticleRow"].Rows[0];
		}
		public int GetReplyID(string Replyed){
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm = new SqlDataAdapter("select * from Article where id =" + Convert.ToInt32(Replyed),Source);
			Comm.Fill(DS,"FindReply");
			string k="";
			if(DS.Tables["FindReply"].Rows[0]["reply"].ToString()=="0"){
				k=DS.Tables["FindReply"].Rows[0]["id"].ToString();
			}
			else{
				k = DS.Tables["FindReply"].Rows[0]["reply"].ToString();
			}
			return Convert.ToInt32(k);
		}
		public string ValiUser(string IuputID,string Pass){
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm = new SqlDataAdapter("select * from Disuser where id Like '" + IuputID + "'",Source);
			Comm.Fill(DS,"Users");
			if(DS.Tables["Users"].Rows.Count > 0){
				if(DS.Tables["Users"].Rows[0]["pass"].ToString()==Pass){
					return DS.Tables["Users"].Rows[0]["name"].ToString();
				}
				else{
					return "";
				}
			}
			else{
				return "";
			}
		}
		public DataTable FullSearch(string FindWhat,string FindCate){
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm =null;
			if(FindCate=="全文"){
				Comm = new SqlDataAdapter("select * from Article where body Like '%" + FindWhat + "%'",Source);
			}
			else if(FindCate=="主旨"){
				Comm = new SqlDataAdapter("select * from Article where subject Like '%" + FindWhat + "%'",Source);
			}
			Comm.Fill(DS,"SearchRes");
			return DS.Tables["SearchRes"];
		}
		public int GetMaxID(){
			DataSet DS = new DataSet();
			SqlConnection Source = new SqlConnection(ConnectString);
			SqlDataAdapter Comm = new SqlDataAdapter("SELECT Max(Article.id) AS idMax FROM Article",Source);
			Comm.Fill(DS,"MaxID");
			string k= DS.Tables["MaxID"].Rows[0]["idMax"].ToString();
			return Convert.ToInt32(k) + 1;
		}
	}

}