/// <summary>
/// 获取房产简单信息
/// </summary>
private string GetRoomSimpleInfo(DataRow row)
{
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "房屋ID不能为空"));
}
var roomId = AppGlobal.StrToLong(row["RoomID"].ToString());
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var sql = @"SELECT a.RoomID,a.RoomName,a.RoomSign,b.StateName AS RoomState
FROM Tb_HSPR_Room a
LEFT JOIN Tb_HSPR_RoomState b ON a.RoomState=b.RoomState
WHERE a.RoomID=@RoomID";
var data = conn.Query(sql, new { RoomID = roomId }).FirstOrDefault();
if (data == null)
{
return(JSONHelper.FromString(false, "未查询到房屋信息"));
}
return(new ApiResult(true, data).toJson());
}
}
/// <summary>
/// 模糊查询报事设备
/// </summary>
private string GetIncidentEquipments(DataRow row)
{
var regionalId = 0L;
var spaceId = "";
var keywords = "";
var pageSize = 20;
var pageIndex = 1;
if (row.Table.Columns.Contains("RegionalID") && !string.IsNullOrEmpty(row["RegionalID"].ToString()))
{
regionalId = AppGlobal.StrToLong(row["RegionalID"].ToString());
}
if (row.Table.Columns.Contains("SpaceID") && !string.IsNullOrEmpty(row["SpaceID"].ToString()))
{
spaceId = row["SpaceID"].ToString();
}
if (row.Table.Columns.Contains("Keywords") && !string.IsNullOrEmpty(row["Keywords"].ToString()))
{
keywords = row["Keywords"].ToString();
}
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 (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var sql = @"SELECT a.EquipmentId,a.EquipmentName,b.SpaceName FROM Tb_Eq_Equipment a
LEFT JOIN Tb_Eq_Space b ON a.SpaceId=b.SpaceId
WHERE isnull(a.IsDelete,0)=0 ";
if (regionalId != 0 || !string.IsNullOrEmpty(spaceId))
{
sql += $@" AND a.SpaceId IN
(
SELECT SpaceId FROM Tb_Eq_Space WHERE IncidentArea=convert(bigint,{regionalId})
AND IncidentArea<>convert(bigint,0) AND IncidentArea<>'' AND isnull(IsDelete,0)=0
UNION
SELECT '{spaceId}' AS SpaceId
)";
}
if (!string.IsNullOrEmpty(keywords))
{
sql += $" AND a.EquipmentName LIKE '%{keywords}%' ";
}
var data = GetListDapper(out int pageCount, out int count, sql, pageIndex, pageSize, "EquipmentName", 1, "EquipmentId", conn);
var json = new ApiResult(true, data).toJson();
json = json.Insert(json.Length - 1, ",\"PageCount:\":" + pageCount);
return(json);
}
}
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 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());
}
}
/// <summary>
///
/// </summary>
private string GetCustomerHouseholdSimpleInfo(DataRow row)
{
if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "缺少参数CustID").toJson());
}
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = 0L;
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].AsString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
}
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var sql = @"SELECT CustID,CustName,isnull(MobilePhone,LinkmanTel) AS CustMobile
FROM Tb_HSPR_Customer WHERE CustID=@CustID;
SELECT HoldID,isnull(MemberName,isnull(Surname,isnull(Name,'未登记姓名'))) AS HoldName,
isnull(MobilePhone,LinkManTel) AS HoldMobile,isnull(b.DictionaryName,'未知') AS RelationShip
FROM Tb_HSPR_Household a
LEFT JOIN Tb_Dictionary_Relation b ON a.Relationship=b.DictionaryCode
WHERE a.CustID=@CustID AND isnull(a.IsDelete,0)=0";
if (roomId != 0)
{
sql += " AND a.RoomID=@RoomID";
}
var reader = conn.QueryMultiple(sql, new { CustID = custId, RoomID = roomId });
var custInfo = reader.Read().FirstOrDefault();
var holdInfo = reader.Read();
return(new ApiResult(true, new { CustInfo = custInfo, HoldInfo = holdInfo }).toJson());
}
}
/// <summary>
/// 获取预存记录
/// </summary>
private string GetPrecPaidHistory(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "请选择房屋所在项目").toJson());
}
if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "未指定欠费主体").toJson());
}
var communityId = row["CommunityId"].AsString();
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = 0L;
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].AsString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
}
var pageSize = 10;
var pageIndex = 1;
if (row.Table.Columns.Contains("PageIndex") && !string.IsNullOrEmpty(row["PageIndex"].AsString()))
{
pageIndex = AppGlobal.StrToInt(row["PageIndex"].AsString());
pageIndex = (pageIndex <= 0 ? 1 : pageIndex);
}
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查找到小区信息"));
}
var commId = AppGlobal.StrToInt(community.CommID);
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_CHARGE);
using (var chargeConn = new SqlConnection(chargeConnectionString))
{
var sql = $@"SELECT * FROM
(
SELECT a.BillsDate,a.PrecAmount,
stuff((
SELECT ',' + CostName
FROM Tb_HSPR_PreCostsDetail x
LEFT JOIN {community.DBName}.dbo.Tb_HSPR_CostItem y ON x.CostID=y.CostID
WHERE x.ReceID=a.ReceID
AND isnull(x.IsDelete,0) = 0
FOR XML PATH ('')
), 1, 1, '') AS CostNames,
row_number() OVER (ORDER BY a.BillsDate DESC) AS RID
FROM Tb_HSPR_PreCostsReceipts a
WHERE a.CustID=@CustID AND a.RoomID=@RoomID
) AS t
WHERE t.RID>(@PageIndex-1)*@PageSize AND t.RID<=(@PageIndex*@PageSize);";
var data = chargeConn.Query(sql, new { CustID = custId, RoomID = roomId, PageSize = pageSize, PageIndex = pageIndex });
return(new ApiResult(true, data).toJson());
}
}
/// <summary>
/// 手动绑定房屋
/// </summary>
private string BindingRoom(DataRow row)
{
if (!row.Table.Columns.Contains("UserID") || string.IsNullOrEmpty(row["UserID"].AsString()))
{
return(new ApiResult(false, "缺少参数UserID").toJson());
}
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].AsString()))
{
return(new ApiResult(false, "缺少参数RoomID").toJson());
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "缺少参数CommunityId").toJson());
}
var userId = row["UserID"].AsString();
var roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
var communityId = row["CommunityId"].AsString();
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查询到小区信息"));
}
// 读取当前用户身份标识及关联客户CustID
var userIdentity = GetUserIdentity(community, userId, roomId, out long custId, out long holdId);
// 绑定管控
var control = GetAppBindingRoomControl(community.CorpID, community.Id);
if (control.CheckCustName == false && control.CheckCustMobile == false && control.CheckCustPapersInfo == false)
{
if (userIdentity == UserRoomIdentity.FamilyMember && control.AllowHouseholdBind == false)
{
return(JSONHelper.FromString(false, "抱歉，此房屋暂不支持家庭成员绑定"));
}
if (userIdentity == UserRoomIdentity.Tenant && control.AllowLesseeBind == false)
{
return(JSONHelper.FromString(false, "抱歉，此房屋暂不支持租户绑定"));
}
if (userIdentity == UserRoomIdentity.Other && control.AllowStrangerBind == false)
{
return(JSONHelper.FromString(false, "抱歉，此房屋暂不支持陌生人绑定"));
}
}
using (var erpConn = new SqlConnection(GetConnectionStr(community)))
{
var chars = new char[] { ',', '，', '、', '/', '／', '\\', '＼', '|', '｜', ' ', ';', '；', ':', '：', '.', '．', '。' };
var tmp = new CustomerIdentity[3];
var sql = @"SELECT a.CustID,a.LiveType,
ltrim(rtrim(isnull(b.CustName,''))) AS CustName,
ltrim(rtrim(isnull(b.MobilePhone,''))) AS CustMobile,
ltrim(rtrim(isnull(b.Linkman,''))) AS Linkman,
ltrim(rtrim(isnull(b.LinkmanTel,''))) AS LinkmanTel,
ltrim(rtrim(isnull(b.PaperCode,''))) AS CustPapers
FROM Tb_HSPR_CustomerLive a
LEFT JOIN Tb_HSPR_Customer b ON a.CustID=b.CustID
WHERE a.RoomID=@RoomID AND isnull(a.IsDelLive,0)=0
ORDER BY a.IsActive DESC,a.LiveType ASC;";
var custs = erpConn.Query <CustomerIdentity>(sql, new { RoomID = roomId }).ToList();
if (custs.Count == 0)
{
return(JSONHelper.FromString(false, "该房屋暂未关联客户，无法绑定"));
}
// 需要验证客户姓名
if (control.CheckCustName)
{
if (!row.Table.Columns.Contains("CustName") || string.IsNullOrEmpty(row["CustName"].AsString()))
{
return(JSONHelper.FromString(false, "客户姓名不能为空"));
}
tmp[0] = custs.Find(obj =>
{
if (!string.IsNullOrEmpty(obj.CustName))
{
if (obj.CustName.Split(chars, StringSplitOptions.RemoveEmptyEntries).Contains(row["CustName"].AsString()))
{
return(true);
}
}
if (!string.IsNullOrEmpty(obj.Linkman))
{
if (obj.Linkman.Split(chars, StringSplitOptions.RemoveEmptyEntries).Contains(row["CustName"].AsString()))
{
return(true);
}
}
return(false);
});
if (tmp[0] == null)
{
return(JSONHelper.FromString(false, "客户姓名验证失败"));
}
}
// 需要验证客户手机号
if (control.CheckCustMobile)
{
if (!row.Table.Columns.Contains("CustMobile") || string.IsNullOrEmpty(row["CustMobile"].AsString()))
{
return(JSONHelper.FromString(false, "客户手机号不能为空"));
}
tmp[1] = custs.Find(obj =>
{
if (!string.IsNullOrEmpty(obj.CustMobile))
{
if (obj.CustMobile.Split(chars, StringSplitOptions.RemoveEmptyEntries).Contains(row["CustMobile"].AsString()))
{
return(true);
}
}
if (!string.IsNullOrEmpty(obj.LinkmanTel))
{
if (obj.LinkmanTel.Split(chars, StringSplitOptions.RemoveEmptyEntries).Contains(row["CustMobile"].AsString()))
{
return(true);
}
}
return(false);
});
if (tmp[1] == null)
{
return(JSONHelper.FromString(false, "客户手机号验证失败"));
}
}
// 需要验证客户证件号码
if (control.CheckCustPapersInfo)
{
if (!row.Table.Columns.Contains("CustPapers") || string.IsNullOrEmpty(row["CustPapers"].AsString()))
{
return(JSONHelper.FromString(false, "客户证件号码不能为空"));
}
String custPapers = row["CustPapers"].ToString();
//如果参数仅有四位 则验证后四位
if (!String.IsNullOrEmpty(custPapers) && custPapers.Length == 4)
{
tmp[2] = custs.Find(obj =>
{
if (!string.IsNullOrEmpty(obj.CustPapers))
{
var array = obj.CustPapers.Split(chars, StringSplitOptions.RemoveEmptyEntries);
foreach (var model in array)
{
if (model.ToLower().EndsWith(custPapers.ToLower()))
{
return(true);
}
}
}
return(false);
});
}
else
{
tmp[2] = custs.Find(obj =>
{
if (!string.IsNullOrEmpty(obj.CustPapers))
{
if (obj.CustPapers.Split(chars, StringSplitOptions.RemoveEmptyEntries).Contains(custPapers))
{
return(true);
}
}
return(false);
});
}
if (tmp[2] == null)
{
return(JSONHelper.FromString(false, "客户证件号码验证失败"));
}
}
tmp = tmp.Where(obj => obj != null).ToArray();
var codes = tmp.Select(obj => obj.GetHashCode());
// 如果存在多个，说明验证是在多个客户之间完成（如：满足客户1姓名 + 满足客户2手机号），不允许
if (codes.Distinct().Count() != 1)
{
return(JSONHelper.FromString(false, "客户信息验证失败"));
}
return(BindingRoom(control, community, userId, tmp[0].CustID, roomId));
}
}
/// <summary>
/// 获取欠费列表
/// </summary>
private string GetArrearsFees(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].AsString()))
{
return(new ApiResult(false, "用户信息错误").toJson());
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "请选择房屋所在项目").toJson());
}
if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "未指定欠费主体").toJson());
}
var userId = row["UserId"].AsString();
var communityId = row["CommunityId"].AsString();
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = 0L;
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].AsString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
}
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查找到小区信息"));
}
var commId = AppGlobal.StrToInt(community.CommID);
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_CHARGE);
List <string> BindCostList = new List <string>();
JObject PaymentCycleSetting = new JObject();
using (IDbConnection conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var sql = @"SELECT isnull(object_id(N'Tb_HSPR_PaymentBind',N'U'),0)";
if (conn.QueryFirstOrDefault <long>(sql) != 0)
{
BindCostList = conn.Query <string>("SELECT CostID FROM Tb_HSPR_PaymentBind WHERE CommID = @CommID AND ISNULL(IsDelete,0) = 0", new { CommID = commId }).ToList();
}
sql = @"SELECT isnull(object_id(N'Tb_HSPR_PaymentBindingDateModelSet',N'U'),0)";
if (conn.QueryFirstOrDefault <long>(sql) != 0)
{
// 需求9759，视图新增返回参数IsHis，用于判断是否需要缴纳历史欠费
var Setting = conn.QueryFirstOrDefault("SELECT CheckBoxID,MonthNum,IsHis FROM View_HSPR_PaymentBindingDateModelSet_Filter WHERE CommID = @CommID", new { CommID = commId });
// Type值：0=默认全选，1=任意选择，2=按月份数量一次性整月选择，3=一次性至少缴纳几个月费用
int Type = 0;
int MonthNum = 0;
if (null != Setting)
{
int CheckBoxID = 1;
if (Setting.CheckBoxID != null)
{
CheckBoxID = Convert.ToInt32(Setting.CheckBoxID);
}
if (Setting.MonthNum != null)
{
MonthNum = Convert.ToInt32(Setting.MonthNum);
}
switch (CheckBoxID)
{
case 1:
{
Type = 0;
MonthNum = 0;
}
break;
case 2:
{
Type = 1;
MonthNum = 0;
}
break;
case 4:
{
Type = 2;
MonthNum = 1;
}
break;
case 6:
{
Type = 2;
MonthNum = 3;
}
break;
case 8:
{
Type = 2;
MonthNum = 6;
}
break;
case 10:
{
Type = 2;
MonthNum = 12;
}
break;
case 12:
{
Type = 3;
}
break;
default:
{
Type = 0;
MonthNum = 0;
}
break;
}
}
PaymentCycleSetting.Add("Type", Type);
PaymentCycleSetting.Add("MonthNum", MonthNum);
PaymentCycleSetting.Add("IsHis", Setting != null ? Setting.IsHis : 1);
}
}
var shieldCost = "";
// 查询需要屏蔽的费用
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT isnull(object_id(N'Tb_Control_AppCostItem',N'U'),0)";
// 优先取ERP
if (string.IsNullOrEmpty(shieldCost) && appConn.Query <long>(sql).FirstOrDefault() != 0)
{
sql = @"SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND CommunityID=@Id AND AllowShow=0;
SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND isnull(CommunityID,'')='' AND AllowShow=0;";
var reader = appConn.QueryMultiple(sql, new { CorpID = community.CorpID, Id = community.Id });
var data1 = reader.Read <string>().FirstOrDefault();
var data2 = reader.Read <string>().FirstOrDefault();
var data = data1 ?? data2;
if (!string.IsNullOrEmpty(data))
{
using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
sql = $@"SELECT CostCode FROM Tb_HSPR_CorpCostItem
WHERE CorpCostID IN
(
SELECT Value FROM SplitString('{data}',',',1)
)";
var costCodes = erpConn.Query <string>(sql).Select(obj => $"'{obj}'");
shieldCost = $@"AND CostCode NOT IN ({ string.Join(",", costCodes.ToArray()) })";
}
}
}
}
using (var chargeConn = new SqlConnection(chargeConnectionString))
{
var list = new List <PMSAppFeesCostSimpleModel>();
var sql = $@"/* 欠费未缴清 */
SELECT FeesID,x.CostID,CostName,isnull(SysCostSign,'Unknown') AS SysCostSign,
isnull(DueAmount,0.00) AS DueAmount,
isnull(DebtsAmount,0.00) AS DebtsAmount,
CASE WHEN isnull(x.DebtsLateAmount, 0)>0
THEN dbo.funGetLateFeeDebts(x.CommID,x.FeesID,isnull(x.DebtsLateAmount, 0))
ELSE 0.0 END AS LateFeeAmount,
convert(varchar(10),isnull(FeesDueDate,getdate()),120) AS FeesDueDate
FROM Tb_HSPR_Fees x
LEFT JOIN { community.DBName }.dbo.Tb_HSPR_CostItem y ON x.CostID=y.CostID
WHERE x.CustID=@CustID AND x.RoomID=@RoomID
AND isnull(x.IsCharge,0)=0 AND isnull(x.IsBank,0)=0 AND isnull(x.IsPrec,0)=0 AND isnull(IsFreeze,0)=0
{ shieldCost }
ORDER BY SysCostSign ASC,FeesDueDate DESC;
/* 仅剩违约金 */
/*
SELECT FeesID,CostID,CostName,isnull(SysCostSign,'Unknown') AS SysCostSign,
isnull(DueAmount,0.00) AS DueAmount,
isnull(DebtsAmount,0.00) AS DebtsAmount,
isnull(LateFeeAmount,0.00) AS LateFeeAmount,
convert(varchar(10),isnull(FeesDueDate,getdate()),120) AS FeesDueDate
FROM view_HSPR_Fees_SearchFilter x
WHERE x.CustID=@CustID AND x.RoomID=@RoomID
AND isnull(x.IsCharge,0)=1 AND isnull(x.IsBank,0)=0 AND isnull(x.IsPrec,0)=0 AND isnull(IsFreeze,0)=0
AND isnull(x.LateFeeAmount,0.0)>0
{ shieldCost }
ORDER BY SysCostSign ASC,FeesDueDate DESC;
*/";
var reader = chargeConn.QueryMultiple(sql, new { CommID = community.CommID, RoomID = roomId, CustID = custId });
var data1 = reader.Read();
//var data2 = reader.Read();
var data = data1.ToList();
for (int i = 0; i < data.Count; i++)
{
var feesInfo = data[i];
var tmp = list.Find(obj => obj.CostID == feesInfo.CostID);
if (tmp == null)
{
tmp = new PMSAppFeesCostSimpleModel()
{
CostID = feesInfo.CostID,
CostName = feesInfo.CostName,
SysCostSign = feesInfo.SysCostSign
};
tmp.Fees = new List <PMSAppFeesSimpleModel>();
list.Add(tmp);
if (tmp.SysCostSign == "Unknown")
{
tmp.SysCostSign = null;
}
}
var model = new PMSAppFeesSimpleModel()
{
FeesID = feesInfo.FeesID,
DueAmount = feesInfo.DueAmount,
DebtsAmount = feesInfo.DebtsAmount,
LateFeeAmount = feesInfo.LateFeeAmount,
FeesDueDate = feesInfo.FeesDueDate
};
tmp.TotalDueAmount += model.DueAmount;
tmp.TotalDebtsAmount += model.DebtsAmount;
tmp.TotalLateFeeAmount += model.LateFeeAmount;
if (feesInfo.SysCostSign == "B0001")
{
tmp.Expanded = 1;
}
tmp.Fees.Add(model);
}
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
// 读取积分信息
sql = @"SELECT * FROM Tb_Control_AppPoint WHERE CommunityID=@CommunityId AND isnull(CommunityID,'')<>'' AND IsEnable=1
UNION ALL
SELECT * FROM Tb_Control_AppPoint WHERE CorpID=@CorpId AND isnull(CommunityID,'')='' AND IsEnable=1";
var controlInfo = appConn.Query <Tb_Control_AppPoint>(sql, new { CommunityId = communityId, CorpId = community.CorpID }).FirstOrDefault();
if (controlInfo == null || controlInfo.IsEnable == false)
{
controlInfo = Tb_Control_AppPoint.DefaultControl;
}
// 用户积分余额
sql = @"SELECT PointBalance FROM Tb_App_UserPoint WHERE UserID=@UserID";
var balance = appConn.Query <int>(sql, new { UserID = userId }).FirstOrDefault();
var costSign = new List <string>();
// 允许抵用物业费
if (controlInfo.AllowDeductionPropertyFees)
{
costSign.Add("B0001");
}
// 允许抵用车位费
if (controlInfo.AllowDeductionParkingFees)
{
costSign.Add("B0002");
}
return(new ApiResult(true, new
{
ArrearsFees = list,
BindCostList,
PaymentCycleSetting,
Points = new
{
UserPointBalance = balance,
AllowDeductionPropertyFees = controlInfo.AllowDeductionPropertyFees,
AllowDeductionParkingFees = controlInfo.AllowDeductionParkingFees,
AllowDeductionOtherPropertyFees = controlInfo.AllowDeductionOtherPropertyFees,
PointExchangeRatio = controlInfo.PointExchangeRatio,
SysCostSign = costSign
},
}).toJson());
}
}
}
/// <summary>
/// 获取历史账单列表
/// </summary>
private string GetFeesHistoryList(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].AsString()))
{
return(new ApiResult(false, "用户信息错误").toJson());
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "请选择房屋所在项目").toJson());
}
if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "未指定欠费主体").toJson());
}
var userId = row["UserId"].AsString();
var communityId = row["CommunityId"].AsString();
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = 0L;
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].AsString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
}
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查找到小区信息"));
}
var commId = AppGlobal.StrToInt(community.CommID);
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_CHARGE);
var shieldCost = "";
// 查询需要屏蔽的费用
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT isnull(object_id(N'Tb_Control_AppCostItem',N'U'),0)";
if (appConn.Query <long>(sql).FirstOrDefault() != 0)
{
sql = @"SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND CommunityID=@Id AND AllowShow=0;
SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND isnull(CommunityID,'')='' AND AllowShow=0;";
var reader = appConn.QueryMultiple(sql, new { CorpID = community.CorpID, Id = community.Id });
var data1 = reader.Read <string>().FirstOrDefault();
var data2 = reader.Read <string>().FirstOrDefault();
var data = data1 ?? data2;
if (!string.IsNullOrEmpty(data))
{
using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
sql = $@"SELECT CostCode FROM Tb_HSPR_CorpCostItem
WHERE CorpCostID IN
(
SELECT Value FROM SplitString('{data}',',',1)
)";
var costCodes = erpConn.Query <string>(sql).Select(obj => $"'{obj}'");
shieldCost = $@"AND CostCode NOT IN ({ string.Join(",", costCodes.ToArray()) })";
}
}
}
}
using (var chargeConn = new SqlConnection(chargeConnectionString))
{
var list = new List <PMSAppFeesCostSimpleModel>();
var sql = $@"/* 欠费已缴清 */
SELECT FeesID,x.CostID,CostName,
isnull(PaidAmount,0.00) AS PaidAmount,
isnull(PrecAmount, 0.00) AS PrecAmount,
isnull(SysCostSign,'Unknown') AS SysCostSign,
isnull(DueAmount,0.00) AS DueAmount,
isnull(DebtsAmount,0.00) AS DebtsAmount,
CASE WHEN isnull(x.DebtsLateAmount, 0)>0
THEN dbo.funGetLateFeeDebts(x.CommID,x.FeesID,isnull(x.DebtsLateAmount,0))
ELSE 0.0 END AS LateFeeAmount,
convert(varchar(10),isnull(FeesDueDate,getdate()),120) AS FeesDueDate
FROM Tb_HSPR_Fees x
LEFT JOIN { community.DBName }.dbo.Tb_HSPR_CostItem y ON x.CostID=y.CostID
WHERE /*x.CustID=@CustID AND*/ x.RoomID=@RoomID
AND (isnull(x.IsCharge,0)=1 OR isnull(x.IsPrec,0)=1)
{ shieldCost }
ORDER BY SysCostSign ASC,FeesDueDate DESC;";
var data = chargeConn.Query(sql, new { CommID = community.CommID, RoomID = roomId /*, CustID = custId*/ });
for (int i = 0; i < data.Count(); i++)
{
var feesInfo = data.ToList()[i];
var tmp = list.Find(obj => obj.CostID == feesInfo.CostID);
if (tmp == null)
{
tmp = new PMSAppFeesCostSimpleModel()
{
CostID = feesInfo.CostID,
CostName = feesInfo.CostName,
SysCostSign = feesInfo.SysCostSign
};
tmp.Fees = new List <PMSAppFeesSimpleModel>();
list.Add(tmp);
if (tmp.SysCostSign == "Unknown")
{
tmp.SysCostSign = null;
}
}
var model = new PMSAppFeesSimpleModel()
{
FeesID = feesInfo.FeesID,
DueAmount = feesInfo.DueAmount,
DebtsAmount = feesInfo.DebtsAmount,
LateFeeAmount = feesInfo.LateFeeAmount,
FeesDueDate = feesInfo.FeesDueDate,
PaidAmount = feesInfo.PaidAmount,
PrecAmount = feesInfo.PrecAmount
};
tmp.TotalDueAmount += model.DueAmount;
tmp.TotalDebtsAmount += model.DebtsAmount;
tmp.TotalLateFeeAmount += model.LateFeeAmount;
tmp.TotalPaidAmount += model.PaidAmount;
tmp.TotalPrecAmount += model.PrecAmount;
if (feesInfo.SysCostSign == "B0001")
{
tmp.Expanded = 1;
}
tmp.Fees.Add(model);
}
return(new ApiResult(true, new
{
ArrearsFees = list
}).toJson());
}
}
/// <summary>
/// 获取问卷问题列表第二版，包含多选题
/// </summary>
private string GetQuestionnaireDetail(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区id不能为空"));
}
if (!row.Table.Columns.Contains("TaskId") || string.IsNullOrEmpty(row["TaskId"].ToString()))
{
return(JSONHelper.FromString(false, "任务id不能为空"));
}
if (!row.Table.Columns.Contains("RoomId") || string.IsNullOrEmpty(row["RoomId"].ToString()))
{
return(JSONHelper.FromString(false, "房屋id不能为空"));
}
var communityId = row["CommunityId"].ToString();
var planId = row["TaskId"].ToString();
var roomId = AppGlobal.StrToLong(row["RoomId"].ToString());
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查询到小区信息"));
}
using (var conn = new SqlConnection(GetConnectionStr(community)))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var trans = conn.BeginTransaction();
try
{
var visitManage = new VisitManage_bl();
var sql = @"SELECT ID FROM Tb_Visit_VisitingCustomersDetail
WHERE PlanID=@PlanID AND RoomID=@RoomID AND isnull(IsDelete,0)=0";
var detailId = conn.Query <string>(sql, new { PlanID = planId, RoomID = roomId }, trans).FirstOrDefault();
if (detailId == null)
{
detailId = visitManage.AddVisitRecord_v2(planId, roomId, conn, trans);
}
sql = @"SELECT count(1) FROM Tb_Visit_VisitingCustomersQuestionnaire
WHERE VisitingCustomersDetailID=@DetailId AND isnull(IsDelete,0)=0";
if (conn.Query <int>(sql, new { PlanID = planId, DetailId = detailId }, trans).FirstOrDefault() == 0)
{
visitManage.ImportQuestion_v2(planId, detailId, conn, trans);
}
trans.Commit();
sql = @"SELECT PlanObjective FROM Tb_Visit_Plan WHERE ID=@PlanID;
SELECT isnull(a.VisitWayID,a.AppointmentVisitWayID) AS VisitWayID,b.VisitWay,
IsUploadEnclosure,a.InterviewedObject,a.ContactTelephone,a.SignatureImg,
a.RelationsWithOwners AS Relation
FROM Tb_Visit_VisitingCustomersDetail a
LEFT JOIN Tb_Visit_VisitWay b ON isnull(a.VisitWayID,a.AppointmentVisitWayID)=b.ID
WHERE PlanID=@PlanID AND RoomID=@RoomID AND isnull(a.IsDelete,0)=0;
SELECT a.ID AS IID,d.VisitCategory,e.QuestionnaireCategory,a.IsScore,f.Name AS IssueType,a.IssueName,a.IssueResult,
isnull(a.IssueStandardScore,0) AS IssueStandardScore,a.IssueProperty,a.Remark,a.Sort,
isnull(a.RealScore,0) AS RealScore
FROM Tb_Visit_VisitingCustomersQuestionnaire a
LEFT JOIN Tb_Visit_VisitingCustomersDetail b ON a.VisitingCustomersDetailID=b.ID
LEFT JOIN Tb_Visit_Plan c ON b.PlanID=c.ID
LEFT JOIN Tb_Visit_VisitCategory d ON a.VisitCategoryID=d.ID
LEFT JOIN Tb_Visit_QuestionnaireCategory e ON a.QuestionnaireCategoryID=e.ID
LEFT JOIN Tb_Visit_Dictionary f ON a.IssueType=f.ID
WHERE a.ID IN
(
SELECT ID FROM Tb_Visit_VisitingCustomersQuestionnaire
WHERE VisitingCustomersDetailID IN
(
SELECT ID FROM Tb_Visit_VisitingCustomersDetail WHERE PlanID=@PlanID AND RoomID=@RoomID AND isnull(IsDelete,0)=0
) AND isnull(IsDelete,0)=0
);
SELECT a.ID,a.QuestionID AS CustomerQuestionID,a.[Option] AS Content,a.OptionStandardScore AS Score,IsChoose
FROM Tb_Visit_VisitingCustomersQuestionnaireOption a
LEFT JOIN Tb_Visit_VisitingCustomersQuestionnaire b ON a.QuestionID=b.ID
WHERE QuestionID IN
(
SELECT ID FROM Tb_Visit_VisitingCustomersQuestionnaire
WHERE VisitingCustomersDetailID IN
(
SELECT ID FROM Tb_Visit_VisitingCustomersDetail WHERE PlanID=@PlanID AND RoomID=@RoomID AND isnull(IsDelete,0)=0
) AND isnull(IsDelete,0)=0
) ORDER BY b.Sort,a.Sort;";
var reader = conn.QueryMultiple(sql, new { PlanID = planId, RoomID = roomId });
var planObjective = reader.Read <string>().FirstOrDefault();
var visitInfo = reader.Read().FirstOrDefault();
var questions = reader.Read();
var questionOptions = reader.Read().ToList();
var group = new Dictionary <string, List <dynamic> >();
foreach (var questionInfo in questions)
{
var options = questionOptions.FindAll(obj => obj.CustomerQuestionID == questionInfo.IID);
questionInfo.Options = options.Select(obj => new { OptionID = obj.ID, Content = obj.Content, Score = obj.Score, IsChoose = obj.IsChoose });
foreach (var tmp in options)
{
questionOptions.Remove(tmp);
}
}
var visitWayId = visitInfo?.VisitWayID?.ToString();
var visitWay = visitInfo?.VisitWay?.ToString();
var isUploadEnclosure = visitInfo?.IsUploadEnclosure?.ToString();
var interviewedObject = visitInfo?.InterviewedObject?.ToString();
var interviewedMobile = visitInfo?.ContactTelephone?.ToString();
var relationsWithOwnersisit = visitInfo?.Relation?.ToString();
var signatureImg = visitInfo?.SignatureImg?.ToString();
return(new ApiResult(true, new
{
DetailId = detailId,
PlanObjective = planObjective,
VisitWayID = visitWayId,
VisitWay = visitWay,
SignatureImg = signatureImg,
IsUploadEnclosure = isUploadEnclosure,
InterviewedObject = interviewedObject,
InterviewedMobile = interviewedMobile,
RelationsWithOwners = relationsWithOwnersisit,
Questions = questions
}).toJson());
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message + ex.StackTrace));
}
}
}
/// <summary>
/// 免费申请
/// </summary>
private string IncidentFreeApply(DataRow row)
{
if (!row.Table.Columns.Contains("IncidentID") || string.IsNullOrEmpty(row["IncidentID"].AsString()))//报事ID
{
return(new ApiResult(false, "缺少参数IncidentID").toJson());
}
if (!row.Table.Columns.Contains("FeesFreeReason") || string.IsNullOrEmpty(row["FeesFreeReason"].AsString()))
{
return(new ApiResult(false, "缺少参数FeesFreeReason").toJson());
}
var incidentId = AppGlobal.StrToLong(row["IncidentID"].ToString());
var feesFreeReason = row["FeesFreeReason"].ToString();
var incidentInfo = GetIncidentInfo(incidentId);
using (var conn = new SqlConnection(Global_Fun.BurstConnectionString(incidentInfo.CommID, Global_Fun.BURST_TYPE_CHARGE)))
{
// 该报事下至少一笔费用已经收取、冲抵、减免、代扣
var sql = @"SELECT count(1) FROM Tb_HSPR_Fees
WHERE IncidentID=@IncidentID AND (IsCharge=1 OR PaidAmount>0 OR WaivAmount>0 OR IsBank=1 OR IsPrec=1);";
var i = conn.Query <int>(sql, new { IncidentID = incidentId }).FirstOrDefault();
if (i > 0)
{
return(new ApiResult(false, "费用已收取，不允许免费").toJson());
}
}
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
// 是否已经提交申请
var sql = @"SELECT count(1) FROM Tb_HSPR_IncidentFreeApply
WHERE IncidentID=@IncidentID AND AuditState='审核中';";
int i = conn.Query <int>(sql, new { IncidentID = incidentId }, trans).FirstOrDefault();
if (i > 0)
{
trans.Commit();
return(new ApiResult(false, "已提交申请，请勿重复申请").toJson());
}
// 判断该报事本身是否为不收费
sql = @"SELECT count(1) FROM Tb_HSPR_IncidentAccept
WHERE IncidentID=@IncidentID AND isnull(IsFee,0)=0";
i = conn.Query <int>(sql, new { IncidentID = incidentId }, trans).FirstOrDefault();
if (i > 0)
{
trans.Commit();
return(new ApiResult(false, "该报事已是免费状态").toJson());
}
var iid = Guid.NewGuid().ToString();
var auditState = "审核中";
var auditUser = default(string);
var title = $"{Global_Var.UserName}提交的报事编号为【{incidentInfo.IncidentNum}】的免费申请";
sql = @"INSERT INTO Tb_HSPR_IncidentAuditingWorkFlow(IID, KeyIID, CommID, RoleCode, IsAudit, AuditType,orderID)
SELECT newid(), @KeyIID, @CommID, RoleCode, 0, 0,orderID FROM Tb_HSPR_IncidentAuditingSet WHERE AuditType=3
AND TypeCode=(SELECT BigCorpTypeCode FROM Tb_HSPR_IncidentAccept WHERE IncidentID=@IncidentID)
ORDER BY OrderID;";
// 插入有审核权限的岗位
i = conn.Execute(sql, new
{
KeyIID = iid,
CommID = incidentInfo.CommID,
IncidentID = incidentId
}, trans);
if (i == 0)
{
trans.Rollback();
return(JSONHelper.FromString(false, "未设置审核岗位，不能提交申请"));
}
sql = @"INSERT INTO Tb_HSPR_IncidentFreeApply(IID, CommID,CommName,IncidentID,IncidentNum,Title,FreeReason,
WorkStartDate,UserCode,UserName,AuditUser,AuditState)
SELECT @IID,a.CommID,b.CommName,a.IncidentID,a.IncidentNum,@Title,
@FreeReason,getdate(),@UserCode,@UserName,@AuditUser,@AuditState
FROM Tb_HSPR_IncidentAccept a
LEFT JOIN Tb_HSPR_Community b ON a.CommID=b.CommID
WHERE a.IncidentID=@IncidentID;";
i = conn.Execute(sql, new
{
IID = iid,
IncidentID = incidentId,
Title = title,
UserName = Global_Var.LoginUserName,
UserCode = Global_Var.LoginUserCode,
FreeReason = feesFreeReason,
AuditUser = auditUser,
AuditState = auditState
}, trans);
trans.Commit();
if (i > 0)
{
// 推送给审核人
PMSIncidentPush.SynchPushIncidentFreeApplied(iid);
return(JSONHelper.FromString(true, "申请成功"));
}
trans.Rollback();
return(JSONHelper.FromString(false, "申请失败"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message + Environment.NewLine + ex.StackTrace));
}
}
}
private string MakeScore(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["InfoID"].ToString()))
{
return(JSONHelper.FromString(false, "活动id不能为空"));
}
if (!row.Table.Columns.Contains("Score") || string.IsNullOrEmpty(row["Score"].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();
int Score = AppGlobal.StrToInt(row["Score"].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 appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT Mobile FROM Tb_User WHERE Id=@UserId";
var usermobile = appConn.Query <string>(sql, new { UserId = UserID }).FirstOrDefault();
using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
erpConn.Open();
var trans = erpConn.BeginTransaction();
try
{
// 判断是否是业主
sql = @"SELECT CustID FROM Tb_HSPR_Customer
WHERE CustID=(SELECT TOP 1 CustID FROM Tb_HSPR_CustomerLive WHERE RoomID=@RoomID AND IsDelLive=0 AND LiveType=1)
AND(MobilePhone LIKE @Mobile OR LinkmanTel LIKE @Mobile)
UNION ALL
SELECT CustID FROM Tb_HSPR_Household WHERE RoomID=@RoomID AND isnull(IsDelete,0)=0";
var custId = erpConn.Query <long>(sql, new { RoomID = RoomID, Mobile = $"%{usermobile}%" }, trans).FirstOrDefault();
if (custId == 0)
{
trans.Rollback();
return(JSONHelper.FromString(false, "抱歉，仅业主或家属才能评分"));
}
// 判断是否已报名
sql = @"SELECT convert(nvarchar(36),ActivitiesPersonId) FROM Tb_HSPR_CommActivitiesPerson_New
WHERE RoomId=@RoomID AND ActivitiesId=@ActivitieId";
var iid = erpConn.Query <string>(sql, new { RoomID = RoomID, ActivitieId = InfoID }, trans).FirstOrDefault();
if (string.IsNullOrEmpty(iid))
{
trans.Rollback();
return(JSONHelper.FromString(false, "抱歉，您未参加该活动，无法评分~"));
}
sql = @"SELECT Score FROM Tb_HSPR_CommActivitiesPerson_New WHERE ActivitiesPersonId=@IID";
var score = erpConn.Query <int>(sql, new { IID = iid }, trans).FirstOrDefault();
if (score > 0)
{
trans.Rollback();
return(JSONHelper.FromString(false, "您或您的家属已经评过分啦~"));
}
sql = @"UPDATE Tb_HSPR_CommActivitiesPerson_New SET Score=@Score WHERE ActivitiesPersonId=@IID";
var i = erpConn.Execute(sql, new { IID = iid, Score = Score }, trans);
if (i == 1)
{
trans.Commit();
return(JSONHelper.FromString(true, "评分成功"));
}
trans.Rollback();
return(JSONHelper.FromString(false, "评分失败"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, "评分失败:" + ex.Message + ex.StackTrace));
}
}
}
}
/// <summary>
/// 新增临时停车信息
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
private string TempPayFees(string requestParam)
{
try
{
if (string.IsNullOrEmpty(requestParam))
{
new Logger().WriteLog("鸿坤道闸临停信息请求参数", DateTime.Now.ToString() + "--" + requestParam);
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, "请求参数不能为空");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
string ContionString = PubConstant.GetConnectionString("HKSQLConnection").ToString();
IDbConnection con = new SqlConnection(ContionString);
Tb_HSPR_HKCostInfo costInfo = JsonHelp.JsonDeserialize <Tb_HSPR_HKCostInfo>(requestParam);
string CommID = null;//项目ID
string sKey = "A2YGWFV7QP5KFOMLXIYRW";
int parkId = costInfo.parkId;
int duration = costInfo.duration;
string dutyId = costInfo.dutyId;
string endTime = costInfo.endTime;
string feeAli = costInfo.feeAli;
string feeCash = costInfo.feeCash;
string feeCoupon = costInfo.feeCoupon;
string feeException = costInfo.feeException;
string feeExpected = costInfo.feeExpected;
string feeFree = costInfo.feeFree;
string feeWechat = costInfo.feeWechat;
string roomNum = costInfo.roomNum;
string startTime = costInfo.startTime;
string tollManName = costInfo.tollManName;
string HtcSign = costInfo.sign;
//查询当前数据是否已经存在天问系统
string tmpStr = "SELECT * FROM Tb_HSPR_TemporaryCarPrecostsDetail WHERE dutyId='" + dutyId + "'";
DataTable dtTemporaryCar = new DbHelperSQLP(ContionString).Query(tmpStr).Tables[0];
if (dtTemporaryCar.Rows.Count > 0)
{
new Logger().WriteLog("鸿坤道闸临停信息同步异常", DateTime.Now.ToString() + "--" + "该条临停数据已经存在天问系统:" + tmpStr);
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, "该条临停数据已经存在天问系统");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
if (parkId <= 0)
{
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, "车场ID不能为空");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
string signParm = "{\"duration\":" + duration + ",\"dutyId\":\"" + dutyId + "\",\"endTime\":\"" + endTime
+ "\",\"feeAli\":" + feeAli + ",\"feeCash\":" + feeCash + ",\"feeCoupon\":" + feeCoupon + ",\"feeException\":"
+ feeException + ",feeExpected:" + feeExpected + ",feeFree:" + feeFree + ",feeWechat:" + feeWechat
+ ",\"roomNum\":\"" + roomNum + "\",\"startTime\":\"" + startTime + "\",\"tollManName\":\"" + tollManName + "\"}";
Dictionary <String, String> param = JSONHelper.StringToDictionary <string, string>(signParm);
string signStr = BuildParamStr(param) + "&key=" + sKey + "";
string sign = MD5Encrypt(signStr);
if (sign != HtcSign)
{
new Logger().WriteLog("鸿坤道闸临停信息同步异常", DateTime.Now.ToString() + "--" + "签名错误:" + sign);
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, "签名错误");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
string ctpStr = "SELECT * FROM Tb_HTC_CommToPark WHERE ParkId='" + parkId + "'";
DataTable dtCommToPark = new DbHelperSQLP(ContionString).Query(ctpStr).Tables[0];
if (dtCommToPark.Rows.Count > 0)
{
DataRow retRow = dtCommToPark.Rows[0];
CommID = retRow["CommID"].ToString();
}
else
{
new Logger().WriteLog("鸿坤道闸临停信息同步异常", DateTime.Now.ToString() + "--" + "车场ID无法匹配天问CommID:" + ctpStr);
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, "车场ID无法匹配天问CommID");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
long CustId = 0;
long RoomId = 0;
long CostId = 0;
long CorpStanId = 0;
long StanId = 0;
string UserCode = "";
string csStr = "SELECT * FROM Tb_HTC_CostStandard WHERE ParkId='" + parkId + "'";
DataTable dtCostStandard = new DbHelperSQLP(ContionString).Query(csStr).Tables[0];
if (dtCostStandard.Rows.Count > 0)
{
DataRow retRow = dtCostStandard.Rows[0];
CustId = AppGlobal.StrToLong(retRow["CustId"].ToString());
RoomId = AppGlobal.StrToLong(retRow["RoomId"].ToString());
CostId = AppGlobal.StrToLong(retRow["CostId"].ToString());
CorpStanId = AppGlobal.StrToLong(retRow["CorpStanId"].ToString());
StanId = AppGlobal.StrToLong(retRow["StanId"].ToString());
UserCode = retRow["UserCode"].ToString();
}
else
{
new Logger().WriteLog("鸿坤道闸临停信息同步异常", DateTime.Now.ToString() + "--" + "车场ID无法匹配天问收费标准:" + csStr);
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, "车场ID无法匹配天问收费标准");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
try
{
DynamicParameters parameters = new DynamicParameters();
var InfoCode = Guid.NewGuid();
parameters.Add("@InfoCode", InfoCode);
parameters.Add("@CardID", null);
parameters.Add("@CardType", null);
parameters.Add("@GoName", null);
parameters.Add("@CommID", CommID);
parameters.Add("@OverTime", startTime);
parameters.Add("@OutTime", endTime);
parameters.Add("@CarNumber", null);
parameters.Add("@Money", feeExpected);
parameters.Add("@IssueDate", null);
parameters.Add("@dutyId", dutyId);
parameters.Add("@feeCash", feeCash);
parameters.Add("@feeWechat", feeWechat);
parameters.Add("@feeAli", feeAli);
int b = con.Execute("Proc_HSPR_TemporaryCarPrecostsDetail_Insert", parameters, null, null, CommandType.StoredProcedure);
con.Dispose();
if (b > 0)
{
string Memo = "";
if (AppGlobal.StrToDec(feeAli) > 0)
{
Memo = "支付宝收款";
CreateCarFees(AppGlobal.StrToInt(CommID), CustId, RoomId, AppGlobal.StrToDec(feeAli), CorpStanId,
StanId, Memo, CostId, UserCode);
}
if (AppGlobal.StrToDec(feeWechat) > 0)
{
Memo = "微信收款";
CreateCarFees(AppGlobal.StrToInt(CommID), CustId, RoomId, AppGlobal.StrToDec(feeWechat), CorpStanId,
StanId, Memo, CostId, UserCode);
}
if (AppGlobal.StrToDec(feeCash) > 0)
{
Memo = "现金收款";
CreateCarFees(AppGlobal.StrToInt(CommID), CustId, RoomId, AppGlobal.StrToDec(feeCash), CorpStanId,
StanId, Memo, CostId, UserCode);
}
new Logger().WriteLog("鸿坤道闸临停信息请求参数", DateTime.Now.ToString() + "--同步成功 " + Memo);
string State = "true";
string ErrorContent = JSONHelper.FromStringHK(false, "同步成功");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
else
{
new Logger().WriteLog("鸿坤道闸临停信息请求参数", DateTime.Now.ToString() + "--同步失败!");
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, "同步失败");
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
}
catch (Exception ex)
{
new Logger().WriteLog("鸿坤道闸临停信息同步异常", DateTime.Now.ToString() + "--" + ex.ToString());
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, ex.Message);
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
}
catch (Exception ex)
{
new Logger().WriteLog("鸿坤道闸临停信息同步异常", DateTime.Now.ToString() + "--" + ex.ToString());
string State = "false";
string ErrorContent = JSONHelper.FromStringHK(false, ex.Message);
log.ErrorContent = ErrorContent;
log.State = State;
LogAdd(log);
return(ErrorContent);
}
}
/// <summary>
/// 邀请用户
/// </summary>
private string InviteUser(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("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参数"));
}
var communityId = row["CommunityId"].AsString();
var userMobile = row["UserMobile"].ToString();
var inviteMobile = row["InviteMobile"].ToString();
var inviteName = row["InviteName"].ToString();
var inviteType = row["InviteType"].ToString();
var relationship = UserRoomIdentityCode.Other;
var roomId = 0L;
var relationId = default(string);
if (row.Table.Columns.Contains("RelationId") && !string.IsNullOrEmpty(row["RelationId"].ToString()))
{
relationId = row["RelationId"].ToString();
}
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].ToString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].ToString());
}
if (roomId == 0 && string.IsNullOrEmpty(relationId))
{
return(JSONHelper.FromString(false, "未指定绑定房屋"));
}
if (inviteType == ((int)UserRoomIdentity.Customer).ToString())
{
relationship = UserRoomIdentityCode.Customer2;
}
else if (inviteType == ((int)UserRoomIdentity.FamilyMember).ToString())
{
relationship = UserRoomIdentityCode.FamilyMember;
}
else if (inviteType == ((int)UserRoomIdentity.Tenant).ToString())
{
relationship = UserRoomIdentityCode.Tenant;
}
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = GetConnectionStringStr(Community);
string message;
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT CommName FROM Tb_Community WHERE Id=@Id";
var commName = appConn.Query <string>(sql, new { Id = communityId }).FirstOrDefault();
if (roomId == 0)
{
sql = @"SELECT CustId,RoomId,RoomSign,CustName FROM Tb_User_Relation WHERE Id=@RelationId";
}
else
{
sql = @"SELECT CustId,RoomId,RoomSign,CustName FROM Tb_User_Relation WHERE UserId IN(SELECT Id FROM Tb_User WHERE Mobile=@Mobile)";
}
var relationInfo = appConn.Query(sql, new { RelationId = relationId, Mobile = userMobile }).FirstOrDefault();
if (relationInfo == null)
{
return(JSONHelper.FromString(false, "该房屋所属业主还未注册到业主App"));
}
var custId = AppGlobal.StrToLong(relationInfo.CustId);
var roomSign = relationInfo.RoomSign?.ToString();
var custName = relationInfo.CustName?.ToString();
var holdId = 0L;
// 判断客户信息是否已经插入到家庭成员表
using (var erpConn = new SqlConnection(strcon))
{
sql = @"SELECT HoldID FROM Tb_HSPR_Household
WHERE CustID=@CustID AND RoomID=@RoomID
AND (MobilePhone like @MobilePhone OR LinkManTel like @MobilePhone) AND isnull(IsDelete,0)=0";
holdId = erpConn.Query <long>(sql, new { CustID = custId, RoomID = roomId, MobilePhone = $"%{inviteMobile}%" }).FirstOrDefault();
if (holdId == 0)
{
// 客户信息插入到家庭成员表
var 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);
parameters.Add("@IsDelete", 0);
erpConn.Execute("Proc_HSPR_Household_Insert_Phone", parameters, null, null, CommandType.StoredProcedure);
// 家庭成员表id
holdId = parameters.Get <long>("@HoldID");
}
}
var userInfo = new MobileSoft.Model.Unified.Tb_User();
var isAdd = false;
// 判断用户是否已经注册
sql = "SELECT Id FROM Tb_User WHERE Mobile=@Mobile";
userInfo.Id = appConn.Query <string>(sql, new { Mobile = inviteMobile }).FirstOrDefault();
if (userInfo.Id == null)
{
isAdd = true;
// 注册用户
userInfo.Id = Guid.NewGuid().ToString();
userInfo.Name = inviteName;
userInfo.Mobile = inviteMobile;
userInfo.Pwd = "123456";
userInfo.NickName = inviteMobile;
userInfo.Sex = 1;
userInfo.RegDate = DateTime.Now;
new MobileSoft.BLL.Unified.Bll_Tb_User().Add(userInfo);
}
// 查询用户是否已经绑定该房屋
sql = @"SELECT Id FROM Tb_User_Relation WHERE UserId=@UserId AND RoomId=@RoomID";
var hasBinded = appConn.Query(sql, new { UserId = userInfo.Id, RoomID = roomId }).Count();
if (hasBinded > 0)
{
// 已经绑定，解锁，更新家庭成员id
sql = @"UPDATE Tb_User_Relation SET Locked=0,CustHoldId=@HoldID WHERE UserId=@UserId AND RoomId=@RoomID";
appConn.Execute(sql, new { UserId = userInfo.Id, RoomID = roomId, HoldID = holdId });
}
else
{
// 未绑定，绑定房屋
sql = @"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)";
appConn.Execute(sql, new
{
Id = Guid.NewGuid().ToString(),
UserId = userInfo.Id,
CommunityId = communityId,
CustId = custId,
RoomId = roomId,
CustName = custName,
RoomSign = roomSign,
CustMobile = userMobile
});
}
// 短信内容
if (Community.CorpID == 1975)
{
message = string.Format("\"{0}\"业主{1}为您绑定了编号为：{2}的房屋，关注微信公众号“第一亚太物业”，您可以对该房屋进行报事和缴费等操作。",
commName, custName, roomSign);
if (isAdd)
{
message = string.Format("\"{0}\"业主{1}邀请您关注微信公众号“第一亚太物业”随时掌握房屋、小区信息，您的账号为：{2}，初始密码为：123456。",
commName, custName, inviteMobile);
}
}
else if (Community.CorpID == 1970)
{
message = string.Format("温馨提示：\"{0}\"业主{1}为您绑定了编号为：{2}房屋，您可以对该房屋进行报事和缴费等操作。",
commName, custName, roomSign);
if (isAdd)
{
message = string.Format(@"温馨提示：“{0}”业主{1}邀请您使用泰禾业主App，您的账号为：{2}，初始密码为：123456，请及时下载登录泰禾App并修改密码,下载地址：http://t.cn/Rdo20xY",
commName, custName, inviteMobile);
}
}
else
{
message = string.Format("温馨提示：\"{0}\"业主{1}为您绑定了编号为：{2}的房屋，您可以对该房屋进行报事和缴费等操作。",
commName, custName, roomSign);
if (isAdd)
{
message = string.Format("温馨提示：“{0}”业主{1}为您绑定了编号为：{2}的房屋，您的账号为：{3}，初始密码为：123456，您可以对该房屋进行报事和缴费等操作。",
commName, custName, roomSign, inviteMobile);
}
}
SendShortMessage(inviteMobile, message, out string errorMessage, Community.CorpID);
}
return(JSONHelper.FromString(true, "邀请成功"));
}
private string ActivityApply(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["InfoID"].ToString()))
{
return(JSONHelper.FromString(false, "活动id不能为空"));
}
if (!row.Table.Columns.Contains("ApplyUser") || string.IsNullOrEmpty(row["ApplyUser"].ToString()))
{
return(JSONHelper.FromString(false, "报名人不能为空"));
}
if (!row.Table.Columns.Contains("Mobile") || string.IsNullOrEmpty(row["Mobile"].ToString()))
{
return(JSONHelper.FromString(false, "报名人不能为空"));
}
if (!row.Table.Columns.Contains("ApplyPerson") || string.IsNullOrEmpty(row["ApplyPerson"].ToString()))
{
return(JSONHelper.FromString(false, "报名人数不能为空"));
}
var UserID = row["UserID"].ToString();
var CommID = AppGlobal.StrToInt(row["CommID"].ToString());
var RoomID = AppGlobal.StrToLong(row["RoomID"].ToString());
var InfoID = row["InfoID"].ToString();
var ApplyUser = row["ApplyUser"].ToString();
var Mobile = row["Mobile"].ToString();
var ApplyPerson = AppGlobal.StrToInt(row["ApplyPerson"].ToString());
var IID = "";
if (row.Table.Columns.Contains("IID") && !string.IsNullOrEmpty(row["IID"].ToString()))
{
IID = row["IID"].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 appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT Mobile FROM Tb_User WHERE Id=@UserId";
var usermobile = appConn.Query <string>(sql, new { UserId = UserID }).FirstOrDefault();
using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
erpConn.Open();
var trans = erpConn.BeginTransaction();
try
{
// 判断是否是业主
sql = @"SELECT CustID FROM Tb_HSPR_Customer
WHERE CustID=(SELECT TOP 1 CustID FROM Tb_HSPR_CustomerLive WHERE RoomID=@RoomID AND IsDelLive=0 AND LiveType=1)
AND(MobilePhone LIKE @Mobile OR LinkmanTel LIKE @Mobile)
UNION ALL
SELECT CustID FROM Tb_HSPR_Household WHERE RoomID=@RoomID AND isnull(IsDelete,0)=0";
var custId = erpConn.Query <long>(sql, new { RoomID = RoomID, Mobile = $"%{usermobile}%" }, trans).FirstOrDefault();
if (custId == 0)
{
trans.Rollback();
return(JSONHelper.FromString(false, "抱歉，仅业主或家属才能报名"));
}
// 判断是否超出报名限制
sql = @"SELECT EveryLimit FROM Tb_HSPR_CommActivities_New WHERE ActivitiesId=@ActivitieId";
var everyLimit = erpConn.Query <int>(sql, new { ActivitieId = InfoID }, trans).FirstOrDefault();
if (ApplyPerson > everyLimit && everyLimit != 0)
{
trans.Rollback();
return(JSONHelper.FromString(false, $"每户最多 {everyLimit} 人参加哦~"));
}
// 判断是否满员
sql = @"SELECT isnull(ActivitiesLimit,0) AS ActivitiesLimit,
(SELECT isnull(sum(isnull(SignUpCount,0)),0) FROM Tb_HSPR_CommActivitiesPerson_New
WHERE ActivitiesId=x.ActivitiesId AND RoomId<>@RoomID AND isnull(IsDelete,0)=0) AS ApplyCount
FROM Tb_HSPR_CommActivities_New x WHERE ActivitiesId=@ActivitieId";
var tmpResult = erpConn.Query(sql, new { ActivitieId = InfoID, RoomID = RoomID }, trans).FirstOrDefault();
var totalLimit = (int)tmpResult.ActivitiesLimit;
var applyCount = (int)tmpResult.ApplyCount;
if (totalLimit < applyCount + ApplyPerson)
{
trans.Rollback();
if (totalLimit - applyCount == 0)
{
return(JSONHelper.FromString(false, $"已经满员了哦~"));
}
return(JSONHelper.FromString(false, $"快满员了，最多还允许 {totalLimit - applyCount} 人参加哦~"));
}
// 判断是否已报名
sql = @"SELECT SignUpCount FROM Tb_HSPR_CommActivitiesPerson_New
WHERE CustId=@CustID AND RoomId=@RoomID AND ActivitiesId=@ActivitieId AND isnull(IsDelete,0)=0";
var signUpCount = erpConn.Query <int>(sql, new { CustID = custId, RoomID = RoomID, ActivitieId = InfoID }, trans).FirstOrDefault();
if (signUpCount > 0)
{
if (string.IsNullOrEmpty(IID))
{
trans.Rollback();
return(JSONHelper.FromString(false, "您或您的家属已经报名了~"));
}
else
{
sql = @"UPDATE Tb_HSPR_CommActivitiesPerson_New SET SignUpCount=@ApplyPerson,JoinCount=@ApplyPerson
WHERE ActivitiesPersonId=@IID";
erpConn.Execute(sql, new { ApplyPerson = ApplyPerson, IID = IID }, trans);
trans.Commit();
return(JSONHelper.FromString(true, "修改报名成功"));
}
}
// 报名
sql = @"INSERT INTO Tb_HSPR_CommActivitiesPerson_New(ActivitiesPersonId,CommId,ActivitiesId,CustId,CustName,RoomId,
RoomSign,LinkPhone,SignUpCount,JoinCount,Score,RegisTime,IsDelete)
SELECT newid(),a.CommId,a.ActivitiesId,
@CustId,(SELECT CustName FROM Tb_HSPR_Customer WHERE CustID=@CustId),
@RoomId,(SELECT RoomSign FROM Tb_HSPR_Room WHERE RoomID=@RoomId),
@Mobile,@ApplyPerson,@ApplyPerson,0,getdate(),0
FROM Tb_HSPR_CommActivities_New a
WHERE a.ActivitiesId=@ActivitieId";
int i = erpConn.Execute(sql, new
{
CustId = custId,
RoomId = RoomID,
ApplyPerson = ApplyPerson,
Mobile = Mobile,
ActivitieId = InfoID
}, trans);
if (i == 1)
{
trans.Commit();
return(JSONHelper.FromString(true, "报名成功"));
}
trans.Rollback();
return(JSONHelper.FromString(false, "报名失败"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, "报名失败:" + ex.Message + ex.StackTrace));
}
}
}
}
/// <summary>
/// 订单确认收款
/// </summary>
/// <param name="OrderId"></param>
/// <returns></returns>
public static string ReceBusinessOrder(string OrderId, int realAmount)
{
try
{
using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection()))
{
string sql = "SELECT * FROM Tb_Charge_Receipt WHERE OrderId=@OrderId";
Tb_Charge_Receipt Receipt = conn.Query <Tb_Charge_Receipt>(sql, new { OrderId = OrderId }).SingleOrDefault();
if (Receipt.IsPay.ToString() == "已付款")
{
return("订单已付款");
}
//订单确认收款,收款动作写在下面
Receipt.IsPay = "已付款";
Receipt.PayDate = DateTime.Now;
Receipt.Method = "微信";
Receipt.RealAmount = realAmount / 100.0m;
// 对于没有使用优惠券的，直接设置Amount
if (Receipt.IsUseCoupon <= 0)
{
conn.Execute("UPDATE Tb_Charge_Receipt SET IsPay=@IsPay,PayDate=@PayDate,Method='支付宝',Amount=@RealAmount,RealAmount=@RealAmount WHERE Id=@Id",
new
{
IsPay = Receipt.IsPay.ToString(),
PayDate = Receipt.PayDate.ToString(),
RealAmount = Receipt.RealAmount,
Id = Receipt.Id.ToString()
});
IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = conn.Query <Tb_Charge_ReceiptDetail>(@"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode", new { ReceiptCode = Receipt.Id });
foreach (Tb_Charge_ReceiptDetail item in ReceiptDetails)
{
Tb_Resources_Details resources = conn.Query <Tb_Resources_Details>("SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID", new { ResourcesID = item.ResourcesID }).FirstOrDefault();
if (resources != null)
{
decimal detailAmount = 0.0m;
if (resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate > DateTime.Now)
{
detailAmount = resources.GroupBuyPrice.Value * item.Quantity;
}
else
{
detailAmount = (resources.ResourcesSalePrice - resources.ResourcesDisCountPrice) * item.Quantity;
}
conn.Execute(@"UPDATE Tb_Charge_ReceiptDetail SET
SalesPrice=@SalesPrice,
DiscountPrice=@DiscountPrice,
GroupPrice=@GroupPrice,
DetailAmount=@DetailAmount,
OffsetMoney=0
WHERE ResourcesID=@ResourcesID AND ReceiptCode=@ReceiptCode",
new
{
SalesPrice = resources.ResourcesSalePrice,
DiscountPrice = resources.ResourcesDisCountPrice,
GroupPrice = ((resources.IsGroupBuy == "是" && resources.GroupBuyEndDate.HasValue && resources.GroupBuyEndDate > DateTime.Now) ? resources.GroupBuyPrice : null),
DetailAmount = detailAmount,
ResourcesID = resources.ResourcesID,
ReceiptCode = item.ReceiptCode
});
// 减库存
conn.Execute("UPDATE Tb_Resources_Details SET ResourcesCount=(ResourcesCount-@Count) WHERE ResourcesID=@ResourcesID",
new { Count = AppGlobal.StrToLong(item.Quantity.ToString()), ResourcesID = resources.ResourcesID });
}
}
}
else
{
IEnumerable <Tb_Charge_ReceiptDetail> ReceiptDetails = conn.Query <Tb_Charge_ReceiptDetail>(@"SELECT * FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode", new { ReceiptCode = Receipt.Id });
IEnumerable <Tb_Resources_Details> resources = conn.Query <Tb_Resources_Details>(@"SELECT * FROM Tb_Resources_Details
WHERE ResourcesID in(SELECT ResourcesID FROM Tb_Charge_ReceiptDetail WHERE ReceiptCode=@ReceiptCode)", new { ReceiptCode = Receipt.Id });
decimal totalAmount = 0.0m;
// 商品总价，此处计算的是不算优惠券时的总价
// 此时Tb_Charge_ReceiptDetail.DetailAmount保存的是未计算优惠券时的价格，最终价格需要另行计算
foreach (Tb_Resources_Details item in resources)
{
foreach (Tb_Charge_ReceiptDetail receiptDetail in ReceiptDetails)
{
if (receiptDetail.ResourcesID == item.ResourcesID)
{
// 团购
if (item.IsGroupBuy == "是" && item.GroupBuyEndDate.HasValue && item.GroupBuyEndDate > DateTime.Now)
{
receiptDetail.DetailAmount = (item.GroupBuyPrice.Value * receiptDetail.Quantity);
}
else
{
receiptDetail.DetailAmount = (item.ResourcesSalePrice - item.ResourcesDisCountPrice) * receiptDetail.Quantity;
}
totalAmount += receiptDetail.DetailAmount.Value;
break;
}
}
}
conn.Execute("UPDATE Tb_Charge_Receipt SET IsPay=@IsPay,PayDate=@PayDate,Method='微信',Amount=@Amount,RealAmount=@RealAmount WHERE Id=@Id",
new
{
IsPay = Receipt.IsPay.ToString(),
PayDate = Receipt.PayDate.ToString(),
Amount = totalAmount,
RealAmount = Receipt.RealAmount,
Id = Receipt.Id.ToString()
});
// 本次使用的抵扣券金额
//decimal couponMoney = totalAmount - realAmount;
//decimal tempUsedCouponMoney = 0.0m;// 暂时没有用处，判断本次使用的抵扣券是否和订单详细记录里面使用的抵扣券价格一样
foreach (Tb_Resources_Details item in resources)
{
foreach (Tb_Charge_ReceiptDetail receiptDetail in ReceiptDetails)
{
if (receiptDetail.ResourcesID == item.ResourcesID)
{
//if (receiptDetail.OffsetMoney.HasValue && receiptDetail.OffsetMoney.Value > 0)
//{
// tempUsedCouponMoney += receiptDetail.OffsetMoney.Value;
//}
//if (receiptDetail.OffsetMoney2.HasValue && receiptDetail.OffsetMoney2.Value > 0)
//{
// tempUsedCouponMoney += receiptDetail.OffsetMoney2.Value;
//}
//else
//{
// receiptDetail.OffsetMoney = 0.0m;
//}
// 更新订单详情表商品付费金额信息
conn.Execute(@"UPDATE Tb_Charge_ReceiptDetail SET
SalesPrice=@SalesPrice,
DiscountPrice=@DiscountPrice,
GroupPrice=@GroupPrice,
DetailAmount=@DetailAmount
WHERE ResourcesID=@ResourcesID AND ReceiptCode=@ReceiptCode",
new
{
SalesPrice = item.ResourcesSalePrice,
DiscountPrice = item.ResourcesDisCountPrice,
GroupPrice = ((item.IsGroupBuy == "是" && item.GroupBuyEndDate.HasValue && item.GroupBuyEndDate > DateTime.Now) ? item.GroupBuyPrice : null),
DetailAmount = receiptDetail.DetailAmount.Value - receiptDetail.OffsetMoney.Value,
ResourcesID = receiptDetail.ResourcesID,
ReceiptCode = receiptDetail.ReceiptCode
});
// 减库存
conn.Execute("UPDATE Tb_Resources_Details SET ResourcesCount=(ResourcesCount-@Count) WHERE ResourcesID=@ResourcesID",
new { Count = AppGlobal.StrToLong(receiptDetail.Quantity.ToString()), ResourcesID = item.ResourcesID });
break;
}
}
}
}
return("success");
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
/// <summary>
/// 延期申请
/// </summary>
private string IncidentDelayApply(DataRow row)
{
if (!row.Table.Columns.Contains("IncidentID") || string.IsNullOrEmpty(row["IncidentID"].ToString()))
{
return(new ApiResult(false, "缺少参数IncidentID").toJson());
}
if (!row.Table.Columns.Contains("DelayHours") || string.IsNullOrEmpty(row["DelayHours"].ToString()))
{
return(new ApiResult(false, "缺少参数DelayHours").toJson());
}
if (!row.Table.Columns.Contains("DelayDate") || string.IsNullOrEmpty(row["DelayDate"].ToString()))
{
return(new ApiResult(false, "缺少参数DelayDate").toJson());
}
if (!row.Table.Columns.Contains("DelayReason") || string.IsNullOrEmpty(row["DelayReason"].ToString()))
{
return(new ApiResult(false, "缺少参数DelayReason").toJson());
}
var incidentId = AppGlobal.StrToLong(row["IncidentID"].ToString());
var delayHours = AppGlobal.StrToInt(row["DelayHours"].ToString());
var delayDate = row["DelayDate"].ToString();
var delayReason = row["DelayReason"].ToString();
var incidentInfo = GetIncidentInfo(incidentId);
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
var sql = @"SELECT count(1) FROM Tb_HSPR_IncidentDelayApply
WHERE IncidentID=@IncidentID AND (AuditState IS NULL OR AuditState='审核中');";
// 是否已经提交申请
int i = conn.Query <int>(sql, new { IncidentID = incidentId }, trans).FirstOrDefault();
if (i > 0)
{
trans.Commit();
return(JSONHelper.FromString(false, "已提交申请，请勿重复申请"));
}
var iid = Guid.NewGuid().ToString();
var auditState = "审核中";
var title = $"{Global_Var.UserName}提交的报事编号为【{incidentInfo.IncidentNum}】的延期申请";
sql = @"INSERT INTO Tb_HSPR_IncidentAuditingWorkFlow(IID, KeyIID, CommID, RoleCode, IsAudit, AuditType,orderID)
SELECT newid(), @KeyIID, @CommID, RoleCode, 0, 0,orderID
FROM Tb_HSPR_IncidentAuditingSet
WHERE AuditType=0
AND TypeCode=(SELECT BigCorpTypeCode FROM Tb_HSPR_IncidentAccept WHERE IncidentID=@IncidentID)
ORDER BY OrderID;";
// 插入有审核权限的岗位
i = conn.Execute(sql, new
{
KeyIID = iid,
CommID = incidentInfo.CommID,
IncidentID = incidentId
}, trans);
if (i == 0)
{
trans.Rollback();
return(JSONHelper.FromString(false, "未设置审核岗位，不能提交申请"));
}
sql = @"INSERT INTO Tb_HSPR_IncidentDelayApply(IID,CommID,CommName,IncidentID,IncidentNum,DealLimit,
RoomSign,BigReasonName,BigReasonCode,SmallReasonName,SmalReasonCode,Title,DelayReason,
DelayHours,DealyDate,WorkStartDate,UserCode,UserName,AuditState)
SELECT @IID,a.CommID,b.CommName,a.IncidentID,a.IncidentNum,isnull(a.DealLimit,0) AS DealLimit,
c.RoomSign,d.TypeName AS BigTypeName,BigCorpTypeCode,e.TypeName AS FineTypeName,FineCorpTypeCode,
@Title,@DelayReason,@DelayHours,@DealyDate,getdate(),@UserCode,@UserName,@AuditState
FROM Tb_HSPR_IncidentAccept a
LEFT JOIN Tb_HSPR_Community b ON a.CommID=b.CommID
LEFT JOIN Tb_HSPR_Room c ON a.RoomID=c.RoomID
LEFT JOIN Tb_HSPR_CorpIncidentType d ON d.TypeCode=a.BigCorpTypeCode
LEFT JOIN Tb_HSPR_CorpIncidentType e ON e.TypeCode=a.FineCorpTypeCode
WHERE a.IncidentID=@IncidentID;";
i = conn.Execute(sql, new
{
IID = iid,
IncidentID = incidentId,
Title = title,
UserName = Global_Var.LoginUserName,
UserCode = Global_Var.LoginUserCode,
DelayReason = delayReason,
DelayHours = delayHours,
DealyDate = delayDate,
AuditState = auditState
}, trans);
trans.Commit();
if (i > 0)
{
// 推送给审核人
PMSIncidentPush.SynchPushIncidentDelayApplied(iid);
return(JSONHelper.FromString(true, "申请成功"));
}
trans.Rollback();
return(JSONHelper.FromString(false, "申请失败"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message + Environment.NewLine + ex.StackTrace));
}
}
}
private string GetWaitWorkCount_QiangDan(DataRow Row)
{
string Result = JSONHelper.FromString(false, "未知错误!");;
DataTable dTable = Mobile_GetWaitWorkCount_QiangDan(Row["UserCode"].ToString(), AppGlobal.StrToLong(Row["CommID"].ToString()), Row["OrganCode"].ToString());
if (dTable.Rows.Count > 0)
{
Result = JSONHelper.FromString(dTable);
}
return(Result);
}
private string IncidentAccept(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区编号不能为空"));
}
if (!row.Table.Columns.Contains("Content") || string.IsNullOrEmpty(row["Content"].ToString()))
{
return(JSONHelper.FromString(false, "报事内容不能为空"));
}
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
var content = row["Content"].ToString();
var custId = default(long);
var roomId = default(long);
var images = default(string);
if (row.Table.Columns.Contains("CustID") && !string.IsNullOrEmpty(row["CustID"].ToString()))
{
custId = AppGlobal.StrToLong(row["CustID"].ToString());
}
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].ToString()))
{
roomId = AppGlobal.StrToLong(row["RoomID"].ToString());
}
if (row.Table.Columns.Contains("Images") && !string.IsNullOrEmpty(row["Images"].ToString()))
{
images = row["Images"].ToString();
}
using (var conn = new SqlConnection(Global_Fun.GetConnectionString("SQLConnection").ToString()))
{
var sql = @"DECLARE @IncidentID bigint;
DECLARE @IncidentNum nvarchar(20);
DECLARE @IncidentMan nvarchar(20),@Phone nvarchar(50);
SELECT @IncidentID = isnull(max(IncidentID)+1,(@CommID*CAST(1000000000000 AS bigint) +1))
FROM Tb_HSPR_IncidentAccept
WHERE CommID=@CommID;
EXEC Proc_HSPR_IncidentAssigned_GetCoordinateNum @CommID,3,'',@IncidentNum OUTPUT;
SELECT @IncidentMan=CustName,@Phone=isnull(MobilePhone,LinkmanTel)
FROM Tb_HSPR_Customer WHERE CustID=@CustID;
INSERT INTO Tb_HSPR_IncidentAccept(IncidentID,CommID,CustID,RoomID,DrClass,IncidentNum,IncidentPlace,
IncidentMan,IncidentDate,IncidentMode,IncidentContent,Phone,AdmiMan,AdmiDate,IncidentImgs,
Duty,IncidentSource,BigCorpTypeID,BigCorpTypeCode)
VALUES(@IncidentID,@CommID,@CustID,@RoomID,1,@IncidentNum,'户内',@IncidentMan,getdate(),'小程序（安全生产月）',
@IncidentContent,@Phone,'保利微服务',getdate(),@Images,'物业类','客户报事',100000092,'000100030001');";
var i = conn.Execute(sql, new { CommID = commId, CustID = custId, RoomID = roomId, IncidentContent = content, Images = images });
if (i == 1)
{
return(JSONHelper.FromString(true, "提交成功"));
}
return(JSONHelper.FromString(false, "提交失败"));
}
}
public static int ReceivePrecFees_POS(string Memo, int iCommID, long iCustID, long iRoomID, string[] CostIDs
, string strChargeMode, string[] iPrecAmounts, string strUserCode, ref string HitResult, ref string strBillsDate, ref string ErrMsg, ref long iReceID)
{
int iReturn = 0;
bool IsPrint = true;
int Percent = 0;//完成百分比
iReceID = 0;
HitResult = "";
string strBillsSign = "";
int iPrintTimes = 0;
DateTime dBillsDate = DateTime.Now;
strBillsDate = dBillsDate.ToString();
int iAccountWay = TypeRule.TWAccountWay.PreCosts;
string strReceMemo = "";
int iIsDelete = 0;
//string strInvoiceBills = "";
//long iInvoiceTypeID = 0;
string strInvoiceBill = "";
string strInvoiceUnit = "";
string strRemitterUnit = "";
string strBankName = "";
string strBankAccount = "";
string strChequeBill = "";
long iBillTypeID = 0;
try
{
#region 保存明细
string strSQL = " and CommID = " + iCommID.ToString() + " ";
strSQL = strSQL + " and CustID = -1 and RoomID = -1 ";
DataTable dTableDetail = HSPR_PreCostsDetail_Filter(strSQL);
if (dTableDetail != null)
{
for (int i = 0; i < CostIDs.Length; i++)
{
DataRow DRow = dTableDetail.NewRow();
DRow["RecdID"] = 0;
DRow["PrecID"] = 0;
DRow["CommID"] = iCommID;
DRow["CostID"] = AppGlobal.StrToLong(CostIDs[i]);
DRow["CustID"] = iCustID;
DRow["RoomID"] = iRoomID;
decimal amount = AppGlobal.StrToDec(iPrecAmounts[i]);
//DRow["PrecAmount"]=AppGlobal.StrToDec(PrecAmount.Value.ToString());
if (amount <= 0)
{
DRow["PrecAmount"] = 0;
}
else
{
DRow["PrecAmount"] = amount;
}
string strPrecMemo = Memo;
if (strPrecMemo == "请点击费用备注生成费用期间")
{
strPrecMemo = "";
}
DRow["PrecMemo"] = strPrecMemo;
DRow["BillsSign"] = strBillsSign;
DRow["PrecDate"] = DateTime.Now.ToString();
DRow["UserCode"] = strUserCode;
DRow["CostIDs"] = AppGlobal.StrToLong(CostIDs[i]);
DRow["RoomSign"] = "";
DRow["CostNames"] = "";
DRow["UserName"] = "";
DRow["ChargeMode"] = strChargeMode;
DRow["AccountWay"] = TypeRule.TWAccountWay.PreCosts;
DRow["HandID"] = 0;
DRow["HandIDs"] = "";
DRow["ParkNames"] = "";
DRow["IsDelete"] = 0;
dTableDetail.Rows.Add(DRow);
}
dTableDetail.AcceptChanges();
}
#endregion
HitResult = "";
IsPrint = false;
decimal iTotalAmount = 0;
int iFeesLimitCount = 0;
string strProcName = "Proc_HSPR_PreCosts_InsertAuto";
string ChildSQLEx = "";
string[] arrSQLEx = new string[10];
int iSelCount = 0;
int iCut = 0;
//每段笔数
int iExcCount = 10;
//*行计数器
int k = 0;
Decimal Len = 1;
if (dTableDetail != null)
{
if (dTableDetail.Rows.Count != 0)
{
Len = Convert.ToDecimal(dTableDetail.Rows.Count + 1);
foreach (DataRow DRow in dTableDetail.Rows)
{
k++;
Percent = Convert.ToInt32(k / Len * 100);
#region 取值
int iiCommID = AppGlobal.StrToInt(DRow["CommID"].ToString());
long iiCustID = AppGlobal.StrToLong(DRow["CustID"].ToString());
long iiRoomID = AppGlobal.StrToLong(DRow["RoomID"].ToString());
long iiCostID = AppGlobal.StrToLong(DRow["CostID"].ToString());
decimal iiPrecAmount = AppGlobal.StrToDec(DRow["PrecAmount"].ToString());
string strPrecMemo = DRow["PrecMemo"].ToString();
long iiHandID = AppGlobal.StrToLong(DRow["HandID"].ToString());
string strTmpCostIDs = DRow["CostIDs"].ToString();
string strTmpHandIDs = DRow["HandIDs"].ToString();
#endregion
#region 合计
iTotalAmount = iTotalAmount + iiPrecAmount;
if (iiPrecAmount > 0)
{
iFeesLimitCount++;
}
#endregion
//有需要预交的费用
if (iiPrecAmount > 0)
{
#region 明细生成
ChildSQLEx = " exec " + strProcName + " " + iiCommID.ToString() + ","
+ iiCustID.ToString() + ","
+ iiRoomID.ToString() + ","
+ iiCostID.ToString() + ","
+ iiPrecAmount.ToString() + ","
+ "$ReceID$" + ","
+ "N'" + strPrecMemo + "',"
+ iiHandID.ToString() + ","
+ "N'" + strTmpCostIDs + "',"
+ "N'" + strTmpHandIDs + "'"
+ "\r\n";
#region 分成多段
if ((iSelCount < (iExcCount * (iCut + 1))) && (iSelCount >= iExcCount * iCut))
{
arrSQLEx[iCut] = arrSQLEx[iCut] + ChildSQLEx;
}
iSelCount++;
if (iSelCount >= (iExcCount * (iCut + 1)))
{
iCut++;
}
#endregion
#endregion
}
}
//有需要预交的费用
if (iFeesLimitCount > 0)
{
#region 票据
DataTable dTable = HSPR_BillUse_GetBillsSignUseRange(iCommID, "", "POS机端");
if (dTable.Rows.Count > 0)
{
DataRow DRow = dTable.Rows[0];
iBillTypeID = AppGlobal.StrToLong(DRow["BillTypeID"].ToString());
strBillsSign = DRow["BillsSign"].ToString();
HitResult = strBillsSign;
}
dTable.Dispose();
#endregion
//预交收据
iReceID = HSPR_PreCostsReceipts_InsertAuto(iCommID, iCustID, iRoomID, strBillsSign
, iPrintTimes, strBillsDate, strUserCode, strChargeMode, iAccountWay, strReceMemo, iTotalAmount, iIsDelete
, strInvoiceBill, strInvoiceUnit, strRemitterUnit, strBankName, strBankAccount, strChequeBill, iBillTypeID
, arrSQLEx[0], arrSQLEx[1], arrSQLEx[2], arrSQLEx[3], arrSQLEx[4], arrSQLEx[5], arrSQLEx[6], arrSQLEx[7], arrSQLEx[8], arrSQLEx[9]);
#region 收据记录
if (strBillsSign != "")
{
if (iReceID != 0)
{
int iSourceType = 2;//预交收据
HSPR_BillUseInstead_InsUpdate(iCommID, iBillTypeID, strBillsSign, strUserCode, iReceID, iSourceType, TypeRule.TWBillUseCase.PreCosts);
}
//更新号段数量和金额
if ((iBillTypeID != 0) && (strBillsSign != ""))
{
HSPR_BillUse_UpdateData(iCommID, iBillTypeID, strBillsSign);
}
}
#endregion
#region 回發票據號
string ContionStringr = ConnectionString;
string strSqlr = " select BillsSign,BillsDate from Tb_HSPR_PreCostsReceipts where receid='" + iReceID + "' ";
DataTable dtr = new DbHelperSQLP(PubConstant.GetConnectionString("SQLConnection").ToString()).Query(strSqlr).Tables[0];
if (dtr.Rows.Count > 0)
{
DataRow DRow = dtr.Rows[0];
strBillsSign = DRow["BillsSign"].ToString();
HitResult = strBillsSign;
strBillsDate = DRow["BillsDate"].ToString();
}
#endregion
if (iReceID != 0)
{
IsPrint = true;
}
else
{
HitResult = "预存费用失败！";
IsPrint = false;
}
}
}
else
{
HitResult = "请添加需要预交的费用！";
IsPrint = false;
}
}
}
catch (Exception ex)
{
ErrMsg = ex.Message;
}
finally
{
}
if (IsPrint == true)
{
iReturn = 1;
}
else
{
iReturn = 0;
}
return(iReturn);
}
/// <summary>
/// 报事评价
/// </summary>
private string IncidentEvaluate(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区编号不能为空"));
}
if (!row.Table.Columns.Contains("IncidentID") || string.IsNullOrEmpty(row["IncidentID"].ToString()))
{
return(JSONHelper.FromString(false, "报事编号不能为空"));
}
if (!row.Table.Columns.Contains("ServiceQuality") || string.IsNullOrEmpty(row["ServiceQuality"].ToString()))
{
return(JSONHelper.FromString(false, "评价星级不能为空"));
}
if (!row.Table.Columns.Contains("CommentContent") || string.IsNullOrEmpty(row["CommentContent"].ToString()))
{
return(JSONHelper.FromString(false, "评价内容不能为空"));
}
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
var incidentId = AppGlobal.StrToLong(row["IncidentID"].ToString());
var serviceQuality = row["ServiceQuality"].ToString();
var commentContent = row["CommentContent"].ToString();
//查询小区
var community = GetCommunity(commId.ToString());
if (community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var sql = @"SELECT * FROM Tb_HSPR_IncidentAccept WHERE IncidentID=@IncidentID;";
var incidentInfo = conn.Query <PMSIncidentAcceptModel>(sql, new { IncidentID = incidentId }).FirstOrDefault();
if (incidentInfo == null)
{
return(JSONHelper.FromString(false, "不存在此报事信息"));
}
var control = PMSIncidentAccept.GetIncidentControlSet(conn);
if (control.IncidentComprehensiveEvaluationOptional.Contains("一般") == false && serviceQuality == "一般")
{
serviceQuality = "满意";
}
// 业主自己从业主App评价，受访人默认为客户本人、没有回访人，此条件可以用于ERP端判断用
sql = @"INSERT INTO Tb_HSPR_IncidentReply(CommID,IncidentID,ReplyType,
ReplyDate,ReplyContent,ServiceQuality,ReplyWay,ReplyResult,IsDelete)
VALUES(@CommID,@IncidentID,'App业主自评',getdate(),@CommentContent,@ServiceQuality,@ReplyWay,@ReplyResult,0);
UPDATE Tb_HSPR_IncidentAccept SET ServiceQuality=@ServiceQuality,CustComments=@CommentContent,
IsClose=1,CloseTime=getdate()
WHERE IncidentID=@IncidentID;";
conn.Execute(sql, new
{
CommID = community.CommID,
IncidentID = incidentId,
CommentContent = commentContent,
ServiceQuality = serviceQuality,
ReplyWay = "客户线上评价",
ReplyResult = "1"
});
return(JSONHelper.FromString(true, "报事评价成功!"));
}
}
/// <summary>
/// 报事受理
/// </summary>
private string IncidentAccept(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区编号不能为空"));
}
if (!row.Table.Columns.Contains("IncidentPlace") || string.IsNullOrEmpty(row["IncidentPlace"].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, "联系方式不能为空"));
}
var communityId = row["CommunityId"].AsString();
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查询到小区信息"));
}
var incidentPlace = row["IncidentPlace"].ToString();
var content = row["Content"].ToString();
var custId = AppGlobal.StrToLong(row["CustID"].ToString());
var roomId = AppGlobal.StrToLong(row["RoomID"].ToString());
var regionalId = 0L;
var phone = row["Phone"].ToString();
var incidentImgs = "";
var reserveDate = DateTime.Now.ToString();
var incidentMan = row["IncidentMan"].ToString();
// 1=户内报事，2=公区报事，3=通知开水，4=通知开电，5=人员放行，6=车辆放行
var type = 0;
var isTousu = 0;
var appName = "业主App";
if (row.Table.Columns.Contains("RegionalID") && !string.IsNullOrEmpty(row["RegionalID"].ToString()))
{
regionalId = AppGlobal.StrToLong(row["RegionalID"].ToString());
}
if (row.Table.Columns.Contains("IncidentImgs") && !string.IsNullOrEmpty(row["IncidentImgs"].ToString()))
{
incidentImgs = row["IncidentImgs"].ToString();
}
if (row.Table.Columns.Contains("ReserveDate") && !string.IsNullOrEmpty(row["ReserveDate"].ToString()))
{
reserveDate = row["ReserveDate"].ToString();
}
if (row.Table.Columns.Contains("IsTousu") && !string.IsNullOrEmpty(row["IsTousu"].ToString()))
{
isTousu = AppGlobal.StrToInt(row["IsTousu"].ToString());
}
if (row.Table.Columns.Contains("Type") && !string.IsNullOrEmpty(row["Type"].ToString()))
{
type = AppGlobal.StrToInt(row["Type"].ToString());
}
if (row.Table.Columns.Contains("AppName") && !string.IsNullOrEmpty(row["AppName"].ToString()))
{
appName = row["AppName"].ToString();
}
var bigCorpTypeID = 0L;
var bigCorpTypeCode = default(string);
var fineCorpTypeID = 0L;
var fineCorpTypeCode = default(string);
var incidentSource = "客户报事";
var drClass = 1;
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
// 新版报事控制
var control = PMSIncidentAccept.GetIncidentControlSet();
if (control.DefaultIndoorIncidentAcceptTypeID != 0 &&
(incidentPlace == "户内" || incidentPlace == "户内报事" || incidentPlace == "业主权属"))
{
bigCorpTypeID = control.DefaultIndoorIncidentAcceptTypeID;
}
else if (control.DefaultPublicIncidentAcceptTypeID != 0 &&
(incidentPlace == "公区" || incidentPlace == "公区报事" || incidentPlace == "公共区域"))
{
bigCorpTypeID = control.DefaultPublicIncidentAcceptTypeID;
}
// 1 = 户内报事，2 = 公区报事，3 = 通知开水，4 = 通知开电，5 = 人员放行，6 = 车辆放行
switch (type)
{
case 3: drClass = 2; fineCorpTypeID = control.DefaultNotifyOpenWaterIncidentAcceptTypeID; break; // 通知开水
case 4: drClass = 2; fineCorpTypeID = control.DefaultNotifyOpenPowerIncidentAcceptTypeID; break; // 通知开电
case 5: drClass = 2; fineCorpTypeID = control.DefaultLetPersonPassIncidentAcceptTypeID; break; // 人员放行
case 6: drClass = 2; fineCorpTypeID = control.DefaultLetCarPassIncidentAcceptTypeID; break; // 车辆放行
}
if (bigCorpTypeID == 0 && fineCorpTypeID == 0)
{
using (var conn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT * FROM Tb_Control_AppIncidentAccept
WHERE CommunityID IS NOT NULL AND CommunityID=@CommunityID AND IsEnable=1 AND IsDelete=0
UNION ALL
SELECT * FROM Tb_Control_AppIncidentAccept
WHERE CommunityID IS NULL AND CorpID=@CorpID AND IsEnable=1 AND IsDelete=0";
try
{
var data = conn.Query(sql, new { CorpID = community.CorpID, CommunityID = community.Id }).FirstOrDefault();
if (data != null)
{
if (incidentPlace == "户内" || incidentPlace == "户内报事" || incidentPlace == "业主权属")
{
bigCorpTypeID = data.DefaultIndoorIncidentTypeID;
}
else
{
bigCorpTypeID = data.DefaultPublicIncidentTypeID;
}
incidentSource = data.DefaultIncidentSource;
}
}
catch (Exception)
{
}
}
}
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
var sql = @"SELECT IncidentID,IncidentNum FROM Tb_HSPR_IncidentAccept
WHERE CommID=@CommID AND RoomID=@RoomID AND RegionalID=@RegionalID AND IncidentContent=@IncidentContent
AND isnull(IsDelete,0)=0 AND isnull(IsClose,0)=0 AND convert(varchar(13),IncidentDate)=convert(varchar(13),getdate())";
var incidentInfo = conn.Query(sql, new
{
CommID = community.CommID,
RoomID = roomId,
RegionalID = regionalId,
IncidentContent = content
}, trans).FirstOrDefault();
if (incidentInfo != null)
{
trans.Commit();
return(new ApiResult(false, "此报事已受理，请勿重复提交").toJson());
}
var dispLimit = 0.0m;
var dealLimit = 0.0m;
if (bigCorpTypeID != 0)
{
sql = @"SELECT TypeCode FROM Tb_HSPR_CorpIncidentType WHERE CorpTypeID=@BigCorpTypeID;
SELECT isnull(DispLimit,0) FROM Tb_HSPR_CorpIncidentType WHERE CorpTypeID=@BigCorpTypeID;
SELECT CASE WHEN @IncidentPlace='户内' THEN isnull(DealLimit,0) ELSE isnull(DealLimit2,0) END
FROM Tb_HSPR_CorpIncidentType WHERE CorpTypeID=@BigCorpTypeID;";
var reader = conn.QueryMultiple(sql, new { BigCorpTypeID = bigCorpTypeID, IncidentPlace = incidentPlace }, trans);
bigCorpTypeCode = reader.Read <string>().FirstOrDefault();
dispLimit = reader.Read <decimal>().FirstOrDefault();
dealLimit = reader.Read <decimal>().FirstOrDefault();
}
if (fineCorpTypeID != 0)
{
sql = @"SELECT TypeCode FROM Tb_HSPR_CorpIncidentType WHERE CorpTypeID=@CorpTypeID;";
fineCorpTypeCode = conn.Query <string>(sql, new { CorpTypeID = fineCorpTypeID }, trans).FirstOrDefault();
}
// 报事id
var incidentId = conn.Query <long>("Proc_HSPR_IncidentAccept_GetMaxNum",
new { CommID = community.CommID, SQLEx = "" }, trans, false, null, CommandType.StoredProcedure).FirstOrDefault();
// 报事编号
var incidentNum = conn.Query <long>("Proc_HSPR_IncidentAccept_GetMaxIncidentNum",
new { CommID = community.CommID, }, trans, false, null, CommandType.StoredProcedure).FirstOrDefault();
// 更新报事编号
conn.Execute("Proc_HSPR_IncidentAssigned_GetCoordinateNum_UpdateSNum",
new { CommID = community.CommID, IncidentType = 3, IncidentHead = "" }, trans, null, CommandType.StoredProcedure);
sql = @"INSERT INTO Tb_HSPR_IncidentAccept(CommID,IncidentID,IncidentNum,IncidentPlace,IncidentSource,IncidentMode,
DrClass,IsTouSu,CustID,RoomID,RegionalID,IncidentMan,IncidentDate,IncidentContent,IncidentImgs,Phone,AdmiMan,AdmiDate,
DispType,DispLimit,DealLimit,ReserveDate,IsFee,Duty,BigCorpTypeID,TypeID,BigCorpTypeCode,IsStatistics,IsDelete,
FineCorpTypeID,FineCorpTypeCode)
VALUES(@CommID,@IncidentID,@IncidentNum,@IncidentPlace,@IncidentSource,@IncidentMode,
@DrClass,0,@CustID,@RoomID,@RegionalID,@IncidentMan,getdate(),@IncidentContent,@IncidentImgs,@Phone,@AdmiMan,getdate(),
0,@DispLimit,@DealLimit,@ReserveDate,0,'物业类',@BigCorpTypeID,@TypeID,@BigCorpTypeCode,1,0,
@FineCorpTypeID,@FineCorpTypeCode);";
conn.Execute(sql, new
{
CommID = community.CommID,
IncidentID = incidentId,
IncidentNum = incidentNum,
IncidentPlace = incidentPlace,
IncidentSource = incidentSource,
IncidentMode = "业主APP",
CustID = custId,
RoomID = roomId,
RegionalID = regionalId,
IncidentMan = incidentMan,
IncidentContent = content,
IncidentImgs = incidentImgs,
Phone = phone,
DispLimit = dispLimit,
DealLimit = dealLimit,
ReserveDate = reserveDate,
BigCorpTypeID = bigCorpTypeID,
BigCorpTypeCode = bigCorpTypeCode,
FineCorpTypeID = fineCorpTypeID,
FineCorpTypeCode = fineCorpTypeCode,
DrClass = drClass,
AdmiMan = appName,
TypeID = $",{bigCorpTypeID},"
}, trans);
trans.Commit();
// 推送信息
PMSIncidentPush.SynchPushIncidentAccepted(incidentId);
return(JSONHelper.FromString(true, "报事成功!稍后会有人员与您联系!"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message + ex.StackTrace));
}
}
}
//房屋租赁列表
public string RentalList_v2(DataRow row)
{
try
{
var communityId = default(string);
var city = default(string);
var houseType = default(string);
var bussType = "租售";
var pageIndex = 1;
var pageSize = 20;
var condition = " AND ProcessState='发布' ";
// 筛选地区
if (row.Table.Columns.Contains("City") && row["City"].ToString() != "")
{
city = row["City"].ToString().Replace("市", "");
condition += $"AND (City LIKE '%{ city }%' OR Province LIKE '%{ city }%')";
}
// 筛选小区
if (row.Table.Columns.Contains("CommunityId") && row["CommunityId"].ToString() != "")
{
communityId = row["CommunityId"].ToString();
condition += $" AND CommunityId='{ communityId }' ";
}
// 筛选户型
if (row.Table.Columns.Contains("HouseType") && row["HouseType"].ToString() != "")
{
houseType = row["HouseType"].ToString();
condition += $" AND HouseType LIKE '%{ communityId }%' ";
}
// 筛选租售
if (row.Table.Columns.Contains("BussType") && row["BussType"].ToString() != "")
{
bussType = row["BussType"].ToString();
condition += $" AND BussType='{ bussType }'";
}
// 筛选价格
var minAmount = AppGlobal.StrToLong(row["StartAmount"]?.ToString());
var maxAmount = AppGlobal.StrToLong(row["EndAmount"]?.ToString());
maxAmount = maxAmount == 0 ? 9999999999 : maxAmount;
if (bussType == "租售")
{
condition += $@" AND isnull(Rent, 0)>={minAmount} AND isnull(Rent, 0)<={maxAmount}";
}
else
{
condition += $@" AND isnull(Amount, 0)>={minAmount} AND isnull(Amount, 0)<={maxAmount}";
}
// App隔离
if (row.Table.Columns.Contains("AppleBundleID") && row["AppleBundleID"].ToString() != "")//苹果包名
{
condition += $" AND CommunityId in(select Id from Tb_Community where AppleBundleID = '{row["AppleBundleID"]}') ";
}
if (row.Table.Columns.Contains("AndroidPackageName") && row["AndroidPackageName"].ToString() != "")//安桌包名
{
condition += $" AND CommunityId in(select Id from Tb_Community where AndroidPackageName = '{row["AndroidPackageName"]}') ";
}
using (var conn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = $@"SELECT Id,CommName,isnull(BussType,'租售') AS BussType,
CASE WHEN Rent IS NULL THEN '面议'
WHEN convert(nvarchar(20),Rent)='面议' THEN '面议'
WHEN convert(decimal(12,2),Rent)=0 THEN '面议'
ELSE convert(nvarchar(20),convert(decimal(12,0),isnull(Rent,0))) END AS Rent,
CASE WHEN Amount IS NULL THEN '面议'
WHEN convert(nvarchar(20),Amount)='面议' THEN '面议'
WHEN convert(decimal(12,2),Amount)=0 THEN '面议'
ELSE convert(nvarchar(20),CONVERT(decimal(12,1),isnull(Amount,0.0)/10000.0))+'万' END AS Amount,
substring(img,0,charindex(',',isnull(Img,''))) AS Img,isnull(Title,CommName) AS Title,
isnull(HouseType,'住宅') AS HouseType,isnull(BuildingType,'住宅') AS BuildingType,
isnull(ProcessDate,RegisterDate) PubDate,RegisterDate,Orientation,BuildArea
FROM View_Tb_Rental WHERE 1=1 {condition}";
var resultSet = GetListDapper(out int pageCount, out int count, sql, pageIndex, pageSize, "RegisterDate", 1, "Id", conn);
var json = new ApiResult(true, resultSet).toJson();
return(json.Insert(json.Length - 1, ",\"PageCount\":" + pageCount));
}
}
catch (Exception ex)
{
return(JSONHelper.FromString(false, 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("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "未指定欠费主体").toJson());
}
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "房间编码不能为空"));
}
var communityId = row["CommunityId"].AsString();
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查找到小区信息"));
}
var commId = AppGlobal.StrToInt(community.CommID);
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var chargeConnectionString = Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_CHARGE);
var shieldCost = "";
// 查询需要屏蔽的费用
using (var appConn = new SqlConnection(PubConstant.UnifiedContionString))
{
var sql = @"SELECT isnull(object_id(N'Tb_Control_AppCostItem',N'U'),0);";
if (appConn.Query <long>(sql).FirstOrDefault() != 0)
{
sql = @"SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND CommunityID=@Id AND AllowShow=0;
SELECT CorpCostID FROM Tb_Control_AppCostItem WHERE CorpID=@CorpID AND isnull(CommunityID,'')='' AND AllowShow=0;";
var reader = appConn.QueryMultiple(sql, new { CorpID = community.CorpID, Id = community.Id });
var data1 = reader.Read <string>().FirstOrDefault();
var data2 = reader.Read <string>().FirstOrDefault();
var data = data1 ?? data2;
if (!string.IsNullOrEmpty(data))
{
using (var erpConn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
sql = @"SELECT CostCode FROM Tb_HSPR_CorpCostItem WHERE CorpCostID IN (SELECT Value FROM SplitString(@Data,',',1))";
var costCodes = erpConn.Query <string>(sql, new { Data = data }).Select(obj => $"'{obj}'");
shieldCost = $@"AND CostCode NOT IN ({ string.Join(",", costCodes.ToArray()) })";
}
}
}
}
using (var chargeConn = new SqlConnection(chargeConnectionString))
{
var sql = @"SELECT isnull(sum(isnull(a.PrecAmount,0.00)),0.00) AS PrecAmount
FROM Tb_HSPR_PreCosts a
WHERE a.CustID=@CustID AND a.RoomID=@RoomID AND a.PrecAmount>0 AND a.IsPrec=1;";
// 预存余额
var precAmount = chargeConn.Query <decimal>(sql, new { CustID = custId, RoomID = roomId }).FirstOrDefault();
sql = $@"/* 欠费未缴清 */
SELECT isnull(sum(isnull(DebtsAmount,0) + isnull(LateFeeAmount,0)),0) AS DebtsAmount
FROM (
SELECT DebtsAmount,CostCode,
CASE WHEN isnull(x.DebtsLateAmount, 0)>0
THEN dbo.funGetLateFeeDebts(x.CommID,x.FeesID,isnull(x.DebtsLateAmount, 0))
ELSE 0.0 END AS LateFeeAmount
FROM Tb_HSPR_Fees x
LEFT JOIN { community.DBName }.dbo.Tb_HSPR_CostItem y ON x.CommID=y.CommID AND x.CostID=y.CostID
WHERE x.CustID=@CustID AND x.RoomID=@RoomID {shieldCost}
AND isnull(x.IsCharge, 0)=0 AND isnull(x.IsBank, 0)=0 AND isnull(x.IsPrec, 0)=0 AND isnull(IsFreeze, 0)=0
)AS t
/* 仅剩违约金 */
/*
SELECT isnull(x.LateFeeAmount,0.00)
FROM view_HSPR_Fees_SearchFilter x
WHERE x.CustID=@CustID AND x.RoomID=@RoomID {shieldCost}
AND isnull(x.IsCharge,0)=1 AND isnull(x.IsBank,0)=0 AND isnull(x.IsPrec,0)=0 AND isnull(IsFreeze,0)=0
AND isnull(x.LateFeeAmount,0.00)>0;
*/";
// 未缴费用，含违约金
var reader = chargeConn.QueryMultiple(sql, new { CustID = custId, RoomID = roomId });
var debtsAmount = reader.Read <decimal>().FirstOrDefault();
//var lateFeeAmount = reader.Read<decimal>().FirstOrDefault();
var lateFeeAmount = 0;
return(new ApiResult(true, new { PrecAmount = precAmount, DebtsAmount = debtsAmount + lateFeeAmount, BillingCycle = 1 }).toJson());
}
}
/// <summary>
/// 人员申报
/// </summary>
private string PersonRegistration(DataRow row)
{
#region 基础数据验证
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].AsString()))
{
return(new ApiResult(false, "缺少参数CommID").toJson());
}
if (!row.Table.Columns.Contains("CustID") || string.IsNullOrEmpty(row["CustID"].AsString()))
{
return(new ApiResult(false, "缺少参数CustID").toJson());
}
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].AsString()))
{
return(new ApiResult(false, "缺少参数RoomID").toJson());
}
if (!row.Table.Columns.Contains("CustName") || string.IsNullOrEmpty(row["CustName"].AsString()))
{
return(new ApiResult(false, "缺少参数CustName").toJson());
}
if (!row.Table.Columns.Contains("PersonName") || string.IsNullOrEmpty(row["PersonName"].AsString()))
{
return(new ApiResult(false, "缺少参数PersonName").toJson());
}
if (!row.Table.Columns.Contains("Gender") || string.IsNullOrEmpty(row["Gender"].AsString()))
{
return(new ApiResult(false, "缺少参数Gender").toJson());
}
if (!row.Table.Columns.Contains("IDNumber") || string.IsNullOrEmpty(row["IDNumber"].AsString()))
{
return(new ApiResult(false, "缺少参数IDNumber").toJson());
}
if (!row.Table.Columns.Contains("Phone") || string.IsNullOrEmpty(row["Phone"].AsString()))
{
return(new ApiResult(false, "缺少参数Phone").toJson());
}
if (!row.Table.Columns.Contains("Relation") || string.IsNullOrEmpty(row["Relation"].AsString()))
{
return(new ApiResult(false, "缺少参数Relation").toJson());
}
if (!row.Table.Columns.Contains("Whence") || string.IsNullOrEmpty(row["Whence"].AsString()))
{
return(new ApiResult(false, "缺少参数Whence").toJson());
}
var commId = AppGlobal.StrToInt(row["CommID"].AsString());
var custId = AppGlobal.StrToLong(row["CustID"].AsString());
var roomId = AppGlobal.StrToLong(row["RoomID"].AsString());
var custName = row["CustName"].AsString();
var personName = row["PersonName"].AsString();
var gender = AppGlobal.StrToInt(row["Gender"].AsString());
var idNumber = row["IDNumber"].AsString();
var phone = row["Phone"].AsString();
var relation = row["Relation"].AsString();
var whence = row["Whence"].AsString();
var remark = default(string);
if (row.Table.Columns.Contains("Remark") && !string.IsNullOrEmpty(row["Remark"].AsString()))
{
remark = row["Remark"].AsString();
}
var additionalInfo = default(string);
if (row.Table.Columns.Contains("AdditionalInfo") && !string.IsNullOrEmpty(row["AdditionalInfo"].AsString()))
{
additionalInfo = row["AdditionalInfo"].AsString();
}
#endregion
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
var sql = @"SELECT PersonName FROM Tb_IOM_PersonRegistration WHERE RoomID=@RoomID AND IDNumber=@IDNumber AND IsDelete=0";
var persons = conn.Query <string>(sql, new { RoomID = roomId, IDNumber = idNumber }, trans);
if (persons.Count() > 0)
{
return(JSONHelper.FromString(false, $"该身份已在本项目登记，登记名为：{persons.First()}"));
}
var regId = Guid.NewGuid().ToString();
var cardId = Guid.NewGuid().ToString();
sql = @"INSERT INTO Tb_IOM_PersonRegistration(IID,CommID,CustID,CustName,RoomID,PersonName,Gender,
IDNumber,Phone,Relation,Whence,RegistrationUser,Remark)
VALUES(@IID,@CommID,@CustID,@CustName,@RoomID,@PersonName,@Gender,
@IDNumber,@Phone,@Relation,@Whence,@RegistrationUser,@Remark);";
var i = conn.Execute(sql, new
{
IID = regId,
CommID = commId,
CustID = custId,
CustName = custName,
RoomID = roomId,
PersonName = personName,
Gender = gender,
IDNumber = idNumber,
Phone = phone,
Relation = relation,
Whence = whence,
RegistrationUser = Global_Var.LoginUserCode,
Remark = remark
}, trans);
if (i == 1)
{
// 添加出入证记录信息
sql = @"DECLARE @PassNo varchar(12);
SELECT @PassNo=dbo.PadLeft(convert(varchar(12),isnull(max(convert(bigint,PassNo)),0)+1),'0',6)
FROM Tb_IOM_PassCard WHERE CommID=@CommID AND IsDelete=0;
INSERT INTO Tb_IOM_PassCard(IID,CommID,RoomID,PersonRegistrationID,PassNo,AddUser)
VALUES(@IID,@CommID,@RoomID,@PersonRegistrationID,@PassNo,@AddUser);";
conn.Execute(sql, new
{
IID = cardId,
CommID = commId,
RoomID = roomId,
PersonRegistrationID = regId,
AddUser = Global_Var.LoginUserCode
}, trans);
// 存在额外登记信息
sql = @"INSERT INTO Tb_IOM_PersonRegistrationAdditionalInfo(IID,PersonRegistrationID,AdditionalInfoID,Result)
VALUES(newid(),@PersonRegistrationID,@AdditionalInfoID,@Result);";
var jArray = (JArray)JsonConvert.DeserializeObject(additionalInfo);
foreach (var item in jArray)
{
conn.Execute(sql, new
{
PersonRegistrationID = regId,
AdditionalInfoID = item["IID"].ToString(),
Result = item["Value"].ToString()
}, trans);
}
}
trans.Commit();
return(JSONHelper.FromString(true, cardId));
}
catch (Exception ex)
{
trans.Rollback();
return(new ApiResult(false, ex.Message + ex.StackTrace).toJson());
}
}
}
/// <summary>
/// 非正常关闭申请
/// </summary>
private string IncidentUnnormalCloseApply(DataRow row)
{
if (!row.Table.Columns.Contains("IncidentID") || string.IsNullOrEmpty(row["IncidentID"].ToString()))
{
return(new ApiResult(false, "缺少参数IncidentID").toJson());
}
if (!row.Table.Columns.Contains("CloseReason") || string.IsNullOrEmpty(row["CloseReason"].ToString()))
{
return(new ApiResult(false, "缺少参数CloseReason").toJson());
}
if (!row.Table.Columns.Contains("CloseSituation") || string.IsNullOrEmpty(row["CloseSituation"].ToString()))
{
return(new ApiResult(false, "缺少参数CloseSituation").toJson());
}
var incidentId = AppGlobal.StrToLong(row["IncidentID"].ToString());
var CloseReason = row["CloseReason"].ToString();
var CloseSituation = row["CloseSituation"].ToString();
var imgs = default(string);
if (row.Table.Columns.Contains("Imgs") && !string.IsNullOrEmpty(row["Imgs"].AsString()))
{
imgs = row["Imgs"].AsString();
}
var incidentInfo = GetIncidentInfo(incidentId);
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
var sql = @"SELECT count(1) FROM Tb_HSPR_IncidentUnnormalClose
WHERE IncidentID=@IncidentID AND (AuditState IS NULL OR AuditState='审核中');";
// 是否已经提交申请
int i = conn.Query <int>(sql, new { IncidentID = incidentId }, trans).FirstOrDefault();
if (i > 0)
{
trans.Commit();
return(JSONHelper.FromString(false, "已提交申请，请勿重复申请"));
}
var iid = Guid.NewGuid().ToString();
var auditState = "审核中";
var title = $"{Global_Var.UserName}提交的报事编号为【{incidentInfo.IncidentNum}】的非正常关闭申请";
sql = @"INSERT INTO Tb_HSPR_IncidentAuditingWorkFlow(IID, KeyIID, CommID, RoleCode, IsAudit, AuditType,orderID)
SELECT newid(), @KeyIID, @CommID, RoleCode, 0, 2,orderID
FROM Tb_HSPR_IncidentAuditingSet
WHERE AuditType=2
AND TypeCode=(SELECT BigCorpTypeCode FROM Tb_HSPR_IncidentAccept WHERE IncidentID=@IncidentID)
ORDER BY OrderID;";
// 插入有审核权限的岗位
i = conn.Execute(sql, new
{
KeyIID = iid,
CommID = incidentInfo.CommID,
IncidentID = incidentId
}, trans);
if (i == 0)
{
trans.Rollback();
return(JSONHelper.FromString(false, "未设置审核岗位，不能提交申请"));
// 此处不再更新主表数据
//conn.Execute(@"UPDATE Tb_HSPR_IncidentAccept SET IsClose=1,CloseTime=getdate(),CloseType=1,NoNormalCloseReasons=@CloseReason,
// CloseSituation=@CloseSituation,CloseUserCode=@CloseUserCode WHERE IncidentID=@IncidentID;",
// new
// {
// IncidentID = IncidentID,
// CloseUserCode = Global_Var.LoginUserCode,
// CloseSituation = CloseSituation,
// CloseReason = CloseReason
// }, trans);
}
sql = @"INSERT INTO Tb_HSPR_IncidentUnnormalClose(IID,CommID,CommName,IncidentID,IncidentNum,
RoomSign,Title,CloseReason,WorkStartDate,UserCode,UserName,AuditState)
SELECT @IID,a.CommID,b.CommName,IncidentID,IncidentNum,c.RoomSign,@Title,
@CloseReason,getdate(),@UserCode,@UserName,@AuditState
FROM Tb_HSPR_IncidentAccept a
LEFT JOIN Tb_HSPR_Community b ON a.CommID=b.CommID
LEFT JOIN Tb_HSPR_Room c ON a.RoomID=c.RoomID
WHERE IncidentID=@IncidentID;";
i = conn.Execute(sql, new
{
IID = iid,
IncidentID = incidentId,
Title = title,
UserName = Global_Var.LoginUserName,
UserCode = Global_Var.LoginUserCode,
CloseReason = CloseReason,
AuditState = auditState
}, trans);
if (!string.IsNullOrEmpty(imgs))
{
sql = @"INSERT INTO Tb_HSPR_IncidentAdjunct(AdjunctCode,IncidentID, AdjunctName,FilPath,FileExName,FileSize,ImgGUID)
VALUES(@AdjunctCode,@IncidentID,'非正常关闭图片',@FilPath,@FileExName,@FileSize,@ImgGUID)";
var tmp = imgs.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var item in tmp)
{
Thread.Sleep(50);
var adjunctCode = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random((int)(DateTime.Now.Ticks & 0xffffffffL) | (int)(DateTime.Now.Ticks >> 32)).Next(100, 999).ToString();
conn.Execute(sql, new
{
AdjunctCode = adjunctCode,
IncidentID = incidentId,
FilPath = item,
FileExName = Path.GetExtension(item),
FileSize = 0,
ImgGUID = iid
}, trans);
}
}
trans.Commit();
if (i > 0)
{
// 推送给审核人
PMSIncidentPush.SynchPushIncidentUnnormalCloseApplied(iid);
return(JSONHelper.FromString(true, "申请成功"));
}
trans.Rollback();
return(JSONHelper.FromString(false, "申请失败"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message + Environment.NewLine + ex.StackTrace));
}
}
}
