D7QUANTAI/DSWeb/Areas/SoftMng/DAL/MsInfoClientFeedback/MsInfoClientFeedbackDAL.cs

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.SoftMng.Models.MsInfoClientFeedback;
using DSWeb.EntityDA;
using DSWeb.Areas.CommMng.Models;
using DSWeb.DataAccess;
using HcUtility.Comm;
using System.IO;

namespace DSWeb.SoftMng.DAL.MsInfoClientFeedbackDAL
{
    public class MsInfoClientFeedbackDAL
    {
        #region 主列表查询
        static public List<VW_Feedback_InfoEntity> GetDataList(string strCondition, string USERID, string SHOWNAME, string COMPANYID, string sort = null)
        {
            var rangstr = GetRangDAStr("index", USERID, SHOWNAME, COMPANYID);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
            String strSql = "SELECT * FROM [VW_Feedback_Info] where (isDelete=0 or isDelete is null) ";
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql += " and " + strCondition;
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql += " order by " + sortstring;
            }
            else
            {
                strSql += " order by modifyDate desc";
            }
            return SetData(strSql);
        }

        private static List<VW_Feedback_InfoEntity> SetData(String strSql)
        {
            var headList = new List<VW_Feedback_InfoEntity>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    VW_Feedback_InfoEntity data = new VW_Feedback_InfoEntity();
                    #region Set DB data to Object
                    data.ID = (reader["ID"] == null ? 0 : Convert.ToInt32(reader["ID"]));//ID
                    data.STATUS = (reader["STATUS"] == null ? "0" : Convert.ToString(reader["STATUS"]));//STATUS
                    data.FEEDBACKID = (reader["FEEDBACKID"] == null ? "" : Convert.ToString(reader["FEEDBACKID"]));//FEEDBACKID
                    //data.BEGINDATE = (reader["BEGINDATE"] == null ? "" : Convert.ToString(reader["BEGINDATE"]));//BEGINDATE
                    if (reader["BEGINDATE"] != null && reader["BEGINDATE"].ToString().Trim().IndexOf("0001") < 0 && reader["BEGINDATE"].ToString().Trim().IndexOf("1900") < 0 && reader["BEGINDATE"].ToString().Trim() != "")
                    {
                        data.BEGINDATE = Convert.ToDateTime(reader["BEGINDATE"]);
                    }
                    data.FEEDBACKTYPE = (reader["FEEDBACKTYPE"] == null ? "0" : Convert.ToString(reader["FEEDBACKTYPE"]));//FEEDBACKTYPE
                    data.COMPANYID = (reader["COMPANYID"] == null ? "" : Convert.ToString(reader["COMPANYID"]));//COMPANYID
                    data.MASTER = (reader["MASTER"] == null ? "" : Convert.ToString(reader["MASTER"]));//MASTER
                    data.ACTION = (reader["ACTION"] == null ? "" : Convert.ToString(reader["ACTION"]));//ACTION
                    data.PROJECT = (reader["PROJECT"] == null ? "" : Convert.ToString(reader["PROJECT"]));//PROJECT
                    data.EXPENSE = (reader["EXPENSE"] == null ? 0 : Convert.ToDecimal(reader["EXPENSE"]));//EXPENSE
                    data.USERTYPE = (reader["USERTYPE"] == null ? "0" : Convert.ToString(reader["USERTYPE"]));//USERTYPE
                    data.REMARKID = (reader["REMARKID"] == null ? "" : Convert.ToString(reader["REMARKID"]));//REMARKID
                    data.REMARKID_HF = (reader["REMARKID_HF"] == null ? "" : Convert.ToString(reader["REMARKID_HF"]));//REMARKID
                    data.REMARK = (reader["REMARK"] == null ? "" : Convert.ToString(reader["REMARK"]));//REMARK
                    data.REMARK_HF = (reader["REMARK_HF"] == null ? "" : Convert.ToString(reader["REMARK_HF"]));//REMARK
                    data.MODIFYUSER = (reader["MODIFYUSER"] == null ? "" : Convert.ToString(reader["MODIFYUSER"]));//MODIFYUSER
                    //data.MODIFYDATE = (reader["MODIFYDATE"] == null ? "" : Convert.ToString(reader["MODIFYDATE"]));//MODIFYDATE
                    if (reader["MODIFYDATE"] != null && reader["MODIFYDATE"].ToString().Trim().IndexOf("0001") < 0 && reader["MODIFYDATE"].ToString().Trim().IndexOf("1900") < 0 && reader["MODIFYDATE"].ToString().Trim() != "")
                    {
                        data.MODIFYDATE = Convert.ToDateTime(reader["MODIFYDATE"]);
                    }
                    data.MODIFYUSERGID = (reader["MODIFYUSERGID"] == null ? "" : Convert.ToString(reader["MODIFYUSERGID"]));//MODIFYUSERGID
                    data.STLNAME = (reader["STLNAME"] == null ? "" : Convert.ToString(reader["STLNAME"]));
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        static public VW_Feedback_InfoEntity GetData(string strCondition, string USERID, string SHOWNAME, string COMPANYID)
        {
            var rangstr = GetRangDAStr("index", USERID, SHOWNAME, COMPANYID);
            if (!string.IsNullOrEmpty(rangstr))
            {
                if (!string.IsNullOrEmpty(strCondition))
                {
                    strCondition = strCondition + " and " + rangstr;
                }
                else
                {
                    strCondition = rangstr;
                }
            }
            String strSql = "SELECT * FROM [VW_Feedback_Info] where (isDelete=0 or isDelete is null)";
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql += " and " + strCondition;
            }
            var list = SetData(strSql);//strPath, strPhotoPath, 
            if (list.Count > 0)
            {
                return list[0];
            }
            return new VW_Feedback_InfoEntity();
        }
        #endregion

