using System;
using System.Collections.Generic;
using System.Text;
using MyCRM.IDAL;
using MyCRM.Models;
using System.Data;
using System.Data.SqlClient;
using MyCRM.DBUtility;

namespace MyCRM.DAL
{
    /// <summary>
    /// 計劃數據層
    /// </summary>
   public class PlanService:IPlan
    {
       /// <summary>
       /// 根據機會ID查詢計劃
       /// </summary>
       /// <param name="chinceid"></param>
       /// <returns></returns>
       public List<Sal_plan>GetPlanByChanceid(int chinceid)
       {
           string sql = "select * from Sal_plan where plan_chance_id="+chinceid;
           return GetPlanBySql(sql);
       }
       /// <summary>
       /// 修改計劃項
       /// </summary>
       /// <param name="todo"></param>
       /// <param name="planid"></param>
       /// <returns></returns>
       public int UpdatePlan(string todo,int planid)
       {
           string sql = "update Sal_plan set plan_todo='"+todo+"' where plan_id="+planid;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 修改執行效果
       /// </summary>
       /// <param name="planresult"></param>
       /// <param name="planid"></param>
       /// <returns></returns>
       public int UpdatePlanResult(string planresult, int planid)
       {
           string sql = "update Sal_plan set plan_result='" + planresult + "' where plan_id=" + planid;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 刪除計劃項
       /// </summary>
       /// <param name="planid"></param>
       /// <returns></returns>
       public int DelPlan(int planid)
       {
           string sql = "delete from sal_plan where plan_id=" + planid;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 添加計劃
       /// </summary>
       /// <param name="plan"></param>
       /// <returns></returns>
       public int AddPlan(Sal_plan plan)
       {
           string sql = "insert into Sal_plan values("+plan.Chance.Chance_id+",'"+plan.Plan_date+"','"+plan.Plan_todo+"','"+plan.Plan_result+"')";
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
        /// <summary>
        /// 依據sql語句查詢用戶
        /// </summary>
        /// <param name="safeSql">sql語句</param>
        /// <returns>返回集合</returns>
        public List<Sal_plan> GetPlanBySql(string safeSql)
        {
            List<Sal_plan> list = new List<Sal_plan>();
            using (DataTable table = DBHelper.GetDataSet(safeSql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Sal_plan plan = new Sal_plan();
                    ChanceService cs = new ChanceService();
                    plan.Chance = cs.GetChanceById((int)row["plan_chance_id"]);
                    plan.Plan_id = (int)row["plan_id"];
                    plan.Plan_date = (DateTime)row["plan_date"];
                    plan.Plan_todo=(string)row["plan_todo"];
                    plan.Plan_result = (string)row["plan_result"];
                    list.Add(plan);
                }
                return list;
            }
        }
    }
}