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 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 SetData(String strSql) { var headList = new List(); 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 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 SetDataFeedbackInfo(String strSql) { var headList = new List(); 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("
", "\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 } }