        #region 明细表查询
        static public List<MsInfoClientFeedbackInfoEntity> GetFeedbackInfoList(string strCondition, string USERID, string SHOWNAME, string COMPANYID, string sort = null)
        {
            String strSql = "SELECT *,REMARKTYPENAME=(case when REMARKTYPE=0 then '问' else '答' end) FROM [FeedbackInfo] where 1=1 ";
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql += " and " + strCondition;
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql += " order by " + sortstring;
            }
            else
            {
                strSql += " order by modifyDate";
            }
            return SetDataFeedbackInfo(strSql);
        }

        private static List<MsInfoClientFeedbackInfoEntity> SetDataFeedbackInfo(String strSql)
        {
            var headList = new List<MsInfoClientFeedbackInfoEntity>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    MsInfoClientFeedbackInfoEntity data = new MsInfoClientFeedbackInfoEntity();
                    #region Set DB data to Object
                    data.ID = (reader["ID"] == null ? 0 : Convert.ToInt32(reader["ID"]));//ID
                    data.FEEDBACKID = (reader["FEEDBACKID"] == null ? "" : Convert.ToString(reader["FEEDBACKID"]));//FEEDBACKID
                    data.REMARKID = (reader["REMARKID"] == null ? "" : Convert.ToString(reader["REMARKID"]));//REMARKID
                    data.REMARKTYPE = (reader["REMARKTYPE"] == null ? false : Convert.ToBoolean(reader["REMARKTYPE"]));//REMARKTYPE
                    data.REMARK = (reader["REMARK"] == null ? "" : Convert.ToString(reader["REMARK"]));//.Replace("<br>", "\r\n");
                    data.CREATER = (reader["CREATER"] == null ? "" : Convert.ToString(reader["CREATER"]));//CREATER
                    //data.CREATEDATE = (reader["CREATEDATE"] == null ? "" : Convert.ToString(reader["CREATEDATE"]));//CREATEDATE
                    if (reader["CREATEDATE"] != null && reader["CREATEDATE"].ToString().Trim().IndexOf("0001") < 0 && reader["CREATEDATE"].ToString().Trim().IndexOf("1900") < 0 && reader["CREATEDATE"].ToString().Trim() != "")
                    {
                        data.CREATEDATE = Convert.ToDateTime(reader["CREATEDATE"]);
                    }
                    data.MODIFYUSER = (reader["MODIFYUSER"] == null ? "" : Convert.ToString(reader["MODIFYUSER"]));//MODIFYUSER
                    //data.MODIFYDATE = (reader["MODIFYDATE"] == null ? "" : Convert.ToString(reader["MODIFYDATE"]));//MODIFYDATE
                    if (reader["MODIFYDATE"] != null && reader["MODIFYDATE"].ToString().Trim().IndexOf("0001") < 0 && reader["MODIFYDATE"].ToString().Trim().IndexOf("1900") < 0 && reader["MODIFYDATE"].ToString().Trim() != "")
                    {
                        data.MODIFYDATE = Convert.ToDateTime(reader["MODIFYDATE"]);
                    }
                    data.REMARKTYPENAME = (reader["REMARKTYPENAME"] == null ? "" : Convert.ToString(reader["REMARKTYPENAME"]));//REMARKTYPENAME
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        static public MsInfoClientFeedbackInfoEntity GetDataFeedbackInfo(string strCondition)
        {
            String strSql = "SELECT *,REMARKTYPENAME=(case when REMARKTYPE=0 then '问' else '答' end) FROM [FeedbackInfo] where 1=1 ";
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql += " and " + strCondition;
            }
            var list = SetDataFeedbackInfo(strSql);
            if (list.Count > 0)
            {
                return list[0];
            }
            return new MsInfoClientFeedbackInfoEntity();
        }
        #endregion

        #region 取得客户的维护类型（年、次结）
        static public String GetSTLNAME(string LINKGID)
        {
            T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
            String strSql = "select top 1 STLNAME from info_client where GID='" + LINKGID + "'";
            string sSTLNAME = T_ALL_DA.GetStrSQL("STLNAME", strSql.ToString());
            return sSTLNAME;
        }
        #endregion 
      
        #region 删除
        public static DBResult Delete(VW_Feedback_InfoEntity headData)
        {
            DBResult result = new DBResult();
            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                try
                {
                    var cmdDelete = db.GetSqlStringCommand("delete from [FeedbackInfo] where feedbackID='" + headData.FEEDBACKID + "'");
                    db.ExecuteNonQuery(cmdDelete, tran);

                    cmdDelete = db.GetSqlStringCommand("delete from [Feedback] where feedbackID='" + headData.FEEDBACKID + "'");
                    db.ExecuteNonQuery(cmdDelete, tran);

                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();
                    result.Success = false;
                    result.Message = "删除出现错误，请重试";
                    return result;
                }
            }
            result.Success = true;
            result.Message = "操作成功";
            return result;
        }
        #endregion
      
        #region 删除明细列表
        public static DBResult DeleteFeedbackInfo(MsInfoClientFeedbackInfoEntity headData)
        {
            DBResult result = new DBResult();
            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                try
                {
                    var cmdDelete = db.GetSqlStringCommand("delete from [FeedbackInfo] where REMARKID='" + headData.REMARKID + "'");
                    db.ExecuteNonQuery(cmdDelete, tran);
                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();
                    result.Success = false;
                    result.Message = "删除出现错误，请重试";
                    return result;
                }
            }
            result.Success = true;
            result.Message = "操作成功";
            return result;
        }
        #endregion
        
        #region 权限范围
        public static string GetRangDAStr(string tb, string userid, string username, string companyid)
        {
            string str = "";
            var strSql = new StringBuilder();
            strSql.Append("SELECT VISIBLERANGE,OPERATERANGE from VW_User_Authority where [NAME]='modMsInfoClientFeedback' and USERID='" + userid + "' and ISDELETE=0");

            string visiblerange = "4";
            string operaterange = "4";
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    visiblerange = Convert.ToString(reader["VISIBLERANGE"]);
                    operaterange = Convert.ToString(reader["OPERATERANGE"]);
                    break;
                }
                reader.Close();
            }
            if (visiblerange == "4")
            {
                str = "1<0";
            }
            else if (visiblerange == "3")
            {
                str = " (companyID in(select SHORTNAME from info_client where SALE='" + username + "' or OP='" + username + "' or DOC='" + username + "') or companyID='')";
            }
            else if (visiblerange == "2")
            {
                str = " (companyID in(select SHORTNAME from info_client where SALE='" + username + "' or OP='" + username + "' or DOC='" + username + "') or companyID='')";
            }
            else if (visiblerange == "1")
            {
                str = "";
            }
            else
            {
                str = "";
            }
            if (str != "")
            {
                str = "(" + str + ")";
            }
            return str;
        }
        #endregion
      
        #region 提交
        public static DBResult onEnter(VW_Feedback_InfoEntity headData)
        {
            DBResult result = new DBResult();
            Database db = DatabaseFactory.CreateDatabase();
            using (var conn = db.CreateConnection())
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                try
                {
                    var cmdDelete = db.GetSqlStringCommand("update [Feedback] set status=3 where feedbackID='" + headData.FEEDBACKID + "'");
                    db.ExecuteNonQuery(cmdDelete, tran);
                    tran.Commit();
                }
                catch (Exception)
                {
                    tran.Rollback();
                    result.Success = false;
                    result.Message = "操作出现错误，请重试";
                    return result;
                }
            }
            result.Success = true;
            result.Message = "操作成功";
            return result;
        }
        #endregion
    }
}