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 CustomerService : ICustomer
    {
        /// <summary>
        /// 添加客戶
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public int AddCustomer(Cst_customer customer)
        {
            string sql = "insert into cst_customer values('"+customer.Customer_no+"','"+customer.Customer_name+"','"+customer.Customer_region+"',"+customer.Customer_manager_id+",'"+customer.Customer_manager_name+"',"+customer.Customer_level+",'"+customer.Customer_level_label+"',"+customer.Customer_satisfy+","+customer.Customer_credit+",'"+customer.Customer_addr+"','"+customer.Customer_zip+"','"+customer.Customer_tel+"','"+customer.Customer_fax+"','"+customer.Customer_website+"','"+customer.Customer_licence_no+"','"+customer.Customer_chieftain+"',"+customer.Customer_bankroll+","+customer.Customer_turnover+",'"+customer.Customer_bank+"','"+customer.Customer_bank_account+"','"+customer.Customer_local_tax_no+"','"+customer.Customer_national_tax_no+"','"+customer.Customer_status+"')";
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 修改客戶
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public int UpdateCustomer(Cst_customer customer)
        {
            string sql = "update cst_customer set Customer_name='" + customer.Customer_name + "',Customer_region='" + customer.Customer_region + "',Customer_manager_id=" + customer.Customer_manager_id + ",Customer_manager_name='" + customer.Customer_manager_name + "',Customer_level=" + customer.Customer_level + ",Customer_level_label='" + customer.Customer_level_label + "',Customer_satisfy=" + customer.Customer_satisfy + ",Customer_credit=" + customer.Customer_credit + ",Customer_addr='" + customer.Customer_addr + "',Customer_zip='" + customer.Customer_zip + "',Customer_tel='" + customer.Customer_tel + "',Customer_fax='" + customer.Customer_fax + "',Customer_website='" + customer.Customer_website + "',Customer_licence_no='" + customer.Customer_licence_no + "',Customer_chieftain='" + customer.Customer_chieftain + "',Customer_bankroll=" + customer.Customer_bankroll + ",Customer_turnover=" + customer.Customer_turnover + ",Customer_bank='" + customer.Customer_bank + "',Customer_bank_account='" + customer.Customer_bank_account + "',Customer_local_tax_no='" + customer.Customer_local_tax_no + "',Customer_national_tax_no='" + customer.Customer_national_tax_no + "',Customer_status='" + customer.Customer_status + "'where Customer_no='" + customer.Customer_no + "'";
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 刪除客戶
        /// </summary>
        /// <param name="customerid"></param>
        /// <returns></returns>
        public int DelCustomer(string customerid)
        {
            string sql = "update cst_customer set Customer_status='" + 3 + "' where Customer_no='" + customerid + "'";
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 條件查詢
        /// </summary>
        /// <param name="name"></param>
        /// <param name="title"></param>
        /// <param name="ren"></param>
        /// <returns></returns>
        public List<Cst_customer> GetCustomerSelect(string num, string name, string manager,string adr,string level)
        {
            string sql = "select * from Cst_customer  ";
            string tiaojian = "";
            if (num != "")
            {
                if (tiaojian == "")
                {
                    tiaojian = "where customer_no='" + num + "' ";
                }
                else
                {
                    tiaojian += "and customer_no='" + num + "' ";
                }
            }
            if (name != "")
                {
                    if (tiaojian == "")
                    {
                        tiaojian = " where customer_name='" + name + "' ";
                    }
                    else
                    {
                        tiaojian += " and customer_name='" + name + "' ";
                    }
                }
            if (manager != "")
                {
                    if (tiaojian == "")
                    {
                        tiaojian = " where customer_manager_name='" + manager + "' ";
                    }
                    else
                    {
                        tiaojian += " and customer_manager_name='" + manager + "' ";
                    }
                }
            if (adr != "")
                {
                    if (tiaojian == "")
                    {
                        tiaojian = " where customer_region='" + adr + "' ";
                    }
                    else
                    {
                        tiaojian += " and customer_region='" + adr + "' ";
                    }
                }
            if (level != "")
                {
                    if (tiaojian == "")
                    {
                        tiaojian = " where customer_level_label='" + level + "' ";
                    }
                    else
                    {
                        tiaojian += " and customer_level_label='" + level + "' ";
                    }
                }

            
            sql = sql+tiaojian;
            return GetCustomerBySql(sql);
        }
        /// <summary>
        /// 根據客戶ID查詢信息
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public Cst_customer GetCustomerByCustomerId(string customer)
        {
            string sql = "select * from cst_customer where Customer_no='" + customer+"'";
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                Cst_customer customers = new Cst_customer();

                customers.Customer_no = (string)reader["Customer_no"];
                customers.Customer_name = (string)reader["customer_name"];
                customers.Customer_region = (string)reader["customer_region"];
                customers.Customer_manager_id = (int)reader["customer_manager_id"];
                customers.Customer_manager_name = (string)reader["customer_manager_name"];
                customers.Customer_level = (int)reader["customer_level"];
                customers.Customer_level_label = (string)reader["customer_level_label"];
                customers.Customer_satisfy = (int)reader["customer_satisfy"];
                customers.Customer_credit = (int)reader["customer_credit"];
                customers.Customer_addr = (string)reader["customer_addr"];
                customers.Customer_zip = (string)reader["customer_zip"];
                customers.Customer_tel = (string)reader["customer_tel"];
                customers.Customer_fax = (string)reader["customer_fax"];
                customers.Customer_website = (string)reader["customer_website"];
                customers.Customer_licence_no = (string)reader["customer_licence_no"];
                customers.Customer_chieftain = (string)reader["customer_chieftain"];
                customers.Customer_bankroll = (int)reader["customer_bankroll"];
                customers.Customer_turnover = (int)reader["customer_turnover"];
                customers.Customer_bank = (string)reader["customer_bank"];
                customers.Customer_bank_account = (string)reader["customer_bank_account"];
                customers.Customer_local_tax_no = (string)reader["customer_local_tax_no"];
                customers.Customer_national_tax_no = (string)reader["customer_national_tax_no"];
                customers.Customer_status = (string)reader["customer_status"];
                reader.Close();
                return customers;
            }
            else
            {
                reader.Close();
                return null;
            }
        
        }
        /// <summary>
        /// 查詢所有客戶
        /// </summary>
        /// <returns></returns>
        public List<Cst_customer> GetAllCustomer()
        {
            string sql = "select * from cst_customer";
            return GetCustomerBySql(sql);
        }
        /// <summary>
        /// 依據sql語句查詢用戶
        /// </summary>
        /// <param name="safeSql">sql語句</param>
        /// <returns>返回集合</returns>
        public List<Cst_customer> GetCustomerBySql(string safeSql)
        {
            List<Cst_customer> list = new List<Cst_customer>();

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

                    customers.Customer_no = (string)row["Customer_no"];
                    customers.Customer_name = (string)row["customer_name"];
                    customers.Customer_region = (string)row["customer_region"];
                    customers.Customer_manager_id = (int)row["customer_manager_id"];
                    customers.Customer_manager_name = (string)row["customer_manager_name"];
                    customers.Customer_level = (int)row["customer_level"];
                    customers.Customer_level_label = (string)row["customer_level_label"];
                    customers.Customer_satisfy = (int)row["customer_satisfy"];
                    customers.Customer_credit = (int)row["customer_credit"];
                    customers.Customer_addr = (string)row["customer_addr"];
                    customers.Customer_zip = (string)row["customer_zip"];
                    customers.Customer_tel = (string)row["customer_tel"];
                    customers.Customer_fax = (string)row["customer_fax"];
                    customers.Customer_website = (string)row["customer_website"];
                    customers.Customer_licence_no = (string)row["customer_licence_no"];
                    customers.Customer_chieftain = (string)row["customer_chieftain"];
                    customers.Customer_bankroll = (int)row["customer_bankroll"];
                    customers.Customer_turnover = (int)row["customer_turnover"];
                    customers.Customer_bank = (string)row["customer_bank"];
                    customers.Customer_bank_account = (string)row["customer_bank_account"];
                    customers.Customer_local_tax_no = (string)row["customer_local_tax_no"];
                    customers.Customer_national_tax_no = (string)row["customer_national_tax_no"];
                    customers.Customer_status = (string)row["customer_status"];

                    list.Add(customers);
                }

                return list;
            }
        }
    }
}