/// <summary>
/// 泰禾高端定制
/// </summary>
private string GetCommunityServices(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区编号不能为空"));
}
string communityId = row["CommunityId"].AsString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
DataTable dataTable = conn.ExecuteReader(string.Format(@"SELECT TOP 9 Sort,CustomizedType,CustomizedImage,CarouselDescribe,CarouselImage,ImgLink,Phone FROM Tb_CommunityService
WHERE isnull(IsDelete,0)=0 AND CommunityId LIKE '%{0}%'", communityId)).ToDataSet().Tables[0];
return(JSONHelper.FromString(dataTable));
}
}
private string GetCommunityServiceTel_ZhongJi(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 roomId = row["RoomID"].ToString();
string communityId = row["CommunityId"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
using (IDbConnection conn = new SqlConnection(strcon))
{
string sql = @"SELECT Heading AS UserName,InfoContent AS MobileTel FROM view_Tb_HSPR_CommunityInfo_filter_CommServiceTel WHERE CommID = @CommID";
DataTable dt = conn.ExecuteReader(sql, new { CommID = Community.CommID }).ToDataSet().Tables[0];
return(JSONHelper.FromString(dt));
}
}
/// <summary>
/// 中集 只获取业主的未缴费项
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private string GetDebtsFeesList_ZhongJi(DataRow row)
{
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "用户编码不能为空"));
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区编码不能为空"));
}
string RoomID = row["RoomID"].ToString();
string CommunityId = row["CommunityId"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommunityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
StringBuilder sb = new StringBuilder();
sb.Append(@" select FeesID,CostName,DueAmount,isnull(DebtsAmount,0) DebtsAmount,isnull(WaivAmount,0) AS WaivAmount,isnull(PrecAmount,0) AS PrecAmount,
isnull(PaidAmount,0) AS PaidAmount,ISNULL(LateFeeAmount,0) AS LateFeeAmount,CONVERT(varchar(100),FeesDueDate,111) as FeesDueDate, ");
sb.Append("isnull(IsCharge,0) As IsCharge,FeesStateDate,FeesEndDate from view_HSPR_Fees_SearchFilter ");
sb.AppendFormat(" where CommID={0}", Community.CommID);
sb.AppendFormat(" and RoomID={0}", RoomID);
sb.Append(" and custid in(select x.CustID from Tb_HSPR_CustomerLive x LEFT JOIN view_HSPR_Fees_Filter y ON x.RoomID =y.RoomID where isnull(IsDelLive,0)=0 and LiveType = 1)");
sb.Append(" and ischarge=0 and IsBank=0 and IsFreeze=0 and IsProperty=0 and ISNULL(IsPrec,0)=0 ");
sb.Append(" order by FeesID desc ");
DataSet ds = new DbHelperSQLP(strcon).Query(sb.ToString());
return(JSONHelper.FromString(ds.Tables[0]));
}
/// <summary>
/// 根据CommunityID获取CommID再获取组团区域
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private string GetRegionList(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(new ApiResult(false, "缺少CommunityId").toJson());
}
string CommunityId = row["CommunityId"].ToString();
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommunityId);
if (null == Community)
{
return(new ApiResult(false, "没有找到对应的小区信息").toJson());
}
string connStr = new Business.CostInfo().GetConnectionStringStr(Community);
DataTable dt = null;
using (IDbConnection conn = new SqlConnection(connStr)) {
string SQLEx = "AND CommID = " + Community.CommID + " AND ISNULL(IsDelete,0) = 0";
dt = conn.ExecuteReader("Proc_HSPR_Region_Filter", new { SQLEx = SQLEx }, null, null, CommandType.StoredProcedure).ToDataSet().Tables[0];
}
List <Dictionary <string, object> > list = new List <Dictionary <string, object> >();
if (null != dt && dt.Rows.Count > 0)
{
Dictionary <string, object> dic;
foreach (DataRow item in dt.Rows)
{
dic = new Dictionary <string, object>
{
{ "RegionID", item["RegionID"] },
{ "RegionName", item["RegionName"] }
};
list.Add(dic);
}
}
return(new ApiResult(true, list).toJson());
}
/// <summary>
/// 作废访客
/// </summary>
/// <param name="Row"></param>
/// <returns></returns>
private string DeleteVisitor(DataRow Row)
{
if (!Row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(Row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!Row.Table.Columns.Contains("VisitorId") || string.IsNullOrEmpty(Row["VisitorId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少VisitorId参数"));
}
//项目小区表ID
string CommunityId = Row["CommunityId"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommunityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string VisitorId = Row["VisitorId"].ToString();
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
int result = 0;
using (IDbConnection conn = new SqlConnection(strcon))
{
result = conn.Execute("UPDATE Tb_HSPR_APP_Visitor SET IsDelete = 1 WHERE Id = @Id", new { Id = VisitorId }, null, null, CommandType.Text);
}
if (result > 0)
{
return(new ApiResult(true, "作废成功").toJson());
}
return(new ApiResult(false, "作废失败,请重试").toJson());
}
/// <summary>
/// 获取楼栋管家号码
/// </summary>
private string GetCommunityServiceTel_Junfa(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 roomId = row["RoomID"].ToString();
string communityId = row["CommunityId"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
using (IDbConnection conn = new SqlConnection(strcon))
{
string sql = @"SELECT HousekeeperName,HousekeeperTel
FROM Tb_HSPR_Building WHERE BuildSNum =
(SELECT BuildSNum FROM Tb_HSPR_Room WHERE RoomID =@RoomID )AND CommID =@CommID";
DataTable dt = conn.ExecuteReader(sql, new { CommID = Community.CommID, RoomID = roomId }).ToDataSet().Tables[0];
return(JSONHelper.FromString(dt));
}
}
/// <summary>
/// 获取历史账单
/// </summary>
private string GetFeesHistoryList(DataRow row)
{
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "用户编码不能为空"));
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区编码不能为空"));
}
string RoomID = row["RoomID"].ToString();
string CommunityId = row["CommunityId"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommunityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
StringBuilder sb = new StringBuilder();
sb.Append(" select FeesID,CostName,DueAmount,DebtsAmount,WaivAmount,0 AS PrecAmount,(PaidAmount+PrecAmount) AS PaidAmount,CONVERT(varchar(100),FeesDueDate,111) as FeesDueDate, CONVERT(varchar(100),FeesChargeDate,111) as FeesChargeDate,");
sb.Append("isnull(IsCharge,0) As IsCharge,FeesStateDate,FeesEndDate from view_HSPR_Fees_Filter ");
sb.AppendFormat(" where CommID={0}", Community.CommID);
sb.AppendFormat(" and RoomID={0}", RoomID);
sb.Append(" and (IsCharge=1 or ISNULL(IsPrec,0)=1) order by FeesID desc ");
DataSet ds = new DbHelperSQLP(strcon).Query(sb.ToString());
return(JSONHelper.FromString(ds.Tables[0]));
}
/// <summary>
/// 获取访客记录
/// </summary>
/// <param name="Row"></param>
/// <returns></returns>
private string GetVisitorRecord(DataRow Row)
{
if (!Row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(Row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!Row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(Row["UserID"].ToString()))
{
return(JSONHelper.FromString(false, "缺少UserID参数"));
}
if (!Row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(Row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "缺少RoomID参数"));
}
string CommunityId = Row["CommunityId"].ToString();
string UserID = Row["UserID"].ToString();
string RoomID = Row["RoomID"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommunityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
DataTable dt;
using (IDbConnection conn = new SqlConnection(strcon))
{
dt = conn.ExecuteReader(@"SELECT TOP 20 * FROM Tb_HSPR_APP_Visitor WHERE UserID = @UserID and CommID = @CommID
and RoomID = @RoomID ORDER BY EndDate DESC",
new { CommID = Community.CommID, UserID = UserID, RoomID = RoomID }).ToDataSet().Tables[0];
}
List <Dictionary <string, object> > list = new List <Dictionary <string, object> >();
if (null != dt && dt.Rows.Count > 0)
{
var hasCar = dt.Columns.Contains("CarSign");
Dictionary <string, object> dic;
foreach (DataRow item in dt.Rows)
{
dic = new Dictionary <string, object>
{
{ "Id", item["Id"] }
, { "CommID", item["CommID"] }
, { "UserID", item["UserID"] }
, { "RoomID", item["RoomID"] }
, { "RoomSign", item["RoomSign"] }
, { "CustID", item["CustID"] }
, { "CustName", item["CustName"] }
, { "Phone", item["Phone"] }
, { "VisitorName", item["VisitorName"] }
, { "VisitorSex", item["VisitorSex"] }
, { "StartDate", item["StartDate"] }
, { "EndDate", item["EndDate"] }
, { "ScanDate", item["ScanDate"] }
, { "IsDelete", item["IsDelete"] }
};
if (hasCar)
{
dic.Add("CarSign", item["CarSign"]);
}
list.Add(dic);
}
}
return(new ApiResult(true, list).toJson());
}
/// <summary>
/// 创建访客
/// </summary>
/// <param name="Row"></param>
/// <returns></returns>
private string CreateVisitor(DataRow Row)
{
if (!Row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(Row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!Row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(Row["UserID"].ToString()))
{
return(JSONHelper.FromString(false, "缺少UserID参数"));
}
if (!Row.Table.Columns.Contains("RelationID") || string.IsNullOrEmpty(Row["RelationID"].ToString()))
{
return(JSONHelper.FromString(false, "缺少RelationID参数"));
}
if (!Row.Table.Columns.Contains("VisitorName") || string.IsNullOrEmpty(Row["VisitorName"].ToString()))
{
return(JSONHelper.FromString(false, "缺少VisitorName参数"));
}
if (!Row.Table.Columns.Contains("VisitorSex") || string.IsNullOrEmpty(Row["VisitorSex"].ToString()))
{
return(JSONHelper.FromString(false, "缺少VisitorSex参数"));
}
if (!Row.Table.Columns.Contains("StartDate") || string.IsNullOrEmpty(Row["StartDate"].ToString()))
{
return(JSONHelper.FromString(false, "缺少StartDate参数"));
}
if (!Row.Table.Columns.Contains("EndDate") || string.IsNullOrEmpty(Row["EndDate"].ToString()))
{
return(JSONHelper.FromString(false, "缺少EndDate参数"));
}
//预设值邀请ID
string Id = Guid.NewGuid().ToString();
//项目小区表ID
string CommunityId = Row["CommunityId"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommunityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string CommID = Community.CommID;
string UserID = Row["UserID"].ToString();
string RelationID = Row["RelationID"].ToString();
string VisitorName = Row["VisitorName"].ToString();
string VisitorSex = Row["VisitorSex"].ToString();
if (!"1".Equals(VisitorSex) && !"0".Equals(VisitorSex))
{
VisitorSex = "0";
}
DateTime StartDate = Convert.ToDateTime(Row["StartDate"].ToString());
DateTime EndDate = Convert.ToDateTime(Row["EndDate"].ToString());
if (EndDate <= StartDate)
{
return(JSONHelper.FromString(false, "有效期结束时间必须大于开始时间"));
}
string RoomID;
string RoomSign;
string CustID;
string CustName;
string Phone;
var carSign = "";
if (Row.Table.Columns.Contains("CarSign"))
{
carSign = Row["CarSign"].ToString();
}
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
dynamic info = conn.Query("SELECT CustId,CustName,CustMobile,RoomId,RoomSign FROM Tb_User_Relation WHERE Id = @RelationID AND UserId = @UserId AND CommunityId = @CommunityId", new { RelationID = RelationID, UserId = UserID, CommunityId = CommunityId }, null, true, null, CommandType.Text).FirstOrDefault();
if (null == info)
{
return(new ApiResult(false, "未查找到该房屋").toJson());
}
RoomID = info.RoomId;
RoomSign = info.RoomSign;
CustID = info.CustId;
CustName = info.CustName;
Phone = info.CustMobile;
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
int result = 0;
using (IDbConnection conn = new SqlConnection(strcon))
{
result = conn.Execute("INSERT INTO Tb_HSPR_APP_Visitor (Id,CommID,UserID,RoomID,RoomSign,CustID,CustName,Phone,VisitorName,VisitorSex,StartDate,EndDate) VALUES(@Id,@CommID,@UserID,@RoomID,@RoomSign,@CustID,@CustName,@Phone,@VisitorName,@VisitorSex,@StartDate,@EndDate)",
new { Id = Id, CommID = CommID, UserID = UserID, RoomID = RoomID, RoomSign = RoomSign, CustID = CustID, CustName = CustName, Phone = Phone, VisitorName = VisitorName, VisitorSex = VisitorSex, StartDate = StartDate, EndDate = EndDate }, null, null, CommandType.Text);
if (!string.IsNullOrEmpty(carSign))
{
conn.Execute($"UPDATE Tb_HSPR_APP_Visitor SET CarSign='{carSign}' WHERE Id='{Id}'");
}
}
if (result > 0)
{
Dictionary <string, object> dic = new Dictionary <string, object>
{
{ "Id", Id }
, { "CommID", CommID }
, { "UserID", UserID }
, { "RoomID", RoomID }
, { "RoomSign", RoomSign }
, { "CustID", CustID }
, { "CustName", CustName }
, { "Phone", Phone }
, { "VisitorName", VisitorName }
, { "VisitorSex", VisitorSex }
, { "CarSign", carSign }
, { "StartDate", StartDate }
, { "EndDate", EndDate }
, { "ScanDate", null }
, { "IsDelete", 0 }
};
return(new ApiResult(true, dic).toJson());
}
return(new ApiResult(false, "生成邀请码失败,请重试").toJson());
}
/// <summary>
/// 获取楼栋管家号码
/// </summary>
private string GetCommunityServiceTel(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区编号不能为空"));
}
string communityId = row["CommunityId"].AsString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
if (Community.CorpID == 1985)
{
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "房屋编号不能为空"));
}
}
string roomId = null;
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].ToString()))
{
roomId = row["RoomID"].ToString();
}
// 没有房号，读取物业中心电话
if (string.IsNullOrEmpty(roomId) || roomId == "0")
{
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
string sql = @"SELECT isnull(Tel,'') AS ServiceTel FROM Tb_Community WHERE Id=@Id";
string serviceTel = conn.Query <string>(sql, new { Id = row["CommunityId"].ToString() }).FirstOrDefault();
if (string.IsNullOrEmpty(serviceTel))
{
return(JSONHelper.FromString(false, "未设置物业中心电话"));
}
return(JSONHelper.FromString(true, serviceTel));
}
}
else
{
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
// 有房号，从erp中读取楼栋管家电话
using (IDbConnection conn = new SqlConnection(strcon))
{
string sql = "";
// 金辉版本，楼栋管家设置的是岗位，俊发、海亮、嘉和
if (Community.CorpID == 2021 || Community.CorpID == 2045 || Community.CorpID == 2046)
{
sql = @"SELECT UserName,MobileTel FROM Tb_Sys_User WHERE isnull(IsDelete,0)=0 AND IsMobile=1 AND isnull(MobileTel,'')<>''
AND UserCode IN (SELECT DISTINCT UserCode FROM Tb_Sys_UserRole WHERE RoleCode IN (SELECT RoleCode FROM Tb_HSPR_BuildHousekeeper WHERE CommID=@CommID AND BuildSNum IN (SELECT BuildSNum FROM Tb_HSPR_Room WHERE CommID=@CommID AND RoomID=@RoomID AND isnull(IsDelete,0)=0)))";
}
else
{
if (Community.CorpID == 1985)//需求4773 新增管家字段
{
sql = @"SELECT ISNULL(HouseKeeperAlias,'') AS UserName,ISNULL(HousekeeperTel,'') AS MobileTel
FROM Tb_HSPR_Building WHERE BuildSNum =
(SELECT BuildSNum FROM Tb_HSPR_Room WHERE RoomID =@RoomID )AND CommID =@CommID";
}
else
{
sql = @"SELECT UserName,MobileTel FROM Tb_Sys_User
WHERE isnull(IsDelete,0)=0 AND isnull(MobileTel,'')<>'' AND UserCode IN (
SELECT UserCode FROM Tb_HSPR_BuildHousekeeper WHERE CommID=@CommID AND BuildSNum=(
SELECT BuildSNum FROM Tb_HSPR_Room WHERE RoomID=@RoomID))";
if (Community.CorpID == 2087)
{
sql = @"SELECT UserName,MobileTel FROM Tb_Sys_User
WHERE isnull(IsDelete,0)=0 AND isnull(MobileTel,'')<>'' AND UserCode IN (
SELECT UserCode FROM Tb_Sys_UserRole WHERE RoleCode IN(
SELECT RoleCode FROM Tb_HSPR_BuildHousekeeper WHERE CommID=@CommID AND BuildSNum=(
SELECT BuildSNum FROM Tb_HSPR_Room WHERE RoomID=@RoomID)))";
}
}
}
DataTable dt = conn.ExecuteReader(sql, new { CommID = Community.CommID, RoomID = roomId }).ToDataSet().Tables[0];
if (dt.Rows.Count <= 0)
{
if (Community.CorpID == 1953)//中集
{
var list = new List <object>();
list.Add(new { UserName = "******", MobileTel = "02861629888" });
return(new ApiResult(true, list).toJson());
}
}
return(JSONHelper.FromString(dt));
}
}
}
/// <summary>
/// 获取社区信息，亲情提示、社区资讯等。
/// </summary>
private string GetComunityNewsList(DataRow row, string infoType)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区编号不能为空"));
}
string communityId = row["CommunityId"].AsString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
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());
}
using (IDbConnection conn = new SqlConnection(strcon))
{
string sql = string.Format(@"SELECT InfoID,Heading,IssueDate,ImageUrl FROM view_HSPR_CommunityInfo_Filter
WHERE isnull(IsDelete,0)=0 AND isnull(IsAudit, 0)=0
AND (CommID={0} OR CommID=0) AND InfoType='{1}' AND (ShowEndDate IS NULL OR ShowEndDate >= getdate())", Community.CommID, infoType);
if (infoType == "sqwh")
{
int intType = 0;
string strType = "";
if (row.Table.Columns.Contains("RedType") && !string.IsNullOrEmpty(row["RedType"].ToString()))
{
intType = AppGlobal.StrToInt(row["RedType"].ToString());
}
switch (intType)
{
case 1:
strType = ";党建团队";
break;
case 2:
strType = ";党建报道";
break;
case 3:
strType = ";党建百科";
break;
case 4:
strType = ";志愿之星";
break;
case 5:
strType = ";志愿组织";
break;
case 6:
strType = ";志愿活动";
break;
}
if (string.IsNullOrEmpty(strType))
{
sql = string.Format(@"SELECT InfoID,Heading,IssueDate,ImageUrl FROM view_HSPR_CommunityInfo_Filter
WHERE isnull(IsDelete,0)=0 AND isnull(IsAudit, 0)=0
AND (CommID={0} OR CommID=0) AND InfoType='{1}' AND (ShowEndDate IS NULL OR ShowEndDate >= getdate()) and (Heading like '%{2}' or Heading like '%{3}' or Heading like '%{4}' or Heading like '%{5}' or Heading like '%{6}' or Heading like '%{7}')", Community.CommID, infoType, ";党建团队", ";党建报道", ";党建百科", ";志愿之星", ";志愿组织", ";志愿活动");
}
else
{
sql = string.Format(@"SELECT InfoID,Heading,IssueDate,ImageUrl FROM view_HSPR_CommunityInfo_Filter
WHERE isnull(IsDelete,0)=0 AND isnull(IsAudit, 0)=0
AND (CommID={0} OR CommID=0) AND InfoType='{1}' AND (ShowEndDate IS NULL OR ShowEndDate >= getdate()) and Heading like '%{2}'", Community.CommID, infoType, strType);
}
}
DataSet ds = GetList(out int pageCount, out int count, sql, pageIndex, pageSize, "IssueDate", 1, "InfoID", strcon);
DataTable dt = ds.Tables[0];
if (infoType == "sqwh")
{
if (dt.Rows.Count > 0)
{
DataColumn dc1 = new DataColumn("redType", typeof(string));
dt.Columns.Add(dc1);
for (int i = 0; i < dt.Rows.Count; i++)
{
string headType = "";
string headName = dt.Rows[i]["Heading"].ToString();
string[] arrayInfo = headName.Split(';');
headName = arrayInfo[0];
if (arrayInfo.Length >= 2)
{
switch (arrayInfo[1])
{
case "党建团队":
headType = "1";
break;
case "党建报道":
headType = "2";
break;
case "党建百科":
headType = "3";
break;
case "志愿之星":
headType = "4";
break;
case "志愿组织":
headType = "5";
break;
case "志愿活动":
headType = "6";
break;
}
}
dt.Rows[i]["Heading"] = headName;
dt.Rows[i]["redType"] = headType;
}
}
}
string result = new ApiResult(true, dt).toJson();
return(result.Insert(result.Length - 1, ",\"PageCount\":" + pageCount));
}
}
/// <summary>
/// 邀请用户_华南城微信
/// </summary>
private string InviteUser_hnc(DataRow Row)
{
#region 基础参数校验
if (!Row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(Row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!Row.Table.Columns.Contains("UserMobile") || string.IsNullOrEmpty(Row["UserMobile"].ToString()))
{
return(JSONHelper.FromString(false, "缺少UserMobile参数"));
}
if (!Row.Table.Columns.Contains("RelationIds") || string.IsNullOrEmpty(Row["RelationIds"].ToString()))
{
return(JSONHelper.FromString(false, "缺少RelationIds参数"));
}
if (!Row.Table.Columns.Contains("InviteMobile") || string.IsNullOrEmpty(Row["InviteMobile"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteMobile参数"));
}
if (!Row.Table.Columns.Contains("InviteName") || string.IsNullOrEmpty(Row["InviteName"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteName参数"));
}
if (!Row.Table.Columns.Contains("InviteType") || string.IsNullOrEmpty(Row["InviteType"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteType参数"));
}
string communityId = Row["CommunityId"].AsString();
string userMobile = Row["UserMobile"].ToString();
string RelationIds = Row["RelationIds"].ToString().Trim();
string inviteMobile = Row["InviteMobile"].ToString();
string inviteName = Row["InviteName"].ToString();
string inviteType = Row["InviteType"].ToString();
List <string> RelationList = new List <string>();
foreach (var item in RelationIds.Split(','))
{
if (!string.IsNullOrEmpty(item))
{
RelationList.Add(item);
}
}
if (RelationList.Count == 0)
{
return(new ApiResult(false, "邀请失败,请选择要邀请的房屋").toJson());
}
#endregion
#region 处理邀请类型
// ERP添加家属信息，0：业主，1：家属，2：租户，3：其它
if (inviteType == "0")
{
inviteType = "0029";
}
else if (inviteType == "1")
{
inviteType = "0030";
}
else if (inviteType == "2")
{
inviteType = "0031";
}
else
{
inviteType = "0032";
}
#endregion
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (null == Community)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
try
{
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
List <string> RoomSignList = new List <string>();
// 当前小区
string sql = @"SELECT CommName FROM Tb_Community WHERE Id=@Id";
string commName = conn.Query <string>(sql, new { Id = communityId }).First();
// 要绑定的房屋
sql = @"SELECT Id AS RelationId, Convert(bigint,CustId) AS CustId, Convert(bigint,RoomId) AS RoomId,RoomSign,CustName FROM Tb_User_Relation WHERE Id IN ('" + string.Join("','", RelationList) + "')";
List <dynamic> relationSet = conn.Query(sql).ToList();
for (int i = 0; i < relationSet.Count; i++)
{
RoomSignList.Add(relationSet[i].RoomSign);
relationSet[i].CustHoldId = Proc_HSPR_Household_Insert_Phone(strcon, Community.CommID, relationSet[i].CustId, relationSet[i].RoomId, inviteName, inviteMobile, inviteType);
}
// 1、查询被邀者的手机号是否已经注册
sql = "SELECT * FROM Tb_User WHERE Mobile = @Mobile";
Tb_User tb_User = conn.QueryFirstOrDefault <Tb_User>(sql, new { Mobile = inviteMobile });
if (null != tb_User)
{
for (int i = 0; i < relationSet.Count; i++)
{
// 1.2、查询被邀者是否已经绑定该房屋
sql = @"SELECT RoomSign,Convert(bigint,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 = tb_User.Id, Relation = relationSet[i].RelationId });
if (userRelationSet.Count() > 0)
{
// 已经绑定但锁定则解锁
sql = @"UPDATE Tb_User_Relation SET Locked = 0, CustHoldId = @HoldID WHERE UserId=@UserId AND RoomId IN
(SELECT RoomId FROM Tb_User_Relation WHERE Id=@Relation)";
conn.Execute(sql, new { UserId = tb_User.Id, HoldID = relationSet[i].CustHoldId, Relation = relationSet[i].RelationId });
}
// 未绑定
else
{
// 1.3、直接给被邀者绑定该房屋
string 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,@Id 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 { Id = tb_User.Id, NewRelation = newRelation, RelationId = relationSet[i].RelationId });
conn.Execute(@"UPDATE Tb_User_Relation SET CustHoldId=@HoldID WHERE Id=@RelationId", new { HoldID = relationSet[i].CustHoldId, RelationId = newRelation });
}
}
}
else
{
// 2、被邀者没有注册账号，创建账号并绑定房屋关系
string UserId = Guid.NewGuid().ToString();
conn.Execute("INSERT INTO Tb_User(Id,Name,Mobile,NickName,Pwd,Sex,RegDate) VALUES(@UserId,@Name,@Mobile,@Name,'123456',1,GETDATE())",
new
{
UserId = UserId,
Name = inviteName,
Mobile = inviteMobile,
},
null, null, CommandType.Text);
for (int i = 0; i < relationSet.Count; i++)
{
string newRelation = Guid.NewGuid().ToString();
conn.Execute(@"INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,RoomId,RegDate,CustName,RoomSign,CustMobile,Locked)
SELECT @Id AS Id, @UserId AS UserId,CommunityId,CustId,RoomID,GETDATE(),CustName,RoomSign,CustMobile,
0 AS Locked FROM Tb_User_Relation WHERE Id = @RelationId",
new
{
Id = newRelation,
UserId = UserId,
RelationId = relationSet[i].RelationId
});
conn.Execute(@"UPDATE Tb_User_Relation SET CustHoldId=@HoldID WHERE Id=@RelationId", new { HoldID = relationSet[i].CustHoldId, RelationId = newRelation });
}
}
string message = string.Format("{0}业主为您绑定了编号为{1}的房屋，关注微信公众号“第一亚太物业”，您可以对该房屋进行报事和缴费等操作。",
commName, string.Join("、", RoomSignList));
SendShortMessage(Row["Mobile"].ToString(), message, out string errorMessage);
return(JSONHelper.FromString(true, "邀请成功"));
}
}
catch (Exception ex)
{
return(JSONHelper.FromString(false, "邀请失败(" + ex.Message + ")"));
}
}
/// <summary>
/// 邀请用户
/// </summary>
private string Invite(DataRow Row)
{
if (!Row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(Row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!Row.Table.Columns.Contains("UserMobile") || string.IsNullOrEmpty(Row["UserMobile"].ToString()))
{
return(JSONHelper.FromString(false, "缺少UserMobile参数"));
}
if (!Row.Table.Columns.Contains("RelationId") || string.IsNullOrEmpty(Row["RelationId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少RelationId参数"));
}
if (!Row.Table.Columns.Contains("InviteMobile") || string.IsNullOrEmpty(Row["InviteMobile"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteMobile参数"));
}
if (!Row.Table.Columns.Contains("InviteName") || string.IsNullOrEmpty(Row["InviteName"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteName参数"));
}
if (!Row.Table.Columns.Contains("InviteType") || string.IsNullOrEmpty(Row["InviteType"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteType参数"));
}
string communityId = Row["CommunityId"].AsString();
string userMobile = Row["UserMobile"].ToString();
string relationId = Row["RelationId"].ToString();
string inviteMobile = Row["InviteMobile"].ToString();
string inviteName = Row["InviteName"].ToString();
int inviteType = AppGlobal.StrToInt(Row["InviteType"].ToString());
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
string sql, message, inviteUserId, newRelation;
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
// 获取App信息
sql = @"SELECT * FROM Tb_AppInfo WHERE Type=2 AND CorpID=@CorpID";
dynamic appInfo = conn.Query(sql, new { CorpID = Community.CorpID }).FirstOrDefault();
string appName = (appInfo?.AppName) ?? "";
string downloadUrl = (appInfo?.DownloadUrl) ?? "";
sql = @"SELECT CommName FROM Tb_Community WHERE Id=@Id";
string commName = conn.Query <string>(sql, new { Id = communityId }).FirstOrDefault();
sql = @"SELECT CustId,RoomId,RoomSign,CustName FROM Tb_User_Relation WHERE Id=@RelationId";
dynamic relationInfo = conn.Query(sql, new { RelationId = relationId }).FirstOrDefault();
string custId = relationInfo.CustId;
string roomId = relationInfo.RoomId;
string roomSign = relationInfo.RoomSign;
string custName = relationInfo.CustName;
// 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 FROM Tb_User_Relation WHERE UserId=@UserId AND RoomId IN
(SELECT RoomId FROM Tb_User_Relation WHERE Id=@Relation)";
if (conn.Query <string>(sql, new { UserId = inviteUserId, Relation = relationId }).Count() > 0)
{
conn.Execute(@"UPDATE Tb_User_Relation SET Locked=0 WHERE UserId=@UserId AND RoomId IN
(SELECT RoomId FROM Tb_User_Relation WHERE Id=@Relation)",
new { UserId = inviteUserId, Relation = relationId });
return(JSONHelper.FromString(false, "该用户已经绑定该房屋"));
}
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,@Id AS UserId,CommunityId,CustId,roomid,getdate(),CustName,RoomSign,CustMobile,
0 AS Locked,@InviteType AS CustHoldId
FROM Tb_User_Relation WHERE Id=@RelationId";
conn.Execute(sql, new { Id = inviteUserId, NewRelation = newRelation, RelationId = relationId, InviteType = inviteType });
message = string.Format("{0}温馨提示：\"{1}\"业主{2}为您绑定了编号为【{3}】房屋，您可以对该房屋进行报事和缴费等操作。",
appName, commName, custName, roomSign);
SendMessage(Community.CorpID, inviteMobile, message);
}
}
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,@Mobile,'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, InviteType = inviteType });
if (string.IsNullOrEmpty(downloadUrl))
{
message = string.Format("{0}温馨提示：\"{1}\"业主{2}邀请您使用{0}，您的账号为：{3}，密码为：123456，请及时下载登录{0}并修改密码，下载地址：{4}", appName, commName, custName, inviteMobile, downloadUrl);
}
else
{
message = string.Format("{0}温馨提示：\"{1}\"业主{2}邀请您使用{0}，您的账号为：{3}，密码为：123456，请及时下载登录{0}并修改密码。", appName, commName, custName, inviteMobile);
}
SendMessage(Community.CorpID, inviteMobile, message);
}
using (IDbConnection conn2 = new SqlConnection(strcon))
{
// ERP添加家属信息
if (inviteType == 1)
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@HoldID", 0, DbType.Int64, ParameterDirection.InputOutput);
parameters.Add("@CommID", Community.CommID);
parameters.Add("@CustID", custId);
parameters.Add("@RoomID", roomId);
parameters.Add("@Surname", inviteName);
parameters.Add("@Name", inviteName);
parameters.Add("@MobilePhone", inviteMobile);
parameters.Add("@MemberName", inviteName);
parameters.Add("@Linkman", inviteName);
parameters.Add("@LinkManTel", inviteMobile);
parameters.Add("@Relationship", "0032");
conn2.Execute("Proc_HSPR_Household_Insert", 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 });
}
// 添加租户信息
else
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@TenantName", inviteName);
parameters.Add("@TenantMobile", inviteMobile);
parameters.Add("@CommID", Community.CommID);
parameters.Add("@RoomID", roomId);
parameters.Add("@CustID", 0, DbType.Int64, ParameterDirection.Output);
conn2.Execute("Proc_HSPR_Customer_AddTenant", parameters, null, null, CommandType.StoredProcedure);
conn.Execute("UPDATE Tb_User_Relation SET CustID=@CustID WHERE Id=@RelationId",
new { CustID = parameters.Get <Int64>("@CustID"), RelationId = newRelation });
}
}
if (resultSet.Count() > 0)
{
return(JSONHelper.FromString(false, "该手机号已注册，已直接绑定房号"));
}
else
{
return(JSONHelper.FromString(true, "邀请成功"));
}
}
}
