/// <summary>
/// 获取该楼层下房屋列表
/// </summary>
public string GetFloorRoomList(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区ID不能为空"));
}
if (!row.Table.Columns.Contains("BuildSNum") || string.IsNullOrEmpty(row["BuildSNum"].ToString()))
{
return(JSONHelper.FromString(false, "楼栋不能为空"));
}
if (!row.Table.Columns.Contains("UnitSNum") || string.IsNullOrEmpty(row["UnitSNum"].ToString()))
{
return(JSONHelper.FromString(false, "单元不能为空"));
}
if (!row.Table.Columns.Contains("FloorNum") || string.IsNullOrEmpty(row["FloorNum"].ToString()))
{
return(JSONHelper.FromString(false, "楼层不能为空"));
}
string communityId = row["CommunityId"].ToString();
int buildSNum = AppGlobal.StrToInt(row["BuildSNum"].ToString());
int unitSNum = AppGlobal.StrToInt(row["UnitSNum"].ToString());
int floorNum = AppGlobal.StrToInt(row["FloorNum"].ToString());
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new CostInfo().GetConnectionStringStr(Community);
using (IDbConnection con = new SqlConnection(strcon))
{
string sql = string.Format(@"SELECT a.RoomID,isnull(a.RoomSign,a.RoomName) AS RoomSign,a.RoomName,b.CustID,c.CustName,c.MobilePhone,a.ContSubDate
FROM Tb_HSPR_Room a
LEFT JOIN Tb_HSPR_CustomerLive b ON a.RoomID=b.RoomID
LEFT JOIN Tb_HSPR_Customer c ON b.CustID=c.CustID
WHERE isnull(b.IsDelLive,0)=0 AND b.LiveType=1 AND isnull(c.IsDelete,0)=0
AND isnull(a.IsDelete, 0)=0 AND a.CommID={0} AND a.BuildSNum={1} AND a.UnitSNum={2} AND a.FloorSNum={3}",
Community.CommID, buildSNum, unitSNum, floorNum);
return(JSONHelper.FromString(con.ExecuteReader(sql).ToDataSet().Tables[0]));
}
}
/// <summary>
/// 检查该绑定关系的业主信息是否还存在,不存在则删除绑定关系
/// </summary>
private void CheckRoomInfoCanDelete(string CommunityId, string RoomID, string MobilePhone, string RelationId)
{
if (string.IsNullOrEmpty(CommunityId))
{
return;
}
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommunityId);
//构造链接字符串
if (null == Community)
{
return;
}
string strcon = new CostInfo().GetConnectionStringStr(Community);
//默认为1,防止默认删除
int count = 1;
using (IDbConnection conn = new SqlConnection(strcon))
{
count = conn.Query(@"SELECT b.CustID,b.CustName,b.MobilePhone,c.RoomSign FROM Tb_HSPR_CustomerLive a
LEFT JOIN Tb_HSPR_Customer b ON a.CustID=b.CustID
LEFT JOIN Tb_HSPR_Room c on a.RoomID=c.RoomID
WHERE a.LiveType=1 AND isnull(a.IsDelLive,0)=0 AND a.RoomID = @RoomID
AND b.MobilePhone like @MobilePhone",
new { RoomID = RoomID, MobilePhone = "%" + MobilePhone + "%" }).Count();
}
if (count <= 0)
{
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
conn.Execute("DELETE Tb_User_Relation WHERE Id = @Id", new { Id = RelationId });
}
}
}
/// <summary>
/// 获取欠费或预存信息
/// </summary>
private string GetFeesStatistics_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 communityId = row["CommunityId"].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 CostInfo().GetConnectionStringStr(Community);
string sql = string.Format(@"SELECT PrecAmount FROM
(
SELECT SUM(a.PrecAmount) AS PrecAmount FROM view_HSPR_PreCosts_Filter a
WHERE a.CommID={0} AND a.RoomID={1}
AND convert(varchar(8000),a.CostNames)='住宅物业服务费'
AND a.PrecAmount>0 AND a.IsPrec=1
) AS t" , Community.CommID, RoomID);
DataSet ds = new DbHelperSQLP(strcon).Query(sql);
if (ds != null & ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
decimal PrecAmount = AppGlobal.StrToDec(ds.Tables[0].Rows[0]["PrecAmount"].ToString());
DateTime dateTime = DateTime.Now.AddMonths(1);
dateTime = new DateTime(dateTime.Year, dateTime.Month, 1).AddDays(-1);
// 读取未缴
sql = string.Format(@"SELECT SUM(ISNULL(x.DebtsAmount,0) + ISNULL(x.LateFeeAmount,0)) AS DebtsAmount FROM
view_HSPR_Fees_SearchFilter x WHERE x.DebtsAmount > 0 AND ISNULL(x.IsCharge, 0) = 0 AND ISNULL(x.IsBank, 0) = 0 AND ISNULL(x.IsPrec, 0) = 0 AND ISNULL(IsFreeze, 0) = 0
AND x.RoomID = {0}" , RoomID);
sql += " AND x.CustID IN(SELECT CustID FROM Tb_HSPR_CustomerLive xx where xx.RoomID=x.RoomID AND ISNULL(IsDelLive,0)=0 AND LiveType = 1) ";
DataTable dt = new DbHelperSQLP(strcon).Query(sql).Tables[0];
decimal DebtsAmount = AppGlobal.StrToDec(dt.Rows[0]["DebtsAmount"].ToString());
// 俊发判断是不是成都地区，如果是成都，按月缴费，其他地区按年
int BillingCycle = 1;
if (Community.CorpID == 1985)
{
using (var conn = new SqlConnection(strcon))
{
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());
}
else
{
return(new ApiResult(true, new { PrecAmount = 0, DebtsAmount = 0, BillingCycle = 1 }).toJson());
}
}
/// <summary>
/// 获取该账号在当前房屋下的身份以及绑定了当前房屋的账号数量
/// </summary>
private string GetIdentityAndBindingCount(DataRow row)
{
if (!row.Table.Columns.Contains("Mobile") || string.IsNullOrEmpty(row["Mobile"].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("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "房屋编号不能为空"));
}
string communityId = row["CommunityId"].AsString();
string roomID = row["RoomID"].AsString();
string mobile = row["Mobile"].AsString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new CostInfo().GetConnectionStringStr(Community);
using (var conn = new SqlConnection(PubConstant.UnifiedContionString))
{
string sql = string.Format(@"SELECT count(1) AS Count FROM Tb_User_Relation WHERE isnull(Locked,0)=0 AND RoomId='{0}';", roomID);
string bindingCount = conn.Query(sql).First().Count.ToString();
using (var conn2 = new SqlConnection(strcon))
{
sql = string.Format(@"DECLARE @RoomID BIGINT, @IsCust INT, @IsTenant INT, @IsHousehold INT;
SET @RoomID={0};
SELECT @IsCust=count(1) FROM Tb_HSPR_CustomerLive a LEFT JOIN Tb_HSPR_Customer b ON a.CustID = b.CustID
WHERE isnull(IsDelLive,0)= 0 AND LiveType = 1
AND a.RoomID = @RoomID AND b.MobilePhone LIKE '%{1}%';
SELECT @IsTenant=count(1) FROM Tb_HSPR_Household a
WHERE a.RoomID=@RoomID AND a.Relationship='{4}' AND a.MobilePhone LIKE '%{1}%';
SELECT @IsHousehold = count(1) FROM Tb_HSPR_Household
WHERE isnull(IsDelete, 0) = 0 AND RoomID = @RoomID AND MobilePhone LIKE '%{1}%'
AND Relationship NOT IN('{2}', '{3}', '{4}', '{5}');
IF @IsCust=0 AND @IsTenant=0 AND @IsHousehold=0
BEGIN
IF exists(SELECT * FROM Tb_HSPR_Household WHERE RoomID=@RoomID AND MobilePhone LIKE '%{1}%'
AND Relationship='{3}')
BEGIN
SET @IsCust=1;
END
END
IF @IsCust=1 AND @IsHousehold=1
BEGIN
SET @IsHousehold=0;
END
SELECT @IsCust AS IsCust, @IsTenant AS IsTenant, @IsHousehold AS IsHousehold; ",
roomID, mobile, UserRoomIdentityCode.Customer1, UserRoomIdentityCode.Customer2,
UserRoomIdentityCode.Tenant, UserRoomIdentityCode.Other);
GridReader multi = conn2.QueryMultiple(sql);
int isCust = 0, isTenant = 0, isHousehold = 0;
while (!multi.IsConsumed)
{
//读取当前结果集
var result = multi.Read().ToList()[0];
if (result != null)
{
isCust = result.IsCust;
isTenant = result.IsTenant;
isHousehold = result.IsHousehold;
}
break;
}
return(new ApiResult(true, new { BindingCount = bindingCount, IsCust = isCust, IsTenant = isTenant, IsHousehold = isHousehold }).toJson());
}
}
}
private string AddRoomAndUserInfo(DataRow row)
{
try
{
string CustID = row["CustID"].ToString();
string CommID = row["CommID"].ToString();
string RoomID = row["RoomID"].ToString();
string Phone = row["Phone"].ToString();
string CustName = row["CustName"].ToString();
string RoomSign = row["RoomSign"].ToString();
string BuildingName = row["BuildingName"].ToString();
string floor = row["floor"].ToString();
string Unit = row["Unit"].ToString();
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(CommID);
//构建链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
DateTime dtNow = DateTime.Now;
string connStr = new CostInfo().GetConnectionStringStr(Community);
using (IDbConnection con = new SqlConnection(connStr))
{
//判断用户信息进行处理
string sql = "select CustID from Tb_HSPR_Customer with(nolock) where CustID='" + CustID.Trim() + "'";
DataTable dt2 = con.ExecuteReader(sql).ToDataSet().Tables[0];
if (dt2.Rows.Count <= 0)
{
//需要添加用户信息
string insertSql = @"INSERT INTO Tb_HSPR_Customer(CustID,CommID,CustName,IsDelete,MobilePhone,Memo) VALUES(" + CustID + "," + Community.CommID + ",'" + CustName + "',0,'" + Phone + "','通过同步接口自动添加')";
con.Execute(insertSql);
}
//判断房屋信息进行处理
sql = "select RoomID from Tb_HSPR_Room with(nolock) where RoomID='" + RoomID.Trim() + "'";
dt2 = con.ExecuteReader(sql).ToDataSet().Tables[0];
if (dt2.Rows.Count <= 0)
{
//判断楼栋信息进行处理
string buildSnum = "1";
string buildID = Community.CommID + "000001";
sql = "select BuildID,BuildSNum from Tb_HSPR_Building with(nolock) where BuildName='" + BuildingName.Trim() + "' and CommID=" + Community.CommID;
DataTable dt = con.ExecuteReader(sql).ToDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
buildID = dt.Rows[0]["BuildID"].ToString();
buildSnum = dt.Rows[0]["BuildSNum"].ToString();
}
else
{
sql = "select top 1 BuildID,BuildSNum from tb_hspr_building with(nolock) where commid=" + Community.CommID + " order by BuildID desc";
dt = con.ExecuteReader(sql).ToDataSet().Tables[0];
if (dt.Rows.Count > 0)
{
buildID = (long.Parse(dt.Rows[0]["BuildID"].ToString()) + 1).ToString();
buildSnum = (int.Parse(dt.Rows[0]["BuildSNum"].ToString()) + 1).ToString();
}
//添加楼栋信息
string insertSq1l = @"INSERT INTO tb_hspr_building(BuildID,CommID,BuildSign,BuildName,BuildSNum,IsDelete) VALUES(" + buildID + "," + Community.CommID + ",'" + BuildingName + "','" + BuildingName + "'," + buildSnum + ",0)";
con.Execute(insertSq1l);
}
//添加房屋信息
string insertSql2 = @"INSERT INTO Tb_HSPR_Room(RoomID,CommID,RoomSign,RoomName,RegionSNum,BuildSNum,UnitSNum,FloorSNum,PropertyRights,BuildArea,InteriorArea,CommonArea,PropertyUses,RoomState,ChargeTypeID,UsesState
,IsDelete,PoolRatio,TakeOverDate,ActualSubDate,BuildsRenovation) VALUES(" + RoomID + "," + Community.CommID + ",'" + RoomSign + "','" + RoomSign + "',0," + buildSnum + "," + Unit + "," + floor + ",'自有产权',0.00,0.00,0.00,'住宅',1,0,0,0,0.00,'" + dtNow.ToString() + "','" + dtNow.ToString() + "','非精装')";
con.Execute(insertSql2);
}
//判断房屋和用户关系处理
sql = "select LiveID,CustID from Tb_HSPR_CustomerLive with(nolock) where RoomID='" + RoomID.Trim() + "'";
DataTable dt1 = con.ExecuteReader(sql).ToDataSet().Tables[0];
if (dt1.Rows.Count > 0)
{
if (!CustID.Equals(dt1.Rows[0]["CustID"].ToString()))
{
string updateSq1 = @"update Tb_HSPR_CustomerLive set CustID=" + CustID + "where LiveID=" + dt1.Rows[0]["LiveID"].ToString();
con.Execute(updateSq1);
}
}
else
{
long liveID = long.Parse(dtNow.ToString("yyyyMMddHHmmssfff") + "1");
//添加中间表关系
string insertSq1l = @"INSERT INTO Tb_HSPR_CustomerLive(LiveID,RoomID,CustID,LiveType,ChargingTime,IsActive,IsDelLive,IsDebts,IsSale,LiveState) VALUES(" + liveID + "," + RoomID + "," + CustID + ",1,'" + dtNow.ToString() + "',1,0,1,0,0)";
con.Execute(insertSq1l);
}
}
return(JSONHelper.FromString(true, "添加成功"));
}
catch (Exception ex)
{
return(JSONHelper.FromString(false, "添加失败,请联系管理员"));
}
}