/// <summary>
/// 获取平台公告
/// </summary>
private string GetAnnouncement(DataRow row, string mac)
{
int TopNum = 2;
string CommunityId = "";
if (row.Table.Columns.Contains("TopNum"))
{
TopNum = AppGlobal.StrToInt(row["TopNum"].ToString());
}
if (!row.Table.Columns.Contains("CommunityId"))
{
return(JSONHelper.FromString(new DataTable()));
}
else
{
CommunityId = row["CommunityId"].ToString();
}
string sql = @"SELECT top " + TopNum + @" A.Id as [InfoID],UserId,A.NoticeType,A.Title AS [Heading],CAST(A.Content AS VARCHAR(MAX)) as Content,A.IssueDate, (SELECT '[' + T.CommName + ']' FROM dbo.Tb_Community T WHERE CHARINDEX(T.Id, A.CommunityId)> 0 FOR XML PATH('')) AS CommName, A.CommunityId,
A.IsDelete,A.ImageURL as ImageUrl ,A.ContentURL FROM Tb_Notice A
LEFT OUTER JOIN dbo.Tb_Community B ON CHARINDEX(B.Id, A.CommunityId)> 0
WHERE 1 = 1 and isnull(A.IsDelete,0) = 0 AND b.Id = '" + CommunityId + @"' and isnull(a.Noticetype,0) = '1'
GROUP BY A.Id,UserId,A.NoticeType,A.Title,CAST(A.Content AS VARCHAR(MAX)),A.IssueDate,
A.CommunityId,
A.IsDelete,A.ImageURL,A.ContentURL order by a.IssueDate desc" ;
DataTable dTable = new DbHelperSQLP(PubConstant.UnifiedContionString.ToString()).Query(string.Format(sql)).Tables[0];
if (dTable.Rows.Count > 0)
{
return(JSONHelper.FromString(dTable));
}
else
{
dTable.Dispose();
dTable = new DataTable();
return(JSONHelper.FromString(dTable));
}
}
public bool CalcCostStanSettingAmountWithMonths(string CommID, string CustID, string RoomID, string CostID, string StanID, string HandID, int months, out decimal totalAmount)
{
totalAmount = 0;
using (IDbConnection conn = new SqlConnection(PubConstant.hmWyglConnectionString))
{
// 计算费用开始时间
string beginDateStr = conn.Query <string>("Proc_HSPR_Fees_CalcBeginDateFilter", new
{
CommID = CommID,
CustID = CustID,
RoomID = RoomID,
CostID = CostID,
HandID = HandID
}, null, false, null, CommandType.StoredProcedure).FirstOrDefault();
if (string.IsNullOrEmpty(beginDateStr))
{
beginDateStr = DateTime.Now.ToString();
}
DateTime FeesStateDate = Convert.ToDateTime(beginDateStr);
DateTime FeesEndDate = FeesStateDate.AddMonths(months);
//// 计算应缴费用
//dynamic info = conn.Query("Proc_HSPR_Fees_CalcAmount", new
//{
// CommID = CommID,
// CustID = CustID,
// RoomID = RoomID,
// HandID = HandID,
// CostID = CostID,
// StanID = StanID,
// FeesStateDate = FeesStateDate,
// FeesEndDate = FeesEndDate,
// Amount = 0,
// Amount2 = 0
//}, null, false, null, CommandType.StoredProcedure).FirstOrDefault();
//if (null == info)
//{
// return false;
//}
//totalAmount = info.DueAmount;
//return true;
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" and CommID={0}", CommID);
sb.AppendFormat(" and CustID={0}", CustID);
sb.AppendFormat(" and RoomID={0}", RoomID);
sb.AppendFormat(" and CostID={0}", CostID);
SqlParameter[] parameters =
{
new SqlParameter("@SQLEx", SqlDbType.VarChar, 255)
};
parameters[0].Value = sb.ToString();
DataSet Ds = new DbHelperSQLP(PubConstant.hmWyglConnectionString).RunProcedure("Proc_HSPR_CostStanSetting_Filter", parameters, "RetDataSet");
if (Ds == null || Ds.Tables.Count <= 0)
{
return(false);
}
try
{
DateTime date1 = FeesStateDate;
DateTime date2 = FeesEndDate;
date2 = date2.AddMonths(1).AddDays(-1);
while (date1 < date2)
{
SqlParameter[] paramete =
{
new SqlParameter("@CommID", SqlDbType.Int),
new SqlParameter("@CustID", SqlDbType.BigInt),
new SqlParameter("@RoomID", SqlDbType.BigInt),
new SqlParameter("@HandID", SqlDbType.BigInt),
new SqlParameter("@CostID", SqlDbType.BigInt),
new SqlParameter("@StanID", SqlDbType.BigInt),
new SqlParameter("@FeesStateDate", SqlDbType.DateTime),
new SqlParameter("@FeesEndDate", SqlDbType.DateTime),
new SqlParameter("@Amount", SqlDbType.Decimal),
new SqlParameter("@Amount2", SqlDbType.Decimal)
};
paramete[0].Value = CommID;
paramete[1].Value = CustID;
paramete[2].Value = RoomID;
paramete[3].Value = 0;
paramete[4].Value = CostID;
paramete[5].Value = StanID;
paramete[6].Value = date1;
paramete[7].Value = date1.AddMonths(months).AddDays(-1);
paramete[8].Value = 0;
paramete[9].Value = 1;
DataTable dTableCalc = new DbHelperSQLP(PubConstant.hmWyglConnectionString).RunProcedure("Proc_HSPR_Fees_CalcAmount", paramete, "RetDataSet").Tables[0];
if (dTableCalc.Rows.Count > 0)
{
DataRow DRowCalc = dTableCalc.Rows[0];
totalAmount = AppGlobal.StrToDec(DRowCalc["DueAmount"].ToString());
dTableCalc.Dispose();
return(true);
}
dTableCalc.Dispose();
date1 = date1.AddMonths(1);
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}
}