serverservice.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 ServerService:IServer
    {
       /// <summary>
       /// 添加服务
       /// </summary>
       /// <param name="ser"></param>
       /// <returns></returns>
       public int AddServer(Cst_service ser)
       {
           string sql="insert into Cst_service(service_id,service_type,service_title,service_customer_no,service_customer_name,service_status,service_request,service_create_id,service_create_by,service_create_date) values("+ser.Service_id+",'"+ser.Service_type+"','"+ser.Service_title+"','"+ser.Customer.Customer_no+"','"+ser.Customer.Customer_name+"','"+ser.Service_status+"','"+ser.Service_request+"',"+ser.Service_create_id+",'"+ser.Service_create_by+"','"+ser.Service_create_date+"')";
           int result = DBHelper.ExecuteCommand(sql);
           return result;
           
       }
       /// <summary>
       /// 更新分配人
       /// </summary>
       /// <param name="ser"></param>
       /// <returns></returns>
       public int Update(Cst_service ser)
       {
           string sql = "update cst_service set service_status='已分配',service_due_id=" + ser.Service_due_id + ",service_due_to='" + ser.Service_due_to + "',service_due_date='" + ser.Service_due_date + "' where service_id="+ser.Service_id;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 更新处理
       /// </summary>
       /// <param name="ser"></param>
       /// <returns></returns>
       public int UpdateService(Cst_service ser)
       {
           string sql = "update cst_service set service_status='已处理',service_deal='" + ser.Service_deal + "',service_deal_id=" + ser.Service_deal_id + ",service_deal_by='" + ser.Service_deal_by + "',service_deal_date='"+ser.Service_deal_date+"' where service_id=" + ser.Service_id;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 更新反馈
       /// </summary>
       /// <param name="ser"></param>
       /// <returns></returns>
       public int UpdateFeed(Cst_service ser)
       {
           string sql = "update cst_service set service_status='已反馈',service_result='" + ser.Service_result + "',service_satisfy=" + ser.Service_satisfy + "  where service_id=" + ser.Service_id;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 删除服务
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public int Del(int id)
       {
           string sql = "delete from cst_service where service_id=" +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_service> GetServiceSelect(string name, string title, string server,DateTime statime,DateTime endtime,string type)
       {
           string sqlname = "";
           if (name != "")
           {
               sqlname = " and service_customer_name='" + name + "' ";
           }
           string sqltitle = "";
           if (title != "")
           {
               sqltitle = " and service_title like '%" + title + "%' ";
           }
           string sqlren = "";
           if (server != "")
           {
               sqlren = " and service_type='" + server + "' ";
           }
           string sqltime = "";
           if(statime.ToString()!=""&&endtime.ToString()!="")
           {
               sqltime = "and service_create_date between Convert(DateTime,'" + statime + "') and Convert(DateTime,'" + endtime + "')";
           }

           string sql = "select * from cst_service where service_status='"+type+"'";
           sql += sqlname;
           sql += sqltitle;
           sql += sqlren;
           sql += sqltime;
           List<Cst_service> list = new List<Cst_service>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Cst_service cs = new Cst_service();
                   cs.Service_id = (int)row["service_id"];
                   CustomerService css = new CustomerService();
                   cs.Customer = css.GetCustomerByCustomerId((string)row["service_customer_no"]);
                   cs.Service_type = (string)row["service_type"];
                   cs.Service_create_date = (DateTime)row["service_create_date"];
                   cs.Service_create_by = (string)row["service_create_by"];
                   cs.Service_create_id = (int)row["service_create_id"];
                   cs.Service_request = (string)row["service_request"];
                   cs.Service_status = (string)row["service_status"];
                   cs.Service_title = (string)row["service_title"];
                   cs.Service_customer_name = (string)row["Service_customer_name"];
                   list.Add(cs);
               }

               return list;
           }
       }
       /// <summary>
       /// 根据服务状态查询
       /// </summary>
       /// <returns></returns>
       public List<Cst_service> GetSer(string type)
       {
           string sql = "select * from cst_service where service_status='"+type+"'";
           List<Cst_service> list = new List<Cst_service>();
           using (DataTable table = DBHelper.GetDataSet(sql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Cst_service cs = new Cst_service();
                   cs.Service_id = (int)row["service_id"];
                   CustomerService css = new CustomerService();
                   cs.Customer = css.GetCustomerByCustomerId((string)row["service_customer_no"]);
                   cs.Service_type = (string)row["service_type"];
                   cs.Service_create_date = (DateTime)row["service_create_date"];
                   cs.Service_create_by = (string)row["service_create_by"];
                   cs.Service_create_id = (int)row["service_create_id"];
                   cs.Service_request = (string)row["service_request"];
                   cs.Service_status = (string)row["service_status"];
                   cs.Service_title = (string)row["service_title"];
                   cs.Service_customer_name = (string)row["Service_customer_name"];
                   list.Add(cs);
               }
               return list;
           }
       }
       /// <summary>
       /// 根据服务ID查询
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public Cst_service  GetServiceByserId(int id)
       {
           string sql = "select * from cst_service where service_id=" +id;
            SqlDataReader reader = DBHelper.GetReader(sql);
            if (reader.Read())
            {
                   Cst_service cs = new Cst_service();

                   cs.Service_id = (int)reader["service_id"];
                    string no=(string)reader["service_customer_no"];
                   cs.Service_type = (string)reader["service_type"];
                   cs.Service_create_date = (DateTime)reader["service_create_date"];
                   cs.Service_create_by = (string)reader["service_create_by"];
                   cs.Service_create_id = (int)reader["service_create_id"];
                   cs.Service_request = (string)reader["service_request"];
                   cs.Service_status = (string)reader["service_status"];
                   cs.Service_title = (string)reader["service_title"];
                   cs.Service_customer_name = (string)reader["Service_customer_name"];
                   cs.Service_due_id=(int)reader["service_due_id"];
                   cs.Service_due_to=(string)reader["service_due_to"];
                   cs.Service_due_date=(DateTime)reader["service_due_date"];
                reader.Close();
                CustomerService css = new CustomerService();
                   cs.Customer = css.GetCustomerByCustomerId(no);
                return cs;
            }
              else
            {
                reader.Close();
                return null;
            }
           }
       /// <summary>
       /// 查询反馈
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public Cst_service GetServiByserId(int id)
       {
           string sql = "select * from cst_service where service_id=" + id;
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               Cst_service cs = new Cst_service();

               cs.Service_id = (int)reader["service_id"];
               string no = (string)reader["service_customer_no"];
               cs.Service_type = (string)reader["service_type"];
               cs.Service_create_date = (DateTime)reader["service_create_date"];
               cs.Service_create_by = (string)reader["service_create_by"];
               cs.Service_create_id = (int)reader["service_create_id"];
               cs.Service_request = (string)reader["service_request"];
               cs.Service_status = (string)reader["service_status"];
               cs.Service_title = (string)reader["service_title"];
               cs.Service_customer_name = (string)reader["Service_customer_name"];
               cs.Service_due_id = (int)reader["service_due_id"];
               cs.Service_due_to = (string)reader["service_due_to"];
               cs.Service_due_date = (DateTime)reader["service_due_date"];
               cs.Service_deal_id = (int)reader["Service_deal_id"];
               cs.Service_deal = (string)reader["Service_deal"];
               cs.Service_deal_by = (string)reader["Service_deal_by"];
               cs.Service_deal_date = (DateTime)reader["Service_deal_date"];
               reader.Close();
               CustomerService css = new CustomerService();
               cs.Customer = css.GetCustomerByCustomerId(no);
               return cs;
           }
           else
           {
               reader.Close();
               return null;
           }
       }
       public Cst_service GetByserId(int id)
       {
           string sql = "select * from cst_service where service_id=" + id;
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               Cst_service cs = new Cst_service();

               cs.Service_id = (int)reader["service_id"];
               string no = (string)reader["service_customer_no"];
               cs.Service_type = (string)reader["service_type"];
               cs.Service_create_date = (DateTime)reader["service_create_date"];
               cs.Service_create_by = (string)reader["service_create_by"];
               cs.Service_create_id = (int)reader["service_create_id"];
               cs.Service_request = (string)reader["service_request"];
               cs.Service_status = (string)reader["service_status"];
               cs.Service_title = (string)reader["service_title"];
               cs.Service_customer_name = (string)reader["Service_customer_name"];
               cs.Service_due_id = (int)reader["service_due_id"];
               cs.Service_due_to = (string)reader["service_due_to"];
               cs.Service_due_date = (DateTime)reader["service_due_date"];
               cs.Service_deal_id = (int)reader["Service_deal_id"];
               cs.Service_deal = (string)reader["Service_deal"];
               cs.Service_deal_by = (string)reader["Service_deal_by"];
               cs.Service_deal_date = (DateTime)reader["Service_deal_date"];
               cs.Service_satisfy=(int)reader["Service_satisfy"];
               cs.Service_result=(string)reader["Service_result"];
                   reader.Close();
               CustomerService css = new CustomerService();
               cs.Customer = css.GetCustomerByCustomerId(no);
               return cs;
           }
           else
           {
               reader.Close();
               return null;
           }
       }
       }
       }