HENGCHENG/DSWeb/Areas/SoftMng/DAL/Feedback.cs

using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using DSWeb.SoftMng.DBUtility;
// ReSharper disable once CheckNamespace
namespace DSWeb.SoftMng.DAL
{
    //Feedback
    public partial class FeedbackDAL
    {
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Feedback");
            strSql.Append(" where ");

            strSql.Append(" id = @id  ");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DSWeb.SoftMng.Model.Feedback model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Feedback(");
            strSql.Append("masterPhone,masterEmail,custManager,telephone,email,qq,dirMaster,dirPhone,dirEmail,dirQQ,feedbackID,proMaster,proPhone,proEmail,proQQ,beginDate,endDate,action,actionType,expense,project,feedbackType,completeDate,acceptanceSuggestion,acceptanceMaster,acceptanceDate,createUser,createDate,modifyUser,modifyDate,isDelete,userType,question,attachMent,productName,status,companyID,department,master,CUST_MOBILE,COMPANY_NAME,SOURCE_TYPE,PROCESS_RESULT");
            strSql.Append(") values (");
            strSql.Append("@masterPhone,@masterEmail,@custManager,@telephone,@email,@qq,@dirMaster,@dirPhone,@dirEmail,@dirQQ,@feedbackID,@proMaster,@proPhone,@proEmail,@proQQ,@beginDate,@endDate,@action,@actionType,@expense,@project,@feedbackType,@completeDate,@acceptanceSuggestion,@acceptanceMaster,@acceptanceDate,@createUser,@createDate,@modifyUser,@modifyDate,@isDelete,@userType,@question,@attachMent,@productName,@status,@companyID,@department,@master,@custMobile,@companyName,@sourceType,@processResult");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                        new SqlParameter("@masterPhone", SqlDbType.VarChar,30) ,
                        new SqlParameter("@masterEmail", SqlDbType.VarChar,100) ,
                        new SqlParameter("@custManager", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@telephone", SqlDbType.VarChar,30) ,
                        new SqlParameter("@email", SqlDbType.VarChar,100) ,
                        new SqlParameter("@qq", SqlDbType.VarChar,20) ,
                        new SqlParameter("@dirMaster", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@dirPhone", SqlDbType.VarChar,30) ,
                        new SqlParameter("@dirEmail", SqlDbType.VarChar,100) ,
                        new SqlParameter("@dirQQ", SqlDbType.VarChar,20) ,
                        new SqlParameter("@feedbackID", SqlDbType.Char,10) ,
                        new SqlParameter("@proMaster", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@proPhone", SqlDbType.VarChar,30) ,
                        new SqlParameter("@proEmail", SqlDbType.VarChar,100) ,
                        new SqlParameter("@proQQ", SqlDbType.VarChar,20) ,
                        new SqlParameter("@beginDate", SqlDbType.DateTime) ,
                        new SqlParameter("@endDate", SqlDbType.DateTime) ,
                        new SqlParameter("@action", SqlDbType.Int,4) ,
                        new SqlParameter("@actionType", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@expense", SqlDbType.Decimal,9) ,
                        new SqlParameter("@project", SqlDbType.NVarChar,150) ,
                        new SqlParameter("@feedbackType", SqlDbType.Int,4) ,
                        new SqlParameter("@completeDate", SqlDbType.DateTime) ,
                        new SqlParameter("@acceptanceSuggestion", SqlDbType.NVarChar,500) ,
                        new SqlParameter("@acceptanceMaster", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@acceptanceDate", SqlDbType.DateTime) ,
                        new SqlParameter("@createUser", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@createDate", SqlDbType.DateTime) ,
                        new SqlParameter("@modifyUser", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@modifyDate", SqlDbType.DateTime) ,
                        new SqlParameter("@isDelete", SqlDbType.Bit,1) ,
                        new SqlParameter("@userType", SqlDbType.Int,4) ,
                        new SqlParameter("@question", SqlDbType.NVarChar,500) ,
                        new SqlParameter("@attachMent", SqlDbType.NText) ,
                        new SqlParameter("@productName", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@status", SqlDbType.Int,4) ,
                        new SqlParameter("@companyID", SqlDbType.NVarChar,150) ,
                        new SqlParameter("@department", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@master", SqlDbType.NVarChar,50) ,
                        new SqlParameter("@custMobile", SqlDbType.NVarChar,20) ,
                        new SqlParameter("@companyName", SqlDbType.NVarChar,40),
                        new SqlParameter("@sourceType", SqlDbType.NVarChar,2),
                        new SqlParameter("@processResult", SqlDbType.NVarChar,500)

            };

            parameters[0].Value = model.masterPhone ?? (Object)DBNull.Value;
            parameters[1].Value = model.masterEmail ?? (Object)DBNull.Value;
            parameters[2].Value = model.custManager ?? (Object)DBNull.Value;
            parameters[3].Value = model.telephone ?? (Object)DBNull.Value;
            parameters[4].Value = model.email ?? (Object)DBNull.Value;
            parameters[5].Value = model.qq ?? (Object)DBNull.Value;
            parameters[6].Value = model.dirMaster ?? (Object)DBNull.Value;
            parameters[7].Value = model.dirPhone ?? (Object)DBNull.Value;
            parameters[8].Value = model.dirEmail ?? (Object)DBNull.Value;
            parameters[9].Value = model.dirQQ ?? (Object)DBNull.Value;
            parameters[10].Value = model.feedbackID ?? (Object)DBNull.Value;
            parameters[11].Value = model.proMaster ?? (Object)DBNull.Value;
            parameters[12].Value = model.proPhone ?? (Object)DBNull.Value;
            parameters[13].Value = model.proEmail ?? (Object)DBNull.Value;
            parameters[14].Value = model.proQQ ?? (Object)DBNull.Value;
            parameters[15].Value = model.beginDate ?? (Object)DBNull.Value;
            parameters[16].Value = model.endDate ?? (Object)DBNull.Value;
            parameters[17].Value = model.action ?? (Object)DBNull.Value;
            parameters[18].Value = model.actionType ?? (Object)DBNull.Value;
            parameters[19].Value = model.expense ?? (Object)DBNull.Value;
            parameters[20].Value = model.project ?? (Object)DBNull.Value;
            parameters[21].Value = model.feedbackType ?? (Object)DBNull.Value;
            parameters[22].Value = model.completeDate ?? (Object)DBNull.Value;
            parameters[23].Value = model.acceptanceSuggestion ?? (Object)DBNull.Value;
            parameters[24].Value = model.acceptanceMaster ?? (Object)DBNull.Value;
            parameters[25].Value = model.acceptanceDate ?? (Object)DBNull.Value;
            parameters[26].Value = model.createUser ?? (Object)DBNull.Value;
            parameters[27].Value = model.createDate ?? (Object)DBNull.Value;
            parameters[28].Value = model.modifyUser ?? (Object)DBNull.Value;
            parameters[29].Value = model.modifyDate ?? (Object)DBNull.Value;
            parameters[30].Value = model.isDelete ?? (Object)DBNull.Value;
            parameters[31].Value = model.userType ?? (Object)DBNull.Value;
            parameters[32].Value = model.question ?? (Object)DBNull.Value;
            parameters[33].Value = model.attachMent ?? (Object)DBNull.Value;
            parameters[34].Value = model.productName ?? (Object)DBNull.Value;
            parameters[35].Value = model.status ?? (Object)DBNull.Value;
            parameters[36].Value = model.companyID ?? (Object)DBNull.Value;
            parameters[37].Value = model.department ?? (Object)DBNull.Value;
            parameters[38].Value = model.master ?? (Object)DBNull.Value;
            parameters[39].Value = model.CUST_MOBILE ?? (Object)DBNull.Value;
            parameters[40].Value = model.COMPANY_NAME ?? (Object)DBNull.Value;
            parameters[41].Value = model.SOURCE_TYPE ?? (Object)DBNull.Value;
            parameters[42].Value = model.PROCESS_RESULT ?? (Object)DBNull.Value;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(DSWeb.SoftMng.Model.Feedback model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Feedback set ");

            strSql.Append(" masterPhone = @masterPhone,");
            strSql.Append(" masterEmail = @masterEmail,");
            strSql.Append(" custManager = @custManager,");
            strSql.Append(" telephone = @telephone,");
            strSql.Append(" email = @email,");
            strSql.Append(" qq = @qq,");
            strSql.Append(" dirMaster = @dirMaster,");
            strSql.Append(" dirPhone = @dirPhone,");
            strSql.Append(" dirEmail = @dirEmail,");
            strSql.Append(" dirQQ = @dirQQ,");
            strSql.Append(" feedbackID = @feedbackID,");
            strSql.Append(" proMaster = @proMaster,");
            strSql.Append(" proPhone = @proPhone,");
            strSql.Append(" proEmail = @proEmail,");
            strSql.Append(" proQQ = @proQQ,");
            strSql.Append(" beginDate = @beginDate,");
            strSql.Append(" endDate = @endDate,");
            strSql.Append(" action = @action,");
            strSql.Append(" actionType = @actionType,");
            strSql.Append(" expense = @expense,");
            strSql.Append(" project = @project,");
            strSql.Append(" feedbackType = @feedbackType,");
            strSql.Append(" completeDate = @completeDate,");
            strSql.Append(" acceptanceSuggestion = @acceptanceSuggestion,");
            strSql.Append(" acceptanceMaster = @acceptanceMaster,");
            strSql.Append(" acceptanceDate = @acceptanceDate,");
            strSql.Append(" createUser = @createUser,");
            strSql.Append(" createDate = @createDate,");
            strSql.Append(" modifyUser = @modifyUser,");
            strSql.Append(" modifyDate = @modifyDate,");
            strSql.Append(" isDelete = @isDelete,");
            strSql.Append(" userType = @userType,");
            strSql.Append(" question = @question,");
            strSql.Append(" attachMent = @attachMent,");
            strSql.Append(" productName = @productName,");
            strSql.Append(" status = @status,");
            strSql.Append(" companyID = @companyID,");
            strSql.Append(" department = @department,");
            strSql.Append(" master = @master,");
            strSql.Append(" CUST_MOBILE = @custMobile,");
            strSql.Append(" COMPANY_NAME = @companyName,");
            strSql.Append(" SOURCE_TYPE = @sourceType,");
            strSql.Append(" PROCESS_RESULT = @processType");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                        new SqlParameter("@id", SqlDbType.Int,4) ,
                                    new SqlParameter("@masterPhone", SqlDbType.VarChar,30) ,
                                    new SqlParameter("@masterEmail", SqlDbType.VarChar,100) ,
                                    new SqlParameter("@custManager", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@telephone", SqlDbType.VarChar,30) ,
                                    new SqlParameter("@email", SqlDbType.VarChar,100) ,
                                    new SqlParameter("@qq", SqlDbType.VarChar,20) ,
                                    new SqlParameter("@dirMaster", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@dirPhone", SqlDbType.VarChar,30) ,
                                    new SqlParameter("@dirEmail", SqlDbType.VarChar,100) ,
                                    new SqlParameter("@dirQQ", SqlDbType.VarChar,20) ,
                                    new SqlParameter("@feedbackID", SqlDbType.Char,10) ,
                                    new SqlParameter("@proMaster", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@proPhone", SqlDbType.VarChar,30) ,
                                    new SqlParameter("@proEmail", SqlDbType.VarChar,100) ,
                                    new SqlParameter("@proQQ", SqlDbType.VarChar,20) ,
                                    new SqlParameter("@beginDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@endDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@action", SqlDbType.Int,4) ,
                                    new SqlParameter("@actionType", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@expense", SqlDbType.Decimal,9) ,
                                    new SqlParameter("@project", SqlDbType.NVarChar,150) ,
                                    new SqlParameter("@feedbackType", SqlDbType.Int,4) ,
                                    new SqlParameter("@completeDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@acceptanceSuggestion", SqlDbType.NVarChar,500) ,
                                    new SqlParameter("@acceptanceMaster", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@acceptanceDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@createUser", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@createDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@modifyUser", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@modifyDate", SqlDbType.DateTime) ,
                                    new SqlParameter("@isDelete", SqlDbType.Bit,1) ,
                                    new SqlParameter("@userType", SqlDbType.Int,4) ,
                                    new SqlParameter("@question", SqlDbType.NVarChar,500) ,
                                    new SqlParameter("@attachMent", SqlDbType.NText) ,
                                    new SqlParameter("@productName", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@status", SqlDbType.Int,4) ,
                                    new SqlParameter("@companyID", SqlDbType.NVarChar,150) ,
                                    new SqlParameter("@department", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@master", SqlDbType.NVarChar,50) ,
                                    new SqlParameter("@custMobile", SqlDbType.NVarChar,20) ,
                                    new SqlParameter("@companyName", SqlDbType.NVarChar,40),
                                    new SqlParameter("@sourceType", SqlDbType.NVarChar,2),
                                    new SqlParameter("@processType", SqlDbType.NVarChar,500)
            };

            parameters[0].Value = model.id;
            parameters[1].Value = model.masterPhone ?? (Object)DBNull.Value;
            parameters[2].Value = model.masterEmail ?? (Object)DBNull.Value;
            parameters[3].Value = model.custManager ?? (Object)DBNull.Value;
            parameters[4].Value = model.telephone ?? (Object)DBNull.Value;
            parameters[5].Value = model.email ?? (Object)DBNull.Value;
            parameters[6].Value = model.qq ?? (Object)DBNull.Value;
            parameters[7].Value = model.dirMaster ?? (Object)DBNull.Value;
            parameters[8].Value = model.dirPhone ?? (Object)DBNull.Value;
            parameters[9].Value = model.dirEmail ?? (Object)DBNull.Value;
            parameters[10].Value = model.dirQQ ?? (Object)DBNull.Value;
            parameters[11].Value = model.feedbackID ?? (Object)DBNull.Value;
            parameters[12].Value = model.proMaster ?? (Object)DBNull.Value;
            parameters[13].Value = model.proPhone ?? (Object)DBNull.Value;
            parameters[14].Value = model.proEmail ?? (Object)DBNull.Value;
            parameters[15].Value = model.proQQ ?? (Object)DBNull.Value;
            parameters[16].Value = model.beginDate ?? (Object)DBNull.Value;
            parameters[17].Value = model.endDate ?? (Object)DBNull.Value;
            parameters[18].Value = model.action ?? (Object)DBNull.Value;
            parameters[19].Value = model.actionType ?? (Object)DBNull.Value;
            parameters[20].Value = model.expense ?? (Object)DBNull.Value;
            parameters[21].Value = model.project ?? (Object)DBNull.Value;
            parameters[22].Value = model.feedbackType ?? (Object)DBNull.Value;
            parameters[23].Value = model.completeDate ?? (Object)DBNull.Value;
            parameters[24].Value = model.acceptanceSuggestion ?? (Object)DBNull.Value;
            parameters[25].Value = model.acceptanceMaster ?? (Object)DBNull.Value;
            parameters[26].Value = model.acceptanceDate ?? (Object)DBNull.Value;
            parameters[27].Value = model.createUser ?? (Object)DBNull.Value;
            parameters[28].Value = model.createDate ?? (Object)DBNull.Value;
            parameters[29].Value = model.modifyUser ?? (Object)DBNull.Value;
            parameters[30].Value = model.modifyDate ?? (Object)DBNull.Value;
            parameters[31].Value = model.isDelete ?? (Object)DBNull.Value;
            parameters[32].Value = model.userType ?? (Object)DBNull.Value;
            parameters[33].Value = model.question ?? (Object)DBNull.Value;
            parameters[34].Value = model.attachMent ?? (Object)DBNull.Value;
            parameters[35].Value = model.productName ?? (Object)DBNull.Value;
            parameters[36].Value = model.status ?? (Object)DBNull.Value;
            parameters[37].Value = model.companyID ?? (Object)DBNull.Value;
            parameters[38].Value = model.department ?? (Object)DBNull.Value;
            parameters[39].Value = model.master ?? (Object)DBNull.Value;
            parameters[40].Value = model.CUST_MOBILE ?? (Object)DBNull.Value;
            parameters[41].Value = model.COMPANY_NAME ?? (Object)DBNull.Value;
            parameters[42].Value = model.SOURCE_TYPE ?? (Object)DBNull.Value;
            parameters[43].Value = model.PROCESS_RESULT ?? (Object)DBNull.Value;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Feedback ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public int DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Feedback ");
            strSql.Append(" where ID in (" + idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 按条件批量删除
        /// </summary>
        public int DeleteListWhere(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Feedback ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id, masterPhone, masterEmail, custManager, telephone, email, qq, dirMaster, dirPhone, dirEmail, dirQQ, feedbackID, proMaster, proPhone, proEmail, proQQ, beginDate, endDate, action, actionType, expense, project, feedbackType, completeDate, acceptanceSuggestion, acceptanceMaster, acceptanceDate, createUser, createDate, modifyUser, modifyDate, isDelete, userType, question, attachMent, productName, status, companyID, department, master,cust_mobile,company_name,source_type,ACCEPT_TIME,PROCESS_RESULT  ");
            strSql.Append("  from Feedback ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            DSWeb.SoftMng.Model.Feedback model = new DSWeb.SoftMng.Model.Feedback();
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Feedback ");
            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(" * ");
            strSql.Append(" FROM Feedback ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <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);
            strSql.Append(")AS Row, T.*  from Feedback T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row > {0} and TT.Row <= {1}", startIndex, endIndex);
            //公共代码
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Feedback ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.ExcuteScalarSQL(strSql.ToString());
        }
    }
}