/// <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 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, "绑定成功"));
}
}
}
