public string GetUserOrders(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户id不能为空"));
}
var userId = row["UserId"].ToString();
var state = OrderState.All;
if (row.Table.Columns.Contains("State") && !string.IsNullOrEmpty(row["State"].ToString()))
{
state = (OrderState)AppGlobal.StrToInt(row["State"].ToString());
}
var condition = "";
switch (state)
{
case OrderState.WaitingPay:
condition = " AND a.IsPay='未付款'";
break;
case OrderState.WaitingSendOut:
condition = " AND a.IsPay='已付款' AND isnull(IsDeliver,'未发货')='未发货'";
break;
case OrderState.WaitingReceiving:
condition = " AND a.IsPay='已付款' AND isnull(a.IsDeliver,'未发货')='已发货' AND isnull(IsReceive,'未收货')='未收货'";
break;
case OrderState.WaitingEvaluation:
condition = " AND a.IsPay='已付款' AND isnull(a.IsDeliver,'未发货')='已发货' AND isnull(IsReceive,'未收货')='已收货'";
break;
case OrderState.WaitingRefund:
break;
case OrderState.Refunded:
condition = " AND a.IsPay='已付款' AND isnull(a.HandleState,'已退款')='已退款'";
break;
case OrderState.Deleted:
condition = " AND a.IsDelete=1";
break;
}
var sql = $@"SELECT * FROM
(
SELECT row_number() OVER (ORDER BY a.ReceiptDate DESC) AS RN,
a.Id,b.BussName,isnull(b.BussNature,'周边商家') AS BussNature,a.ReceiptSign,
isnull(a.IsPay,'未付款') AS IsPay,
isnull(a.IsDeliver,'未发货') AS IsDeliver,
isnull(a.IsReceive,'未收货') AS IsReceive,
a.HandleState,a.ReceiptDate,
(SELECT count(1) FROM Tb_Charge_ReceiptDetail c WHERE c.ReceiptCode=a.Id) AS GoodsCount
FROM Tb_Charge_Receipt a
INNER JOIN Tb_System_BusinessCorp b ON a.BussId=b.BussId
WHERE a.UserId=@UserId { condition }
) AS t
WHERE t.RN BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize;";
using (var conn = new SqlConnection(PubConstant.BusinessContionString))
{
var data = conn.Query(sql, new { UserId = userId }).ToList();
if (data.Count > 0)
{
sql = "";
for (int i = 0; i < data.Count; i++)
{
var orderInfo = data[i];
}
}
}
return(null);
}
/// <summary>
/// 搜索房屋
/// </summary>
private string SearchRoom(DataRow row)
{
var bussType = "";
var title = "";
var city = "";
var communityId = "";
var houseType = "";
var amount = "";
var pageIndex = 1;
var pageSize = 10;
#region 查询条件
if (row.Table.Columns.Contains("BussType") && !string.IsNullOrEmpty(row["BussType"].AsString()))
{
bussType = row["BussType"].AsString();
}
if (row.Table.Columns.Contains("Title") && !string.IsNullOrEmpty(row["Title"].AsString()))
{
title = row["Title"].AsString();
}
if (row.Table.Columns.Contains("City") && !string.IsNullOrEmpty(row["City"].AsString()))
{
city = row["City"].AsString();
}
if (row.Table.Columns.Contains("CommunityId") && !string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
communityId = row["CommunityId"].AsString();
}
if (row.Table.Columns.Contains("HouseType") && !string.IsNullOrEmpty(row["HouseType"].AsString()))
{
houseType = row["HouseType"].AsString();
}
if (row.Table.Columns.Contains("Amount") && !string.IsNullOrEmpty(row["Amount"].AsString()))
{
amount = row["Amount"].AsString();
}
if (row.Table.Columns.Contains("PageIndex") && !string.IsNullOrEmpty(row["PageIndex"].AsString()))
{
pageIndex = AppGlobal.StrToInt(row["PageIndex"].AsString());
}
#endregion
using (var conn = new SqlConnection(PubConstant.UnifiedContionString))
{
var condition = "";
var parameter = new DynamicParameters();
#region 查询条件
// 业务类型
if (!string.IsNullOrEmpty(bussType))
{
if (bussType == "出租")
{
condition += " AND BussType in ('整租','合租')";
}
else
{
condition += $" AND BussType=@BussType";
parameter.Add("@BussType", bussType);
}
}
// 标题
if (!string.IsNullOrEmpty(title))
{
condition += $" AND Title LIKE @Title";
parameter.Add("@Title", $"%{ title }%");
}
// 城市
if (!string.IsNullOrEmpty(city))
{
condition += $" AND City=@City";
parameter.Add("@City", city);
}
// 项目
if (!string.IsNullOrEmpty(communityId))
{
condition += $" AND CommunityId=@CommunityId";
parameter.Add("@CommunityId", communityId);
}
// 户型
if (!string.IsNullOrEmpty(houseType))
{
if (houseType.Contains("+"))
{
condition += $" AND BedRooms>@BedRooms";
parameter.Add("@BedRooms", AppGlobal.StrToInt(houseType.Replace("+", "")));
}
else
{
condition += $" AND BedRooms=@BedRooms";
parameter.Add("@BedRooms", AppGlobal.StrToInt(houseType));
}
}
// 金额
if (!string.IsNullOrEmpty(amount))
{
var tmp = amount.Split('-').ToList();
if (tmp.Count() < 2)
{
tmp.Insert(0, "0");
}
condition += $" AND Amount>=@A1 AND Amount<=@A2";
parameter.Add("@A1", AppGlobal.StrToDec(tmp[0]));
parameter.Add("@A2", AppGlobal.StrToDec(tmp[1]));
}
#endregion
String sqlNewAdd = String.Empty;
String sqlTbRental = String.Empty;
String sqlStr = "SELECT isnull(col_length('Tb_Rental','AmountMax'),0)";
if (conn.Query <int>(sqlStr).FirstOrDefault() > 0)
{
sqlNewAdd = "[Description],LinkMan,LinkManTel,Orientation,AmountMax,BuildingAreaMax,HasElevator,CommName,SalesMan,SalesMobile,";
sqlTbRental = "[Description],LinkMan,LinkManTel,Orientation,AmountMax,BuildingAreaMax,HasElevator,CommunityName as CommName,SalesMan,SalesMobile,";
}
var sql = $@"SELECT * FROM
(
SELECT Id,Title,CommunityId,City,BussType,BuildingArea,HouseType,Renovation,Amount,PubDate,Img,Tags,{sqlNewAdd}
row_number() OVER (ORDER BY PUBDATE DESC) AS RID
FROM
(
SELECT Id,Title,BussType,BuildingArea,Renovation,Amount,PubDate,CommunityId,City,{sqlTbRental}
CASE a.BedRooms WHEN 0 THEN '' ELSE substring('123456789',a.BedRooms,1)+'室' END+
CASE a.LivingRooms WHEN 0 THEN '' ELSE substring('123456789',a.LivingRooms,1)+'厅' END+
CASE ISNULL(a.KitchenRooms ,0) WHEN 0 THEN '' ELSE substring('123456789',a.KitchenRooms,1)+'厨' END+
CASE a.BathRooms WHEN 0 THEN '' ELSE substring('123456789',a.BathRooms,1)+'卫' END AS HouseType,
CASE CommunityId WHEN @CommunityId THEN 0 ELSE 1 END AS SN1,
CASE City WHEN @City THEN 0 ELSE 1 END AS SN2,
(SELECT Top 1 Value FROM SplitString(Img,',',1)) AS Img,
CASE WHEN BussType IN ('整租','合租') THEN
STUFF((
SELECT ','+[Name] FROM (
SELECT row_number() over(partition by CATEGORYID order by IID ) as rowIndex,* FROM Tb_Rental_TagSetting
WHERE IID IN
(
SELECT VALUE FROM SplitString(
(SELECT stuff((SELECT ','+Tags FROM (SELECT top 3 * FROM Tb_Rental_RoomTag x WHERE x.RentalID =a.Id AND X.CategoryID IN (2,5)) as R FOR XML PATH('')),1,1,'')),',',1)
)
) AS T ORDER BY Sort DESC FOR XML PATH('')
),1,1,'')
WHEN BussType = '出售' THEN
STUFF((
SELECT ','+[Name] FROM (
SELECT row_number() over(partition by CATEGORYID order by IID ) as rowIndex,* FROM Tb_Rental_TagSetting
WHERE IID IN
(
SELECT VALUE FROM SplitString(
(SELECT stuff((SELECT ','+Tags FROM (SELECT top 3 * FROM Tb_Rental_RoomTag x WHERE x.RentalID =a.Id AND X.CategoryID IN (2,5)) as R FOR XML PATH('')),1,1,'')),',',1)
)
) AS T ORDER BY Sort DESC FOR XML PATH('')
),1,1,'')
else
stuff((SELECT ','+Name FROM Tb_Rental_TagSetting
WHERE IID IN
(
SELECT VALUE FROM SplitString(
(SELECT stuff((SELECT ','+Tags FROM (SELECT top 3 * FROM Tb_Rental_RoomTag x WHERE x.RentalID =a.Id AND X.CategoryID IN (2,5)) as R FOR XML PATH('')),1,1,'')),',',1)
) ORDER BY Sort DESC FOR XML PATH('')
),1,1,'')
end as Tags
FROM Tb_Rental a
WHERE ProcessState='发布' AND IsDelete=0 { condition }
) AS t
) AS t
WHERE t.RID>(@PageIndex-1)*@PageSize AND t.RID<=(@PageIndex*@PageSize);
SELECT count(1) FROM Tb_Rental
WHERE ProcessState='发布' AND IsDelete=0 { condition };";
parameter.Add("@CommunityId", communityId);
parameter.Add("@City", city);
parameter.Add("@PageIndex", pageIndex);
parameter.Add("@PageSize", pageSize);
var reader = conn.QueryMultiple(sql, parameter);
var data = reader.Read();
var total = reader.Read <int>().FirstOrDefault();
var pageCount = (int)Math.Ceiling((double)total / pageSize);
var json = new ApiResult(true, data).toJson();
json = json.Insert(json.Length - 1, $",\"PageCount\":{pageCount}");
return(json);
}
}
/// <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);
}
}
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, "提交失败"));
}
}
/// <summary>
/// 获取物管运营平台商城、物管商城推荐商品列表
/// </summary>
private string GetPropertyStoreRecommendGoodsList(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "小区编码不能为空"));
}
var communityId = row["CommunityId"].AsString();
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查找到小区信息"));
}
var pageSize = 10;
var pageIndex = 1;
if (row.Table.Columns.Contains("PageSize") && !string.IsNullOrEmpty(row["PageSize"].AsString()))
{
pageSize = AppGlobal.StrToInt(row["PageSize"].AsString());
}
if (row.Table.Columns.Contains("PageIndex") && !string.IsNullOrEmpty(row["PageIndex"].AsString()))
{
pageIndex = AppGlobal.StrToInt(row["PageIndex"].AsString());
}
using (var conn = new SqlConnection(PubConstant.BusinessContionString))
{
var sql = $@"SELECT BussID,ResourcesID AS GoodsID,ResourcesName AS GoodsName,ResourcesSimple AS GoodsSimpleName,Img AS GoodsImg,
IsGroupBuy,ResourcesSalePrice AS SalePrice,ResourcesDisCountPrice AS DiscountPrice,
CASE WHEN IsGroupBuy='是' AND GroupBuyStartDate<=getdate() AND GroupBuyEndDate>=getdate()
THEN isnull(GroupBuyPrice,-1.0)
ELSE ResourcesSalePrice-isnull(ResourcesDisCountPrice,0.0) END AS ActualPrice,
CASE WHEN IsBp='是' THEN 1 ELSE 0 END AS IsHot,
CASE WHEN IsRecommend='是' THEN 1 ELSE 0 END AS IsRecommend,RecommendSetDate,
(
SELECT count(1) FROM Tb_Charge_ReceiptDetail x
LEFT JOIN Tb_Charge_Receipt y ON x.ReceiptCode=y.Id
WHERE x.ResourcesID=a.ResourcesID AND isnull(y.IsDelete,0)=0 AND y.IsPay='已付款'
) AS SoldCount
FROM Tb_Resources_Details a
WHERE IsRelease='是' AND isnull(IsStopRelease,'否')='否' AND IsRecommend='是' AND isnull(IsDelete,0)=0
AND BussID IN
(
SELECT BussId FROM Tb_System_BusinessCorp
WHERE isnull(BussNature,'周边商家') IN('平台商城','物管商城') AND isnull(IsDelete,0)=0
AND BussId IN
(
SELECT BussId FROM Tb_System_BusinessConfig WHERE CommunityId='{community.Id}'
UNION ALL
SELECT BussId FROM Tb_System_BusinessCorp_Config WHERE CorpId={community.CorpID}
)
)";
var data = GetListDapper <PMSAppGoodsSimpleModel>(out int pageCount, out int count, sql, pageIndex, pageSize,
"RecommendSetDate", 1, "GoodsID", conn).ToList();
data.ForEach(obj =>
{
obj.GoodsImg = obj.GoodsImg?.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)[0];
});
var json = new ApiResult(true, data).toJson();
return(json.Insert(json.Length - 1, ",\"PageCount\":" + pageCount));
}
}
/// <summary>
/// 变更责任岗位责任人
/// </summary>
private string HandoverTask(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区id不能为空"));
}
if (!row.Table.Columns.Contains("TaskId") || string.IsNullOrEmpty(row["TaskId"].ToString()))
{
return(JSONHelper.FromString(false, "任务id不能为空"));
}
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
var taskId = row["TaskId"].ToString();
var newUser = default(string);
var newRole = default(string);
if (row.Table.Columns.Contains("NewUser") && !string.IsNullOrEmpty(row["NewUser"].ToString()))
{
newUser = row["NewUser"].ToString();
}
if (row.Table.Columns.Contains("NewRole") && !string.IsNullOrEmpty(row["NewRole"].ToString()))
{
newRole = row["NewRole"].ToString();
}
if (string.IsNullOrEmpty(newUser) && string.IsNullOrEmpty(newRole))
{
return(JSONHelper.FromString(false, "责任人和责任岗位不能同时为空"));
}
using (var conn = new SqlConnection(Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_SUPERVISION)))
{
var sql = $@"DECLARE @OldTaskRoleCode nvarchar(max);
DECLARE @OldTaskRoleName nvarchar(max);
DECLARE @OldTaskUserCode nvarchar(max);
DECLARE @OldTaskUserName nvarchar(max);
DECLARE @NewTaskRoleName nvarchar(max);
DECLARE @NewTaskUserName nvarchar(max);
SELECT @OldTaskRoleCode=TaskRoleCode,@OldTaskRoleName=TaskRoleName,
@OldTaskUserCode=TaskUserCode,@OldTaskUserName=TaskUserName
FROM Tb_Supervision_Task WHERE TaskId=@TaskId;
UPDATE Tb_Supervision_Task SET TaskUserCode=@NewUser,TaskRoleCode=@NewRole WHERE TaskId=@TaskId;
SELECT @NewTaskRoleName=STUFF((SELECT ','+RoleName FROM { Global_Var.ERPDatabaseName }.dbo.Tb_Sys_Role
WHERE RoleCode IN(SELECT Value FROM { Global_Var.ERPDatabaseName }.dbo.SplitString(
(SELECT TaskRoleCode FROM Tb_Supervision_Task WHERE TaskId=@TaskId),',',1)) FOR XML PATH('')), 1, 1, '');
SELECT @NewTaskUserName=STUFF((SELECT ','+UserName FROM { Global_Var.ERPDatabaseName }.dbo.Tb_Sys_User
WHERE UserCode IN(SELECT Value FROM { Global_Var.ERPDatabaseName }.dbo.SplitString(
(SELECT TaskUserCode FROM Tb_Supervision_Task WHERE TaskId=@TaskId),',',1)) FOR XML PATH('')), 1, 1, '');
UPDATE Tb_Supervision_Task SET TaskUserName=@NewTaskUserName,TaskRoleName=@NewTaskRoleName WHERE TaskId=@TaskId;
INSERT INTO Tb_Supervision_TaskHandover(IID,TaskId,OldTaskUserCode,OldTaskUserName,OldTaskRoleCode,
OldTaskRoleName,NewTaskUserCode,NewTaskUserName,NewTaskRoleCode,NewTaskRoleName,HandoverTime,OperateUser)
VALUES(newid(),@TaskId,@OldTaskUserCode,@OldTaskUserName,@OldTaskRoleCode,@OldTaskRoleName,
@NewUser,@NewTaskUserName,@NewRole,@NewTaskRoleName,getdate(),@UserCode);";
int i = conn.Execute(sql, new { NewUser = newUser, NewRole = newRole, TaskId = taskId, UserCode = Global_Var.LoginUserCode });
if (i == 3)
{
return(JSONHelper.FromString(true, "变更成功"));
}
return(JSONHelper.FromString(false, "变更失败"));
}
}
/// <summary>
/// 上传离线任务数据
/// </summary>
private string UploadTaskData(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区id不能为空"));
}
JObject data = (JObject)JsonConvert.DeserializeObject(row["Data"].ToString());
if (string.IsNullOrEmpty(data["TaskId"].ToString()))
{
return(JSONHelper.FromString(false, "任务id不能为空"));
}
var taskId = data["TaskId"].ToString();
var taskState = data["TaskState"].ToString(); // 任务状态
var taskDidBeginTime = data["DidBeginTime"].ToString(); // 任务开始执行时间
var taskDidEndTime = data["DidEndTime"].ToString(); // 任务完成时间
var didEndUserCode = data["DidEndUserCode"].ToString(); // 任务完成人Code
var didEndUserName = data["DidEndUserName"].ToString(); // 任务完成人名称
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
var connStr = Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_SUPERVISION);
using (var conn = new SqlConnection(connStr))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
var trans = conn.BeginTransaction();
try
{
var sql = $@"SELECT count(1) FROM Tb_Supervision_Task WITH(NOLOCK)
WHERE TaskId='{ taskId }' AND isnull(IsClose,0)=0;";
if (conn.Query <int>(sql, null, trans).FirstOrDefault() == 0)
{
trans.Rollback();
return(JSONHelper.FromString(true, "该任务已关闭或已删除"));
}
// 更新任务标准
var standardTable = JsonConvert.DeserializeObject <List <TaskStandardScoreModel> >(data["Standards"].ToString());
UpdateSupervisionTaskStandard(standardTable, commId, taskId, conn, trans);
// 执行事务
trans.Commit();
trans = conn.BeginTransaction();
// 更新任务状态
UpdateSupervisionTask(commId, taskId, taskState, taskDidBeginTime, taskDidEndTime, didEndUserCode, didEndUserName, conn, trans);
trans.Commit();
return(JSONHelper.FromString(true, "保存成功"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message));
}
}
}
/// <summary>
/// 生成商家订单
/// </summary>
public string GenerateBusinessOrder(DataSet Ds, string communityId, string BussId, string UserId, string txnTime, ref bool IsOk, ref int Amount, ref int CouponAmount, ref string OrderId, string Name, string Mobile, string DeliverAddress, WxPayConfig wxPayConfig)
{
DataRow Row = Ds.Tables[0].Rows[0];
int?corpId = null;
if (Row.Table.Columns.Contains("CorpId") && !string.IsNullOrEmpty(Row["CorpId"].ToString()))
{
corpId = AppGlobal.StrToInt(Row["CorpId"].ToString());
}
var iid = Guid.NewGuid().ToString();
// 生成商家收款订单
Tb_Charge_Receipt Receipt = new Tb_Charge_Receipt
{
Id = iid,
CorpId = corpId,
BussId = BussId.ToString(),
UserId = UserId,
OrderId = Guid.NewGuid().ToString().Replace("-", ""),
Name = Name,
IsPay = "未付款",
IsReceive = "未收货",
IsDeliver = "未发货",
ReceiptMemo = Row["ReceiptMemo"].ToString(),
ReceiptType = "通用票据",
ReceiptDate = DateTime.Now,
PayDate = null,
MchId = wxPayConfig.MCHID,
Partner = wxPayConfig.MCHID,
PrepayStr = "",
txnTime = txnTime.ToString(),
ReturnCode = "",
ReturnMsg = "",
Express = "",
ExpressNum = "",
Mobile = Mobile, //联系电话
DeliverAddress = DeliverAddress, //收货地址
IsDelete = 0
};
// 是否使用优惠券抵扣
if (Row.Table.Columns.Contains("UseCoupon"))
{
Receipt.IsUseCoupon = AppGlobal.StrToInt(Row["UseCoupon"].ToString());
}
using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection()))
{
// 生成订单票据号
DynamicParameters dp = new DynamicParameters();
dp.Add("@Id", BussId);
dp.Add("@OrderLength", 32);
dp.Add("@Num", "", DbType.String, ParameterDirection.Output);
conn.Execute("Proc_GetOrderNum", dp, null, null, CommandType.StoredProcedure);
Receipt.ReceiptSign = dp.Get <string>("@Num");
// 保存订单信息
conn.Insert(Receipt);
if (!string.IsNullOrEmpty(communityId))
{
conn.Execute("UPDATE Tb_Charge_Receipt SET CommunityId=@CommunityId WHERE ID=@IID",
new { CommunityId = communityId, IID = iid });
}
/*
* 计算此时的订单金额，此时不应该保存商品的价格信息；
* 如果用户不付款，且订单未取消期间，商家更改了商品单价或可优惠价或规格追加价，付款时则需要重新计算
*
* 1、获取用户在该商家的可用优惠券余额
* 2、获取商品此时的单价和可优惠价，对应的规格型号的追加单价
* 3、计算当前订单内的商品是否支持优惠券抵扣，如果支持，计算最多能抵扣的价格并更新优惠券余额
*/
// 1、获取用户优惠券余额
dynamic balanceInfo = conn.Query <dynamic>(@"SELECT isnull(Balance1,0) AS Balance1,isnull(Balance2,0) AS Balance2
FROM
(SELECT Balance1=(SELECT Balance FROM Tb_Resources_CouponBalance
WHERE UserId=@UserId
AND CorpId=@CorpId AND BussId=@BussId),
Balance2=(SELECT Balance FROM Tb_Resources_CouponBalance
WHERE UserId=@UserId
AND CorpId=@CorpId AND BussId IS NULL)) as x;",
new { UserId = Receipt.UserId, CorpId = corpId, BussId = Receipt.BussId }).FirstOrDefault();
decimal totalAmount = 0.0m; // 商品总价
decimal userCouponBalance = 0.0m; // 用户可用优惠券余额
decimal totalUseCouponMoney = 0.0m; // 当前能使用优惠券抵扣的钱
decimal bussBalance = 0.0m; // 商家专用券余额
decimal corpBalance = 0.0m; // 物管通用券余额
if (balanceInfo != null)
{
bussBalance = balanceInfo.Balance1;
corpBalance = balanceInfo.Balance2;
userCouponBalance = bussBalance + corpBalance;
}
// 2、获取商品价格信息
foreach (DataRow DetailRow in Ds.Tables["Product"].Rows)
{
// 订单商品详情
Tb_Charge_ReceiptDetail ReceiptDetail = new Tb_Charge_ReceiptDetail();
ReceiptDetail.RpdCode = Guid.NewGuid().ToString();
ReceiptDetail.ReceiptCode = Receipt.Id;
ReceiptDetail.ShoppingId = DetailRow["ShoppingId"].ToString();
ReceiptDetail.ResourcesID = DetailRow["Id"].ToString();
ReceiptDetail.RpdMemo = DetailRow["RpdMemo"].ToString();
ReceiptDetail.Quantity = DataSecurity.StrToInt(DetailRow["Quantity"].ToString());
ReceiptDetail.OffsetMoney = 0.0m;
ReceiptDetail.OffsetMoney2 = 0.0m;
// 商品的单价和可优惠单价
string sql = @"SELECT * FROM Tb_Resources_Details
WHERE isnull(IsDelete,0)=0 AND IsRelease='是' AND IsStopRelease='否' AND ResourcesID=@ResourcesID";
Tb_Resources_Details ResourcesDetail = conn.Query <Tb_Resources_Details>(sql, new { ResourcesID = ReceiptDetail.ResourcesID }).FirstOrDefault();
// 商品存在，期间未失效
if (ResourcesDetail != null)
{
// 库存不足，跳过
if (ReceiptDetail.Quantity > ResourcesDetail.ResourcesCount)
{
continue;
}
ReceiptDetail.SalesPrice = ResourcesDetail.ResourcesSalePrice;
ReceiptDetail.DiscountPrice = ResourcesDetail.ResourcesDisCountPrice;
bool IsInGroupBuyTime = false;
if (ResourcesDetail.IsGroupBuy == "是")
{
if (ResourcesDetail.GroupBuyStartDate.HasValue && ResourcesDetail.GroupBuyStartDate.Value <= DateTime.Now &&
ResourcesDetail.GroupBuyEndDate.HasValue && ResourcesDetail.GroupBuyEndDate.Value >= DateTime.Now)
{
IsInGroupBuyTime = true;
ReceiptDetail.GroupPrice = ResourcesDetail.GroupBuyPrice;
}
}
// 1、商品单价
decimal price = 0.0m;
if (IsInGroupBuyTime)
{
price = (ResourcesDetail.GroupBuyPrice.Value - ResourcesDetail.ResourcesDisCountPrice) * ReceiptDetail.Quantity;
}
else
{
price = (ResourcesDetail.ResourcesSalePrice - ResourcesDetail.ResourcesDisCountPrice) * ReceiptDetail.Quantity;
}
// 2、商品规格追加价格
sql = "SELECT * FROM View_Tb_ResourcesSpecificationsPrice WHERE ShoppingId=@ShoppingId";
MobileSoft.Model.Resources.Tb_ResourcesSpecificationsPrice propertyInfo = conn.Query <MobileSoft.Model.Resources.Tb_ResourcesSpecificationsPrice>(sql, new { ShoppingId = DetailRow["ShoppingId"].ToString() }).FirstOrDefault();
if (propertyInfo != null)
{
price = price + propertyInfo.Price.Value * ReceiptDetail.Quantity;
}
// 当前商品需要抵扣的金额
decimal currCouponMoney = 0.0m;
// 3、商品支持抵扣券 并且 还有用户还有足够的优惠券余额
if (ResourcesDetail.IsSupportCoupon == "是" &&
Receipt.IsUseCoupon.HasValue &&
Receipt.IsUseCoupon.Value > 0 &&
userCouponBalance > 0)
{
// 计算当前商品需要抵扣的金额
currCouponMoney = (ResourcesDetail.MaximumCouponMoney.HasValue ? ResourcesDetail.MaximumCouponMoney.Value : 0) * ReceiptDetail.Quantity;
if (bussBalance > 0)
{
if (bussBalance >= currCouponMoney)
{
// 优惠券使用明细
ReceiptDetail.OffsetMoney = currCouponMoney;
ReceiptDetail.OffsetMoney2 = 0.0m;
totalUseCouponMoney += currCouponMoney;
bussBalance -= currCouponMoney;
userCouponBalance -= currCouponMoney;
}
else
{
// 先用专用券抵扣
ReceiptDetail.OffsetMoney = bussBalance;
totalUseCouponMoney += bussBalance;
userCouponBalance -= bussBalance;
// 专用券不够差额
decimal differ = currCouponMoney - bussBalance;
// 专用券不够，通用券余额大于0
if (corpBalance > 0)
{
// 通用券足够抵扣
if (corpBalance >= differ)
{
ReceiptDetail.OffsetMoney2 = differ;
totalUseCouponMoney += differ;
corpBalance -= differ;
userCouponBalance -= differ;
}
// 通用券不足以抵扣
else
{
ReceiptDetail.OffsetMoney2 = corpBalance;
totalUseCouponMoney += corpBalance;
userCouponBalance -= corpBalance;
corpBalance = 0;
}
}
}
}
else
{
// 没有专用券，但是有通用券
if (corpBalance > 0)
{
// 通用券足够抵扣
if (corpBalance > currCouponMoney)
{
ReceiptDetail.OffsetMoney2 = currCouponMoney;
totalUseCouponMoney += currCouponMoney;
corpBalance -= currCouponMoney;
userCouponBalance -= currCouponMoney;
}
// 通用券不足以抵扣
else
{
ReceiptDetail.OffsetMoney2 = corpBalance;
totalUseCouponMoney += corpBalance;
userCouponBalance -= corpBalance;
corpBalance = 0;
}
}
}
}
// 订单商品总价追加
totalAmount += price;
// 插入订单内商品数据
conn.Insert(ReceiptDetail);
// 删除购物车规格明细
conn.Execute("DELETE FROM Tb_ShoppingDetailed WHERE ShoppingId=@ShoppingId", new { ShoppingId = ReceiptDetail.ShoppingId });
// 删除购物车
conn.Execute("DELETE FROM Tb_ShoppingCar WHERE Id=@ShoppingId", new { ShoppingId = ReceiptDetail.ShoppingId });
}
else
{
// 商品已经失效的处理，直接从购物车删除
// 删除购物车规格明细
conn.Execute("DELETE FROM Tb_ShoppingDetailed WHERE ShoppingId=@ShoppingId", new { ShoppingId = ReceiptDetail.ShoppingId });
// 删除购物车
conn.Execute("DELETE FROM Tb_ShoppingCar WHERE Id=@ShoppingId", new { ShoppingId = ReceiptDetail.ShoppingId });
}
}
// 3、更新用户优惠券信息
if (totalUseCouponMoney > 0)
{
conn.Execute("proc_Resources_CouponBalance_Use",
new { UserId = Receipt.UserId, CorpId = corpId, BussId = Receipt.BussId, UseMoney = totalUseCouponMoney },
null, null, CommandType.StoredProcedure);
}
// ref参数处理
IsOk = true;
Amount = (int)((totalAmount - totalUseCouponMoney) * 100);
CouponAmount = (int)(totalUseCouponMoney * 100);
OrderId = Receipt.OrderId;
}
return("生成订单成功");
}
/// <summary>
/// 邀请用户
/// </summary>
private string Invite(DataRow Row)
{
if (!Row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(Row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!Row.Table.Columns.Contains("UserMobile") || string.IsNullOrEmpty(Row["UserMobile"].ToString()))
{
return(JSONHelper.FromString(false, "缺少UserMobile参数"));
}
if (!Row.Table.Columns.Contains("RelationId") || string.IsNullOrEmpty(Row["RelationId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少RelationId参数"));
}
if (!Row.Table.Columns.Contains("InviteMobile") || string.IsNullOrEmpty(Row["InviteMobile"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteMobile参数"));
}
if (!Row.Table.Columns.Contains("InviteName") || string.IsNullOrEmpty(Row["InviteName"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteName参数"));
}
if (!Row.Table.Columns.Contains("InviteType") || string.IsNullOrEmpty(Row["InviteType"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteType参数"));
}
string communityId = Row["CommunityId"].AsString();
string userMobile = Row["UserMobile"].ToString();
string relationId = Row["RelationId"].ToString();
string inviteMobile = Row["InviteMobile"].ToString();
string inviteName = Row["InviteName"].ToString();
int inviteType = AppGlobal.StrToInt(Row["InviteType"].ToString());
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string strcon = new Business.CostInfo().GetConnectionStringStr(Community);
string sql, message, inviteUserId, newRelation;
using (IDbConnection conn = new SqlConnection(PubConstant.UnifiedContionString))
{
// 获取App信息
sql = @"SELECT * FROM Tb_AppInfo WHERE Type=2 AND CorpID=@CorpID";
dynamic appInfo = conn.Query(sql, new { CorpID = Community.CorpID }).FirstOrDefault();
string appName = (appInfo?.AppName) ?? "";
string downloadUrl = (appInfo?.DownloadUrl) ?? "";
sql = @"SELECT CommName FROM Tb_Community WHERE Id=@Id";
string commName = conn.Query <string>(sql, new { Id = communityId }).FirstOrDefault();
sql = @"SELECT CustId,RoomId,RoomSign,CustName FROM Tb_User_Relation WHERE Id=@RelationId";
dynamic relationInfo = conn.Query(sql, new { RelationId = relationId }).FirstOrDefault();
string custId = relationInfo.CustId;
string roomId = relationInfo.RoomId;
string roomSign = relationInfo.RoomSign;
string custName = relationInfo.CustName;
// 1、查询被邀者的手机号是否已经注册
sql = "SELECT top 1 Id FROM Tb_User WHERE Mobile=@Mobile";
IEnumerable <string> resultSet = conn.Query <string>(sql, new { Mobile = inviteMobile });
if (resultSet.Count() > 0)
{
inviteUserId = resultSet.First();
// 1.2、查询被邀者是否已经绑定该房屋
sql = @"SELECT RoomSign FROM Tb_User_Relation WHERE UserId=@UserId AND RoomId IN
(SELECT RoomId FROM Tb_User_Relation WHERE Id=@Relation)";
if (conn.Query <string>(sql, new { UserId = inviteUserId, Relation = relationId }).Count() > 0)
{
conn.Execute(@"UPDATE Tb_User_Relation SET Locked=0 WHERE UserId=@UserId AND RoomId IN
(SELECT RoomId FROM Tb_User_Relation WHERE Id=@Relation)",
new { UserId = inviteUserId, Relation = relationId });
return(JSONHelper.FromString(false, "该用户已经绑定该房屋"));
}
else
{
// 1.3、直接给被邀者绑定该房屋
newRelation = Guid.NewGuid().ToString();
sql = @"INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,RoomId,RegDate,CustName,RoomSign,CustMobile,Locked,CustHoldId)
SELECT @NewRelation AS Id,@Id AS UserId,CommunityId,CustId,roomid,getdate(),CustName,RoomSign,CustMobile,
0 AS Locked,@InviteType AS CustHoldId
FROM Tb_User_Relation WHERE Id=@RelationId";
conn.Execute(sql, new { Id = inviteUserId, NewRelation = newRelation, RelationId = relationId, InviteType = inviteType });
message = string.Format("{0}温馨提示：\"{1}\"业主{2}为您绑定了编号为【{3}】房屋，您可以对该房屋进行报事和缴费等操作。",
appName, commName, custName, roomSign);
SendMessage(Community.CorpID, inviteMobile, message);
}
}
else
{
// 2、被邀者没有注册账号
inviteUserId = Guid.NewGuid().ToString();
newRelation = Guid.NewGuid().ToString();
conn.Execute(@"INSERT INTO Tb_User(Id,Name,Mobile,NickName,Pwd,Sex,RegDate) VALUES(@UserId,@Name,@Mobile,@Mobile,'123456',1,getdate());
INSERT INTO Tb_User_Relation(Id,UserId,CommunityId,CustId,RoomId,RegDate,CustName,RoomSign,CustMobile,Locked)
SELECT @NewRelation AS Id, @UserId AS UserId,CommunityId,CustId,roomid,getdate(),CustName,RoomSign,CustMobile,
0 AS Locked FROM Tb_User_Relation WHERE Id=@RelationId",
new { UserId = inviteUserId, Name = inviteName, Mobile = inviteMobile, NewRelation = newRelation, RelationId = relationId, InviteType = inviteType });
if (string.IsNullOrEmpty(downloadUrl))
{
message = string.Format("{0}温馨提示：\"{1}\"业主{2}邀请您使用{0}，您的账号为：{3}，密码为：123456，请及时下载登录{0}并修改密码，下载地址：{4}", appName, commName, custName, inviteMobile, downloadUrl);
}
else
{
message = string.Format("{0}温馨提示：\"{1}\"业主{2}邀请您使用{0}，您的账号为：{3}，密码为：123456，请及时下载登录{0}并修改密码。", appName, commName, custName, inviteMobile);
}
SendMessage(Community.CorpID, inviteMobile, message);
}
using (IDbConnection conn2 = new SqlConnection(strcon))
{
// ERP添加家属信息
if (inviteType == 1)
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@HoldID", 0, DbType.Int64, ParameterDirection.InputOutput);
parameters.Add("@CommID", Community.CommID);
parameters.Add("@CustID", custId);
parameters.Add("@RoomID", roomId);
parameters.Add("@Surname", inviteName);
parameters.Add("@Name", inviteName);
parameters.Add("@MobilePhone", inviteMobile);
parameters.Add("@MemberName", inviteName);
parameters.Add("@Linkman", inviteName);
parameters.Add("@LinkManTel", inviteMobile);
parameters.Add("@Relationship", "0032");
conn2.Execute("Proc_HSPR_Household_Insert", parameters, null, null, CommandType.StoredProcedure);
long custHoldId = parameters.Get <long>("@HoldID");
conn.Execute(@"UPDATE Tb_User_Relation SET CustHoldId=@HoldID WHERE Id=@RelationId",
new { HoldID = custHoldId, RelationId = newRelation });
}
// 添加租户信息
else
{
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@TenantName", inviteName);
parameters.Add("@TenantMobile", inviteMobile);
parameters.Add("@CommID", Community.CommID);
parameters.Add("@RoomID", roomId);
parameters.Add("@CustID", 0, DbType.Int64, ParameterDirection.Output);
conn2.Execute("Proc_HSPR_Customer_AddTenant", parameters, null, null, CommandType.StoredProcedure);
conn.Execute("UPDATE Tb_User_Relation SET CustID=@CustID WHERE Id=@RelationId",
new { CustID = parameters.Get <Int64>("@CustID"), RelationId = newRelation });
}
}
if (resultSet.Count() > 0)
{
return(JSONHelper.FromString(false, "该手机号已注册，已直接绑定房号"));
}
else
{
return(JSONHelper.FromString(true, "邀请成功"));
}
}
}
/// <summary>
/// 登录
/// </summary>
public bool Login(ref Transfer Trans)
{
var table = XmlToDatatTable(Trans.Attribute);
var row = table.Rows[0];
if (!row.Table.Columns.Contains("Net") || string.IsNullOrEmpty(row["Net"].ToString()))
{
Trans.Result = new ApiResult(false, "请选择登录服务器").toJson();
}
if (!row.Table.Columns.Contains("Account") || string.IsNullOrEmpty(row["Account"].ToString()))
{
Trans.Result = new ApiResult(false, "账号错误").toJson();
}
if (!row.Table.Columns.Contains("LoginPwd") || string.IsNullOrEmpty(row["LoginPwd"].ToString()))
{
Trans.Result = new ApiResult(false, "密码错误").toJson();
}
var server = AppGlobal.StrToInt(row["Net"].ToString());
var account = row["Account"].ToString();
var password = row["LoginPwd"].ToString();
var accountSplited = account.Split(new[] { '-' }, StringSplitOptions.RemoveEmptyEntries);
if (accountSplited.Length <= 1)
{
Trans.Result = new ApiResult(false, "账号格式错误").toJson();
}
account = account.Remove(0, accountSplited[0].Length + 1);
// 企业id
var corpId = AppGlobal.StrToInt(accountSplited[0]);
if (corpId == 0)
{
Trans.Result = new ApiResult(false, "账号格式错误").toJson();
}
var commId = 0;
if (row.Table.Columns.Contains("CommID") && !string.IsNullOrEmpty(row["CommID"].ToString()))
{
commId = AppGlobal.StrToInt(row["CommID"].ToString());
}
// 内网环境不验证密码，用于调试方便
var verifyPassword = true;
if (HttpContext.Current.Request.Url.Host.ToLowerInvariant() == "localhost")
{
verifyPassword = false;
}
if (verifyPassword == false && row.Table.Columns.Contains("VerifyPassword") && !string.IsNullOrEmpty(row["VerifyPassword"].ToString()))
{
verifyPassword = AppGlobal.StrToInt(row["VerifyPassword"].ToString()) > 0;
}
// 手机号登录
var mobileLogin = false;
if (row.Table.Columns.Contains("MobileLogin") && !string.IsNullOrEmpty(row["MobileLogin"].ToString()))
{
mobileLogin = AppGlobal.StrToInt(row["MobileLogin"].ToString()) > 0;
}
Trans.Result = TWERPAccountLogin(server, corpId, commId, account, password, out bool loginResult, verifyPassword, mobileLogin);
return(loginResult);
}
/// <summary>
/// 生成商家订单
/// </summary>
/// <param name="Ds"></param>
/// <param name="BussId"></param>
/// <param name="UserId"></param>
/// <param name="txnTime"></param>
/// <param name="IsOk"></param>
/// <param name="Amount"></param>
/// <param name="OrderId"></param>
/// <returns></returns>
public string GenerateBusinessOrder(DataSet Ds, string communityId, string BussId, string UserId, string txnTime, ref bool IsOk, ref string Amount, ref string OrderId, WxPayConfig wxPayConfig)
{
DataRow Row = Ds.Tables[0].Rows[0];
IDbConnection Conn = new SqlConnection(ConnectionDb.GetBusinessConnection());
string BusinessOrderId = Guid.NewGuid().ToString();
//生成商家收款订单
string Id = Guid.NewGuid().ToString();
Tb_Charge_Receipt EntityReceipt = new Tb_Charge_Receipt();
EntityReceipt.Id = Id;
EntityReceipt.BussId = BussId.ToString();
EntityReceipt.OrderId = BusinessOrderId;
// 是否使用抵扣券
if (Row.Table.Columns.Contains("UseCoupon"))
{
EntityReceipt.IsUseCoupon = AppGlobal.StrToInt(Row["UseCoupon"].ToString());
}
OrderId = BusinessOrderId;
string ReceiptSign = "";//获得订单号
//获取票据号
DynamicParameters dp = new DynamicParameters();
dp.Add("@Id", BussId);
dp.Add("@OrderLength", 32);
dp.Add("@Num", "", DbType.String, ParameterDirection.Output);
using (IDbConnection IDbConn = new SqlConnection(ConnectionDb.GetBusinessConnection()))
{
IDbConn.Execute("Proc_GetOrderNum", dp, null, null, CommandType.StoredProcedure);
ReceiptSign = dp.Get <string>("@Num");
}
EntityReceipt.ReceiptSign = ReceiptSign.ToString();
EntityReceipt.UserId = UserId.ToString();
IDbConnection ConnUnified = new SqlConnection(ConnectionDb.GetUnifiedConnectionString());
string QueryUser = "******";
Tb_User EntityUser = ConnUnified.Query <Tb_User>(QueryUser, new { UserId = UserId }).SingleOrDefault();
//查找用户名称
EntityReceipt.Name = EntityUser.Name.ToString();
EntityReceipt.Mobile = EntityUser.Mobile.ToString();
EntityReceipt.IsPay = "未付款";
EntityReceipt.IsReceive = "未收货";
EntityReceipt.Amount = 0;
EntityReceipt.ReceiptMemo = Row["ReceiptMemo"].ToString();
EntityReceipt.ReceiptType = "通用票据";
EntityReceipt.ReceiptDate = DateTime.Now;
EntityReceipt.MchId = wxPayConfig.MCHID;
EntityReceipt.Partner = wxPayConfig.MCHID;
EntityReceipt.PrepayStr = "";
EntityReceipt.txnTime = txnTime.ToString();
EntityReceipt.ReturnCode = "";
EntityReceipt.ReturnMsg = "";
EntityReceipt.IsDeliver = "未发货";
EntityReceipt.Express = "";
EntityReceipt.ExpressNum = "";
EntityReceipt.DeliverAddress = "";
EntityReceipt.PayDate = DateTime.Now;
EntityReceipt.IsDelete = 0;
Conn.Insert(EntityReceipt);
if (!string.IsNullOrEmpty(communityId))
{
Conn.Execute("UPDATE Tb_Charge_Receipt SET CommunityId=@CommunityId WHERE ID=@IID",
new { CommunityId = communityId, IID = EntityReceipt.Id });
}
decimal TotalAmount = 0.00M;
//收成商家收款明细
foreach (DataRow DetailRow in Ds.Tables[1].Rows)
{
Tb_Charge_ReceiptDetail EntityReceiptDetail = new Tb_Charge_ReceiptDetail();
EntityReceiptDetail.RpdCode = Guid.NewGuid().ToString();
EntityReceiptDetail.ReceiptCode = Id;
EntityReceiptDetail.ResourcesID = DetailRow["Id"].ToString();
EntityReceiptDetail.Quantity = DataSecurity.StrToInt(DetailRow["Quantity"].ToString());
string QueryResourcesSql = "SELECT * FROM Tb_Resources_Details WHERE ResourcesID=@ResourcesID";
Tb_Resources_Details T = Conn.Query <Tb_Resources_Details>(QueryResourcesSql, new { ResourcesID = DetailRow["Id"].ToString() }).SingleOrDefault();
EntityReceiptDetail.SalesPrice = T.ResourcesSalePrice;
EntityReceiptDetail.DiscountPrice = T.ResourcesDisCountPrice;
EntityReceiptDetail.GroupPrice = T.GroupBuyPrice;
EntityReceiptDetail.DetailAmount = T.ResourcesSalePrice - T.ResourcesDisCountPrice;
EntityReceiptDetail.RpdMemo = DetailRow["RpdMemo"].ToString();
Conn.Insert(EntityReceiptDetail);
//计算订单的总金额
TotalAmount = TotalAmount + DataSecurity.StrToDecimal(EntityReceiptDetail.DetailAmount.ToString());
}
//更新订单总金额
EntityReceipt.Amount = TotalAmount;
Amount = Convert.ToString(TotalAmount * 100);
IsOk = true;
Conn.Update(EntityReceipt);
return("生成订单成功");
}
/// <summary>
/// 生成订单 SetOrder
/// </summary>
/// <param name="row"></param>
/// ShoppingDetailed 购物车明细编号【必填】
/// UserAddressId 收货地址编号【必填】
/// UserId 用户编号【必填】
/// 返回：
/// true:""
/// false:errmessage
/// <returns></returns>
private string SetOrder(DataRow row)
{
if (!row.Table.Columns.Contains("ShoppingDetailed") || string.IsNullOrEmpty(row["ShoppingDetailed"].ToString()))
{
return(JSONHelper.FromString(false, "购物车明细编号不能为空"));
}
if (!row.Table.Columns.Contains("UserAddressId") || string.IsNullOrEmpty(row["UserAddressId"].ToString()))
{
return(JSONHelper.FromString(false, "收货地址编号不能为空"));
}
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户编号不能为空"));
}
IDbConnection con = new SqlConnection(PubConstant.GetConnectionString("BusinessContionString"));
string str = "";
try
{
//获取收货地址
Tb_User_Address UserAddress = BussinessCommon.GetAddressModel(row["UserAddressId"].ToString());
//获取购物明细
DataTable Dt_ShoppingDetailed = BussinessCommon.GetShoppingDetailedView(" Id in (" + BussinessCommon.GetShoppingDetailedIdS(row["ShoppingDetailed"].ToString()) + ")");
if (Dt_ShoppingDetailed == null || Dt_ShoppingDetailed.Rows.Count <= 0)
{
return(JSONHelper.FromString(false, "生成订单失败：未找到购物明细"));
}
Tb_Charge_Receipt Order = new Tb_Charge_Receipt();
//根据商家ID分组
var query = from t in Dt_ShoppingDetailed.AsEnumerable()
group t by new { t1 = t.Field <string>("BussId") } into m
select new
{
BussId = m.Key.t1
};
if (query.ToList().Count > 0)
{
query.ToList().ForEach(q =>
{
//生成订单
Order = new Tb_Charge_Receipt();
Order.Id = Guid.NewGuid().ToString();
Order.BussId = q.BussId;
Order.OrderId = Guid.NewGuid().ToString();
Order.ReceiptSign = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999).ToString();
Order.UserId = row["UserId"].ToString();
if (UserAddress != null)
{
if (UserAddress.Address != "" && UserAddress.Address.Split(',').Length > 0)
{
Order.Name = UserAddress.Address.Split(',')[0];
Order.DeliverAddress = UserAddress.Address.Split(',')[1];
}
Order.Mobile = UserAddress.Mobile;
}
else
{
throw new Exception("该收货地址不存在");
}
Order.ReceiptDate = DateTime.Now;
Order.IsReceive = "未收货";
Order.IsDeliver = "未发货";
Order.IsPay = "未付款";
Order.IsDelete = 0;
//查询此商家的所有购物车明细
DataRow[] dr = Dt_ShoppingDetailed.Select("BussId=" + q.BussId);
//生成订单明细
foreach (DataRow item in dr)
{
Tb_Charge_ReceiptDetail OrderDetail = new Tb_Charge_ReceiptDetail();
OrderDetail.RpdCode = Guid.NewGuid().ToString();
OrderDetail.ReceiptCode = Order.Id;
OrderDetail.ResourcesID = item["ResourcesID"].ToString();
OrderDetail.Quantity = AppGlobal.StrToInt(item["Number"].ToString());
OrderDetail.SalesPrice = AppGlobal.StrToDec(item["ResourcesSalePrice"].ToString());
OrderDetail.DiscountPrice = AppGlobal.StrToDec(item["ResourcesDisCountPrice"].ToString());
OrderDetail.GroupPrice = AppGlobal.StrToDec(item["GroupBuyPrice"].ToString());
OrderDetail.DetailAmount = AppGlobal.StrToDec(item["SubtotalMoney"].ToString());;
OrderDetail.RpdMemo = "";
OrderDetail.RpdIsDelete = 0;
//累计订单金额【取销售价格】
Order.Amount += AppGlobal.StrToDec(OrderDetail.SalesPrice.ToString()) * AppGlobal.StrToDec(OrderDetail.Quantity.ToString());
//生成订单明细
con.Insert <Tb_Charge_ReceiptDetail>(OrderDetail);
}
//生成订单
con.Insert <Tb_Charge_Receipt>(Order);
//判断此商家中是否存在该客户
string sqlStr = "select BussId from Tb_Customer_List where BussId='" + q.BussId + "' and UserId='" + row["UserId"] + "' and Mobile='" + UserAddress.Mobile + "'";
List <string> list = con.Query <string>(sqlStr, null, null, true, null, CommandType.Text).ToList <string>();
//如果不存在，将此客户添加至此商家的客户资料中
if (list.Count <= 0)
{
Tb_Customer_List cust = new Tb_Customer_List();
cust.Id = Guid.NewGuid().ToString();
cust.BussId = q.BussId;
cust.UserId = row["UserId"].ToString();
cust.Mobile = UserAddress.Mobile;
cust.CustName = "";
con.Insert <Tb_Customer_List>(cust);
}
});
}
}
catch (Exception ex)
{
str = ex.Message;
}
if (str != "")
{
return(JSONHelper.FromString(false, str));
}
else
{
return(JSONHelper.FromString(true, ""));
}
}
/// <summary>
/// 查询订单 GetORdersAll
/// </summary>
/// <param name="row"></param>
/// UserId 用户编号【必填】
/// PageIndex
/// PageSize
/// IsDeliver 是否发货
/// IsPay 付款状态
/// HandleState 处理状态
/// IsReceive 是否收货
/// 返回：
/// {Result:true,data:[BussName 商家名称,Id 订单ID,ReceiptSign 订单编号,Amount 总金额,HandleState 订单状态,IsReceive 是否收货,IsPay 付款状态,IsDeliver 是否发货,OrderDetailNumber 明细数量,Details:[RpdCode 明细id,Quantity 数量,SalesPrice 销售价格,DetailAmount 小计,ResourcesName 商品名称,Img 商品图片],[]],[],[]}
/// 备注：当IsReceive 值为'已发货'时，订单字段中多二个物流信息：Express 物流名称，ExpressNum 物流编号
/// <returns></returns>
private string GetORdersAll(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户编号不能为空"));
}
int pageCount;
int Counts;
int PageIndex = 1;
int PageSize = 5;
#pragma warning disable CS0219 // 变量“IsDeliver”已被赋值，但从未使用过它的值
string IsDeliver = ""; //是否发货
#pragma warning restore CS0219 // 变量“IsDeliver”已被赋值，但从未使用过它的值
#pragma warning disable CS0219 // 变量“IsPay”已被赋值，但从未使用过它的值
string IsPay = ""; //付款状态
#pragma warning restore CS0219 // 变量“IsPay”已被赋值，但从未使用过它的值
#pragma warning disable CS0219 // 变量“HandleState”已被赋值，但从未使用过它的值
string HandleState = ""; //处理状态
#pragma warning restore CS0219 // 变量“HandleState”已被赋值，但从未使用过它的值
#pragma warning disable CS0219 // 变量“IsReceive”已被赋值，但从未使用过它的值
string IsReceive = ""; //是否收货
#pragma warning restore CS0219 // 变量“IsReceive”已被赋值，但从未使用过它的值
if (row.Table.Columns.Contains("PageIndex") && AppGlobal.StrToInt(row["PageIndex"].ToString()) > 0)
{
PageIndex = AppGlobal.StrToInt(row["PageIndex"].ToString());
}
if (row.Table.Columns.Contains("PageSize") && AppGlobal.StrToInt(row["PageSize"].ToString()) > 0)
{
PageSize = AppGlobal.StrToInt(row["PageSize"].ToString());
}
var FldName = @"BussId,BussName,Id,ReceiptSign,Amount,RealAmount,IsUseCoupon,HandleState,
IsReceive,IsPay,IsDeliver,OrderDetailNumber,OrderId,
IsEevaluate,ReceiptDate,Express,ExpressNum";
StringBuilder sb = new StringBuilder();
try
{
var condition = $" AND r.UserId='{ row["UserId"].ToString() }'";
// 支付状态
if (row.Table.Columns.Contains("IsPay") && row["IsPay"].ToString() != "")
{
condition += $" AND r.IsPay='{ row["IsPay"].ToString() }'";
}
else
{
condition += $" AND isnull(r.IsPay,'未付款')='未付款'";
}
// 发货状态
if (row.Table.Columns.Contains("IsDeliver") && row["IsDeliver"].ToString() != "")
{
condition += $" AND r.IsDeliver='{ row["IsDeliver"].ToString() }'";
}
else
{
condition += $" AND isnull(r.IsDeliver,'未发货')='未发货'";
}
// 收货状态
if (row.Table.Columns.Contains("IsReceive") && row["IsReceive"].ToString() != "")
{
condition += $" AND r.IsReceive='{ row["IsReceive"].ToString() }'";
}
else
{
condition += $" AND isnull(r.IsReceive,'未收货')='未收货'";
}
// 操作确认
if (row.Table.Columns.Contains("HandleState") && row["HandleState"].ToString() != "")
{
condition += $" AND r.HandleState='{ row["HandleState"].ToString() }'";
}
//查询订单
var sql = @"SELECT b.BussName,a.*,
OrderDetailNumber=
(
SELECT COUNT(1) FROM Tb_Charge_ReceiptDetail x
LEFT JOIN Tb_Resources_Details y on x.ResourcesID=y.ResourcesID
WHERE x.ReceiptCode=a.Id and isnull(x.RpdIsDelete,0)=0
AND y.IsRelease='是' AND y.IsStopRelease='否' AND isnull(y.IsDelete,0)=0
),
IsEevaluate=
(
SELECT count(1) FROM Tb_Resources_CommodityEvaluation e
LEFT JOIN Tb_Charge_ReceiptDetail t ON t.RpdCode=e.RpdCode
WHERE t.ReceiptCode=a.Id and isnull(t.RpdIsDelete,0)=0 and isnull(e.IsDelete,0)=0
)
FROM Tb_Charge_Receipt a
LEFT JOIN Tb_System_BusinessCorp b on a.BussId=b.BussId
WHERE isnull(a.IsDelete,0)=0 AND isnull(b.IsDelete,0)=0 " + condition;
DataSet Ds_Order = BussinessCommon.GetList(out pageCount, out Counts, sql, PageIndex, PageSize, "ReceiptDate", 1, "Id", PubConstant.BusinessContionString, FldName);
sb.Append("{\"Result\":\"true\",");
if (Ds_Order != null && Ds_Order.Tables.Count > 0 && Ds_Order.Tables[0].Rows.Count > 0)
{
sb.Append("\"data\":[");
for (int i = 0; i < Ds_Order.Tables[0].Rows.Count; i++)
{
DataRow dr = Ds_Order.Tables[0].Rows[i];
DataSet Ds_OrderDetails = BussinessCommon.GetShoppingDetailed(dr["Id"].ToString());
if (i > 0)
{
sb.Append(",");
}
//sb.Append(JSONHelper.FromDataRow(dr));
string strr = JSONHelper.FromDataRow(dr);
strr = strr.Substring(0, strr.Length - 1);
sb.Append(strr);
if (Ds_OrderDetails != null && Ds_OrderDetails.Tables.Count > 0 && Ds_OrderDetails.Tables[0].Rows.Count > 0)
{
sb.Append(",\"Details\":[");
for (int j = 0; j < Ds_OrderDetails.Tables[0].Rows.Count; j++)
{
DataRow dr_Details = Ds_OrderDetails.Tables[0].Rows[j];
if (j > 0)
{
sb.Append(",");
}
sb.Append(JSONHelper.FromDataRow(dr_Details));
}
sb.Append("]");
}
else
{
sb.Append(",\"Details\":[]");
}
sb.Append("}");
}
sb.Append("]");
}
else
{
sb.Append("\"data\":[]");
}
sb.Append("}");
}
catch (Exception ex)
{
sb = new StringBuilder();
sb.Append(JSONHelper.FromString(false, ex.Message));
}
return(sb.ToString());
}
/// <summary>
/// 获取楼宇树形结构信息
/// </summary>
private string GetBuildingTreeList(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区ID不能为空"));
}
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
var sql = @"SELECT BuildName,BuildSNum FROM Tb_HSPR_Building
WHERE CommID=@CommID AND isnull(IsDelete,0)=0
GROUP BY BuildName,BuildSNum
ORDER BY right('0000000000'+BuildName,10);
SELECT UnitName,UnitSNum,BuildSNum FROM Tb_HSPR_Room
WHERE CommID=@CommID AND UnitSNum IS NOT NULL AND isnull(IsDelete,0)=0
GROUP BY UnitName,UnitSNum,BuildSNum
ORDER BY BuildSNum,right('0000000000'+convert(nvarchar(10),UnitSNum),10);
SELECT FloorName,FloorSNum,UnitSNum,BuildSNum FROM Tb_HSPR_Room
WHERE CommID=@CommID AND UnitSNum IS NOT NULL AND FloorSNum IS NOT NULL AND isnull(IsDelete, 0)=0
GROUP BY FloorName,FloorSNum,UnitSNum,BuildSNum
ORDER BY BuildSNum,UnitSNum,right('0000000000'+convert(nvarchar(10),FloorSNum),10);";
using (var con = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var reader = con.QueryMultiple(sql, new { CommID = commId });
var building = reader.Read();
var unit = reader.Read();
var floor = reader.Read().ToList();
var tempBuilding = new List <object>();
if (building.Count() > 0)
{
// 楼栋
foreach (dynamic buildingItem in building)
{
var tempUnit = new List <object>();
// 单元
foreach (var unitItem in unit)
{
if (unitItem.BuildSNum == buildingItem.BuildSNum)
{
// 单元下楼层
var tempFloor = floor.FindAll(obj => (obj.BuildSNum == unitItem.BuildSNum && obj.UnitSNum == unitItem.UnitSNum));
var floors = tempFloor.Select(obj => new { FloorName = obj.FloorName, FloorSNum = obj.FloorSNum });
tempUnit.Add(new { UnitName = unitItem.UnitName, UnitSNum = unitItem.UnitSNum, Floors = floors });
}
}
tempBuilding.Add(new { BuildName = buildingItem.BuildName, BuildSNum = buildingItem.BuildSNum, Units = tempUnit });
}
}
return(new ApiResult(true, tempBuilding).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 AddParkingInfo(DataRow row)
{
#region 参数校验
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].AsString()))
{
return(new ApiResult(false, "缺少参数UserId").toJson());
}
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "缺少参数CommunityId").toJson());
}
if (!row.Table.Columns.Contains("Title") || string.IsNullOrEmpty(row["Title"].AsString()))
{
return(new ApiResult(false, "缺少参数Title").toJson());
}
if (!row.Table.Columns.Contains("BussType") || string.IsNullOrEmpty(row["BussType"].AsString()))
{
return(new ApiResult(false, "缺少参数BussType").toJson());
}
if (!row.Table.Columns.Contains("BuildingArea") || string.IsNullOrEmpty(row["BuildingArea"].AsString()))
{
return(new ApiResult(false, "缺少参数BuildingArea").toJson());
}
if (!row.Table.Columns.Contains("Floor") || string.IsNullOrEmpty(row["Floor"].AsString()))
{
return(new ApiResult(false, "缺少参数Floor").toJson());
}
if (!row.Table.Columns.Contains("Amount") || string.IsNullOrEmpty(row["Amount"].AsString()))
{
return(new ApiResult(false, "缺少参数Amount").toJson());
}
if (!row.Table.Columns.Contains("Linkman") || string.IsNullOrEmpty(row["Linkman"].AsString()))
{
return(new ApiResult(false, "缺少参数Linkman").toJson());
}
if (!row.Table.Columns.Contains("LinkmanTel") || string.IsNullOrEmpty(row["LinkmanTel"].AsString()))
{
return(new ApiResult(false, "缺少参数LinkmanTel").toJson());
}
if (!row.Table.Columns.Contains("LinkmanSex") || string.IsNullOrEmpty(row["LinkmanSex"].AsString()))
{
return(new ApiResult(false, "缺少参数LinkmanSex").toJson());
}
if (!row.Table.Columns.Contains("Imgs") || string.IsNullOrEmpty(row["Imgs"].AsString()))
{
return(new ApiResult(false, "缺少参数Imgs").toJson());
}
#endregion
var title = row["Title"].ToString();
var userId = row["UserId"].ToString();
var communityId = row["CommunityId"].ToString();
var BuildingArea = AppGlobal.StrToDec(row["BuildingArea"].ToString());
var floor = AppGlobal.StrToDec(row["Floor"].ToString());
var bussType = row["BussType"].ToString();
var amount = AppGlobal.StrToDec(row["Amount"].ToString());
var linkman = row["Linkman"].ToString();
var linkmanTel = row["LinkmanTel"].ToString();
var linkmanSex = AppGlobal.StrToInt(row["LinkmanSex"].ToString());
var imgs = row["Imgs"].ToString();
var description = default(string);
if (row.Table.Columns.Contains("Description") && !string.IsNullOrEmpty(row["Description"].AsString()))
{
description = row["Description"].AsString();
}
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查询到小区信息"));
}
using (var conn = new SqlConnection(PubConstant.UnifiedContionString))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
var iid = Guid.NewGuid().ToString();
var sql = @"INSERT INTO Tb_Rental(Id,CommunityId,UserId,Title,BussType,BuildingArea,Floor,
Amount,Description,Img,LinkMan,LinkManTel,LinkManSex)
VALUES(@Id,@CommunityId,@UserId,@Title,@BussType,@BuildingArea, @Floor,@LivingRooms,@BedRooms,@BathRooms,@KitchenRooms,@BuildingType,@Orientation,@Renovation,
@Amount,@Description,@Img,@LinkMan,@LinkManTel,@LinkManSex,@HasElevator);";
var i = conn.Execute(sql, new
{
Id = iid,
CommunityId = community.Id,
UserId = userId,
Title = title,
BussType = bussType,
Province = community.Province,
City = community.City,
Area = community.Area,
BuildingArea = BuildingArea,
Floor = floor,
Amount = amount,
Description = description,
Img = imgs,
LinkMan = linkman,
LinkManTel = linkmanTel,
LinkManSex = linkmanSex,
}, trans);
if (i == 1)
{
// 读取标签信息
var tags = (JArray)JsonConvert.DeserializeObject(row["Tags"].ToString());
foreach (var item in tags)
{
sql = @"INSERT INTO Tb_Rental_RoomTag(RentalID,CategoryID,Tags)
VALUES(@RentalID,@CategoryID,@Tags);";
conn.Execute(sql, new
{
RentalID = iid,
CategoryID = item["ID"].ToString(),
Tags = string.Join(",", ((JArray)item["Tags"]).ToArray().Select(obj => obj.ToString()))
}, trans);
}
}
trans.Commit();
return(JSONHelper.FromString(true, "添加成功"));
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message));
}
}
}
/// <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 AddBuyAndRentalInfo(DataRow row)
{
#region 参数校验
if (!row.Table.Columns.Contains("Type") || string.IsNullOrEmpty(row["Type"].AsString()) || !int.TryParse(row["Type"].AsString(), out int type))
{
return(new ApiResult(false, "缺少参数Type").toJson());
}
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].AsString()))
{
return(new ApiResult(false, "缺少参数UserId").toJson());
}
String communityId = String.Empty;
if (row.Table.Columns.Contains("CommunityId") && !string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
communityId = row["CommunityId"].ToString();
}
if (!row.Table.Columns.Contains("LivingRooms") || string.IsNullOrEmpty(row["LivingRooms"].AsString()))
{
return(new ApiResult(false, "缺少参数LivingRooms").toJson());
}
if (!row.Table.Columns.Contains("BedRooms") || string.IsNullOrEmpty(row["BedRooms"].AsString()))
{
return(new ApiResult(false, "缺少参数BedRooms").toJson());
}
if (!row.Table.Columns.Contains("BathRooms") || string.IsNullOrEmpty(row["BathRooms"].AsString()))
{
return(new ApiResult(false, "缺少参数BathRooms").toJson());
}
if (!row.Table.Columns.Contains("KitchenRooms") || string.IsNullOrEmpty(row["KitchenRooms"].AsString()))
{
return(new ApiResult(false, "缺少参数KitchenRooms").toJson());
}
String orientation = String.Empty;
if (row.Table.Columns.Contains("Orientation") && !string.IsNullOrEmpty(row["Orientation"].AsString()))
{
orientation = row["Orientation"].ToString();
}
if (!row.Table.Columns.Contains("BuildingArea") || string.IsNullOrEmpty(row["BuildingArea"].AsString()))
{
return(new ApiResult(false, "缺少参数BuildingArea").toJson());
}
if (!row.Table.Columns.Contains("Amount") || string.IsNullOrEmpty(row["Amount"].AsString()))
{
return(new ApiResult(false, "缺少参数Amount").toJson());
}
if (!row.Table.Columns.Contains("Linkman") || string.IsNullOrEmpty(row["Linkman"].AsString()))
{
return(new ApiResult(false, "缺少参数Linkman").toJson());
}
if (!row.Table.Columns.Contains("LinkmanTel") || string.IsNullOrEmpty(row["LinkmanTel"].AsString()))
{
return(new ApiResult(false, "缺少参数LinkmanTel").toJson());
}
int hasElevator = 0;
if (row.Table.Columns.Contains("HasElevator") && !string.IsNullOrEmpty(row["HasElevator"].AsString()))
{
hasElevator = AppGlobal.StrToInt(row["HasElevator"].ToString());
}
if (!row.Table.Columns.Contains("AmountMax") || string.IsNullOrEmpty(row["AmountMax"].AsString()))
{
return(new ApiResult(false, "缺少参数最大金额").toJson());
}
if (!row.Table.Columns.Contains("BuildingAreaMax") || string.IsNullOrEmpty(row["BuildingAreaMax"].AsString()))
{
return(new ApiResult(false, "缺少参数最大建筑面积").toJson());
}
if (!row.Table.Columns.Contains("Title") || string.IsNullOrEmpty(row["Title"].AsString()))
{
return(new ApiResult(false, "缺少参数标题").toJson());
}
String renovation = String.Empty;
if (row.Table.Columns.Contains("Renovation") && !string.IsNullOrEmpty(row["Renovation"].AsString()))
{
renovation = row["Renovation"].ToString();
}
String description = String.Empty;
if (row.Table.Columns.Contains("Description") && !string.IsNullOrEmpty(row["Description"].AsString()))
{
description = row["Description"].ToString();
}
#endregion
var userId = row["UserId"].ToString();
var livingRooms = AppGlobal.StrToInt(row["LivingRooms"].ToString());
var bedRooms = AppGlobal.StrToInt(row["BedRooms"].ToString());
var bathRooms = AppGlobal.StrToInt(row["BathRooms"].ToString());
var kitchenRooms = AppGlobal.StrToInt(row["KitchenRooms"].ToString());
var title = row["Title"].ToString();
var buildingArea = AppGlobal.StrToDec(row["BuildingArea"].ToString());
var bussType = type == 1 ? "求购" : "求租";
var amount = AppGlobal.StrToDec(row["Amount"].ToString());
var linkman = row["Linkman"].ToString();
var linkmanTel = row["LinkmanTel"].ToString();
var amountMax = AppGlobal.StrToDec(row["AmountMax"].ToString());
var buildingAreaMax = AppGlobal.StrToDec(row["BuildingAreaMax"].ToString());
String address = String.Empty;
var community = GetCommunity(communityId);
if (community != null)
{
address = String.Format("{0}{1}{2}", community.Province, community.City, community.Area);
}
using (var conn = new SqlConnection(PubConstant.UnifiedContionString))
{
try
{
var iid = Guid.NewGuid().ToString();
var sql = @"INSERT INTO Tb_Rental(Id,CommunityId,Address,UserId,Title,BussType,Province,City,Area,
BuildingAge,BuildingArea,BuildingType,Floor,FloorCount,LivingRooms,BedRooms,
BathRooms,KitchenRooms,Orientation,TranRoomTime,
Renovation,Amount,Description,Img,LinkMan,LinkManTel,LinkManSex,PubDate,HasElevator,RegisterDate,InformationSource,AmountMax,BuildingAreaMax)
VALUES(@Id,@CommunityId,@Address,@UserId,@Title,@BussType,@Province,@City,@Area,
@BuildingAge,@BuildingArea,@BuildingType,@Floor,@FloorCount,@LivingRooms,@BedRooms,
@BathRooms,@KitchenRooms,@Orientation,@TranRoomTime,
@Renovation,@Amount,@Description,@Img,@LinkMan,@LinkManTel,@LinkManSex,GETDATE(),@HasElevator,GETDATE(),'客户线上提交',@AmountMax,@BuildingAreaMax);";
var i = conn.Execute(sql, new
{
Id = iid,
CommunityId = communityId,
Address = address,
UserId = userId,
BussType = bussType,
Province = community != null ? community.Province : String.Empty,
City = community != null ? community.City : String.Empty,
Area = community != null ? community.Area : String.Empty,
LivingRooms = livingRooms,
BedRooms = bedRooms,
BathRooms = bathRooms,
KitchenRooms = kitchenRooms,
BuildingAge = String.Empty,
BuildingArea = buildingArea,
BuildingType = String.Empty,
Floor = 0,
FloorCount = 0,
Orientation = orientation,
TranRoomTime = String.Empty,
Renovation = renovation,
Amount = amount,
Description = description,
Img = String.Empty,
LinkMan = linkman,
LinkManTel = linkmanTel,
LinkManSex = 0,
HasElevator = hasElevator,
AmountMax = amountMax,
BuildingAreaMax = buildingAreaMax,
Title = title
});
return(JSONHelper.FromString(true, "添加成功"));
}
catch (Exception ex)
{
return(JSONHelper.FromString(false, ex.Message));
}
}
}
/// <summary>
/// 下载任务
/// </summary>
private string DownloadTask(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区id不能为空"));
}
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
using (IDbConnection conn0 = new SqlConnection(PubConstant.hmWyglConnectionString),
conn1 = new SqlConnection(Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_SUPERVISION)))
{
var sql = "";
// 任务列表
// 1、明确指定责任人，只查询责任人
// 2、未指定责任人，筛选具体岗位
// 3、未指定责任人，筛选通用岗位
sql += $@"SELECT a.TaskId FROM Tb_Supervision_Task a WITH(NOLOCK)
WHERE isnull(a.IsClose,0)=0 AND TaskState<>2 AND a.CommID=@CommID
AND a.TaskBeginTime<=getdate() AND a.TaskEndTime>=getdate()
AND
(
charindex(@UserCode,a.TaskUserCode)>0
OR
(isnull(TaskUserCode,'')=''
AND (SELECT count(t.RoleCode) AS RoleCode FROM (
SELECT Value AS RoleCode FROM { Global_Var.ERPDatabaseName }.dbo.SplitString(a.TaskRoleCode,',',1)
InterSect
SELECT RoleCode FROM { Global_Var.ERPDatabaseName }.dbo.Tb_Sys_UserRole WHERE UserCode=@UserCode
) AS T)>0)
OR
(isnull(a.TaskUserCode,'')=''
AND (SELECT count(U.RoleCode) AS RoleCode
FROM (SELECT RoleCode FROM { Global_Var.ERPDatabaseName }.dbo.Tb_Sys_RoleData WHERE CommId=@CommID
AND RoleCode IN (SELECT RoleCode FROM { Global_Var.ERPDatabaseName }.dbo.Tb_Sys_Role
WHERE SysRoleCode IN (SELECT Value AS RoleCode
FROM { Global_Var.ERPDatabaseName }.dbo.SplitString(a.TaskRoleCode,',',1)))
InterSect
SELECT RoleCode FROM { Global_Var.ERPDatabaseName }.dbo.Tb_Sys_UserRole WHERE UserCode=@UserCode)
AS U)>0
)
)";
sql = $@"SELECT TaskId,TaskNO,CommName,a.CommId,isnull(b.IsScore,0) AS IsScore,
CheckTypeName,CheckTypeId,PlanName,
TaskSource,TaskBeginTime,TaskEndTime,DidBeginTime,DidEndTime,DidEndUserCode,DidEndUserName,
(SELECT count(1) FROM Tb_Supervision_TaskStandard WITH(NOLOCK) WHERE TaskId=a.TaskId) AS StandardNum,
isnull(CompositeScore,0) AS CompositeScore,isnull(TaskState,0) AS TaskState,
TaskRoleCode,TaskRoleName,TaskUserCode,TaskUserName,b.ScoreType,
1 AS StandardPager,
{ TASK_POINT_STANDARD_PAGE_SIZE } AS StandardPageSize,
(SELECT count(1)
FROM Tb_Supervision_TaskStandard WITH(NOLOCK)
WHERE TaskId=a.TaskId AND CheckDate IS NULL
) AS StandardCount,
{ TASK_POINT_STANDARD_PAGE_SIZE } AS DeductionPageSize,
(SELECT count(1)
FROM Tb_Supervision_TaskDeductionItem WITH(NOLOCK)
WHERE TaskId=a.TaskId AND isnull(IsDelete,0)=0
) AS DeductionNum
FROM Tb_Supervision_Task a WITH(NOLOCK)
LEFT JOIN { Global_Var.ERPDatabaseName }.dbo.Tb_Supervision_CheckType b ON a.CheckTypeId=b.Id
WHERE TaskId IN ({sql}) ORDER BY TaskEndTime";
try
{
var data = conn1.Query(sql, new
{
CommID = commId,
UserCode = Global_Var.LoginUserCode
}).ToList();
foreach (dynamic taskInfo in data)
{
// 获取任务下的所有检查项目
sql = $@"SELECT DISTINCT
CASE t.CheckMethod
WHEN 1 THEN t.ProfessionalLineScoreItem
WHEN 2 THEN t.CategoryDimensionScoreItem
WHEN 3 THEN t.DepartmentScoreItem
ELSE '' END AS ScoreItemName,
CASE t.CheckMethod
WHEN 1 THEN t.ProfessionalLine
WHEN 2 THEN t.CategoryDimension
WHEN 3 THEN t.Department
ELSE '' END AS ScoreItemID,
t.TaskId,
t.CheckItemName,
t.CheckItemID,
t.Sort
FROM
(
SELECT a.TaskId, b.CheckMethod, c.ProfessionalLine, c.CategoryDimension, c.Department,
d.ScoreItem AS ProfessionalLineScoreItem,
e.ScoreItem AS CategoryDimensionScoreItem,
f.ScoreItem AS DepartmentScoreItem,
c.ItemNameAll AS CheckItemName,
c.ItemId AS CheckItemID,g.Sort
FROM Tb_Supervision_Task a WITH(NOLOCK)
LEFT JOIN { Global_Var.ERPDatabaseName }.dbo.Tb_Supervision_Plan b ON a.PlanId = b.Id
LEFT JOIN Tb_Supervision_TaskStandard c WITH(NOLOCK) ON a.TaskId = c.TaskId
LEFT JOIN { Global_Var.ERPDatabaseName }.dbo.Tb_Supervision_CheckStandard_ScoreItem d ON d.Id = c.ProfessionalLine
LEFT JOIN { Global_Var.ERPDatabaseName }.dbo.Tb_Supervision_CheckStandard_ScoreItem e ON e.Id = c.CategoryDimension
LEFT JOIN { Global_Var.ERPDatabaseName }.dbo.Tb_Supervision_CheckStandard_ScoreItem f ON f.Id = c.Department
LEFT JOIN { Global_Var.ERPDatabaseName }.dbo.Tb_Supervision_CheckStandard_CheckItem g ON c.ItemId = g.Id
WHERE a.CommId=@CommId AND a.TaskBeginTime<=getdate() AND a.TaskEndTime>=getdate()
AND a.TaskId=@TaskId
ORDER BY g.Sort ASC
) AS t";
//需求9900 隆基泰和 根据Sort升序 ORDER BY g.Sort ASC
var checkItems = conn1.Query(sql, new { TaskId = taskInfo.TaskId, CommId = taskInfo.CommId });
var scoreItems = new List <dynamic>();
foreach (dynamic checkItem in checkItems)
{
var scoreTmp = scoreItems.Find(obj => obj.ScoreItemID == checkItem.ScoreItemID);
if (scoreTmp == null)
{
scoreTmp = new
{
TaskId = taskInfo.TaskId,
ScoreItemID = checkItem.ScoreItemID,
ScoreItemName = checkItem.ScoreItemName,
CheckItems = new List <dynamic>()
};
scoreItems.Add(scoreTmp);
}
scoreTmp.CheckItems.Add(checkItem);
}
taskInfo.ScoreItems = scoreItems;
}
return(new ApiResult(true, data).toJson());
}
catch (Exception ex)
{
return(new ApiResult(false, ex.Message).toJson());
}
}
}
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));
}
}
}
}
private string SearchTask(DataRow row)
{
if (!row.Table.Columns.Contains("CommID") || string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "小区id不能为空"));
}
if (!row.Table.Columns.Contains("TaskState") || string.IsNullOrEmpty(row["TaskState"].ToString()))
{
return(JSONHelper.FromString(false, "任务状态不能为空"));
}
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
var taskState = row["TaskState"].ToString();
var taskNO = default(string);
var taskUserCode = default(string);
var taskRoleCode = default(string);
var taskBeginTime = default(string);
var taskEndTime = default(string);
var pageSize = 10;
var pageIndex = 1;
if (row.Table.Columns.Contains("TaskNO") && !string.IsNullOrEmpty(row["TaskNO"].ToString()))
{
taskNO = row["TaskNO"].ToString();
}
if (row.Table.Columns.Contains("TaskUserCode") && !string.IsNullOrEmpty(row["TaskUserCode"].ToString()))
{
taskUserCode = row["TaskUserCode"].ToString();
}
if (row.Table.Columns.Contains("TaskRoleCode") && !string.IsNullOrEmpty(row["TaskRoleCode"].ToString()))
{
taskRoleCode = row["TaskRoleCode"].ToString();
}
if (row.Table.Columns.Contains("TaskBeginTime") && !string.IsNullOrEmpty(row["TaskBeginTime"].ToString()))
{
taskBeginTime = row["TaskBeginTime"].ToString();
}
if (row.Table.Columns.Contains("TaskEndTime") && !string.IsNullOrEmpty(row["TaskEndTime"].ToString()))
{
taskEndTime = row["TaskEndTime"].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(Global_Fun.BurstConnectionString(commId, Global_Fun.BURST_TYPE_SUPERVISION)))
{
var condition = $" a.CommID={commId} AND isnull(a.IsClose,0)=0";
if (!string.IsNullOrEmpty(taskNO))
{
condition += $" AND a.TaskNO LIKE '%{taskNO}%' ";
}
if (!string.IsNullOrEmpty(taskUserCode))
{
condition += $" AND a.TaskUserCode LIKE '%{taskUserCode}%' ";
}
if (!string.IsNullOrEmpty(taskRoleCode))
{
condition += $" AND a.TaskRoleCode LIKE '%{taskRoleCode}%' ";
}
if (!string.IsNullOrEmpty(taskBeginTime) && string.IsNullOrEmpty(taskEndTime))
{
condition += $" AND a.TaskBeginTime>=convert(datetime, '{taskBeginTime}')";
}
else if (string.IsNullOrEmpty(taskBeginTime) && !string.IsNullOrEmpty(taskEndTime))
{
condition += $" AND a.TaskEndTime<=convert(datetime, convert(varchar(10), '{taskEndTime}')+' 23:59:59')";
}
else if (!string.IsNullOrEmpty(taskBeginTime) && !string.IsNullOrEmpty(taskEndTime))
{
condition += $@" AND (
(convert(varchar(10), '{taskBeginTime}')+' 23:59:59'>=a.TaskBeginTime AND
convert(varchar(10), '{taskEndTime}')+' 23:59:59'<=a.TaskEndTime) OR
(convert(varchar(10), '{taskBeginTime}')+' 00:00:00'<=isnull(a.TaskBeginTime,convert(varchar(10), '{taskBeginTime}')+' 00:00:00') AND
convert(varchar(10), '{taskEndTime}')+' 00:00:00'>=isnull(a.TaskBeginTime,convert(varchar(10), '{taskBeginTime}')+' 00:00:00')) OR
(convert(varchar(10), '{taskEndTime}')+' 00:00:00'>=isnull(a.TaskEndTime,convert(varchar(10), '{taskEndTime}')+' 00:00:00') AND
convert(varchar(10), '{taskBeginTime}')+' 00:00:00'<=isnull(a.TaskEndTime,convert(varchar(10), '{taskEndTime}')+' 00:00:00'))
) ";
}
var planState = GetPlanState(taskState);
var sql = $@"SELECT TaskId,TaskNO,CommName,a.CommId,PlanName,TaskSource,
TaskBeginTime, TaskEndTime, isnull(CompositeScore,0) AS CompositeScore, TaskRoleName,TaskUserName,
CASE
WHEN a.TaskState<>0 THEN 0
WHEN a.AddUserCode='{ Global_Var.LoginUserCode }' THEN 1
WHEN (SELECT count(1) FROM (SELECT value FROM { Global_Var.ERPDatabaseName }.dbo.SplitString(b.AuditingRoleCode, ',',1)
INTERSECT
(
SELECT RoleCode FROM { Global_Var.ERPDatabaseName }.dbo.Tb_Sys_UserRole
WHERE UserCode='{ Global_Var.LoginUserCode }'
)) as x)>0 THEN 1
ELSE 0 END AS CanClose,
CASE
WHEN a.AddUserCode='{ Global_Var.LoginUserCode }' THEN 1
WHEN b.AddUserId='{ Global_Var.LoginUserCode }' THEN 1
ELSE 0 END AS CanHandover
FROM Tb_Supervision_Task a WITH(NOLOCK)
WHERE {condition} ";
var taskStateIndex = TaskStatesDefine.ToList().IndexOf(taskState);
if (taskStateIndex == 0)
{
sql += " AND a.TaskState<>2 AND a.TaskEndTime<=getdate()";
}
else
{
sql += $" AND a.TaskState={taskStateIndex - 1}";
}
try
{
var resultSet = GetListDapper(out int pageCount, out int count, sql, pageIndex, pageSize, "TaskEndTime", 0, "TaskId", conn);
var json = new ApiResult(true, resultSet).toJson();
return(json.Insert(json.Length - 1, ",\"PageCount\":" + pageCount));
}
catch (Exception ex)
{
return(new ApiResult(false, ex.Message).toJson());
}
}
}
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>
private string SaveUserAddress(DataRow row)
{
if (!row.Table.Columns.Contains("Mobile") || string.IsNullOrEmpty(row["Mobile"].ToString()))
{
return(JSONHelper.FromString(false, "联系方式不能为空"));
}
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户编码不能为空"));
}
if (!row.Table.Columns.Contains("UserName") || string.IsNullOrEmpty(row["UserName"].ToString()))
{
return(JSONHelper.FromString(false, "收货人不能为空"));
}
if (!row.Table.Columns.Contains("Address") || string.IsNullOrEmpty(row["Address"].ToString()))
{
return(JSONHelper.FromString(false, "配送地址不能为空"));
}
if (!row.Table.Columns.Contains("CommID") && string.IsNullOrEmpty(row["CommID"].ToString()))
{
return(JSONHelper.FromString(false, "配送小区不能为空"));
}
if (!row.Table.Columns.Contains("BuildUnitName") || string.IsNullOrEmpty(row["BuildUnitName"].ToString()))
{
return(JSONHelper.FromString(false, "楼栋单元不能为空"));
}
if (!row.Table.Columns.Contains("RoomName") || string.IsNullOrEmpty(row["RoomName"].ToString()))
{
return(JSONHelper.FromString(false, "房屋名称不能为空"));
}
var mobile = row["Mobile"].ToString();
var userId = row["UserId"].ToString();
var userName = row["UserName"].ToString();
var address = row["Address"].ToString();
var iid = default(string);
var bussId = 0;
var commId = AppGlobal.StrToInt(row["CommID"].ToString());
var buildUnitName = row["BuildUnitName"].ToString();
var roomName = row["RoomName"].ToString();
var remark = default(string);
if (row.Table.Columns.Contains("Id") && !string.IsNullOrEmpty(row["Id"].ToString()))
{
iid = row["Id"].ToString();
}
if (row.Table.Columns.Contains("BussId") && !string.IsNullOrEmpty(row["BussId"].ToString()))
{
bussId = AppGlobal.StrToInt(row["BussId"].ToString());
}
if (row.Table.Columns.Contains("Remark") && !string.IsNullOrEmpty(row["Remark"].ToString()))
{
remark = row["Remark"].ToString();
}
using (var conn = new SqlConnection(PubConstant.BusinessContionString))
{
var sql = @"DECLARE @CommName varchar(50);
SELECT @CommName=CommName FROM Unified.dbo.Tb_Community WHERE CommID=@CommID;
UPDATE Tb_User_Address SET BussId=@BussId,
UserName=@UserName,Address=@Address,Mobile=@Mobile,UpdataTime=getdate(),
CommID=@CommID,CommName=@CommName,BuildUnitName=@BuildUnitName,
RoomName=@RoomName,Remark=@Remark
WHERE Id=@Id;";
if (string.IsNullOrEmpty(iid))
{
iid = Guid.NewGuid().ToString();
sql = @"DECLARE @CommName varchar(50);
SELECT @CommName=CommName FROM Unified.dbo.Tb_Community WHERE CommID=@CommID;
INSERT INTO Tb_User_Address(Id,BussId,UserId,UserName,Address,Mobile,UpdataTime,IsDefault,
CommID,CommName,BuildUnitName,RoomName,Remark)
VALUES(@Id,@BussId,@UserId,@UserName,@Address,@Mobile,getdate(),0,@CommID,
@CommName,@BuildUnitName,@RoomName,@Remark);
IF (SELECT count(1) FROM Tb_User_Address WHERE UserId=@UserId)=1
UPDATE Tb_User_Address SET IsDefault=1 WHERE Id=@Id;";
}
conn.Execute(sql, new
{
Id = iid,
BussId = bussId,
UserId = userId,
UserName = userName,
Address = address,
Mobile = mobile,
CommID = commId,
BuildUnitName = buildUnitName,
RoomName = roomName,
Remark = remark
});
return(JSONHelper.FromString(true, "保存成功"));
}
}
private string GetSunPropertyTask(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].AsString()))
{
return(new ApiResult(false, "缺少参数CommunityId").toJson());
}
var communityId = row["CommunityId"].ToString();
var community = GetCommunity(communityId);
if (community == null)
{
return(JSONHelper.FromString(false, "未查询到小区信息"));
}
var commID = AppGlobal.StrToInt(community.CommID);
PubConstant.tw2bsConnectionString = Global_Fun.Tw2bsConnectionString("1");
PubConstant.hmWyglConnectionString = GetConnectionStr(community);
var personCount = 0;
var safeDate = "";
var fireDate = "";
var list = new List <TaskInfo>();
using (var conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
var sql = @"SELECT COUNT(*) AS COUNT FROM Tb_Sys_RoleData a
LEFT JOIN Tb_Sys_Role b ON a.RoleCode = b.RoleCode
LEFT JOIN Tb_Sys_Department c ON b.DepCode = c.DepCode
LEFT JOIN tb_sys_userrole d ON b.RoleCode = d.RoleCode
LEFT JOIN tb_sys_user e ON d.UserCode = e.UserCode
WHERE a.CommID = @CommID AND e.IsDelete=0
AND DepType = '项目级'";
// 长城要求首页显示
sql = "SELECT COUNT(1) FROM Tb_Sys_User WHERE CommID = @CommID";
sql = @"SELECT COUNT(DISTINCT(e.USERCODE) ) AS Number FROM Tb_Sys_RoleData a
LEFT JOIN Tb_Sys_Role b ON a.RoleCode = b.RoleCode
LEFT JOIN tb_sys_userrole d ON b.RoleCode = d.RoleCode
LEFT JOIN tb_sys_user e ON d.UserCode = e.UserCode
WHERE a.CommID = @CommID AND e.IsDelete = 0 ";
personCount = conn.Query <int>(sql, new { CommID = commID }).FirstOrDefault();
}
// 安全
using (var safeConn = new SqlConnection(Global_Fun.BurstConnectionString(commID, Global_Fun.BURST_TYPE_SAFE)))
{
// 安全巡更
var sql = $@"SELECT TOP 1 convert(varchar(20),LastExecuteTime,120) FROM
(
SELECT BeginTime AS LastExecuteTime FROM Tb_CP_Task_Safe
WHERE IsClose=0 AND IsDelete=0 AND BeginTime<=getdate() AND TaskLevelName='安全巡更' AND CommId={commID}
) AS t
ORDER BY LastExecuteTime DESC;";
safeDate = safeConn.Query <string>(sql).FirstOrDefault();
}
// 设备
using (var eqConn = new SqlConnection(Global_Fun.BurstConnectionString(commID, Global_Fun.BURST_TYPE_EQ)))
{
var sql = $@"SELECT TOP 1 convert(varchar(20),LastExecuteTime,120) FROM
(
SELECT TaskBeginTime AS LastExecuteTime FROM Tb_Eq_Task_Inspection
WHERE IsClose=0 AND IsDelete=0 AND TaskBeginTime<=getdate() AND IsFireControl=0 AND CommId={commID}
) AS t
ORDER BY LastExecuteTime DESC;
SELECT TOP 1 convert(varchar(20),LastExecuteTime,120) FROM
(
SELECT TaskBeginTime AS LastExecuteTime FROM Tb_Eq_Task_Inspection
WHERE IsClose=0 AND IsDelete=0 AND TaskBeginTime<=getdate() AND IsFireControl=1 AND CommId={commID}
) AS t
ORDER BY LastExecuteTime DESC;";
var reader = eqConn.QueryMultiple(sql);
var eqDate = reader.Read <string>().FirstOrDefault();
fireDate = reader.Read <string>().FirstOrDefault();
// 比较安全最大值
var maxDate = AppGlobal.StrToDate(safeDate) > AppGlobal.StrToDate(fireDate) ? safeDate : fireDate;
list.Add(new TaskInfo
{
TaskType = "安全巡检",
LastExecuteTime = maxDate ?? "暂无巡检记录"
});
list.Add(GetEnvironment(commID));
list.Add(new TaskInfo
{
TaskType = "设备巡检",
LastExecuteTime = eqDate ?? "暂无巡检记录"
});
}
return(new ApiResult(true, new { PeoCount = personCount, List = list }).toJson());
}
/// <summary>
/// 添加审核信息
/// </summary>
private string Add(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!row.Table.Columns.Contains("RoomID") || string.IsNullOrEmpty(row["RoomID"].ToString()))
{
return(JSONHelper.FromString(false, "缺少RoomID参数"));
}
if (!row.Table.Columns.Contains("ApplicantUserID") || string.IsNullOrEmpty(row["ApplicantUserID"].ToString()))
{
return(JSONHelper.FromString(false, "缺少ApplicantUserID参数"));
}
if (!row.Table.Columns.Contains("InviteeName") || string.IsNullOrEmpty(row["InviteeName"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteeName参数"));
}
if (!row.Table.Columns.Contains("InviteeMobile") || string.IsNullOrEmpty(row["InviteeMobile"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteeMobile参数"));
}
if (!row.Table.Columns.Contains("InviteeIdentity") || string.IsNullOrEmpty(row["InviteeIdentity"].ToString()))
{
return(JSONHelper.FromString(false, "缺少InviteeIdentity参数"));
}
string communityId = row["CommunityId"].AsString();
string RoomID = row["RoomID"].ToString();
string ApplicantUserID = row["ApplicantUserID"].ToString();
string InviteeName = row["InviteeName"].ToString();
string InviteeMobile = row["InviteeMobile"].ToString();
int InviteeIdentity = AppGlobal.StrToInt(row["InviteeIdentity"].ToString());
string IDCard1 = null;
string IDCard2 = null;
string IDCardNum = null;
if (row.Table.Columns.Contains("IDCard1") && !string.IsNullOrEmpty(row["IDCard1"].ToString()))
{
IDCard1 = row["IDCard1"].ToString();
}
if (row.Table.Columns.Contains("IDCard2") && !string.IsNullOrEmpty(row["IDCard2"].ToString()))
{
IDCard2 = row["IDCard2"].ToString();
}
if (row.Table.Columns.Contains("IDCardNum") && !string.IsNullOrEmpty(row["IDCardNum"].ToString()))
{
IDCardNum = row["IDCardNum"].ToString();
}
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
using (var conn = new SqlConnection(PubConstant.UnifiedContionString))
{
string sql = @"SELECT count(*) FROM Tb_User_Relation WHERE RoomId=@RoomID AND isnull(Locked,0)=0;
SELECT MaxInvite FROM Tb_AuditControl_InviteUser WHERE CorpID=@CorpID;";
GridReader reader = conn.QueryMultiple(sql, new { RoomID = RoomID, CorpID = Community.CorpID });
int count = reader.Read <int>().FirstOrDefault();
int maxInvite = reader.Read <int>().FirstOrDefault();
string IID = Guid.NewGuid().ToString().ToUpper();
sql = @"INSERT INTO Tb_Audit_InviteUser(IID, CommunityID, RoomID, ApplicantUserID,
InviteeName, InviteeMobile, InviteeIdentity, IDCard1, IDCard2, IDCardNum)
VALUES(@IID,@CommunityID, @RoomID, @ApplicantUserID, @InviteeName,
@InviteeMobile, @InviteeIdentity, @IDCard1, @IDCard2, @IDCardNum)";
conn.Execute(sql, new
{
IID = IID,
CommunityID = Community.Id,
RoomID = RoomID,
ApplicantUserID = ApplicantUserID,
InviteeName = InviteeName,
InviteeMobile = InviteeMobile,
InviteeIdentity = InviteeIdentity,
IDCard1 = IDCard1,
IDCard2 = IDCard2,
IDCardNum = IDCardNum
});
// 如果当前绑定用户数已经小于设置的最大绑定数，并且邀请的身份不是业主或家属，自动审核
if (count < (maxInvite + 1) && (InviteeIdentity == 0 || InviteeIdentity == 1))
{
return(Audit(1, IID, "自动审核通过"));
}
return(new ApiResult(true, "提交审核成功").toJson());
}
}
/// <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 GetORdersAll_New(DataRow row)
{
if (!row.Table.Columns.Contains("UserId") || string.IsNullOrEmpty(row["UserId"].ToString()))
{
return(JSONHelper.FromString(false, "用户编号不能为空"));
}
int pageCount;
int Counts;
int PageIndex = 1;
int PageSize = 2;
//string QueryType = "all";
string IsDeliver = ""; //是否发货
string IsPay = ""; //付款状态
string HandleState = ""; //处理状态
string IsReceive = ""; //是否收货
if (row.Table.Columns.Contains("PageIndex") && AppGlobal.StrToInt(row["PageIndex"].ToString()) > 0)
{
PageIndex = AppGlobal.StrToInt(row["PageIndex"].ToString());
}
if (row.Table.Columns.Contains("PageSize") && AppGlobal.StrToInt(row["PageSize"].ToString()) > 0)
{
PageSize = AppGlobal.StrToInt(row["PageSize"].ToString());
}
string whereStr = "";
string FldName = @"CorpId,BussId,BussNature,BussName,Id,ReceiptSign,HandleState,IsReceive,IsPay,IsDeliver,
OrderDetailNumber,Tb_Charge_Receipt.Id AS OrderId,ReceiptDate,Express,ExpressNum,CouponBalance";
StringBuilder sb = new StringBuilder();
try
{
whereStr += " and r.UserId='" + row["UserId"] + "'";
if (row.Table.Columns.Contains("IsPay") && row["IsPay"].ToString() != "")
{
IsPay = row["IsPay"].ToString();
whereStr += " and R.IsPay='" + IsPay + "'";
}
if (row.Table.Columns.Contains("IsDeliver") && row["IsDeliver"].ToString() != "")
{
IsDeliver = row["IsDeliver"].ToString();
whereStr += " and isnull(R.IsDeliver,'未发货')='" + IsDeliver + "'";
}
if (row.Table.Columns.Contains("IsReceive") && row["IsReceive"].ToString() != "")
{
IsReceive = row["IsReceive"].ToString();
whereStr += " and isnull(R.IsReceive,'未收货')='" + IsReceive + "'";
// 乐生活
if (IsReceive == "未收货" && string.IsNullOrEmpty(IsDeliver) && HttpContext.Current.Request.Url.Host.Contains("lsc.easylife365.com"))
{
whereStr += " and isnull(R.IsDeliver,'未收货')='已发货'";
}
else if (IsPay == "已付款" && IsReceive == "已收货")
{
whereStr += " and isnull(R.IsDeliver,'未收货')='已发货' AND isnull(R.IsEvaluated,0)=0";
}
}
//查询订单
string OrderSql = @"select ReturnSatus=0 ,ISNULL(B.BussWorkedTel,B.BussMobileTel) as BussMobile, B.BussName,B.BussNature,R.*,isnull(x.Balance,0) as CouponBalance,
OrderDetailNumber=(select COUNT(1) from Tb_Charge_ReceiptDetail where ReceiptCode=R.Id and RpdIsDelete=0)
from Tb_Charge_Receipt as R inner join Tb_System_BusinessCorp as B on b.BussId = R.BussId
LEFT JOIN Tb_Resources_CouponBalance x ON x.BussId=R.BussId AND x.UserId=R.UserId
where ISNULL( R.IsDelete,0)=0 and isnull(B.IsClose,'未关闭')='未关闭' and ISNULL( B.IsDelete,0)=0 " + whereStr;
DataSet Ds_Order = BussinessCommon.GetList(out pageCount, out Counts, OrderSql, PageIndex, PageSize, "ReceiptDate", 1, "Id", PubConstant.BusinessContionString, FldName);
sb.Append("{\"Result\":\"true\",");
if (Ds_Order != null && Ds_Order.Tables.Count > 0 && Ds_Order.Tables[0].Rows.Count > 0)
{
sb.Append("\"data\":[");
for (int i = 0; i < Ds_Order.Tables[0].Rows.Count; i++)
{
DataRow dr = Ds_Order.Tables[0].Rows[i];
if (dr["IsDeliver"] == null ||
dr["IsDeliver"].ToString() == "" ||
dr["IsDeliver"].ToString() == "未发货")
{
dr["IsReceive"] = null;
}
if (!String.IsNullOrEmpty(dr["IsReceive"].ToString()) &&
!String.IsNullOrEmpty(dr["ConfirmReceivedTime"].ToString()) &&
DateTime.TryParse(dr["ConfirmReceivedTime"].ToString(), out DateTime confirmReceivedTime))
{
dr["ReturnSatus"] = ReturnInfoStatus(dr["IsRetreat"], confirmReceivedTime, (String)dr["IsReceive"]);
}
DataSet Ds_OrderDetails = null;
if (dr["IsPay"] != null && dr["IsPay"].ToString() == "未付款")
{
using (IDbConnection conn = new SqlConnection(ConnectionDb.GetBusinessConnection()))
{
Ds_OrderDetails = BussinessCommon.GetShoppingDetailed_New(dr["Id"].ToString());
}
}
else
{
Ds_OrderDetails = BussinessCommon.GetShoppingDetailed(dr["Id"].ToString());
}
if (i > 0)
{
sb.Append(",");
}
//sb.Append(JSONHelper.FromDataRow(dr));
string strr = JSONHelper.FromDataRow(dr);
strr = strr.Substring(0, strr.Length - 1);
sb.Append(strr);
if (Ds_OrderDetails != null && Ds_OrderDetails.Tables.Count > 0 && Ds_OrderDetails.Tables[0].Rows.Count > 0)
{
sb.Append(",\"Details\":[");
for (int j = 0; j < Ds_OrderDetails.Tables[0].Rows.Count; j++)
{
DataRow dr_Details = Ds_OrderDetails.Tables[0].Rows[j];
if (j > 0)
{
sb.Append(",");
}
sb.Append(JSONHelper.FromDataRow(dr_Details));
// 查询商品属性
using (var bzconn = new SqlConnection(PubConstant.BusinessContionString))
{
var specArr = bzconn.Query(@"select a.PropertyId,a.SpecId,b.SpecName,a.Price,A.DiscountAmount,A.GroupBuyingPrice,C.ResourcesName from Tb_ResourcesSpecificationsPrice a
left join Tb_Resources_Specifications b ON a.SpecId=b.Id
LEFT JOIN Tb_Resources_Details AS C ON A.ResourcesID=C.ResourcesID
where a.ResourcesID=@ResourcesID AND a.SpecId IS NOT NULL
AND a.SpecId IN (SELECT SpecId FROM Tb_ShoppingDetailed WHERE ShoppingId=@ShoppingId);",
new
{
ResourcesID = Ds_OrderDetails.Tables[0].Rows[j]["ResourcesID"],
ShoppingId = Ds_OrderDetails.Tables[0].Rows[j]["ShoppingId"]
});
List <string> list = new List <string>();
foreach (var spec in specArr)
{
var param = new
{
PropertyId = spec.PropertyId,
SpecId = spec.SpecId,
SpecName = spec.SpecName,
DiscountAmount = spec.DiscountAmount,
GroupBuyingPrice = spec.GroupBuyingPrice,
Price = spec.Price,
ResourcesName = spec.ResourcesName,
};
list.Add(JsonConvert.SerializeObject(param));
}
sb.Insert(sb.Length - 1, ",\"Property\":[" + string.Join(",", list) + "]");
}
}
sb.Append("]");
}
else
{
sb.Append(",\"Details\":[]");
}
sb.Append("}");
}
sb.Append("]");
}
else
{
sb.Append("\"data\":[]");
}
sb.Append("}");
}
catch (Exception ex)
{
sb = new StringBuilder();
sb.Append(JSONHelper.FromString(false, ex.Message));
}
return(sb.ToString());
}
/// <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 WaitingAuditList(DataRow row)
{
if (!row.Table.Columns.Contains("CommunityId") || string.IsNullOrEmpty(row["CommunityId"].ToString()))
{
return(JSONHelper.FromString(false, "缺少CommunityId参数"));
}
if (!row.Table.Columns.Contains("PageSize") || string.IsNullOrEmpty(row["PageSize"].ToString()))
{
return(JSONHelper.FromString(false, "缺少PageSize参数"));
}
if (!row.Table.Columns.Contains("PageIndex") || string.IsNullOrEmpty(row["PageIndex"].ToString()))
{
return(JSONHelper.FromString(false, "缺少PageIndex参数"));
}
string communityId = row["CommunityId"].AsString();
int PageSize = AppGlobal.StrToInt(row["PageSize"].AsString());
int PageIndex = AppGlobal.StrToInt(row["PageIndex"].AsString());
string RoomID = null;
string ApplicantUserID = null;
if (row.Table.Columns.Contains("RoomID") && !string.IsNullOrEmpty(row["RoomID"].ToString()))
{
RoomID = row["RoomID"].ToString();
}
if (row.Table.Columns.Contains("ApplicantUserID") && !string.IsNullOrEmpty(row["ApplicantUserID"].ToString()))
{
ApplicantUserID = row["ApplicantUserID"].ToString();
}
//查询小区
Tb_Community Community = new MobileSoft.BLL.Unified.Bll_Tb_Community().GetModel(communityId);
//构造链接字符串
if (Community == null)
{
return(JSONHelper.FromString(false, "该小区不存在"));
}
string sql = $@"SELECT IID,ApplicantUserID,CommunityID,
(SELECT CommName FROM Tb_Community x WHERE x.ID=CommunityID) AS CommName,
RoomID,RoomName,CustName,CustMobile,AddTime,IDCardNum,IDCard1,IDCard2
FROM Tb_Audit_BindingRoom
WHERE CommunityID='{Community.Id}' AND IsAllows IS NULL AND isnull(IsDelete,0)=0";
if (!string.IsNullOrEmpty(RoomID))
{
sql += $@" AND RoomID={RoomID}";
}
if (!string.IsNullOrEmpty(ApplicantUserID))
{
sql += $@" AND ApplicantUserID='{ApplicantUserID}'";
}
DataTable dataTable = GetList(out int pageCount, out int count, sql, PageIndex, PageSize,
"AddTime", 0, "IID", PubConstant.UnifiedContionString).Tables[0];
using (var erpConn = new SqlConnection(GetConnectionStr(Community)))
{
dataTable.Columns.Add(new DataColumn("BuildSNum", typeof(string)));
dataTable.Columns.Add(new DataColumn("UnitSNum", typeof(string)));
dataTable.Columns.Add(new DataColumn("FloorSNum", typeof(string)));
foreach (DataRow item in dataTable.Rows)
{
dynamic roomInfo = erpConn.Query <dynamic>($@"SELECT BuildSNum,UnitSNum,FloorSNum FROM Tb_HSPR_Room
WHERE RoomID={item["RoomID"]}").FirstOrDefault();
if (roomInfo != null)
{
item["BuildSNum"] = roomInfo.BuildSNum.ToString();
item["UnitSNum"] = roomInfo.UnitSNum.ToString();
item["FloorSNum"] = roomInfo.FloorSNum.ToString();
}
}
}
string result = JSONHelper.FromString(true, dataTable);
result = result.Insert(result.Length - 1, ",\"PageCount\":" + pageCount);
return(result);
}
/// <summary>
/// 生成商家订单
/// </summary>
public string SubmitOrder(DataSet Ds)
{
DataRow Row = Ds.Tables[0].Rows[0];
string txnTime = DateTime.Now.ToString("yyyyMMddHHmmss");
string BussId = Row["BussId"].ToString();
string UserId = Row["UserId"].ToString();
string Name = Row["Name"].ToString();
string Mobile = Row["Mobile"].ToString();
string DeliverAddress = Row["DeliverAddress"].ToString();
int? corpId = null;
string communityId = null;
if (Row.Table.Columns.Contains("CorpId") && !string.IsNullOrEmpty(Row["CorpId"].ToString()))
{
corpId = AppGlobal.StrToInt(Row["CorpId"].ToString());
}
if (Row.Table.Columns.Contains("CommunityId") && !string.IsNullOrEmpty(Row["CommunityId"].ToString()))
{
communityId = Row["CommunityId"].ToString();
}
var iid = Guid.NewGuid().ToString();
// 生成商家收款订单
Tb_Charge_Receipt Receipt = new Tb_Charge_Receipt
{
Id = iid,
CorpId = corpId,
BussId = BussId.ToString(),
UserId = UserId,
OrderId = Guid.NewGuid().ToString(),
ReceiptSign = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999).ToString(),
Name = Name,
IsPay = "未付款",
IsReceive = "未收货",
IsDeliver = "未发货",
ReceiptMemo = Row["ReceiptMemo"].ToString(),
ReceiptType = "通用票据",
ReceiptDate = DateTime.Now,
PayDate = null,
MchId = "",
Partner = "",
PrepayStr = "",
txnTime = txnTime.ToString(),
ReturnCode = "",
ReturnMsg = "",
Express = "",
ExpressNum = "",
Mobile = Mobile, //联系电话
DeliverAddress = DeliverAddress, //收货地址
IsDelete = 0
};
// 是否使用优惠券抵扣
if (Row.Table.Columns.Contains("UseCoupon"))
{
Receipt.IsUseCoupon = AppGlobal.StrToInt(Row["UseCoupon"].ToString());
}
using (var conn = new SqlConnection(PubConstant.BusinessContionString))
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
// 保存订单信息
conn.Insert(Receipt, trans);
if (!string.IsNullOrEmpty(communityId))
{
conn.Execute("UPDATE Tb_Charge_Receipt SET CommunityId=@CommunityId WHERE ID=@IID",
new { CommunityId = communityId, IID = iid }, trans);
}
/*
* 计算此时的订单金额，此时不应该保存商品的价格信息；
* 如果用户不付款，且订单未取消期间，商家更改了商品单价或可优惠价或规格追加价，付款时则需要重新计算
*
* 1、获取用户在该商家的可用优惠券余额
* 2、获取商品此时的单价和可优惠价，对应的规格型号的追加单价
* 3、计算当前订单内的商品是否支持优惠券抵扣，如果支持，计算最多能抵扣的价格并更新优惠券余额
*/
// 1、获取用户优惠券余额
dynamic balanceInfo = conn.Query <dynamic>(@"SELECT isnull(Balance1,0) AS Balance1,isnull(Balance2,0) AS Balance2
FROM
(SELECT Balance1 = (SELECT Balance FROM Tb_Resources_CouponBalance
WHERE UserId = @UserId
AND CorpId = @CorpId AND BussId = @BussId),
Balance2 = (SELECT Balance FROM Tb_Resources_CouponBalance
WHERE UserId = @UserId
AND CorpId = @CorpId AND BussId IS NULL)) as x; ",
new { UserId = Receipt.UserId, CorpId = corpId, BussId = Receipt.BussId }, trans).FirstOrDefault();
decimal totalAmount = 0.0m; // 商品总价
decimal userCouponBalance = 0.0m; // 用户可用优惠券余额
decimal totalUseCouponMoney = 0.0m; // 当前能使用优惠券抵扣的钱
decimal bussBalance = 0.0m; // 商家专用券余额
decimal corpBalance = 0.0m; // 物管通用券余额
if (balanceInfo != null)
{
bussBalance = balanceInfo.Balance1;
corpBalance = balanceInfo.Balance2;
userCouponBalance = bussBalance + corpBalance;
}
// 2、获取商品价格信息
foreach (DataRow DetailRow in Ds.Tables["Product"].Rows)
{
// 订单商品详情
Tb_Charge_ReceiptDetail ReceiptDetail = new Tb_Charge_ReceiptDetail();
ReceiptDetail.RpdCode = Guid.NewGuid().ToString();
ReceiptDetail.ReceiptCode = Receipt.Id;
ReceiptDetail.ShoppingId = DetailRow["ShoppingId"].ToString();
ReceiptDetail.ResourcesID = DetailRow["Id"].ToString();
ReceiptDetail.RpdMemo = DetailRow["RpdMemo"].ToString();
ReceiptDetail.Quantity = DataSecurity.StrToInt(DetailRow["Quantity"].ToString());
ReceiptDetail.OffsetMoney = 0.0m;
ReceiptDetail.OffsetMoney2 = 0.0m;
// 商品的单价和可优惠单价
string sql = @"SELECT * FROM Tb_Resources_Details
WHERE isnull(IsDelete, 0) = 0 AND IsRelease = '是' AND IsStopRelease = '否' AND ResourcesID = @ResourcesID";
Tb_Resources_Details ResourcesDetail = conn.Query <Tb_Resources_Details>(sql, new
{
ResourcesID = ReceiptDetail.ResourcesID
}, trans).FirstOrDefault();
// 商品存在，期间未失效
if (ResourcesDetail != null)
{
// 库存不足，跳过
if (ReceiptDetail.Quantity > ResourcesDetail.ResourcesCount)
{
return(JSONHelper.FromString(false, "部分商品库存不足"));
}
ReceiptDetail.SalesPrice = ResourcesDetail.ResourcesSalePrice;
ReceiptDetail.DiscountPrice = ResourcesDetail.ResourcesDisCountPrice;
bool IsInGroupBuyTime = false;
if (ResourcesDetail.IsGroupBuy == "是")
{
if (ResourcesDetail.GroupBuyStartDate.HasValue && ResourcesDetail.GroupBuyStartDate.Value <= DateTime.Now &&
ResourcesDetail.GroupBuyEndDate.HasValue && ResourcesDetail.GroupBuyEndDate.Value >= DateTime.Now)
{
IsInGroupBuyTime = true;
ReceiptDetail.GroupPrice = ResourcesDetail.GroupBuyPrice;
}
}
// 1、商品单价
decimal price = 0.0m;
if (IsInGroupBuyTime)
{
price = (ResourcesDetail.GroupBuyPrice.Value - ResourcesDetail.ResourcesDisCountPrice) * ReceiptDetail.Quantity;
}
else
{
price = (ResourcesDetail.ResourcesSalePrice - ResourcesDetail.ResourcesDisCountPrice) * ReceiptDetail.Quantity;
}
// 2、商品规格追加价格
sql = "SELECT * FROM View_Tb_ResourcesSpecificationsPrice WHERE ShoppingId=@ShoppingId";
MobileSoft.Model.Resources.Tb_ResourcesSpecificationsPrice propertyInfo = conn.Query <MobileSoft.Model.Resources.Tb_ResourcesSpecificationsPrice>(sql, new { ShoppingId = DetailRow["ShoppingId"].ToString() }, trans).FirstOrDefault();
if (propertyInfo != null && propertyInfo.Price.HasValue)
{
price = price + propertyInfo.Price.Value * ReceiptDetail.Quantity;
}
// 当前商品需要抵扣的金额
decimal currCouponMoney = 0.0m;
string isSupportCoupon = "";
// 3、商品支持抵扣券 并且 还有用户还有足够的优惠券余额
if (ResourcesDetail.IsSupportCoupon == "1")
{
isSupportCoupon = "是";
}
else
{
isSupportCoupon = "否";
}
if (isSupportCoupon == "是" &&
Receipt.IsUseCoupon.HasValue &&
Receipt.IsUseCoupon.Value > 0 &&
userCouponBalance > 0)
{
// 计算当前商品需要抵扣的金额
currCouponMoney = (ResourcesDetail.MaximumCouponMoney.HasValue ? ResourcesDetail.MaximumCouponMoney.Value : 0) * ReceiptDetail.Quantity;
if (bussBalance > 0)
{
if (bussBalance >= currCouponMoney)
{
// 优惠券使用明细
ReceiptDetail.OffsetMoney = currCouponMoney;
ReceiptDetail.OffsetMoney2 = 0.0m;
totalUseCouponMoney += currCouponMoney;
bussBalance -= currCouponMoney;
userCouponBalance -= currCouponMoney;
}
else
{
// 先用专用券抵扣
ReceiptDetail.OffsetMoney = bussBalance;
totalUseCouponMoney += bussBalance;
userCouponBalance -= bussBalance;
// 专用券不够差额
decimal differ = currCouponMoney - bussBalance;
// 专用券不够，通用券余额大于0
if (corpBalance > 0)
{
// 通用券足够抵扣
if (corpBalance >= differ)
{
ReceiptDetail.OffsetMoney2 = differ;
totalUseCouponMoney += differ;
corpBalance -= differ;
userCouponBalance -= differ;
}
// 通用券不足以抵扣
else
{
ReceiptDetail.OffsetMoney2 = corpBalance;
totalUseCouponMoney += corpBalance;
userCouponBalance -= corpBalance;
corpBalance = 0;
}
}
}
}
else
{
// 没有专用券，但是有通用券
if (corpBalance > 0)
{
// 通用券足够抵扣
if (corpBalance > currCouponMoney)
{
ReceiptDetail.OffsetMoney2 = currCouponMoney;
totalUseCouponMoney += currCouponMoney;
corpBalance -= currCouponMoney;
userCouponBalance -= currCouponMoney;
}
// 通用券不足以抵扣
else
{
ReceiptDetail.OffsetMoney2 = corpBalance;
totalUseCouponMoney += corpBalance;
userCouponBalance -= corpBalance;
corpBalance = 0;
}
}
}
}
// 订单商品总价追加
totalAmount += price;
// 插入订单内商品数据
conn.Insert(ReceiptDetail, trans);
}
// 删除购物车
conn.Execute("UPDATE Tb_ShoppingCar SET IsDelete=1 WHERE Id=@ShoppingId",
new
{
ShoppingId = ReceiptDetail.ShoppingId
}, trans);
}
// 3、更新用户优惠券信息
if (totalUseCouponMoney > 0)
{
conn.Execute("proc_Resources_CouponBalance_Use",
new { UserId = Receipt.UserId, CorpId = corpId, BussId = Receipt.BussId, UseMoney = totalUseCouponMoney }, trans, null, CommandType.StoredProcedure);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(JSONHelper.FromString(false, ex.Message + Environment.NewLine + ex.StackTrace));
}
}
return(new ApiResult(true, new { Id = Receipt.Id, OrderId = Receipt.OrderId }).toJson());
}
