ZYKJ_API/CallCenterApi/CallCenterAp.../T_Cus_Custom...

using CallCenterApi.DB;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenterApi.DAL
{
    /// <summary>
	/// 数据访问类:T_Cus_CustomerBase
	/// </summary>
	public partial class T_Cus_CustomerBase
    {
        public T_Cus_CustomerBase()
        { }
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("F_CustomerId", "T_Cus_CustomerBase");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int F_CustomerId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_Cus_CustomerBase");
            strSql.Append(" where F_CustomerId=@F_CustomerId");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_CustomerId", SqlDbType.Int,4)
            };
            parameters[0].Value = F_CustomerId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(CallCenterApi.Model.T_Cus_CustomerBase model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_Cus_CustomerBase(");
            strSql.Append("F_Name,F_PhoneNum1,F_PhoneNum2,F_Address,F_CusType,F_AddAgentId,F_Remark,F_YHFKId,F_YHFKName,F_DeptId)");
            strSql.Append(" values (");
            strSql.Append("@F_Name,@F_PhoneNum1,@F_PhoneNum2,@F_Address,@F_CusType,@F_AddAgentId,@F_Remark,@F_YHFKId,@F_YHFKName,@F_DeptId);");
            strSql.Append("select F_Id from T_Cus_CustomerBase order by  F_AddTime desc ");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_Name", model.F_Name),
                    new SqlParameter("@F_PhoneNum1",model.F_PhoneNum1),
                    new SqlParameter("@F_PhoneNum2", model.F_PhoneNum2),
                    new SqlParameter("@F_Address", model.F_Address),
                    new SqlParameter("@F_CusType", model.F_CusType),
                    new SqlParameter("@F_AddAgentId", model.F_AddAgentId),
                    new SqlParameter("@F_Remark", model.F_Remark),
                    new SqlParameter("@F_YHFKId", model.F_YHFKId),
                    new SqlParameter("@F_YHFKName",model.F_YHFKName),
                    new SqlParameter("@F_DeptId", model.F_DeptId),
            };

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return null;
            }
            else
            {
                return obj.ToString();
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CallCenterApi.Model.T_Cus_CustomerBase model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_Cus_CustomerBase set ");
            strSql.Append("F_Name=@F_Name,");
            strSql.Append("F_PhoneNum1=@F_PhoneNum1,");
            strSql.Append("F_PhoneNum2=@F_PhoneNum2,");
            strSql.Append("F_Address=@F_Address,");
            strSql.Append("F_CusType=@F_CusType,");
            strSql.Append("F_AddAgentId=@F_AddAgentId,");
            strSql.Append("F_Remark=@F_Remark,");
            strSql.Append("F_YHFKId=@F_YHFKId,");
            strSql.Append("F_YHFKName=@F_YHFKName,");
            strSql.Append("F_DeptId=@F_DeptId  ");
            strSql.Append(" where F_Id=@F_Id");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_Id",model.F_Id),
                    new SqlParameter("@F_Name", model.F_Name),
                    new SqlParameter("@F_PhoneNum1",model.F_PhoneNum1),
                    new SqlParameter("@F_PhoneNum2", model.F_PhoneNum2),
                    new SqlParameter("@F_Address", model.F_Address),
                    new SqlParameter("@F_CusType", model.F_CusType),
                    new SqlParameter("@F_AddAgentId", model.F_AddAgentId),
                    new SqlParameter("@F_Remark", model.F_Remark),
                    new SqlParameter("@F_YHFKId", model.F_YHFKId),
                    new SqlParameter("@F_YHFKName",model.F_YHFKName),
                    new SqlParameter("@F_DeptId", model.F_DeptId),
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int F_CustomerId)
        {

            StringBuilder strSql = new StringBuilder();
            //strSql.Append("delete from T_Cus_CustomerBase ");
            strSql.Append("update T_Cus_CustomerBase set F_DeleteFlag=1");
            strSql.Append(" where F_CustomerId=@F_CustomerId");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_CustomerId", SqlDbType.Int,4)
            };
            parameters[0].Value = F_CustomerId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string F_CustomerIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("delete from T_Cus_CustomerBase ");
            strSql.Append("update T_Cus_CustomerBase set F_DeleteFlag=1");
            strSql.Append(" where F_CustomerId in (" + F_CustomerIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CallCenterApi.Model.T_Cus_CustomerBase GetModel(string id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 cb.*,ua.F_UserName as AddAgentName   FROM T_Cus_CustomerBase cb left join T_Sys_UserAccount ua on ua.F_UserId=cb.F_AddAgentId ");
            strSql.Append(" where cb.F_Id=@F_Id");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_Id",id)
            };

            CallCenterApi.Model.T_Cus_CustomerBase model = new CallCenterApi.Model.T_Cus_CustomerBase();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CallCenterApi.Model.T_Cus_CustomerBase GetModelByTel(string tel)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 F_Id,F_Name,F_PhoneNum1,F_PhoneNum2,F_Address,F_CusType,F_AddAgentId,F_AddTime,F_Remark,F_YHFKId,F_YHFKName,F_DeptId,F_DeleteFlag  from T_Cus_CustomerBase ");
            strSql.Append(" where F_PhoneNum1=@F_PhoneNum1");
            SqlParameter[] parameters = {
                    new SqlParameter("@F_PhoneNum1",tel)
            };

            CallCenterApi.Model.T_Cus_CustomerBase model = new CallCenterApi.Model.T_Cus_CustomerBase();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CallCenterApi.Model.T_Cus_CustomerBase DataRowToModel(DataRow row)
        {
            CallCenterApi.Model.T_Cus_CustomerBase model = new CallCenterApi.Model.T_Cus_CustomerBase();
            var columns = row.Table.Columns;
            if (row != null)
            {
                model.F_Id = row["F_ID"] == DBNull.Value ? "" : row["F_ID"].ToString();
                model.F_Name = row["F_Name"] == DBNull.Value ? "" : row["F_Name"].ToString();
                model.F_PhoneNum1 = row["F_PhoneNum1"] == DBNull.Value ? "" : row["F_PhoneNum1"].ToString();
                model.F_PhoneNum2 = row["F_PhoneNum2"] == DBNull.Value ? "" : row["F_PhoneNum2"].ToString();
                model.F_Address = row["F_Address"] == DBNull.Value ? "" : row["F_Address"].ToString();
                model.F_CusType = row["F_CusType"] == DBNull.Value ? 0 : Convert.ToInt32(row["F_CusType"]);
                model.F_AddAgentId = row["F_AddAgentId"] == DBNull.Value ? 0 : Convert.ToInt32(row["F_AddAgentId"]);
                model.F_AddTime = row["F_AddTime"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(row["F_AddTime"]);
                model.F_Remark = row["F_Remark"] == DBNull.Value ? "" : row["F_Remark"].ToString();
                model.F_YHFKId = row["F_YHFKId"] == DBNull.Value ? 0 : Convert.ToInt32(row["F_YHFKId"]);
                model.F_YHFKName = row["F_YHFKName"] == DBNull.Value ? "" : row["F_YHFKName"].ToString();
                model.F_DeptId = row["F_DeptId"] == DBNull.Value ? 0 : Convert.ToInt32(row["F_DeptId"]);
                model.F_DeleteFlag = row["F_DeleteFlag"] == DBNull.Value ? 0 : Convert.ToInt32(row["F_DeleteFlag"]);
                if (columns.Contains("AddAgentName")) { model.F_AddAgentName = row["AddAgentName"] == DBNull.Value ? "" : row["AddAgentName"].ToString(); }

            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select cb.*,ua.F_UserName as AddAgentName ");
            strSql.Append(" FROM T_Cus_CustomerBase cb left join T_Sys_UserAccount ua on ua.F_UserId=cb.F_AddAgentId ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" F_CustomerId,F_CustomerName,F_CustomerCode,F_CustomerEName,F_CustomerPym,F_Layer,F_CustomerLayer,F_CategoryId,F_TradeId,F_RegionId,F_IndustryId,F_Address,F_Url,F_Telephone,F_Mobile,F_Fax,F_Email,F_Website,F_PostCode,F_ScaleResume,F_BusinessProfile,F_BusinessLicenseID,F_TaxID,F_OpeningBank,F_BankAccount,F_LegalRepresentative,F_LegalRepresentativeCardID,F_Nature,F_Kind,F_CustomerType,F_MainProduct,F_CustomerFrom,F_TrackInfo,F_TrackState,F_ImportDegree,F_IntentProduct,F_CreateBy,F_CreatedOn,F_ModifyBy,F_ModifiedOn,F_DeleteFlag,F_StatusCode,F_ServiceDept,F_ChargeName,F_ChargeTelephone,F_Province,F_ProductLine,F_ChargeDept,F_AfterSaleName,F_CustomerNature,F_CustomerClass,F_CustomerIndustry,F_RelationShipClass,F_CurrentVersion,F_SystemStartTime,F_QualityGuaranteeEndTime,F_AfterSaleTelephone,F_Notes,F_SystemType,F_City,F_CustomerStatus,F_DeviceCount,F_MaintenanceType,F_ERPCustomerCode,F_ServiceDeptID,F_ProductLineID,F_AfterSaleNameID,F_CustomerNatureID,F_CustomerClassID,F_RelationShipClassID,F_CityID ");
            strSql.Append(" FROM T_Cus_CustomerBase ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM T_Cus_CustomerBase ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.F_CustomerId desc");
            }
            strSql.Append(")AS Row, T.*  from T_Cus_CustomerBase T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "T_Cus_CustomerBase";
			parameters[1].Value = "F_CustomerId";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}