using System;
using System.Data;
using CFC;

namespace WorkLog.Classes
{
	/// <summary>
	/// CAdvice 的摘要說明。
	/// </summary>
	public class CAdvice
	{
		public CAdvice()
		{
			//
			// TODO: 在此處添加構造函數邏輯
			//
		}

		public static DataTable GetByUser(string UserNO)
		{
			CDO.Connect();
			DataTable dt = CDO.GetDataTable("select * from vAdvice where UserNO=" + CSql.Express(UserNO) + " order by AdviceDate desc");
			CDO.Disconnect();
			return dt;
		}

		public static DataTable GetByFilter(int DeptID, DateTime FromAdviceDateID, DateTime ToAdviceDateID)
		{
			CDO.Connect();
			String sql = "select * from vAdvice where ";
			sql += CSql.DateGreaterEqual("AdviceDate", FromAdviceDateID);
			sql += "and" + CSql.DateLessEqual("AdviceDate", ToAdviceDateID);
			if (DeptID != -1)
				sql += "and" + CSql.Equal("DeptID", "int", DeptID);
			DataTable dt = CDO.GetDataTable(sql);
			CDO.Disconnect();
			return dt;
		}

		private static string InsertSql =
			" insert into tAdvice(UserNO, DeptID, AdviceDate, Content) " +
			" values({0}, {1}, {2}, {3}) ";

		public static void Insert(string UserNO, int DeptID, DateTime AdviceDate, string Content)
		{
			CDO.Connect();
			CDO.Execute(String.Format(InsertSql, 
				CSql.Express(UserNO),
				CSql.Express(DeptID),
				CSql.Express(AdviceDate),
				CSql.Express(Content)));
			CDO.Disconnect();
		}

		private static string UpdateSql =
			" update tAdvice set UserNO={1}, DeptID={2}, AdviceDate={3}, Content={4} " +
			" where AdviceID={0}";

		public static void Edit(int AdviceID, string UserNO, int DeptID, DateTime AdviceDate, string Content)
		{
			CDO.Connect();
			CDO.Execute(String.Format(UpdateSql, 
				CSql.Express(AdviceID),
				CSql.Express(UserNO),
				CSql.Express(DeptID),
				CSql.Express(AdviceDate),
				CSql.Express(Content)));
			CDO.Disconnect();
		}

		private static string DeleteSql =
			" delete from tAdvice" +
			" where AdviceID={0}";

		public static void Delete(int AdviceID)
		{
			CDO.Connect();
			CDO.Execute(String.Format(DeleteSql, 
				CSql.Express(AdviceID)));
			CDO.Disconnect();
		}
	}
}