private string GetCommunityList(DataRow row) { if (!row.Table.Columns.Contains("CorpID") || string.IsNullOrEmpty(row["CorpID"].ToString())) { return(JSONHelper.FromString(false, "公司编号不能为空")); } using (IDbConnection conn = new SqlConnection(Global_Fun.Tw2bsConnectionString("1"))) { dynamic dbInfo = conn.Query(@"SELECT DBServer,DBName,DBUser,DBPwd FROM Tb_System_Corp WHERE CorpID=@CorpID", new { CorpID = row["CorpID"].ToString() }).FirstOrDefault(); if (dbInfo != null) { Tb_Community community = new Tb_Community() { DBServer = dbInfo.DBServer, DBName = dbInfo.DBName, DBUser = dbInfo.DBUser, DBPwd = dbInfo.DBPwd }; using (IDbConnection conn2 = new SqlConnection(GetConnectionStringStr(community))) { IEnumerable <dynamic> resultSet = conn2.Query(@"SELECT CommID,CommName FROM Tb_HSPR_Community WHERE isnull(IsDelete,0)=0"); return(new ApiResult(true, resultSet).toJson()); } } return(JSONHelper.FromString(false, "未查询到公司信息")); } }
/// <summary> /// 获取建议 /// </summary> /// <param name="row">评价ID</param> /// 返回: /// SuggestionsID 建议ID /// CommID 小区ID /// CustID 客户ID /// RoomID 房间ID /// RoomSign 房间编号 /// CustName 客户名称 /// LinkPhone 联系方式 /// SuggestionsType 建议类型 /// SuggestionsTitle 建议标题 /// SuggestionsImages 建议图片 /// IsDelete 是否删除 /// SuggestionsContent 建议内容 /// CustEvaluation 评价内容 /// ReplyStats 回复状态 /// ReplyContent 回复内容 /// ReplyDate 回复日期 /// EvaluationDate 评价日期 /// EvaluationLevel 评价级别 /// <returns></returns> private string GetProposalInfo(DataRow row) { if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(JSONHelper.FromString(false, "小区编码不能为空")); } if (!row.Table.Columns.Contains("Id") || string.IsNullOrEmpty(row["Id"].ToString())) { return(JSONHelper.FromString(false, "建议ID不能为空")); } string Id = row["Id"].ToString(); string CommunityId = row["CommunityId"].ToString(); //运营连接字符串 IDbConnection Conn = new SqlConnection(Connection.GetConnection("4")); //获取小区信息 string sql_com = "select * from dbo.Tb_Community where Id=@Id"; Tb_Community Community = Conn.Query <Tb_Community>(sql_com, new { Id = CommunityId }).SingleOrDefault(); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } Conn = new SqlConnection(new CostInfo().GetConnectionStringStr(Community)); string query = "SELECT * FROM Tb_HSPR_CommunitySuggestions WHERE SuggestionsID='" + Id + "'"; DataTable dt = Conn.ExecuteReader(query, null, null, null, CommandType.Text).ToDataSet().Tables[0]; return(JSONHelper.FromString(dt)); }
/// <summary> /// 获取物管运营平台商城、物管商城已有商品分类列表 /// </summary> private List <PMSAppGoodsCategoryModel> GetPropertyStoreGoodsCategory(Tb_Community community) { using (var conn = new SqlConnection(PubConstant.BusinessContionString)) { var sql = @"SELECT ResourcesTypeID AS TypeID,ResourcesTypeName AS TypeName,ResourcesTypeImgUrl AS Icon,Remark FROM Tb_Resources_Type WHERE (isnull(CorpID,1000)=1000 OR CorpID=@CorpID) AND isnull(IsDelete,0)=0 AND ResourcesTypeID IN ( SELECT ResourcesTypeID FROM Tb_Resources_Details WHERE IsRelease='是' AND isnull(IsStopRelease,'否')='否' AND isnull(IsDelete,0)=0 AND BussId IN ( SELECT BussId FROM Tb_System_BusinessCorp WHERE isnull(BussNature,'周边商家') IN('平台商城','物管商城') AND isnull(IsDelete,0)=0 AND BussId IN ( SELECT BussId FROM Tb_System_BusinessConfig WHERE CommunityId=@CommunityId UNION ALL SELECT BussId FROM Tb_System_BusinessCorp_Config WHERE CorpId=@CorpID ) ) ) ORDER BY ResourcesTypeIndex;"; var data = conn.Query <PMSAppGoodsCategoryModel>(sql, new { CorpID = community.CorpID, CommunityId = community.Id }).ToList(); data.ForEach(obj => obj.Icon = obj.Icon?.Trim(',')); return(data); } }
/// <summary> /// 添加或更新家庭成员信息 /// </summary> private long AddHouseholdInfo(Tb_Community community, long custId, long roomId, string userName, string mobile, string relationShip, string memo = "业主App手动绑定房屋") { using (var conn = new SqlConnection(GetConnectionStr(community))) { var sql = @"DECLARE @HoldID bigint; DECLARE @RelationID int; DECLARE @MemberCode bigint; IF @Relationship='0013' OR @Relationship='0029' BEGIN SET @RelationID=1; SET @MemberCode=@CustID; END SELECT TOP 1 @HoldID=HoldID FROM Tb_HSPR_Household WHERE CustID=@CustID AND RoomID=@RoomID AND (MobilePhone LIKE @Mobile OR LinkManTel LIKE @Mobile) ORDER BY IsDelete; IF @HoldID IS NULL OR @HoldID=0 BEGIN SELECT @HoldID=isnull(max(HoldID)+1, (@CommID*cast(100000000 AS BIGINT)+1)) FROM Tb_HSPR_Household WHERE CommID=@CommID; INSERT INTO Tb_HSPR_Household(HoldID,RelationID,CommID,CustID,RoomID,Surname,[Name], MobilePhone,Relationship,InquirePwd,Memo,MemberName,MemberCode, Linkman, LinkManTel,IsDelete,SynchFlag) VALUES(@HoldID,@RelationID,@CommID,@CustID,@RoomID,@Name,@Name,@MobilePhone,@Relationship,'123',@Memo, @Name,@MemberCode,@Name,@MobilePhone,0,0); END ELSE BEGIN UPDATE Tb_HSPR_Household SET CommID=@CommID,CustID=@CustID,RoomID=@RoomID, Surname=@Name,[Name]=@Name,MobilePhone=@MobilePhone,Relationship=@Relationship, Linkman=@Name,LinkManTel=@MobilePhone,MemberCode=@MemberCode,MemberName=@Name, IsDelete=0,SynchFlag=0 WHERE HoldID=@HoldID; END --更新电话表 EXEC Proc_HSPR_Telephone_InsUpate @CommID, @CustID, @HoldID,1; SELECT @HoldID;"; var holdId = conn.Query <long>(sql, new { CommID = community.CommID, RoomID = roomId, CustID = custId, Mobile = $"%{mobile}%", MobilePhone = mobile, Relationship = relationShip, Name = userName, Memo = memo }).FirstOrDefault(); return(holdId); } }
public static string GetConnectionString(Tb_Community T) { string strDBServer = T.DBServer.ToString(); string strDBName = T.DBName.ToString(); string strDBUser = T.DBUser.ToString(); string strDBPwd = T.DBPwd.ToString(); string ConnStr = string.Format("Connect Timeout=100;Connection Lifetime=60;Max Pool Size=1000;Min Pool Size=0;Pooling=true;data source={0};initial catalog={1};PWD={3};persist security info=False;user id={2};packet size=4096", strDBServer, strDBName, strDBUser, strDBPwd); return(ConnStr); }
/// <summary> /// 获取所有建议 /// </summary> /// <param name="row"></param> /// 用户编号:UserId 必填 /// 小区编号:CommunityId 必填 /// <returns></returns> private string GetProposalInfoList(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(JSONHelper.FromString(false, "用户编号不能为空")); } if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(JSONHelper.FromString(false, "小区编号不能为空")); } string CustID = row["UserId"].ToString(); string CommunityId = row["CommunityId"].ToString(); //运营连接字符串 IDbConnection Conn = new SqlConnection(Connection.GetConnection("4")); //获取小区信息 string sql_com = "select * from dbo.Tb_Community where Id=@Id"; Tb_Community Community = Conn.Query <Tb_Community>(sql_com, new { Id = CommunityId }).SingleOrDefault(); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } //获取绑定的房屋 string sql_user = "******"; List <Tb_User_Relation> User_Relation = Conn.Query <Tb_User_Relation>(sql_user, new { UserId = CustID, CommunityId = CommunityId }).ToList <Tb_User_Relation>(); if (User_Relation == null || User_Relation.Count <= 0) { return(JSONHelper.FromString(false, "未绑定任何小区")); } List <Tb_HSPR_CommunitySuggestions> model = new List <Tb_HSPR_CommunitySuggestions>(); DataTable dt = new DataTable(); foreach (Tb_User_Relation item in User_Relation) { Conn = new SqlConnection(new CostInfo().GetConnectionStringStr(Community)); string query = "select * from Tb_HSPR_CommunitySuggestions where CustID=" + item.CustId + " and CommID=" + Community.CommID + " and IsDelete=0 order by IssueDate desc"; if (dt.Rows.Count == 0) { dt = Conn.ExecuteReader(query, null, null, null, CommandType.Text).ToDataSet().Tables[0].Copy(); } else { dt.Merge(Conn.ExecuteReader(query, null, null, null, CommandType.Text).ToDataSet().Tables[0].Copy()); } Conn.Dispose(); } return(JSONHelper.FromString(dt)); }
/// <summary> /// 提交评价 /// </summary> /// 此处的评价不能小于等于0,在实际操作中业主可能不会具体去点几星,建议在这种情况时给一个默认值。 /// <param name="row">评价ID、评价等级</param> /// <returns></returns> private string SetEvaluation(DataRow row) { if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(JSONHelper.FromString(false, "小区编码不能为空")); } if (!row.Table.Columns.Contains("Id") || string.IsNullOrEmpty(row["Id"].ToString())) { return(JSONHelper.FromString(false, "建议ID不能为空")); } if (!row.Table.Columns.Contains("EvaluationLevel") || AppGlobal.StrToInt(row["EvaluationLevel"].ToString()) <= 0) { return(JSONHelper.FromString(false, "评价等级不能为空或者小于等于0")); } string Id = row["Id"].ToString(); string CommunityId = row["CommunityId"].ToString(); string ReplyContent = ""; if (row.Table.Columns.Contains("ReplyContent") && !string.IsNullOrEmpty(row["ReplyContent"].ToString())) { ReplyContent = row["ReplyContent"].ToString(); } //运营连接字符串 IDbConnection Conn = new SqlConnection(Connection.GetConnection("4")); //获取小区信息 string sql_com = "select * from dbo.Tb_Community where Id=@Id"; Tb_Community Community = Conn.Query <Tb_Community>(sql_com, new { Id = CommunityId }).SingleOrDefault(); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } Conn = new SqlConnection(new CostInfo().GetConnectionStringStr(Community)); string query = "SELECT * FROM Tb_HSPR_CommunitySuggestions WHERE SuggestionsID=@Id"; Tb_HSPR_CommunitySuggestions model = Conn.Query <Tb_HSPR_CommunitySuggestions>(query, new { Id = Id }).SingleOrDefault(); model.EvaluationLevel = Convert.ToInt32(row["EvaluationLevel"]); model.CustEvaluation = ReplyContent; model.EvaluationDate = DateTime.Now; Conn.Update <Tb_HSPR_CommunitySuggestions>(model); //new MobileSoft.BLL.Unified.Bll_Tb_User_Recommended().Update(model); return(JSONHelper.FromString(true, "提交服务评价成功")); }
private string GetApplyInfo(DataRow row) { if (!row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(row["UserID"].ToString())) { return(JSONHelper.FromString(false, "用户id不能为空")); } if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString())) { return(JSONHelper.FromString(false, "小区编号不能为空")); } if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString())) { return(JSONHelper.FromString(false, "房屋编号不能为空")); } if (!row.Table.Columns.Contains("InfoID") || string.IsNullOrEmpty(row["RoomID"].ToString())) { return(JSONHelper.FromString(false, "房屋编号不能为空")); } string UserID = row["UserID"].ToString(); int CommID = AppGlobal.StrToInt(row["CommID"].ToString()); long RoomID = AppGlobal.StrToLong(row["RoomID"].ToString()); string InfoID = row["InfoID"].ToString(); //查询小区 Tb_Community Community = GetCommunityByCommID(row["CommID"].ToString()); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } Global_Var.CorpId = Community.CorpID.ToString(); Global_Var.CorpID = Community.CorpID.ToString(); Global_Var.LoginCorpID = Community.CorpID.ToString(); PubConstant.hmWyglConnectionString = GetConnectionStr(Community); using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString)) { var sql = @"SELECT convert(nvarchar(36),a.ActivitiesPersonId) AS IID,a.ActivitiesId AS InfoID,a.CustName, b.ActivitiesStartDate AS StartDate,b.ActivitiesEndDate AS EndDate, a.RoomId,a.RoomSign,a.LinkPhone,a.SignUpCount,a.Score FROM Tb_HSPR_CommActivitiesPerson_New a LEFT JOIN Tb_HSPR_CommActivities_New b ON a.ActivitiesId=b.ActivitiesID WHERE a.ActivitiesId=@ActivitiesId AND a.RoomId=@RoomId AND isnull(a.IsDelete,0)=0"; var applyInfo = erpConn.Query(sql, new { ActivitiesId = InfoID, RoomId = RoomID }).FirstOrDefault(); return(new ApiResult(true, applyInfo).toJson()); } }
private string DeleteInfo(DataRow row) { if (!row.Table.Columns.Contains("CommunityId")) { return(new ApiResult(false, "缺少参数CommunityId").toJson()); } if (!row.Table.Columns.Contains("InfoId")) { return(new ApiResult(false, "缺少参数InfoId").toJson()); } if (!row.Table.Columns.Contains("UserId")) { return(new ApiResult(false, "缺少参数UserId").toJson()); } string communityId = row["CommunityId"].ToString(); string InfoId = row["InfoId"].ToString(); string UserId = row["UserId"].ToString(); // 获取数据库连接字符串 Tb_Community tb_Community = GetCommunity(communityId); if (null == tb_Community) { return(new ApiResult(false, "该小区不存在").toJson()); } string connStr = GetConnectionStringStr(tb_Community); using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { // 需要验证手机号是否一致 string mobile = conn.Query <string>(@"SELECT Mobile FROM Tb_User WHERE Id=@UserID", new { UserID = UserId }).FirstOrDefault(); if (!string.IsNullOrEmpty(mobile)) { using (IDbConnection conn2 = new SqlConnection(connStr)) { if (conn2.Execute(@"UPDATE Tb_HSPR_CommActivities SET IsDelete=1 WHERE ActivitiesID=@ID AND LinkPhone=@Mobile", new { ID = InfoId, Mobile = mobile }) > 0) { return(JSONHelper.FromString(true, "删除成功")); } } } return(JSONHelper.FromString(false, "删除失败")); } }
private string GetCommunityActivity(DataRow row) { if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString())) { return(JSONHelper.FromString(false, "小区编号不能为空")); } int pageSize = 10; int pageIndex = 1; if (row.Table.Columns.Contains("PageSize") && !string.IsNullOrEmpty(row["PageSize"].ToString())) { pageSize = AppGlobal.StrToInt(row["PageSize"].ToString()); } if (row.Table.Columns.Contains("PageIndex") && !string.IsNullOrEmpty(row["PageIndex"].ToString())) { pageIndex = AppGlobal.StrToInt(row["PageIndex"].ToString()); } string CommID = row["CommID"].ToString(); //查询小区 Tb_Community Community = GetCommunityByCommID(CommID); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } Global_Var.CorpId = Community.CorpID.ToString(); Global_Var.CorpID = Community.CorpID.ToString(); Global_Var.LoginCorpID = Community.CorpID.ToString(); PubConstant.hmWyglConnectionString = GetConnectionStr(Community); using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString)) { var sql = $@"SELECT convert(NVARCHAR(36),ActivitiesId) AS InfoID,ActivitiesTheme AS Heading, CreateTime AS IssueDate,ActivitiesImages AS ImageUrl,2 AS Type, CASE WHEN ActivitiesEndDate>getdate() THEN 0 ELSE 1 END AS IsEnd, ActivitiesStartDate AS StartDate, ActivitiesEndDate AS EndDate FROM Tb_HSPR_CommActivities_New WHERE CommID={Community.CommID} AND isnull(IsDelete,0)=0 "; var result = GetListDapper(out int pageCount, out int count, sql, pageIndex, pageSize, "IssueDate", 1, "InfoID", conn); return(new ApiResult(true, result).toJson()); } }
/// <summary> /// 添加租户信息 /// </summary> /// <returns></returns> private bool Proc_AddTenantInfo(Tb_Community tb_Community, string RoomId, out string CustID) { CustID = ""; if (null == tb_Community) { return(false); } if (string.IsNullOrEmpty(RoomId)) { return(false); } //获取ERP连接字符串 string connStr = GetConnectionStringStr(tb_Community); using (IDbConnection conn = new SqlConnection(connStr)) { //生成CustID CustID = conn.Query <string>("SELECT ISNULL(max(CustID) + 1, @CommID * CAST(100000000 AS BIGINT) + 1) AS CustID FROM Tb_HSPR_Customer WHERE CommID = @CommID", new { CommID = tb_Community.CommID }).FirstOrDefault(); if (string.IsNullOrEmpty(CustID)) { return(false); } //生成LiveID string LiveID = conn.Query <string>("SELECT (max(LiveID) + 1) AS CustID FROM Tb_HSPR_CustomerLive", new { }).FirstOrDefault(); if (string.IsNullOrEmpty(LiveID)) { return(false); } dynamic info = conn.Query("SELECT RoomSign, BuildArea FROM Tb_HSPR_Room WHERE ISNULL(IsDelete,0) = 0 AND CommID = @CommID AND RoomId = @RoomId", new { CommID = tb_Community.CommID, RoomId = RoomId }).FirstOrDefault(); if (null == info) { return(false); } string RoomSign = info.RoomSign; decimal BuildArea = info.BuildArea; if (string.IsNullOrEmpty(RoomSign)) { return(false); } RoomSign = RoomSign + "<font color=red>[" + BuildArea.ToString("#0.00") + "]</font><font color=green>[正常入住]</font>"; if (conn.Execute("INSERT INTO Tb_HSPR_CustomerLive(LiveID, RoomID, CustID, LiveType, IsActive, IsDelLive) VALUES(@LiveID, @RoomId, @CustID, 2, 1, 0)", new { LiveID = LiveID, RoomId = RoomId, CustID = CustID }) == 0) { return(false); } return(true); } }
private string CancelApply(DataRow row) { if (!row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(row["UserID"].ToString())) { return(JSONHelper.FromString(false, "用户id不能为空")); } if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString())) { return(JSONHelper.FromString(false, "小区编号不能为空")); } if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString())) { return(JSONHelper.FromString(false, "房屋编号不能为空")); } if (!row.Table.Columns.Contains("InfoID") || string.IsNullOrEmpty(row["RoomID"].ToString())) { return(JSONHelper.FromString(false, "房屋编号不能为空")); } string UserID = row["UserID"].ToString(); int CommID = AppGlobal.StrToInt(row["CommID"].ToString()); long RoomID = AppGlobal.StrToLong(row["RoomID"].ToString()); string InfoID = row["InfoID"].ToString(); //查询小区 Tb_Community Community = GetCommunityByCommID(row["CommID"].ToString()); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } Global_Var.CorpId = Community.CorpID.ToString(); Global_Var.CorpID = Community.CorpID.ToString(); Global_Var.LoginCorpID = Community.CorpID.ToString(); PubConstant.hmWyglConnectionString = GetConnectionStr(Community); using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString)) { var sql = @"UPDATE Tb_HSPR_CommActivitiesPerson_New SET IsDelete=1,DelTime=getdate() WHERE ActivitiesId=@ActivitiesId AND RoomId=@RoomId AND isnull(IsDelete,0)=0"; var i = erpConn.Execute(sql, new { ActivitiesId = InfoID, RoomId = RoomID }); return(new ApiResult(true, "取消报名成功").toJson()); } }
private string GetCommunityNotice(DataRow row) { if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString())) { return(JSONHelper.FromString(false, "小区编号不能为空")); } string CommID = row["CommID"].ToString(); //查询小区 Tb_Community Community = GetCommunityByCommID(CommID); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } Global_Var.CorpId = Community.CorpID.ToString(); Global_Var.CorpID = Community.CorpID.ToString(); Global_Var.LoginCorpID = Community.CorpID.ToString(); PubConstant.hmWyglConnectionString = GetConnectionStr(Community); using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString)) { var sql = @"SELECT TOP 5 * FROM ( SELECT convert(NVARCHAR(36),InfoID) AS InfoID,Heading,IssueDate,ImageUrl,1 AS Type,0 AS IsEnd, NULL AS StartDate,NULL AS EndDate FROM Tb_HSPR_CommunityInfo WHERE CommID=@CommID AND InfoType IN('qqts','sqwh','dtzx') AND isnull(ShowEndDate,'9999')>getdate() AND isnull(IsDelete,0)=0 UNION ALL SELECT convert(NVARCHAR(36),ActivitiesId) AS InfoID,ActivitiesTheme AS Heading, CreateTime AS IssueDate,ActivitiesImages AS ImageUrl,2 AS Type, CASE WHEN ActivitiesEndDate>getdate() THEN 0 ELSE 1 END AS IsEnd, ActivitiesStartDate AS StartDate, ActivitiesEndDate AS EndDate FROM Tb_HSPR_CommActivities_New WHERE CommID=@CommID AND isnull(IsDelete,0)=0 ) AS a ORDER BY a.IssueDate DESC;"; return(new ApiResult(true, conn.Query(sql, new { CommID = CommID })).toJson()); } }
/// <summary> /// 获取人员详情 /// </summary> /// <param name="row"></param> /// <returns></returns> private string GetServicePersonDetail(DataRow row) { if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(new ApiResult(false, "请选择小区").toJson()); } string communityId = row["CommunityId"].ToString(); if (!row.Table.Columns.Contains("PersonID") || string.IsNullOrEmpty(row["PersonID"].ToString())) { return(new ApiResult(false, "请选择评价人员").toJson()); } string personID = row["PersonID"].ToString(); Tb_Community tb_Community = GetCommunity(communityId); if (null == tb_Community) { return(new ApiResult(false, "该小区不存在").toJson()); } string connStr = GetConnectionStringStr(tb_Community); Dictionary <string, object> personDic = new Dictionary <string, object>(); // 先获取人员基本信息 using (IDbConnection conn = new SqlConnection(connStr)) { DataTable dt = conn.ExecuteReader("SELECT * FROM view_HSPR_ServPerson_Filter WHERE CommID = @CommID AND PersonID = @PersonID", new { CommID = tb_Community.CommID, PersonID = personID }, null, null, CommandType.Text).ToDataSet().Tables[0]; if (null == dt || dt.Rows.Count == 0) { return(new ApiResult(false, "该人员不存在").toJson()); } DataRow personRow = dt.Rows[0]; foreach (DataColumn column in dt.Columns) { personDic.Add(column.ColumnName, personRow[column.ColumnName]); } } return(new ApiResult(true, personDic).toJson()); }
private string AgreeComment(DataRow row) { if (!row.Table.Columns.Contains("CommunityId")) { return(new ApiResult(false, "缺少参数CommunityId").toJson()); } if (!row.Table.Columns.Contains("EvaluationId")) { return(new ApiResult(false, "缺少参数EvaluationId").toJson()); } if (!row.Table.Columns.Contains("UserId")) { return(new ApiResult(false, "缺少参数UserId").toJson()); } if (!row.Table.Columns.Contains("Agree")) { return(new ApiResult(false, "缺少参数Agree").toJson()); } string communityId = row["CommunityId"].ToString(); string EvaluationId = row["EvaluationId"].ToString(); string UserId = row["UserId"].ToString(); int Agree = AppGlobal.StrToInt(row["Agree"].ToString()); Tb_Community tb_Community = GetCommunity(communityId); if (null == tb_Community) { return(new ApiResult(false, "该小区不存在").toJson()); } string connStr = GetConnectionStringStr(tb_Community); using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { conn.Execute("Proc_CommuityEvaluation_AgreeOppose_Insert", new { ID = "", UserId = UserId, EvaluationId = EvaluationId, IsAgree = Agree }, null, null, CommandType.StoredProcedure); return(JSONHelper.FromString(true, "操作成功")); } }
private string GetServicePersonList(DataRow row) { if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(new ApiResult(false, "请选择小区").toJson()); } string communityId = row["CommunityId"].ToString(); Tb_Community tb_Community = GetCommunity(communityId); if (null == tb_Community) { return(new ApiResult(false, "该小区不存在").toJson()); } string connStr = GetConnectionStringStr(tb_Community); DataTable dt; using (IDbConnection conn = new SqlConnection(connStr)) { dt = conn.ExecuteReader("SELECT * FROM view_HSPR_ServPerson_Filter WHERE CommID = @CommID", new { CommID = tb_Community.CommID }, null, null, CommandType.Text).ToDataSet().Tables[0]; } List <Dictionary <string, object> > list = new List <Dictionary <string, object> >(); if (null != dt) { Dictionary <string, object> dictionary; foreach (DataRow item in dt.Rows) { dictionary = new Dictionary <string, object>(); foreach (DataColumn column in dt.Columns) { dictionary.Add(column.ColumnName, item[column.ColumnName]); } list.Add(dictionary); } } return(new ApiResult(true, list).toJson()); }
/// <summary> /// 提交建议 /// </summary> /// <param name="row">用户编号、小区编号、建议、客户编号均不能为空</param> /// <returns></returns> private string SubmitProposal(DataRow row) { if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString())) { return(JSONHelper.FromString(false, "用户编码不能为空")); } if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(JSONHelper.FromString(false, "小区编码不能为空")); } if (!row.Table.Columns.Contains("RecommendedContent") || string.IsNullOrEmpty(row["RecommendedContent"].ToString())) { return(JSONHelper.FromString(false, "建议不能为空")); } if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].ToString())) { return(JSONHelper.FromString(false, "客户编号不能为空")); } string UserId = row["UserId"].ToString(); string CommunityId = row["CommunityId"].ToString(); string RecommendedContent = row["RecommendedContent"].ToString(); string CustID = row["CustID"].ToString(); string SuggestionsImages = null; string RoomID = null; if (row.Table.Columns.Contains("SuggestionsImages") && !string.IsNullOrEmpty(row["SuggestionsImages"].ToString())) { SuggestionsImages = row["SuggestionsImages"].ToString(); } if (row.Table.Columns.Contains("RoomID") && string.IsNullOrEmpty(row["RoomID"].ToString())) { RoomID = row["RoomID"].ToString(); } //运营连接字符串 IDbConnection Conn = new SqlConnection(Connection.GetConnection("4")); //获取小区信息 string sql_com = "select * from dbo.Tb_Community where Id=@Id"; List <Tb_Community> Community_list = Conn.Query <Tb_Community>(sql_com, new { Id = CommunityId }).ToList <Tb_Community>(); if (Community_list == null || Community_list.Count <= 0) { return(JSONHelper.FromString(false, "该小区不存在")); } Tb_Community Community = Community_list[0]; //获取绑定信息 string sql_user = "******"; List <Tb_User_Relation> User_Relation = Conn.Query <Tb_User_Relation>(sql_user, new { UserId = UserId, CommunityId = CommunityId, CustId = CustID }).ToList <Tb_User_Relation>(); if (User_Relation == null || User_Relation.Count <= 0) { return(JSONHelper.FromString(false, "该业主没绑定房屋")); } //构建ERP字符串 Conn = new SqlConnection(new CostInfo().GetConnectionStringStr(Community)); Tb_HSPR_CommunitySuggestions model = new Tb_HSPR_CommunitySuggestions(); model.SuggestionsID = Guid.NewGuid().ToString(); model.CommID = Convert.ToInt64(Community.CommID); model.CustID = Convert.ToInt64(User_Relation[0].CustId); model.RoomID = Convert.ToInt64(RoomID ?? User_Relation[0].RoomId); model.RoomSign = User_Relation[0].RoomSign; model.CustName = User_Relation[0].CustName; model.LinkPhone = User_Relation[0].Custmobile; model.IssueDate = DateTime.Now; model.ReplyStats = "未回复"; model.SuggestionsType = "0002"; model.IsDelete = 0; model.SuggestionsContent = RecommendedContent; model.SuggestionsImages = SuggestionsImages; Conn.Insert <Tb_HSPR_CommunitySuggestions>(model); return(JSONHelper.FromString(true, "提交建议成功")); }
private string OnPay(DataRow row) { #region 获取参数 if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(new ApiResult(false, "缺少参数CommunityId").toJson()); } string CommunityId = row["CommunityId"].ToString(); if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].ToString())) { return(new ApiResult(false, "缺少参数CustID").toJson()); } long CustID = Convert.ToInt64(row["CustID"].ToString()); if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString())) { return(new ApiResult(false, "缺少参数RoomID").toJson()); } long RoomID = Convert.ToInt64(row["RoomID"].ToString()); if (!row.Table.Columns.Contains("PayData") || string.IsNullOrEmpty(row["PayData"].ToString())) { return(new ApiResult(false, "缺少参数PayData").toJson()); } string PayData = row["PayData"].ToString(); if (!row.Table.Columns.Contains("PayType") || string.IsNullOrEmpty(row["PayType"].ToString())) { return(new ApiResult(false, "缺少参数PayType").toJson()); } // 默认为微信支付 if (!int.TryParse(row["PayType"].ToString(), out int PayType) || (PayType != 0 && PayType != 1)) { PayType = 0; } #endregion #region 验证小区是否存在 Tb_Community tb_Community = GetCommunity(CommunityId); if (null == tb_Community) { return(new ApiResult(false, "该项目未在运营系统中配置").toJson()); } string erpConnStr = GetConnectionStr(tb_Community); #endregion #region 获取小区支付配置 dynamic payConfig; using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { payConfig = conn.QueryFirstOrDefault("SELECT * FROM Tb_CCBPay_Config WHERE CommunityId = @CommunityId", new { CommunityId = tb_Community.Id }); if (null == payConfig) { return(new ApiResult(false, "该小区未配置支付信息").toJson()); } } #endregion #region 检测支付数据格式 if (!CheckPayData(erpConnStr, CustID, RoomID, PayData, out decimal Amt, out string errMsg, true)) { return(new ApiResult(false, errMsg).toJson()); } if (Amt <= 0.00M) { return(new ApiResult(false, "订单已被支付或者支付金额小于0").toJson()); } #endregion DateTime DateNow = DateTime.Now; string OrderSN = DateNow.ToString("yyyyMMddHHmmssfff") + GetRandomCode(3); Dictionary <string, string> resultDic = new Dictionary <string, string>(); resultDic.Add("MERCHANTID", Convert.ToString(payConfig.MerchantId)); resultDic.Add("POSID", Convert.ToString(payConfig.PosId)); resultDic.Add("BRANCHID", Convert.ToString(payConfig.BranchId)); resultDic.Add("ORDERID", OrderSN); resultDic.Add("PAYMENT", Convert.ToString(Amt)); resultDic.Add("CURCODE", "01"); resultDic.Add("TXCODE", Convert.ToString(payConfig.TxCode)); resultDic.Add("REMARK1", ""); resultDic.Add("REMARK2", ""); resultDic.Add("TYPE", Convert.ToString(payConfig.Type)); string pub = Convert.ToString(payConfig.Pub); if (string.IsNullOrEmpty(pub) || pub.Length < 30) { log.Error("支付配置PUB有误:" + pub); return(new ApiResult(false, "支付配置PUB有误").toJson()); } else { pub = pub.Substring(pub.Length - 30, 30); } resultDic.Add("PUB", pub); resultDic.Add("GATEWAY", 0 == PayType ? "" : "UnionPay"); resultDic.Add("CLIENTIP", ""); resultDic.Add("REGINFO", ""); resultDic.Add("PROINFO", ""); resultDic.Add("REFERER", ""); resultDic.Add("THIRDAPPINFO", string.Format("comccbpay{0}{1}", Convert.ToString(payConfig.MerchantId), "hkccbpay")); string signStr = ""; foreach (var item in resultDic) { signStr += string.Format("{0}={1}&", item.Key, item.Value); } signStr = signStr.Remove(signStr.Length - 1, 1); signStr += "&MAC=" + AppPKI.getMd5Hash(signStr); #region 插入订单表 using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("PayConfigId", payConfig.Id); parameters.Add("OrderSN", OrderSN); parameters.Add("CustID", CustID); parameters.Add("RoomID", RoomID); parameters.Add("PayData", PayData); parameters.Add("Amt", Amt); parameters.Add("CreateTime", DateNow.ToString()); if (conn.Execute(@"INSERT INTO Tb_CCBPay_Order(PayConfigId, OrderSN, CustID, RoomID, PayData, Amt, CreateTime) VALUES(@PayConfigId, @OrderSN, @CustID, @RoomID, @PayData, @Amt, @CreateTime)", parameters) <= 0) { return(new ApiResult(false, "保存订单信息失败,请重试").toJson()); } } #endregion return(new ApiResult(true, signStr).toJson()); }
/// <summary> /// 手动绑定房屋 /// </summary> private string BindingRoom(Tb_Control_AppBindingRoom control, Tb_Community community, string userId, long custId, long roomId) { using (var appConn = new SqlConnection(PubConstant.UnifiedContionString)) { // 用户信息 var sql = @"SELECT Mobile, CASE WHEN Name IS NULL OR len(ltrim(rtrim(Name)))=0 THEN NickName ELSE Name END AS Name FROM Tb_User WHERE Id=@Id;"; var userInfo = appConn.Query(sql, new { Id = userId }).FirstOrDefault(); var userName = userInfo.Name; var userMobile = userInfo.Mobile; using (var erpConn = new SqlConnection(GetConnectionStr(community))) { // 房屋名称 sql = @"SELECT isnull(RoomName,isnull(RoomSign,'')) FROM Tb_HSPR_Room WHERE RoomID=@RoomID;"; var roomName = erpConn.Query <string>(sql, new { RoomID = roomId }).FirstOrDefault(); // 客户信息 sql = "SELECT CustID,CustName,MobilePhone,LinkmanTel FROM Tb_HSPR_Customer WHERE CustID=@CustID;"; var custInfo = erpConn.Query(sql, new { CustID = custId }).FirstOrDefault(); var relationShip = UserRoomIdentityCode.FamilyMember; var custName = custInfo.CustName; var custMobile = default(string); if (custInfo.CustMobile != null && custInfo.CustMobile.Contains(userMobile)) { custMobile = custInfo.CustMobile; relationShip = UserRoomIdentityCode.Customer2; } else if (custInfo.LinkmanTel != null && custInfo.LinkmanTel.Contains(userMobile)) { custMobile = custInfo.LinkmanTel; relationShip = UserRoomIdentityCode.Customer2; } // 绑定房屋 sql = @"IF exists(SELECT * FROM Tb_User_Relation WHERE UserId=@UserId AND CommunityId=@CommunityId AND RoomId=@RoomId) UPDATE Tb_User_Relation SET CustId=@CustId,CustName=@CustName,Locked=0 WHERE UserId=@UserId AND CommunityId=@CommunityId AND RoomId=@RoomID; ELSE BEGIN INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,CustHoldId,RoomId, RegDate,CustName,RoomSign,CustMobile,Locked) VALUES(@Id,@UserId,@CommunityId,@CustId,@CustHoldId,@RoomId,getdate(),@CustName,@RoomSign,@CustMobile,0); END;"; appConn.Execute(sql, new { Id = Guid.NewGuid().ToString(), UserId = userId, CommunityId = community.Id, CustId = custId, CustName = custName, CustMobile = custMobile, RoomId = roomId, RoomSign = roomName, CustHoldId = 0 }); return(JSONHelper.FromString(true, "绑定成功")); } } }
/// <summary> /// 获取用户对于某房屋的身份标识 /// </summary> private UserRoomIdentity GetUserIdentity(Tb_Community community, string userId, long roomId, out long custId, out long holdId) { custId = 0; holdId = 0; var userMobile = ""; using (var conn = new SqlConnection(PubConstant.UnifiedContionString)) { var sql = @"SELECT Mobile FROM Tb_User WHERE Id=@Id"; userMobile = conn.Query <string>(sql, new { Id = userId }).FirstOrDefault(); } using (var conn = new SqlConnection(GetConnectionStr(community))) { // 获取客户信息,可能是业主、租户、家属、临时客户,多个身份时,取最小 LiveType 或最大 HoldID 值 var sql = @"SELECT a.CustID,b.CustName,a.LiveType, ltrim(rtrim(isnull(b.MobilePhone,'')+','+isnull(b.LinkmanTel,''))) AS CustMobile FROM Tb_HSPR_CustomerLive a LEFT JOIN Tb_HSPR_Customer b ON a.CustID=b.CustID AND isnull(b.IsDelete,0)=0 WHERE a.RoomID=@RoomID AND isnull(a.IsDelLive,0)=0 AND (b.MobilePhone LIKE @Mobile OR b.LinkmanTel LIKE @Mobile) ORDER BY a.IsActive DESC,a.LiveType ASC; SELECT a.HoldID,a.CustID,c.CustName,a.Relationship, ltrim(rtrim(isnull(c.MobilePhone,'')+','+isnull(c.LinkmanTel,''))) AS CustMobile FROM Tb_HSPR_Household a LEFT JOIN Tb_HSPR_CustomerLive b ON a.CustID=b.CustID LEFT JOIN Tb_HSPR_Customer c ON a.CustID=c.CustID WHERE a.RoomID=@RoomID AND isnull(a.IsDelete,0)=0 AND isnull(b.IsDelLive,0)=0 AND isnull(c.IsDelete,0)=0 AND (a.MobilePhone LIKE @Mobile OR a.LinkmanTel LIKE @Mobile) ORDER BY HoldID DESC;"; var reader = conn.QueryMultiple(sql, new { RoomID = roomId, Mobile = $"%{userMobile}%" }); var ci1 = reader.Read <CustomerIdentity>().ToList(); var ci2 = reader.Read <CustomerIdentity>().ToList(); var liveType1 = UserRoomIdentity.Other; var liveType2 = UserRoomIdentity.Other; // 客户信息 var tmp = ci1.Find(obj => obj.CustMobile != null && obj.CustMobile.Contains(userMobile)); if (tmp != null) { custId = tmp.CustID; if (tmp.LiveType == 3) { liveType1 = UserRoomIdentity.Tenant; } if (tmp.LiveType == 2) { liveType1 = UserRoomIdentity.FamilyMember; } if (tmp.LiveType == 1) { liveType1 = UserRoomIdentity.Customer; } } // 家属信息 tmp = ci2.Find(obj => obj.CustMobile != null && obj.CustMobile.Contains(userMobile)); if (tmp != null) { custId = tmp.CustID; if (tmp.Relationship == UserRoomIdentityCode.Tenant) { liveType2 = UserRoomIdentity.Tenant; } if (tmp.Relationship == UserRoomIdentityCode.FamilyMember) { liveType2 = UserRoomIdentity.FamilyMember; } if (tmp.Relationship == UserRoomIdentityCode.Customer1 || tmp.Relationship == UserRoomIdentityCode.Customer2) { liveType2 = UserRoomIdentity.Customer; } // 当客户身份与 UserRoomIdentityCode 中定义的不符时,默认为家属 if (liveType2 == UserRoomIdentity.Other) { liveType2 = UserRoomIdentity.FamilyMember; } } return((UserRoomIdentity)Math.Min((int)liveType1, (int)liveType2)); } }
/// <summary> /// 投诉建议列表 /// </summary> private string SaveSuggestion(DataRow row) { if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString())) { return(JSONHelper.FromString(false, "小区编号不能为空")); } if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].ToString())) { return(JSONHelper.FromString(false, "客户编号不能为空")); } if (!row.Table.Columns.Contains("Content") || string.IsNullOrEmpty(row["Content"].ToString())) { return(JSONHelper.FromString(false, "报事内容不能为空")); } if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString())) { return(JSONHelper.FromString(false, "房间编号不能为空")); } if (!row.Table.Columns.Contains("Phone") || string.IsNullOrEmpty(row["Phone"].ToString())) { return(JSONHelper.FromString(false, "联系方式不能为空")); } string Content = row["Content"].ToString(); string CommID = row["CommID"].ToString(); string CustID = row["CustID"].ToString(); string RoomID = row["RoomID"].ToString(); string Phone = row["Phone"].ToString(); string Imgs = ""; if (row.Table.Columns.Contains("Imgs") && !string.IsNullOrEmpty(row["Imgs"].ToString())) { Imgs = row["Imgs"].ToString(); } //查询小区 Tb_Community Community = GetCommunityByCommID(CommID); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } Global_Var.CorpId = Community.CorpID.ToString(); Global_Var.CorpID = Community.CorpID.ToString(); Global_Var.LoginCorpID = Community.CorpID.ToString(); PubConstant.hmWyglConnectionString = GetConnectionStr(Community); Regex regex = new Regex(@"(initial catalog = [^;]+);"); if (regex.IsMatch(PubConstant.hmWyglConnectionString)) { PubConstant.tw2bsConnectionString = regex.Replace(PubConstant.hmWyglConnectionString, @"initial catalog = tw2_bs;"); } using (IDbConnection conn = new SqlConnection(PubConstant.hmWyglConnectionString)) { var custName = conn.Query <string>(@"SELECT CustName FROM Tb_HSPR_Customer WHERE CustID=@CustID", new { CustID = CustID }).FirstOrDefault(); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@CommID", Community.CommID); parameters.Add("@CustID", CustID); parameters.Add("@RoomID", RoomID); parameters.Add("@IncidentDate", DateTime.Now); parameters.Add("@IncidentMan", custName); parameters.Add("@IncidentContent", Content); parameters.Add("@ReserveDate", null); parameters.Add("@Phone", Phone); parameters.Add("@IncidentImgs", Imgs); parameters.Add("@TypeID", null); parameters.Add("@DealLimit", null); parameters.Add("@EmergencyDegree", 0); parameters.Add("@IncidentMode", "App爱我家园"); parameters.Add("@IncidentID", 0, DbType.Int64, ParameterDirection.Output); //需求4067 业主APP填报爱我家园信息时,自动转为工单(incidentaccept表数据),默认将报事类别设定为:投诉-物业管理-综合服务类 conn.Execute("Proc_HSPR_IncidentAccept_PhoneInsert", parameters, null, null, CommandType.StoredProcedure); //获取当前报事 string str = "SELECT * FROM Tb_HSPR_IncidentAccept WHERE IncidentID=@IncidentID"; dynamic model = conn.Query(str, new { IncidentID = parameters.Get <long>(@"IncidentID") }).LastOrDefault(); //zw增加model非空验证 if (model != null) { IncidentAcceptPush.SynchPushIndoorIncidentWithoutIncidentType(new Tb_HSPR_IncidentAccept() { TypeID = model.TypeID, CommID = model.CommID, RoomID = model.RoomID, IncidentID = model.IncidentID, IncidentPlace = model.IncidentPlace, IncidentMan = model.IncidentMan, }); } return(JSONHelper.FromString(true, "提交成功")); } }
/// <summary> /// 投诉建议列表 /// </summary> private string GetSuggestionSimpleList(DataRow row) { if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString())) { return(new ApiResult(false, "小区编号不能为空").toJson()); } if (!row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(row["UserID"].ToString())) { return(new ApiResult(false, "用户ID不能为空").toJson()); } string commID = row["CommID"].AsString(); string userID = row["UserID"].AsString(); int page = 1; int size = 10; if (!row.Table.Columns.Contains("PageIndex") || string.IsNullOrEmpty(row["PageIndex"].ToString())) { page = 1; } else { if (!int.TryParse(row["PageIndex"].AsString(), out page)) { page = 1; } } if (!row.Table.Columns.Contains("PageSize") || string.IsNullOrEmpty(row["PageSize"].ToString())) { size = 5; } else { if (!int.TryParse(row["PageSize"].AsString(), out size)) { size = 5; } } //查询小区 Tb_Community Community = GetCommunityByCommID(commID); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } PubConstant.hmWyglConnectionString = GetConnectionStr(Community); using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { dynamic value = conn.Query(@"SELECT(SELECT cast(RoomID AS VARCHAR)+',' FROM Tb_User_Relation WHERE UserId=@UserId FOR XML PATH('')) AS RoomIDs", new { UserId = userID }).FirstOrDefault().RoomIDs.ToString().Trim(','); string result; if (value == null) { result = new ApiResult(true, new DataTable()).toJson(); return(result.Insert(result.Length - 1, ",\"PageCount\":0")); } int pageCount; int count; string sql = string.Format(@"SELECT a.IncidentID, isnull(convert(VARCHAR(30), a.IncidentDate, 120), '') AS IncidentDate,a.IncidentContent, CASE WHEN a.IsDelete=1 THEN '删除' WHEN (a.DealState=1 AND isnull(a.IsDelete,0)=0) THEN '已回复' WHEN (a.DispType=1 AND isnull(a.IsDelete,0)=0 AND isnull(a.DealState,0)=0) THEN '已查看' ELSE '已提交' END AS State, (SELECT count(1) FROM Tb_HSPR_IncidentReply WHERE isnull(IsDelete,0)=0 AND IncidentID=a.IncidentID AND ReplyWay='客户线上评价') AS HasEvaluate FROM Tb_HSPR_IncidentAccept a WHERE a.IsStatistics=1 AND a.IncidentMode='App投诉建议' AND a.CommID={0} AND a.RoomID IN(SELECT convert(NVARCHAR(30), colName) FROM dbo.funSplitTabel('{1}',',')) AND ISNULL(a.IsDelete,0) = 0", Community.CommID, value.ToString()); if (Global_Var.CorpId == "1973") { sql = string.Format(@"SELECT a.IncidentID, isnull(convert(VARCHAR(30), a.IncidentDate, 120), '') AS IncidentDate,a.IncidentContent, CASE WHEN a.IsDelete=1 THEN '删除' WHEN (a.DealState=1 AND isnull(a.IsDelete,0)=0) THEN '已回复' WHEN (a.DispType=1 AND isnull(a.IsDelete,0)=0 AND isnull(a.DealState,0)=0) THEN '已查看' ELSE '已提交' END AS State, (SELECT count(1) FROM Tb_HSPR_IncidentReply WHERE isnull(IsDelete,0)=0 AND IncidentID=a.IncidentID AND ReplyWay='客户线上评价') AS HasEvaluate FROM Tb_HSPR_IncidentAccept a WHERE a.IsStatistics=1 AND a.IncidentMode='App爱我家园' AND a.CommID={0} AND a.RoomID IN(SELECT convert(NVARCHAR(30), colName) FROM dbo.funSplitTabel('{1}',',')) AND ISNULL(a.IsDelete,0) = 0", Community.CommID, value.ToString()); } DataSet ds = GetList(out pageCount, out count, sql, page, size, "IncidentDate", 1, "IncidentID", PubConstant.hmWyglConnectionString); result = new ApiResult(true, ds.Tables[0]).toJson(); return(result.Insert(result.Length - 1, ",\"PageCount\":" + pageCount)); } }
public void ProcessRequest(HttpContext context) { try { //接收回调xml信息 byte[] byts = new byte[context.Request.InputStream.Length]; context.Request.InputStream.Read(byts, 0, byts.Length); string req = Encoding.UTF8.GetString(byts); PubInfo.GetLog().Info("鸿坤京东收款通接收内容:" + req); if (string.IsNullOrEmpty(req)) { throw new Exception("接口未获取到任何数据"); } //初步解析接收内容,获取到商家编号 var res = XMLUtil.decryptResXmlNew <JdPayResponse>(req); //根据商家编号获取配置信息 WxPayConfig payConfig = GenerateConfig(res.merchant); if (payConfig == null) { throw new Exception("根据商家编号没有找到配置信息"); } //根据配置的密钥进行解析数据 AsynNotifyResponse anyResponse = XMLUtil.decryptResXml <AsynNotifyResponse>(payConfig.APPID, payConfig.KEY, req); //判断订单是否成过 if ("success".Equals(anyResponse.result.desc)) { DateTime DateNow = DateTime.Now; string orderId = anyResponse.tradeNum; string amt = anyResponse.amount.ToString(); // 通过OrderID订单号查询对应订单 using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { dynamic OrderInfo = conn.QueryFirstOrDefault("SELECT * FROM Tb_CCBPay_Order WHERE OrderSN = @OrderSN", new { OrderSN = orderId }); if (null == OrderInfo) { PubInfo.GetLog().Info("鸿坤京东收款通知内容:订单不存在"); return; } if (3 == Convert.ToInt32(OrderInfo.IsSucc)) { PubInfo.GetLog().Info("鸿坤京东收款通知内容:订单已下账"); return; } // 获取支付配置信息 dynamic PayConfig = conn.QueryFirstOrDefault("SELECT * FROM Tb_JDPay_Config WHERE Id = @Id", new { Id = OrderInfo.PayConfigNewId }); if (null == PayConfig) { PubInfo.GetLog().Info("鸿坤京东收款通知内容:支付配置不存在"); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, null, 0, req, 2, "支付配置不存在"); return; } // 获取小区信息 Tb_Community tb_Community = PubInfo.GetCommunity(Convert.ToString(PayConfig.CommunityId)); if (null == tb_Community) { PubInfo.GetLog().Info("鸿坤京东收款通知内容:小区配置不存在"); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, null, 0, req, 2, "小区配置不存在"); return; } decimal SAmt = Convert.ToDecimal(amt) / 100; // 判断金额是否一致 if (Convert.ToDecimal(OrderInfo.Amt) != SAmt) { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:账单金额与实收金额不一致(SAmt={0})", SAmt); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, null, 0, req, 2, "账单金额与实收金额不一致"); return; } string erpConnStr = PubInfo.GetConnectionStr(tb_Community); JObject PayData = JsonConvert.DeserializeObject <JObject>(Convert.ToString(OrderInfo.PayData)); int Type = (int)PayData["Type"]; if (Type == 1) { JArray Data = (JArray)PayData["Data"]; if (null == Data || Data.Count == 0) { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:下账失败(订单支付信息有误)(PayData={0})", OrderInfo.PayData); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, DateNow.ToString(), SAmt, req, 2, "下账失败(订单支付信息有误)"); return; } StringBuilder FeesIds = new StringBuilder(); foreach (JObject item in Data) { FeesIds.Append((string)item["FeesId"] + ","); } if (PubInfo.ReceFees(erpConnStr, out long ReceID, Convert.ToString(tb_Community.CommID), Convert.ToString(OrderInfo.CustID), Convert.ToString(OrderInfo.RoomID), FeesIds.ToString(), 0.00M, "自助缴费-京东")) { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:下账成功(PayData={0})", OrderInfo.PayData); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, DateNow.ToString().ToString(), SAmt, req, 3, "下账成功"); return; } else { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:下账失败(订单支付信息有误)(PayData={0})", OrderInfo.PayData); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, DateNow.ToString().ToString(), SAmt, req, 4, "下账失败"); return; } } else if (Type == 2) { JObject Data = (JObject)PayData["Data"]; if (null == Data) { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:下账失败(订单支付信息有误)(PayData={0})", OrderInfo.PayData); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, DateNow.ToString(), SAmt, req, 2, "支付金额必须大于0"); return; } string CostID = (string)Data["CostID"]; decimal Amt = (Decimal)Data["Amt"]; if (PubInfo.RecePreFees(erpConnStr, out long ReceID, Convert.ToString(tb_Community.CommID), Convert.ToString(OrderInfo.CustID), Convert.ToString(OrderInfo.RoomID), CostID, Amt, "自助缴费-京东")) { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:下账成功(PayData={0})", OrderInfo.PayData); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, DateNow.ToString(), SAmt, req, 3, "下账成功"); return; } else { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:下账失败(订单支付信息有误)(PayData={0})", OrderInfo.PayData); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, DateNow.ToString(), SAmt, req, 4, "下账失败"); return; } } else { PubInfo.GetLog().InfoFormat("鸿坤京东收款通知内容:下账失败(订单支付信息有误)(PayData={0})", OrderInfo.PayData); Business.HKCCBPay.UpdateOrderInfo(PubConstant.UnifiedContionString, orderId, DateNow.ToString(), SAmt, req, 2, "下账失败(订单支付信息有误)"); return; } }
/// <summary> /// 自动绑定房屋 /// </summary> private string AutoBindingRoom(Tb_Control_AppBindingRoom control, Tb_Community community, string userId) { using (var appConn = new SqlConnection(PubConstant.UnifiedContionString)) { // 用户手机号 var sql = @"SELECT Mobile FROM Tb_User WHERE Id=@Id;"; var userMobile = appConn.Query <string>(sql, new { Id = userId }).FirstOrDefault(); using (var erpConn = new SqlConnection(GetConnectionStr(community))) { try { sql = @"/* 客户(业主、租户、临时客户)信息 */ SELECT a.CustID,a.CustName,isnull(a.MobilePhone,a.LinkManTel) AS CustMobile,0 AS HoldID, c.CommID,isnull(b.RoomID,0) AS RoomID,c.RoomSign,c.RoomName,b.LiveType, CASE WHEN (isnull(a.IsDelete,0)+isnull(b.IsDelLive,0)+isnull(c.IsDelete,0))>0 THEN 1 ELSE 0 END AS IsDelete, isnull(b.IsDelLive,0) AS IsDelLive FROM Tb_HSPR_Customer a LEFT JOIN Tb_HSPR_CustomerLive b ON a.CustID=b.CustID LEFT JOIN Tb_HSPR_Room c ON c.RoomID=b.RoomID WHERE a.MobilePhone LIKE @Mobile OR a.LinkManTel LIKE @Mobile; /* 家庭成员信息 */ SELECT a.CustID,b.CustName,isnull(a.MobilePhone,a.LinkManTel) AS CustMobile,a.HoldID, a.CommID,isnull(a.RoomID,0) AS RoomID,d.RoomSign,d.RoomName,c.LiveType, CASE WHEN (isnull(a.IsDelete,0)+isnull(b.IsDelete,0)+isnull(c.IsDelLive,0)+isnull(d.IsDelete,0))>0 THEN 1 ELSE 0 END AS IsDelete, isnull(c.IsDelLive,0) AS IsDelLive FROM Tb_HSPR_Household a LEFT JOIN Tb_HSPR_Customer b ON a.CustID=b.CustID LEFT JOIN Tb_HSPR_CustomerLive c ON a.CustID=c.CustID AND a.RoomID=c.RoomID LEFT JOIN Tb_HSPR_Room d ON c.RoomID=d.RoomID WHERE a.MobilePhone LIKE @Mobile OR a.LinkManTel LIKE @Mobile;"; var reader = erpConn.QueryMultiple(sql, new { Mobile = $"%{userMobile}%" }); var customerData = reader.Read(); var householdData = reader.Read(); // 如果房屋绑定关系已存在,则更新绑定信息,同时同步 Locked 状态 // 若不存在绑定关系且为有效信息,则插入绑定关系 sql = @"DECLARE @CommunityId varchar(36); SELECT @CommunityId=Id FROM Tb_Community WHERE CorpID=@CorpID AND CommID=@CommID; IF exists(SELECT * FROM Tb_User_Relation WHERE UserId=@UserId AND CommunityId=@CommunityId AND RoomId=@RoomId) UPDATE Tb_User_Relation SET CustId=@CustId,CustName=@CustName,CustHoldID=@CustHoldID,Locked=@Locked WHERE UserId=@UserId AND CommunityId=@CommunityId AND RoomId=@RoomID; ELSE IF @Locked=0 INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,CustHoldId,RoomId, RegDate,CustName,RoomSign,CustMobile,Locked) VALUES(@Id,@UserId,@CommunityId,@CustId,@CustHoldId,@RoomId, getdate(),@CustName,@RoomSign,@CustMobile,0); ;"; foreach (dynamic item in householdData) { // 不允许无具体房产客户绑定 if (item.RoomID == 0) { continue; } if (item.RoomName == null && item.RoomName == null) { continue; } // 是否锁定房屋 var locked = Convert.ToInt32((item.IsDelete > 0 || item.IsDelLive > 0)); if (locked == 0) { // 不允许租户绑定 if (control.AllowLesseeBind == false && item.LiveType == 2) { continue; } // 不允许临时客户绑定 if (control.AllowTemporaryBind == false && item.LiveType == 3) { continue; } } appConn.Execute(sql, new { Id = Guid.NewGuid().ToString(), UserId = userId, CorpID = community.CorpID, CommID = item.CommID, CustId = item.CustID, CustName = item.CustName, CustMobile = item.CustMobile, RoomId = item.RoomID, RoomSign = (string.IsNullOrEmpty(item.RoomName) ? item.RoomSign : item.RoomName), Locked = locked, CustHoldId = item.HoldID }); } foreach (dynamic item in customerData) { // 不允许无具体房产客户绑定 if (item.RoomID == 0) { continue; } // 是否锁定房屋 var locked = Convert.ToInt32((item.IsDelete > 0 || item.IsDelLive > 0)); if (locked == 0) { // 不允许租户绑定 if (control.AllowLesseeBind == false && item.LiveType == 2) { continue; } // 不允许临时客户绑定 if (control.AllowTemporaryBind == false && item.LiveType == 3) { continue; } } appConn.Execute(sql, new { Id = Guid.NewGuid().ToString(), UserId = userId, CorpID = community.CorpID, CommID = item.CommID, CustId = item.CustID, CustName = item.CustName, CustMobile = item.CustMobile, RoomId = item.RoomID, RoomSign = (string.IsNullOrEmpty(item.RoomName) ? item.RoomSign : item.RoomName), Locked = locked, CustHoldId = 0 }); } // 读取用户已绑定房屋信息 sql = @"SELECT a.Id AS RelationID,a.CommunityID,b.CommName,a.CustID,a.CustName,a.CustMobile, a.RoomID,a.RoomSign,'' AS RoomName,'' AS BuildSNum,'' AS BuildName,'' AS UnitSNum, null AS TenantName, null AS MemberName, '0' AS IsCust, '0' AS IsHousehold, '0' AS IsTenant, (SELECT count(1) FROM Tb_User_Relation WHERE UserId=@UserId AND Locked=0) AS BindingCount FROM Tb_User_Relation a LEFT JOIN Tb_Community b ON a.CommunityId=b.Id WHERE UserId=@UserId"; var rooms = appConn.Query(sql, new { UserId = userId }); sql = @"/*房屋信息*/ SELECT a.RoomName,b.BuildSNum,b.BuildName,a.UnitSNum FROM Tb_HSPR_Room a LEFT JOIN Tb_HSPR_Building b ON a.CommID=b.CommID AND a.BuildSNum=b.BuildSNum AND isnull(b.IsDelete,0)=0 WHERE RoomID=@RoomID; /*家属信息*/ SELECT isnull(isnull(isnull(a.MemberName,a.Name),a.Surname),a.PaperName) AS MemberName, a.Relationship,b.DictionaryName AS RelationshipName FROM Tb_HSPR_Household a LEFT JOIN Tb_Dictionary_Relation b ON a.Relationship=b.DictionaryCode WHERE RoomID=@RoomID AND isnull(IsDelete,0)=0 AND (isnull(MobilePhone,'') LIKE @Mobile OR isnull(LinkManTel,'') LIKE @Mobile); /*客户信息*/ SELECT a.CustID,a.CustName,b.LiveType FROM Tb_HSPR_Customer a LEFT JOIN Tb_HSPR_CustomerLive b ON a.CustID=b.CustID AND isnull(b.IsDelLive,0)=0 WHERE (isnull(a.MobilePhone,'') LIKE @Mobile OR isnull(a.LinkManTel,'') LIKE @Mobile) AND isnull(a.IsDelete,0)=0 ORDER BY LiveType DESC;"; foreach (var roomInfo in rooms) { reader = erpConn.QueryMultiple(sql, new { RoomID = roomInfo.RoomID, Mobile = $"%{ userMobile }%" }); var tmp1 = reader.Read().FirstOrDefault(); var tmp2 = reader.Read().FirstOrDefault(); var tmp3 = reader.Read().FirstOrDefault(); if (tmp1 != null) { roomInfo.RoomName = tmp1.RoomName; roomInfo.BuildSNum = tmp1.BuildSNum; roomInfo.BuildName = tmp1.BuildName; roomInfo.UnitSNum = tmp1.UnitSNum; } // 家属 if (tmp2 != null) { if (tmp2.RelationshipName?.Contains("业主")) // 本身是业主或共有业主 { roomInfo.IsCust = "1"; roomInfo.CustName = tmp2.MemberName; } else { roomInfo.IsHousehold = "1"; roomInfo.MemberName = tmp2.MemberName; } } if (tmp3.LiveType == 1) // 业主 { roomInfo.IsCust = "1"; roomInfo.CustName = tmp3.CustName; roomInfo.IsHousehold = '0'; roomInfo.MemberName = null; roomInfo.IsTenant = '0'; roomInfo.TenantName = null; } else if (tmp3.LiveType == 2) // 租户 { roomInfo.IsTenant = "1"; roomInfo.TenantName = tmp3.CustName; roomInfo.IsHousehold = '0'; roomInfo.MemberName = null; } } return(new ApiResult(true, rooms).toJson()); } catch (Exception ex) { return(JSONHelper.FromString(false, ex.Message + Environment.NewLine + ex.StackTrace)); } } } }
/// <summary> /// 发布跳蚤信息 /// </summary> private string SubmitSecondHandInfo_th(DataRow row) { if (!row.Table.Columns.Contains("CommunityId")) { return(new ApiResult(false, "缺少参数CommunityId").toJson()); } if (!row.Table.Columns.Contains("Title") || string.IsNullOrEmpty(row["Title"].ToString())) { return(new ApiResult(false, "缺少参数Title").toJson()); } if (!row.Table.Columns.Contains("Content") || string.IsNullOrEmpty(row["Content"].ToString())) { return(new ApiResult(false, "缺少参数Content").toJson()); } if (!row.Table.Columns.Contains("Images")) { return(new ApiResult(false, "缺少参数Images").toJson()); } if (!row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(row["UserID"].ToString())) { return(new ApiResult(false, "缺少参数UserID").toJson()); } if (!row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(row["UserID"].ToString())) { return(new ApiResult(false, "缺少参数UserID").toJson()); } string communityId = row["CommunityId"].ToString(); string Title = row["Title"].ToString().Replace(Environment.NewLine, "\r\n"); string Content = row["Content"].ToString().Replace(Environment.NewLine, "\r\n"); string Images = row["Images"].ToString(); string UserID = row["UserID"].ToString(); string InfoID = null; if (row.Table.Columns.Contains("InfoID") && !string.IsNullOrEmpty(row["InfoID"].ToString())) { InfoID = row["InfoID"].ToString(); } // 获取数据库连接字符串 Tb_Community tb_Community = GetCommunity(communityId); if (null == tb_Community) { return(new ApiResult(false, "该小区不存在").toJson()); } string connStr = GetConnectionStringStr(tb_Community); using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString)) { string sql = @"SELECT isnull(NickName,isnull(Name,'匿名')) AS Name,Mobile FROM Tb_User WHERE Id=@UserId"; dynamic userInfo = conn.Query(sql, new { UserId = UserID }).FirstOrDefault(); if (userInfo == null || string.IsNullOrEmpty(userInfo.Mobile.ToString())) { return(new ApiResult(false, "用户不存在").toJson()); } string userName = userInfo.Name.ToString(); string mobile = userInfo.Mobile.ToString(); using (IDbConnection conn2 = new SqlConnection(connStr)) { if (string.IsNullOrEmpty(InfoID)) { conn2.Execute(@"INSERT INTO Tb_HSPR_CommActivities(ActivitiesID,CommID,ActivitiesType,ActivitiesTheme,ActivitiesContent, CustName,LinkPhone,ActivitiesImages,ActivitiesStartDate,ActivitiesEndDate,IssueDate,IsDelete,isRun) VALUES(newid(),@CommID,'0001',@Title, @Content, @CustName, @LinkPhone,@Images,getdate(), convert(DATETIME,'2099-01-01 23:59:59'),getdate(),0,0)", new { CommID = tb_Community.CommID, Title = Title, Content = Content, CustName = userName, LinkPhone = mobile, Images = Images }); return(JSONHelper.FromString(true, "发布成功")); } else { conn2.Execute(@"UPDATE Tb_HSPR_CommActivities SET ActivitiesTheme=@Title,ActivitiesContent=@Content, ActivitiesImages=@Images,IssueDate=getdate() WHERE ActivitiesID=@InfoID", new { Title = Title, Content = Content, Images = Images, InfoID = InfoID }); return(JSONHelper.FromString(true, "修改成功")); } } } }
/// <summary> /// 已审核列表 /// </summary> private string AuditList(DataRow row) { //commID if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString())) { return(JSONHelper.FromString(false, "缺少CommunityId参数")); } if (!row.Table.Columns.Contains("PageSize") || string.IsNullOrEmpty(row["PageSize"].ToString())) { return(JSONHelper.FromString(false, "缺少PageSize参数")); } if (!row.Table.Columns.Contains("PageIndex") || string.IsNullOrEmpty(row["PageIndex"].ToString())) { return(JSONHelper.FromString(false, "缺少PageIndex参数")); } string CommID = row["CommID"].AsString(); string IsAllows = null; int PageSize = AppGlobal.StrToInt(row["PageSize"].AsString()); int PageIndex = AppGlobal.StrToInt(row["PageIndex"].AsString()); string RoomID = null; if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].ToString())) { RoomID = row["RoomID"].ToString(); } if (row.Table.Columns.Contains("IsAllows") && !string.IsNullOrEmpty(row["IsAllows"].ToString())) { IsAllows = row["IsAllows"].ToString(); } // 查询小区 Tb_Community Community = GetCommunity(CommID); if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } var sql = $@"SELECT IID,ApplicantUserID,CommunityID, (SELECT CommName FROM Tb_Community x WHERE x.ID=CommunityID) AS CommName, RoomID,RoomName,CustName,CustMobile,AddTime,IDCardNum,IDCard1,IDCard2 FROM Tb_Audit_BindingRoom WHERE CommunityID='{Community.Id}' AND isnull(IsDelete,0)=0"; if (!string.IsNullOrEmpty(IsAllows)) { sql += $" AND IsAllows={IsAllows}"; } if (!string.IsNullOrEmpty(RoomID)) { sql += $" AND RoomID={RoomID}"; } DataTable dataTable = GetList(out int pageCount, out int counts, sql, PageIndex, PageSize, "AddTime", 1, "IID", PubConstant.UnifiedContionString).Tables[0]; string result = JSONHelper.FromString(true, dataTable); result = result.Insert(result.Length - 1, ",PageCount:" + pageCount); return(result); }
/// <summary> /// 审核操作 /// </summary> private string Audit(int IsAllows, string IID, string Remark) { try { using (var conn = new SqlConnection(PubConstant.UnifiedContionString)) { // 小区信息 Tb_Community Community = conn.Query <Tb_Community>(@"SELECT * FROM Tb_Community WHERE Id IN (SELECT CommunityID FROM Tb_Audit_BindingRoom WHERE IID=@IID)", new { IID = IID }).FirstOrDefault(); if (Community == null) { return(JSONHelper.FromString(false, "未找到小区信息")); } // ERP数据库连接字符串 PubConstant.hmWyglConnectionString = GetConnectionStringStr(Community); string sql = @"UPDATE Tb_Audit_BindingRoom SET IsAllows=@IsAllows,AuditTime=getdate(),Remark=@Remark WHERE IID=@IID;"; int i = conn.Execute(sql, new { IsAllows = IsAllows, IID = IID, Remark = Remark, }); if (i > 0) { // 审核信息 dynamic AuditInfo = conn.Query(@"SELECT * FROM Tb_Audit_BindingRoom WHERE IID=@IID;", new { IID = IID }).FirstOrDefault(); // 用户信息 dynamic UserInfo = conn.Query(@"SELECT * FROM Tb_User WHERE Id=@Id", new { Id = AuditInfo.ApplicantUserID }).FirstOrDefault(); // 绑定关系 dynamic BindedInfo = conn.Query(@"SELECT * FROM Tb_User_Relation WHERE UserId=@UserID AND RoomId=@RoomID;", new { UserID = AuditInfo.ApplicantUserID, RoomID = AuditInfo.RoomID }).FirstOrDefault(); string UserID = AuditInfo.ApplicantUserID.ToString(); string UserMobile = UserInfo.Mobile.ToString(); string NickName = UserInfo.NickName.ToString(); string CommunityID = AuditInfo.CommunityID.ToString(); string RoomID = AuditInfo.RoomID.ToString(); string CustName = AuditInfo.CustName.ToString(); string CustMobile = AuditInfo.CustMobile.ToString(); // 房屋名称 string RoomName; if (Community.CorpID == 1971) { RoomName = new SqlConnection(PubConstant.hmWyglConnectionString) .Query <string>(@"SELECT isnull(NC_NewRoomSign,isnull(RoomName,RoomSign)) FROM Tb_HSPR_Room WHERE RoomID=@RoomID", new { RoomID = AuditInfo.RoomID }).FirstOrDefault(); } else { RoomName = new SqlConnection(PubConstant.hmWyglConnectionString) .Query <string>(@"SELECT isnull(RoomName,RoomSign) FROM Tb_HSPR_Room WHERE RoomID=@RoomID", new { RoomID = AuditInfo.RoomID }).FirstOrDefault(); } // 短信信息 string messageForTenant, messageForCust, errorMessage; if (IsAllows == 1) { messageForTenant = $"您申请绑定小区[{Community.CommName}]房屋地址为:{RoomName}的申请已审核通过。"; messageForCust = $"用户{NickName}申请绑定您在[{Community.CommName}]房屋地址为:{RoomName}的申请已审核通过。"; } else { Remark = string.IsNullOrEmpty(Remark) ? "无" : Remark; messageForTenant = $"您申请绑定小区[{Community.CommName}]房屋地址为:{RoomName}的申请已被驳回,驳回原因:{Remark}"; messageForCust = $"用户{NickName}申请绑定您在[{Community.CommName}]房屋地址为:{RoomName}的申请已被驳回,驳回原因:{Remark}"; } SendShortMessage(UserMobile, messageForTenant, out errorMessage, Community.CorpID); SendShortMessage(CustMobile, messageForCust, out errorMessage, Community.CorpID); // 该房屋已绑定,但被锁定 if (BindedInfo != null) { conn.Execute("UPDATE Tb_User_Relation SET Locked=0 WHERE Id=@Id", new { Id = BindedInfo.Id }); return(JSONHelper.FromString(true, "操作成功")); } using (var erpConn = new SqlConnection(GetConnectionStringStr(Community))) { // 读取业主信息 sql = @"SELECT CustID,(SELECT TOP 1 CustName FROM Tb_HSPR_Customer b WHERE b.CustID=x.CustID) AS CustName, (SELECT TOP 1 isnull(isnull(MobilePhone,LinkmanTel),'') FROM Tb_HSPR_Customer b WHERE b.CustID=x.CustID) AS CustMobile, RoomID, (SELECT TOP 1 isnull(RoomName,RoomSign) FROM Tb_HSPR_Room WHERE RoomID=@RoomID) AS RoomSign FROM Tb_HSPR_CustomerLive x WHERE RoomID=@RoomID AND isnull(IsDelLive,0)=0 AND LiveType=1"; dynamic CustInfo = erpConn.Query(sql, new { RoomID = RoomID }).FirstOrDefault(); string custId = CustInfo.CustID.ToString(); string ERPCustName = CustInfo.CustName.ToString(); string ERPCustMobile = CustInfo.CustMobile.ToString(); string roomSign = CustInfo.RoomSign.ToString(); // 家庭成员信息、绑定房屋关系 string newRelation = Guid.NewGuid().ToString(); conn.Execute(@"INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,RoomId,RegDate, CustName,RoomSign,CustMobile,Locked) VALUES(@Id,@UserId,@CommunityId,@CustId,@RoomId,getdate(), @CustName,@RoomSign,@CustMobile,0)", new { Id = newRelation, UserId = UserID, CommunityId = CommunityID, CustId = custId, RoomId = RoomID, CustName = ERPCustName, CustMobile = ERPCustMobile, RoomSign = roomSign }); // 家庭成员信息是否存在 sql = @"SELECT HoldID FROM Tb_HSPR_Household WHERE RoomID=@RoomID AND MobilePhone LIKE @MobilePhone"; long holdId = erpConn.Query <long>(sql, new { RoomID = RoomID, MobilePhone = $"%{UserInfo.Mobile.ToString()}%" }).FirstOrDefault(); // 存在则更新 if (holdId > 0) { erpConn.Execute(@"UPDATE Tb_HSPR_Household SET Relationship='0029',IsDelete=0 WHERE HoldID=@HoldID", new { HoldID = holdId }); return(JSONHelper.FromString(true, "操作成功")); } // 插入家庭成员信息 DynamicParameters parameters = new DynamicParameters(); parameters.Add("@HoldID", 0, DbType.Int64, ParameterDirection.Output); parameters.Add("@CommID", Community.CommID); parameters.Add("@CustID", custId); parameters.Add("@RoomID", RoomID); parameters.Add("@Name", CustName); parameters.Add("@MobilePhone", UserInfo.Mobile.ToString()); if (Community.CorpID == 1971) { // 敏捷默认为租户 parameters.Add("@Relationship", "0031"); } else { // 其他默认为家庭成员 parameters.Add("@Relationship", "0030"); } erpConn.Execute("Proc_HSPR_Household_Insert_Phone", parameters, null, null, CommandType.StoredProcedure); holdId = parameters.Get <long>("@HoldID"); conn.Execute(@"UPDATE Tb_User_Relation SET CustHoldId=@HoldID WHERE Id=@RelationId", new { HoldID = holdId, RelationId = newRelation }); return(JSONHelper.FromString(true, "操作成功")); } } return(new ApiResult(false, "操作失败").toJson()); } } catch (Exception ex) { GetLog().Error(ex.Message + Environment.CommandLine + ex.StackTrace); return(new ApiResult(false, ex.Message + ex.StackTrace).toJson()); } }
/// <summary> /// 注册用户微信共众号 RegisterUser_WeiXin /// </summary> /// <param name="Row"></param> /// OpenId 必填 /// <returns></returns> private string RegisterUser_WeiXin_hnc(DataRow Row) { if (!Row.Table.Columns.Contains("OpenId") || string.IsNullOrEmpty(Row["OpenId"].ToString())) { return(JSONHelper.FromString(false, "请使用微信客户端打开")); } string OpenId = Row["OpenId"].ToString(); //微信公众号注册,默认密码为123456 //针对某些公众号系统不需要设置密码的问题(例如华南城) string Pwd; if (!Row.Table.Columns.Contains("Pwd") || string.IsNullOrEmpty(Row["Pwd"].ToString())) { Pwd = "123456"; } else { Pwd = Row["Pwd"].ToString(); } if (!Row.Table.Columns.Contains("Mobile") || string.IsNullOrEmpty(Row["Mobile"].ToString())) { return(JSONHelper.FromString(false, "手机号不能为空")); } string Mobile = Row["Mobile"].ToString(); //查询系统中是否存在该openid DataTable dTable = new DbHelperSQLP(PubConstant.UnifiedContionString.ToString()).Query("SELECT * FROM Tb_User_WeiXin as w JOIN Tb_User as u on w.UserId = u.id where w.WeChatToken = '" + Row["OpenId"].ToString() + "'").Tables[0]; if (null != dTable && dTable.Rows.Count > 0) { //如果系统中已存在该openid,直接返回注册成功 return(JSONHelper.FromString(true, "注册成功")); } //默认姓名为空 string Name = ""; //默认邮箱为空 string Email = ""; //默认QQ为空 string QQ = ""; //默认QQ登录为空 string QQToken = ""; //默认微信登录为空 string WeChatToken = ""; //默认昵称为手机号 string NickName = Mobile; //默认用户头像为空 string UserPic = ""; //默认性别为女 int Sex = 1; if (Row.Table.Columns.Contains("Name") && string.IsNullOrEmpty(Row["Name"].ToString())) { Name = Row["Name"].ToString(); } if (Row.Table.Columns.Contains("Email") && string.IsNullOrEmpty(Row["Email"].ToString())) { Email = Row["Email"].ToString(); } if (Row.Table.Columns.Contains("QQ") && string.IsNullOrEmpty(Row["QQ"].ToString())) { QQ = Row["QQ"].ToString(); } if (Row.Table.Columns.Contains("QQToken") && string.IsNullOrEmpty(Row["QQToken"].ToString())) { QQToken = Row["QQToken"].ToString(); } if (Row.Table.Columns.Contains("WeChatToken") && string.IsNullOrEmpty(Row["WeChatToken"].ToString())) { WeChatToken = Row["WeChatToken"].ToString(); } if (Row.Table.Columns.Contains("NickName") && !string.IsNullOrEmpty(Row["NickName"].ToString())) { NickName = Row["NickName"].ToString(); } if (Row.Table.Columns.Contains("UserPic") && !string.IsNullOrEmpty(Row["UserPic"].ToString())) { UserPic = Row["UserPic"].ToString(); } if (Row.Table.Columns.Contains("Sex") && !string.IsNullOrEmpty(Row["Sex"].ToString())) { Sex = AppGlobal.StrToInt(Row["Sex"].ToString()); } // 如果不存在该用户才新增,否则只增加绑定关系 string UserId; DataTable dt = new DbHelperSQLP(PubConstant.UnifiedContionString.ToString()).Query("SELECT * FROM Tb_User WHERE Mobile = '" + Mobile + "'").Tables[0]; if (null == dt || dt.Rows.Count == 0) { UserId = Guid.NewGuid().ToString(); MobileSoft.Model.Unified.Tb_User User = new MobileSoft.Model.Unified.Tb_User(); User.Id = UserId; User.Name = Name; User.Mobile = Mobile; User.QQ = QQ; User.QQToken = QQToken; User.WeChatNum = WeChatToken; User.WeChatToken = WeChatToken; User.Pwd = Pwd; User.NickName = NickName; User.Email = Email; User.UserPic = UserPic; User.Sex = Sex; User.RegDate = DateTime.Now; //新增用户 new MobileSoft.BLL.Unified.Bll_Tb_User().Add(User); } else { UserId = dt.Rows[0]["Id"].ToString(); } //新增openid int result = new DbHelperSQLP(PubConstant.UnifiedContionString.ToString()).ExecuteSql("insert into Tb_User_WeiXin(UserId,WeChatToken) values('" + UserId + "','" + Row["OpenId"] + "')"); if (result == 0) { return(JSONHelper.FromString(false, "注册失败,请重试")); } try { string erpString = AppGlobal.GetConnectionString("HNC_HM_ConnectionString"); // 查询业主 using (IDbConnection conn = new SqlConnection(erpString)) { IEnumerable <dynamic> resultSet = conn.Query(string.Format(@"SELECT a.CustID,a.CustName,a.CommID,b.RoomID,c.RoomSign FROM Tb_HSPR_Customer as a JOIN Tb_HSPR_CustomerLive AS b ON a.CustID = b.CustID JOIN Tb_HSPR_Room AS c ON b.RoomID = c.RoomID WHERE (b.LiveType=1 OR b.LiveType=2) AND isnull(IsDelLive,0)= 0 AND a.MobilePhone LIKE '%{0}%'", Mobile)); // 业主存在,自动绑定房屋 if (resultSet.Count() > 0) { foreach (dynamic item in resultSet) { Tb_Community tb_Community = getTbCommunity(item.CommID + ""); if (null == tb_Community) { continue; } using (IDbConnection conn2 = new SqlConnection(PubConstant.UnifiedContionString)) { conn2.Execute(@"INSERT INTO Tb_User_Relation(Id, UserId, CommunityId, CustId, RoomId, RegDate, CustName, RoomSign, CustMobile) VALUES(newid(), @UserId, @CommunityId, @CustID, @RoomID, getdate(), @CustName, @RoomSign, @Mobile)", new { UserId = UserId, CommunityId = tb_Community.Id, CustID = item.CustID, RoomID = item.RoomID, CustName = item.CustName, RoomSign = item.RoomSign, Mobile = Mobile }); } } return(JSONHelper.FromString(true, "注册成功,已自动绑定房屋")); } return(JSONHelper.FromString(true, "注册成功")); } } catch (Exception ex) { return(JSONHelper.FromString(true, "注册成功,但自动绑定失败(" + ex.Message + ")")); } }
/// <summary> /// 获取欠费或预存信息 /// </summary> private string GetFeesStatistics(DataRow row) { if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString())) { return(JSONHelper.FromString(false, "小区编码不能为空")); } if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString())) { return(JSONHelper.FromString(false, "房间编码不能为空")); } string communityId = row["CommunityId"].ToString(); string RoomID = row["RoomID"].ToString(); //查询小区 Tb_Community Community = GetCommunity(communityId); //构造链接字符串 if (Community == null) { return(JSONHelper.FromString(false, "该小区不存在")); } string strcon = GetConnectionStringStr(Community); using (var conn = new SqlConnection(GetConnectionStringStr(Community))) { var sql = @"SELECT PrecAmount FROM ( SELECT isnull(sum(isnull(a.PrecAmount,0)),0) AS PrecAmount FROM view_HSPR_PreCosts_Filter a WHERE a.CommID=@CommID AND a.RoomID=@RoomID AND convert(varchar(8000),a.CostNames)='住宅物业服务费' AND a.PrecAmount>0 AND a.IsPrec=1 ) AS t" ; var PrecAmount = conn.Query <decimal>(sql, new { CommID = Community.CommID, RoomID = RoomID }).FirstOrDefault(); sql = @"SELECT isnull(sum(isnull(DebtsAmount,0) + isnull(LateFeeAmount,0)),0) AS DebtsAmount FROM ( SELECT DebtsAmount, case when isnull(a.DebtsLateAmount,0) > 0 then dbo.funGetLateFeeDebts(a.CommID,a.FeesID,isnull(a.DebtsLateAmount,0)) else dbo.funGetLateFeeAll(isnull(f.DelinType,isnull(b.DelinType,0)) ,isnull(f.DelinDay,isnull(b.DelinDay,0)) ,isnull(f.DelinDelay,isnull(b.DelinDelay,0)) ,isnull(f.DelinRates,isnull(b.DelinRates,0)) ,b.RoundingNum ,a.DebtsAmount ,isnull(a.AccountsDueDate,a.FeesDueDate) ,getdate() ,a.CommID,a.FeesID,a.IsProperty,a.IsPrec,a.ContID,a.LeaseContID) end as LateFeeAmount FROM Tb_HSPR_Fees a LEFT JOIN Tb_HSPR_CostItem AS b ON a.CommID=b.CommID AND a.CostID=b.CostID LEFT JOIN Tb_HSPR_CostStandard AS f ON a.StanID=f.StanID WHERE a.RoomID=@RoomID AND ISNULL(a.IsCharge, 0)=0 AND ISNULL(a.IsBank,0)=0 AND ISNULL(a.IsPrec,0)=0 AND ISNULL(a.IsFreeze,0)=0 AND CustID IN(SELECT CustID FROM Tb_HSPR_CustomerLive xx where xx.RoomID=@RoomID AND ISNULL(IsDelLive,0)=0) ) AS t"; var DebtsAmount = conn.Query <decimal>(sql, new { CommID = Community.CommID, RoomID = RoomID }).FirstOrDefault(); // 俊发判断是不是成都地区,如果是成都,按月缴费,其他地区按年 int BillingCycle = 1; if (Community.CorpID == 1985) { if (conn.Query(@"SELECT * FROM Tb_HSPR_Community WHERE CommID=@CommID AND OrganCode='0104'", new { CommID = Community.CommID }).Count() > 0) { BillingCycle = 1; } else { BillingCycle = 12; } } return(new ApiResult(true, new { PrecAmount = PrecAmount, DebtsAmount = DebtsAmount, BillingCycle = BillingCycle }).toJson()); } }
/// <summary> /// 审核操作 /// </summary> private string Audit(int IsAllows, string IID, string Remark) { using (var conn = new SqlConnection(PubConstant.UnifiedContionString)) { string sql = @"UPDATE Tb_Audit_InviteUser SET IsAllows=@IsAllows,AuditTime=getdate(),Remark=@Remark WHERE IID=@IID;"; int i = conn.Execute(sql, new { IsAllows = IsAllows, IID = IID, Remark = Remark, }); if (i > 0) { // 小区信息 Tb_Community Community = conn.Query <Tb_Community>(@"SELECT * FROM Tb_Community WHERE Id IN (SELECT CommunityID FROM Tb_Audit_InviteUser WHERE IID=@IID)", new { IID = IID }).FirstOrDefault(); // 审核信息 dynamic InviteInfo = conn.Query(@"SELECT * FROM Tb_Audit_InviteUser WHERE IID=@IID;", new { IID = IID }).FirstOrDefault(); // 申请人绑定关系 dynamic BindingInfo = conn.Query(@"SELECT * FROM Tb_User_Relation WHERE UserId=@UserID AND RoomId=@RoomID;", new { UserID = InviteInfo.ApplicantUserID, RoomID = InviteInfo.RoomID }).FirstOrDefault(); if (Community != null && InviteInfo != null && BindingInfo != null) { string strcon = GetConnectionStringStr(Community); string relationId = BindingInfo.Id.ToString(); string newRelation = string.Empty; string inviteUserId = null; string communityId = BindingInfo.CommunityId.ToString(); string inviteMobile = InviteInfo.InviteeMobile.ToString(); string inviteName = InviteInfo.InviteeName.ToString(); string relationship = UserRoomIdentityCode.Other; if (InviteInfo.InviteeIdentity == 0) { relationship = UserRoomIdentityCode.Customer2; } else if (InviteInfo.InviteeIdentity == 1) { relationship = UserRoomIdentityCode.FamilyMember; } else if (InviteInfo.InviteeIdentity == 2) { relationship = UserRoomIdentityCode.Tenant; } string message, errorMessage; sql = @"SELECT CustId,RoomId,RoomSign,CustName FROM Tb_User_Relation WHERE Id=@RelationId"; dynamic relationInfo = conn.Query(sql, new { RelationId = relationId }).First(); string custId = relationInfo.CustId.ToString(); string roomId = relationInfo.RoomId.ToString(); string roomSign = relationInfo.RoomSign.ToString(); string custName = relationInfo.CustName.ToString(); long holdId = 0; if (IsAllows == 0) { string mobile = conn.Query <string>(@"SELECT Mobile FROM Tb_User WHERE Id=@UserID", new { UserID = InviteInfo.ApplicantUserID }).FirstOrDefault(); message = string.Format(@"您邀请手机号为“{0}”的住户{1}的申请已被驳回,驳回原因:{2}", inviteMobile, inviteName, (string.IsNullOrEmpty(Remark) ? "无" : Remark)); SendShortMessage(mobile, message, out errorMessage, Community.CorpID); return(JSONHelper.FromString(true, "邀请住户申请驳回成功")); } // 1、查询被邀者的手机号是否已经注册 sql = "SELECT top 1 Id FROM Tb_User WHERE Mobile=@Mobile"; IEnumerable <string> resultSet = conn.Query <string>(sql, new { Mobile = inviteMobile }); if (resultSet.Count() > 0) { inviteUserId = resultSet.First(); // 1.2、查询被邀者是否已经绑定该房屋 sql = @"SELECT RoomSign,isnull(CustHoldId,0) AS CustHoldId FROM Tb_User_Relation WHERE UserId=@UserId AND RoomId IN (SELECT RoomId FROM Tb_User_Relation WHERE Id=@Relation)"; IEnumerable <dynamic> userRelationSet = conn.Query(sql, new { UserId = inviteUserId, Relation = relationId }); // 该已绑定该房屋 if (userRelationSet.Count() > 0) { // 家庭成员id holdId = long.Parse(userRelationSet.First().CustHoldId); // 已经绑定但锁定则解锁 sql = @"UPDATE Tb_User_Relation SET Locked=0 WHERE UserId=@UserId AND RoomId IN (SELECT RoomId FROM Tb_User_Relation WHERE Id=@Relation)"; conn.Execute(sql, new { UserId = inviteUserId, Relation = relationId }); // 更新家庭成员信息 using (IDbConnection conn2 = new SqlConnection(strcon)) { conn2.Execute(@"UPDATE Tb_HSPR_Household SET IsDelete=0,Relationship=@Relationship, MemberName=@Name,Linkman=@Name,Surname=@Name,Name=@Name WHERE HoldID=@HoldID", new { HoldID = holdId, Relationship = relationship, Name = inviteName }); return(JSONHelper.FromString(true, "操作成功")); } } // 未绑定 else { // 1.3、直接给被邀者绑定该房屋 newRelation = Guid.NewGuid().ToString(); sql = @"INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,RoomId,RegDate,CustName, RoomSign,CustMobile,Locked,CustHoldId) SELECT @NewRelation AS Id,@UserId AS UserId,CommunityId,CustId,roomid,getdate(), CustName,RoomSign,CustMobile,0 AS Locked,0 AS CustHoldId FROM Tb_User_Relation WHERE Id=@RelationId"; conn.Execute(sql, new { UserId = inviteUserId, NewRelation = newRelation, RelationId = relationId }); } // 短信内容 message = string.Format("温馨提示:\"{0}\"业主{1}为您绑定了编号为:{2}的房屋,您可以对该房屋进行报事和缴费等操作。", Community.CommName, custName, roomSign); } else { // 2、被邀者没有注册账号,创建账号并绑定房屋关系 inviteUserId = Guid.NewGuid().ToString(); newRelation = Guid.NewGuid().ToString(); conn.Execute(@"INSERT INTO Tb_User(Id,Name,Mobile,NickName,Pwd,Sex,RegDate) VALUES(@UserId,@Name,@Mobile,@Name,'123456',1,getdate()); INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,RoomId,RegDate,CustName,RoomSign,CustMobile,Locked) SELECT @NewRelation AS Id, @UserId AS UserId,CommunityId,CustId,roomid,getdate(),CustName,RoomSign,CustMobile, 0 AS Locked FROM Tb_User_Relation WHERE Id=@RelationId", new { UserId = inviteUserId, Name = inviteName, Mobile = inviteMobile, NewRelation = newRelation, RelationId = relationId }); message = string.Format("温馨提示:“{0}”业主{1}邀请您使用App,并为您绑定了编号为:{2}的房屋,您的账号为:{3},初始密码为:123456。", Community.CommName, custName, roomSign, inviteMobile); } SendShortMessage(inviteMobile, message, out errorMessage, Community.CorpID); using (IDbConnection conn2 = new SqlConnection(strcon)) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("@HoldID", 0, DbType.Int64, ParameterDirection.Output); parameters.Add("@CommID", Community.CommID); parameters.Add("@CustID", custId); parameters.Add("@RoomID", roomId); parameters.Add("@Name", inviteName); parameters.Add("@MobilePhone", inviteMobile); parameters.Add("@Relationship", relationship); if (conn2.State == ConnectionState.Closed) { conn2.Open(); } conn2.Execute("Proc_HSPR_Household_Insert_Phone", parameters, null, null, CommandType.StoredProcedure); long custHoldId = parameters.Get <long>("@HoldID"); conn.Execute(@"UPDATE Tb_User_Relation SET CustHoldId=@HoldID WHERE Id=@RelationId", new { HoldID = custHoldId, RelationId = newRelation }); } return(JSONHelper.FromString(true, "操作成功")); } } return(new ApiResult(false, "操作失败").toJson()); } }