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 ChanceService : IChance
    {
        /// <summary>
        /// 添加機會
        /// </summary>
        /// <param name="chance"></param>
        /// <returns></returns>
        public int AddChance(Sal_chance chance)
        {
            string sql = "insert into Sal_chance values("+chance.Chance_id+",'"+chance.Chance_source+"','"+chance.Chance_customer_name+"','"+chance.Chance_title+"',"+chance.Chance_rate+",'"+chance.Chance_linkman+"','"+chance.Chance_tel+"','"+chance.Chance_desc+"',"+chance.Chance_create_id+",'"+chance.Chance_create_by+"','"+chance.Chance_create_date+"',"+chance.Chance_cue_id+",'"+chance.Chance_due_to+"','"+chance.Chance_due_date+"','"+1+"')";
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 修改機會
        /// </summary>
        /// <param name="chance"></param>
        /// <returns></returns>
        public int UpdateChance(Sal_chance chance)
        {
            string sql = "update Sal_chance set chance_source='" + chance.Chance_source + "',chance_customer_name='" + chance.Chance_customer_name + "',chance_title='" + chance.Chance_title + "',chance_rate=" + chance.Chance_rate + ",chance_linkman='" + chance.Chance_linkman + "',chance_tel='" + chance.Chance_tel + "',chance_desc='" + chance.Chance_desc + "',chance_create_id=" + chance.Chance_create_id + ",chance_create_by='" + chance.Chance_create_by + "',chance_create_date='" + chance.Chance_create_date + "',chance_cue_id=" + chance.Chance_cue_id + ",chance_due_to='" + chance.Chance_due_to + "',chance_due_date='" + chance.Chance_due_date + "' where chance_id=" + chance.Chance_id;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 修改開發狀態
        /// </summary>
        /// <param name="chance"></param>
        /// <returns></returns>
        public int UpdateStatus(Sal_chance chance)
        {
            string sql = "update Sal_chance set chance_status='"+chance.Chance_status+"' where chance_id=" + chance.Chance_id;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 修改指派人和時間
        /// </summary>
        /// <param name="chance"></param>
        /// <returns></returns>
        public int Update(Sal_chance chance)
        {
            string sql = "update Sal_chance set chance_cue_id=" + chance.Chance_cue_id + ",chance_due_to='" + chance.Chance_due_to + "',chance_due_date='" + chance.Chance_due_date + "',chance_status='"+chance.Chance_status+"' where chance_id=" + chance.Chance_id;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 刪除機會
        /// </summary>
        /// <param name="chance"></param>
        /// <returns></returns>
        public int DelChance(Sal_chance chance)
        {
            string sql = "delete from Sal_chance where chance_id=" + chance.Chance_id;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 查詢所有信息
        /// </summary>
        /// <returns>返回對象</returns>
        public List<Sal_chance> GetAllChance()
        {
            string sql = "select * from Sal_chance where chance_status='1'";
            return GetChanceBySql(sql);
        }
        
        /// <summary>
        /// 查詢所有信息除了未指派的
        /// </summary>
        /// <returns></returns>
        public List<Sal_chance> GetAllChances()
        {
            string sql = "select * from Sal_chance where chance_status<>'1'";
            return GetChanceBySql(sql);
        }
        /// <summary>
        /// 未指派條件查詢
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="departId"></param>
        /// <param name="branchId"></param>
        /// <returns></returns>
        public List<Sal_chance> GetChanceSelect(string name,string title,string ren)
        {
            string sqlname = "";
            if (name != "")
            {
                sqlname = " and chance_customer_name='" +name+ "' ";
            }
            string sqltitle = "";
            if (title !="")
            {
                sqltitle = " and chance_title='" + title + "' ";
            }
            string sqlren = "";
            if (ren != "")
            {
                sqlren = " and chance_linkman='"+ren+"' ";
            }

            string sql = "select * from sal_chance where chance_status='"+1+"'";
            sql += sqlname;
            sql += sqltitle;
            sql += sqlren;
            return GetChanceBySql(sql);
        }
        /// <summary>
        /// 已指派條件查詢
        /// </summary>
        /// <param name="name"></param>
        /// <param name="title"></param>
        /// <param name="ren"></param>
        /// <returns></returns>
        public List<Sal_chance> GetChanceSelects(string name, string title, string ren)
        {
            string sqlname = "";
            if (name != "")
            {
                sqlname = " and chance_customer_name='" + name + "' ";
            }
            string sqltitle = "";
            if (title != "")
            {
                sqltitle = " and chance_title='" + title + "' ";
            }
            string sqlren = "";
            if (ren != "")
            {
                sqlren = " and chance_linkman='" + ren + "' ";
            }

            string sql = "select * from sal_chance where chance_status<>'" + 1 + "'";
            sql += sqlname;
            sql += sqltitle;
            sql += sqlren;
            return GetChanceBySql(sql);
        }
        
        /// <summary>
        /// 根據機會ID查詢
        /// </summary>
        /// <param name="Chanceid"></param>
        /// <returns></returns>
        public Sal_chance GetChanceById(int Chanceid)
        {
            string sql = "select * from sal_chance where chance_id=" + Chanceid;
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                Sal_chance chance = new Sal_chance();

                chance.Chance_id = (int)reader["Chance_id"];
                chance.Chance_linkman = (string)reader["Chance_linkman"];
                chance.Chance_rate = (int)reader["Chance_rate"];
                chance.Chance_source = (string)reader["Chance_source"];
                chance.Chance_status = (string)reader["Chance_status"];
                chance.Chance_tel = (string)reader["Chance_tel"];
                chance.Chance_title = (string)reader["Chance_title"];
                chance.Chance_due_to = (string)reader["Chance_due_to"];
                chance.Chance_due_date = (DateTime)reader["Chance_due_date"];
                chance.Chance_desc = (string)reader["Chance_desc"];
                chance.Chance_customer_name = (string)reader["Chance_customer_name"];
                chance.Chance_cue_id = (int)reader["Chance_cue_id"];
                chance.Chance_create_id = (int)reader["Chance_create_id"];
                chance.Chance_create_date = (DateTime)reader["Chance_create_date"];
                chance.Chance_create_by = (string)reader["Chance_create_by"];
                reader.Close();
                return chance;
            }
            else
            {
                reader.Close();
                return null;
            }
        
        }
        /// <summary>
        /// 根據來源ID查詢信息
        /// </summary>
        /// <param name="departId">來源ID</param>
        /// <returns>返回對象</returns>
        public List<Sal_chance> GetChanceByChanceId(int ChanceId)
        {
            string sql = "select * from Sal_chance where ChanceId = " + ChanceId + " and chance_status=1";

            return GetChanceBySql(sql);
            
        }
        /// <summary>
        /// 依據sql語句查詢用戶
        /// </summary>
        /// <param name="safeSql">sql語句</param>
        /// <returns>返回集合</returns>
        public List<Sal_chance> GetChanceBySql(string safeSql)
        {
            List<Sal_chance> list = new List<Sal_chance>();

            using (DataTable table = DBHelper.GetDataSet(safeSql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Sal_chance chance = new Sal_chance();

                    chance.Chance_id = (int)row["Chance_id"];
                    chance.Chance_linkman = (string)row["Chance_linkman"];
                    chance.Chance_rate = (int)row["Chance_rate"];
                    chance.Chance_source = (string)row["Chance_source"];
                    chance.Chance_status = (string)row["Chance_status"];
                    chance.Chance_tel = (string)row["Chance_tel"];
                    chance.Chance_title = (string)row["Chance_title"];
                    chance.Chance_due_to = (string)row["Chance_due_to"];
                    chance.Chance_due_date = (DateTime)row["Chance_due_date"];
                    chance.Chance_desc = (string)row["Chance_desc"];
                    chance.Chance_customer_name = (string)row["Chance_customer_name"];
                    chance.Chance_cue_id = (int)row["Chance_cue_id"];
                    chance.Chance_create_id = (int)row["Chance_create_id"];
                    chance.Chance_create_date = (DateTime)row["Chance_create_date"];
                    chance.Chance_create_by = (string)row["Chance_create_by"];

                    list.Add(chance);
                }

                return list;
            }
        }
    }
}