lostservice.cs

客户关系管理系统 客户关系管理系统

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 LostService:ILost
    {
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="lost"></param>
        /// <returns></returns>
        public int Update(Cst_lost lost)
        {
            string sql = "update cst_lost set lost_lost_date='" + lost.Lost_lost_date + "',lost_delay='" + lost.Lost_delay + "',lost_reason='" + lost.Lost_reason + "',lost_status='" + lost.Lost_status + "'where lost_id=" + lost.Lost_id;
            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_lost> GetLostSelect(string name,string manager,string level)
        {
            string sql = "select * from cst_lost  ";
            string tiaojian = "";
            if (name != "")
            {
                if (tiaojian == "")
                {
                    tiaojian = "where lost_customer_name='" + name + "' ";
                }
                else
                {
                    tiaojian += "and lost_customer_name='" + name + "' ";
                }
            }
            if (manager != "")
            {
                if (tiaojian == "")
                {
                    tiaojian = " where lost_customer_manager_name='" + manager + "' ";
                }
                else
                {
                    tiaojian += " and lost_customer_manager_name='" + manager + "' ";
                }
            }
            if (level != "")
            {
                if (tiaojian == "")
                {
                    tiaojian = " where lost_status='" + level + "' ";
                }
                else
                {
                    tiaojian += " and lost_status='" + level + "' ";
                }
            }
            sql = sql + tiaojian;

            List<Cst_lost> list = new List<Cst_lost>();
            using (DataTable table = DBHelper.GetDataSet(sql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Cst_lost lost = new Cst_lost();
                    lost.Lost_id = (int)row["lost_id"];
                    CustomerService ccs = new CustomerService();
                    lost.Customer = ccs.GetCustomerByCustomerId((string)row["lost_customer_no"]);
                    lost.Lost_customer_manager_id = (int)row["lost_customer_manager_id"];
                    lost.Lost_customer_manager_name = (string)row["lost_customer_manager_name"];
                    lost.Lost_last_order_date = (DateTime)row["lost_last_order_date"];
                    lost.Lost_lost_date = (DateTime)row["lost_lost_date"];
                    lost.Lost_delay = (string)row["lost_delay"];
                    lost.Lost_reason = (string)row["lost_reason"];
                    lost.Lost_status = (string)row["lost_status"];
                    list.Add(lost);
                }

                return list;
            }
        }
        /// <summary>
        /// 根据流失ID查询信息
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public Cst_lost GetLostByLostId(int lostid)
        {
            string sql = "select * from cst_lost where lost_id=" + lostid;
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                Cst_lost lost = new Cst_lost();

                lost.Lost_id = (int)reader["lost_id"];
                string name = (string)reader["lost_customer_no"];
                lost.Lost_customer_manager_id = (int)reader["lost_customer_manager_id"];
                lost.Lost_customer_manager_name = (string)reader["lost_customer_manager_name"];
                lost.Lost_last_order_date = (DateTime)reader["lost_last_order_date"];
                lost.Lost_lost_date = (DateTime)reader["lost_lost_date"];
                lost.Lost_delay = (string)reader["lost_delay"];
                lost.Lost_reason = (string)reader["lost_reason"];
                lost.Lost_status = (string)reader["lost_status"];
                reader.Close();
                CustomerService ccs = new CustomerService();
                lost.Customer = ccs.GetCustomerByCustomerId(name);
                return lost;
            }
            else
            {
                reader.Close();
                return null;
            }

        }
        /// <summary>
        ///查询所有流失客户
        /// </summary>
        /// <returns></returns>
        public List<Cst_lost> GetAllLost()
        {
            string sql="select * from cst_lost";
            List<Cst_lost> list = new List<Cst_lost>();

            using (DataTable table = DBHelper.GetDataSet(sql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Cst_lost lost = new Cst_lost();

                    lost.Lost_id=(int)row["lost_id"];
                    CustomerService ccs=new CustomerService();
                    lost.Customer=ccs.GetCustomerByCustomerId((string)row["lost_customer_no"]);
                    lost.Lost_customer_manager_id=(int)row["lost_customer_manager_id"];
                    lost.Lost_customer_manager_name=(string)row["lost_customer_manager_name"];
                    lost.Lost_last_order_date=(DateTime)row["lost_last_order_date"];
                    lost.Lost_lost_date=(DateTime)row["lost_lost_date"];
                    lost.Lost_delay=(string)row["lost_delay"];
                    lost.Lost_reason=(string)row["lost_reason"];
                    lost.Lost_status=(string)row["lost_status"];
                    list.Add(lost);
                }

                return list;
            }
        }
        /// <summary>
        /// 查询流失客户部分信息
        /// </summary>
        /// <returns></returns>
        public List<Cst_lost> GetLost()
        {
            string sql = "select lost_lost_date,lost_customer_name,lost_customer_manager_name,lost_reason from cst_lost where lost_status='3'";
            List<Cst_lost> list = new List<Cst_lost>();

            using (DataTable table = DBHelper.GetDataSet(sql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Cst_lost lost = new Cst_lost();
                    lost.Lost_customer_name = (string)row["lost_customer_name"];                   
                    lost.Lost_customer_manager_name = (string)row["lost_customer_manager_name"];                   
                    lost.Lost_lost_date = (DateTime)row["lost_lost_date"];                   
                    lost.Lost_reason = (string)row["lost_reason"];                
                    list.Add(lost);
                }

                return list;
            }
        }
        /// <summary>
        /// 条件查询
        /// </summary>
        /// <param name="name"></param>
        /// <param name="title"></param>
        /// <returns></returns>
        public List<Cst_lost> GetLosSelect(string name, string title)
        {
            string sqlname = "";
            if (name != "")
            {
                sqlname = " and lost_customer_name='" + name + "' ";
            }
            string sqltitle = "";
            if (title != "")
            {
                sqltitle = " and lost_customer_manager_name='" + title + "' ";
            }


            string sql = "select lost_lost_date,lost_customer_name,lost_customer_manager_name,lost_reason from cst_lost where lost_status='3'";
            sql += sqlname;
            sql += sqltitle;
            List<Cst_lost> list = new List<Cst_lost>();

            using (DataTable table = DBHelper.GetDataSet(sql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Cst_lost lost = new Cst_lost();


                    lost.Lost_customer_name = (string)row["lost_customer_name"];

                    lost.Lost_customer_manager_name = (string)row["lost_customer_manager_name"];

                    lost.Lost_lost_date = (DateTime)row["lost_lost_date"];

                    lost.Lost_reason = (string)row["lost_reason"];

                    list.Add(lost);
                }

                return list;
            }
        }
    }
}