activityservice.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 ActivityService: IActivity
    {
        /// <summary>
        /// 添加交往记录
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public int Addactivity(Cst_activity activity)
        {
            string sql = "insert into Cst_activity values('" + activity.Customer.Customer_no.Trim() + "','" + activity.Customer.Customer_name + "','" + activity.Activity_date + "','" + activity.Activity_place + "','" + activity.Activity_title + "','" + activity.Activity_desc + "','"+activity.Activity_momo+"')";
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 修改交往记录
        /// </summary>
        /// <param name="linkman"></param>
        /// <returns></returns>
        public int Updateactivity(Cst_activity activity)
        {
            string sql = "update Cst_activity set activity_date='" + activity.Activity_date + "',activity_place='" + activity.Activity_place + "',activity_title='" + activity.Activity_title + "',activity_desc='" + activity.Activity_desc + "',Activity_momo='"+activity.Activity_momo+"' where activity_id=" + activity.Activity_id;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 删除交往记录
        /// </summary>
        /// <param name="linkmanid"></param>
        /// <returns></returns>
        public int Delactivity(int activityid)
        {
            string sql = "delete from Cst_activity where activity_id=" + activityid;
            int result = DBHelper.ExecuteCommand(sql);
            return result;
        }
        /// <summary>
        /// 查询联系人
        /// </summary>
        /// <param name="CustormerId"></param>
        /// <returns></returns>
        public List<Cst_activity> GetActivityByCustormerId(string CustormerId)
        {
            string sql = "select * from Cst_activity where activity_customer_no='" + CustormerId + "'";
            return GetActivityBySql(sql);
        }
        /// <summary>
        /// 根据交往记录ＩＤ查询
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public Cst_activity GetActivityByActivityId(int activityid)
        {
            string sql = "select * from Cst_activity where activity_id=" + activityid;
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                Cst_activity act = new Cst_activity();

                act.Activity_id = (int)reader["Activity_id"];

                act.Activity_cust_name = (string)reader["Activity_cust_name"];
                act.Activity_date = (DateTime)reader["Activity_date"];
                act.Activity_desc = (string)reader["Activity_desc"];
                act.Activity_place = (string)reader["Activity_place"];
                act.Activity_title = (string)reader["Activity_title"];
                act.Activity_momo = (string)reader["Activity_momo"];
                string cst = (string)reader["activity_customer_no"];
                reader.Close();
                CustomerService cs = new CustomerService();
                act.Customer = cs.GetCustomerByCustomerId(cst);
                return act;
            }
            else
            {
                reader.Close();
                return null;
            }

        }
        /// <summary>
        /// 根据客户ID查询
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public Cst_activity GetActivityByCustomerId(string customer)
        {
            string sql = "select * from Cst_activity where linkman_customer_no='" + customer + "'";
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                Cst_activity act = new Cst_activity();

                act.Activity_id = (int)reader["Activity_id"];

                act.Activity_cust_name = (string)reader["Activity_cust_name"];
                act.Activity_date = (DateTime)reader["Activity_date"];
                act.Activity_desc = (string)reader["Activity_desc"];
                act.Activity_place = (string)reader["Activity_place"];
                act.Activity_title = (string)reader["Activity_title"];
                act.Activity_momo = (string)reader["Activity_momo"];
                string cst = (string)reader["activity_customer_no"];
                reader.Close();
                CustomerService cs = new CustomerService();
                act.Customer = cs.GetCustomerByCustomerId(cst);
                return act;
            }
            else
            {
                reader.Close();
                return null;
            }

        }
        /// <summary>
        /// 依据sql语句查询用户
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        ///// <returns>返回集合</returns>
        public List<Cst_activity> GetActivityBySql(string safeSql)
        {
            List<Cst_activity> list = new List<Cst_activity>();

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

                    act.Activity_id = (int)row["Activity_id"];
                    CustomerService cs = new CustomerService();
                    act.Customer = cs.GetCustomerByCustomerId((string)row["activity_customer_no"]);
                    act.Activity_cust_name = (string)row["Activity_cust_name"];
                    act.Activity_date = (DateTime)row["Activity_date"];
                    act.Activity_desc = (string)row["Activity_desc"];
                    act.Activity_place = (string)row["Activity_place"];
                    act.Activity_title = (string)row["Activity_title"];
                    act.Activity_momo = (string)row["Activity_momo"];
                    list.Add(act);
                }

                return list;
            }
        }
    }
}