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());
}
}
