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>
    /// 聯(lián)系人數(shù)據(jù)層
    /// </summary>
    public class LinkManService : ILinkMan
    {
        /// <summary>
        /// 添加聯(lián)系人
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public int AddLinkman(Cst_linkman linkman)
        {
            string sql = "insert into cst_linkman values('" + linkman.Customer.Customer_no.Trim() + "','" + linkman.Customer.Customer_name + "','" + linkman.Linkman_name + "','" + linkman.Linkman_sex + "','" + linkman.Linkman_postion + "','" + linkman.Linkman_tel + "','" + linkman.Linkman_mobile + "','" + linkman.Linkman_memo + "')";
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 修改聯(lián)系人
        /// </summary>
        /// <param name="linkman"></param>
        /// <returns></returns>
        public int UpdateLink(Cst_linkman linkman)
        {
            string sql = "update cst_linkman set linkman_name='" + linkman.Linkman_name + "',linkman_sex='" + linkman.Linkman_sex + "',linkman_postion='" + linkman.Linkman_postion + "',linkman_tel='" + linkman.Linkman_tel + "',linkman_mobile='" + linkman.Linkman_mobile + "',linkman_memo='" + linkman.Linkman_memo + "' where linkman_id="+linkman.Linkman_id;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 刪除聯(lián)系人
        /// </summary>
        /// <param name="linkmanid"></param>
        /// <returns></returns>
        public int DelLinkman(int linkmanid)
        {
            string sql = "delete from cst_linkman where linkman_id=" +linkmanid;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 查詢聯(lián)系人
        /// </summary>
        /// <param name="CustormerId"></param>
        /// <returns></returns>
        public List<Cst_linkman> GetLinkmanByCustormerId(string CustormerId)
        {
            string sql = "select * from cst_linkman where linkman_customer_no='" + CustormerId + "'";
            return GetLinkmanBySql(sql);
        }
        /// <summary>
        /// 根據(jù)ＩＤ查詢聯(lián)系人
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public Cst_linkman GetLinmanByLinkmanId(int linkmanid)
        {
            string sql = "select * from cst_linkman where linkman_id=" +linkmanid;
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                Cst_linkman link = new Cst_linkman();

                link.Linkman_id = (int)reader["Linkman_id"];

                link.Linkman_customer_name = (string)reader["Linkman_customer_name"];
                link.Linkman_name = (string)reader["linkman_name"];
                link.Linkman_sex = (string)reader["linkman_sex"];
                link.Linkman_postion = (string)reader["linkman_postion"];
                link.Linkman_tel = (string)reader["linkman_tel"];
                link.Linkman_mobile = (string)reader["linkman_mobile"];
                link.Linkman_memo = (string)reader["linkman_memo"];
                string cst = (string)reader["linkman_customer_no"];
                reader.Close();
                CustomerService cs = new CustomerService();
                link.Customer = cs.GetCustomerByCustomerId(cst);
                return link;
            }
            else
            {
                reader.Close();
                return null;
            }

        }
        /// <summary>
        /// 查詢聯(lián)系人
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public Cst_linkman GetLinmanByCustomerId(string customer)
        {
            string sql = "select * from cst_linkman where linkman_customer_no='" + customer + "'";
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                Cst_linkman link = new Cst_linkman();

                link.Linkman_id = (int)reader["Linkman_id"];

                link.Linkman_customer_name = (string)reader["Linkman_customer_name"];
                link.Linkman_name = (string)reader["linkman_name"];
                link.Linkman_sex = (string)reader["linkman_sex"];
                link.Linkman_postion = (string)reader["linkman_postion"];
                link.Linkman_tel = (string)reader["linkman_tel"];
                link.Linkman_mobile = (string)reader["linkman_mobile"];
                link.Linkman_memo = (string)reader["linkman_memo"];
                string cst = (string)reader["linkman_customer_no"];
                reader.Close();
                CustomerService cs = new CustomerService();
                link.Customer = cs.GetCustomerByCustomerId(cst);
                return link;
            }
            else
            {
                reader.Close();
                return null;
            }

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

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

                    link.Linkman_id = (int)row["Linkman_id"];
                    CustomerService cs = new CustomerService();
                    link.Customer = cs.GetCustomerByCustomerId((string)row["linkman_customer_no"]);
                    link.Linkman_customer_name = (string)row["Linkman_customer_name"];
                    link.Linkman_name = (string)row["linkman_name"];
                    link.Linkman_sex = (string)row["linkman_sex"];
                    link.Linkman_postion = (string)row["linkman_postion"];
                    link.Linkman_tel = (string)row["linkman_tel"];
                    link.Linkman_mobile = (string)row["linkman_mobile"];
                    link.Linkman_memo = (string)row["linkman_memo"];
                        
                    list.Add(link);
                }

                return list;
            }
        }
    }
}