예제 #1
0
        private string GetPaymentHistoryDetail(DataRow row)
        {
            #region 获取参数并简单校验
            string CommID = string.Empty;
            if (!row.Table.Columns.Contains("CommID"))
            {
                return(new ApiResult(false, "小区编码不能为空").toJson());
            }
            CommID = row["CommID"].ToString();
            Tb_Community community = GetCommunity(CommID);
            if (null == community)
            {
                return(new ApiResult(false, "未查找到小区信息").toJson());
            }
            CommID = community.CommID;
            string ReceID = string.Empty;
            if (row.Table.Columns.Contains("OrderSN"))
            {
                ReceID = row["OrderSN"].ToString();
            }
            PubConstant.tw2bsConnectionString  = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
            PubConstant.hmWyglConnectionString = GetConnectionStr(community);
            var chargeConnectionString = Global_Fun.BurstConnectionString(Convert.ToInt32(CommID), Global_Fun.BURST_TYPE_CHARGE);
            #endregion
            using (IDbConnection conn = new SqlConnection(chargeConnectionString))
            {
                dynamic Receipts = conn.QueryFirstOrDefault("SELECT BillType,BillsDate,BillsAmount,ChargeMode FROM view_HSPR_CustomerBillSign_Filter WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID });
                if (null == Receipts)
                {
                    return(new ApiResult(false, "订单不存在").toJson());
                }
                int     BillType    = Convert.ToInt32(Receipts.BillType);
                string  BillsDate   = Convert.ToString(Receipts.BillsDate);
                decimal BillsAmount = Convert.ToDecimal(Receipts.BillsAmount);
                string  ChargeMode  = Convert.ToString(Receipts.ChargeMode);
                Dictionary <string, object>         result       = new Dictionary <string, object>();
                List <Dictionary <string, string> > FeesInfoList = new List <Dictionary <string, string> >();
                if (BillType == 1)
                {
                    List <dynamic> CostList = conn.Query("SELECT CostID,CostName,FeesID,FeesStateDate,FeesEndDate FROM view_HSPR_Fees_SearchFilter WHERE CommID = @CommID AND ISNULL(FeesID, 0) != 0 AND FeesID IN (SELECT FeesID FROM Tb_HSPR_FeesDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID UNION SELECT FeesID FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID)", new { CommID, ReceID }).ToList();
                    if (null == CostList || CostList.Count <= 0)
                    {
                        return(new ApiResult(false, "订单数据错误").toJson());
                    }
                    foreach (var CostGroup in CostList.GroupBy(item => Convert.ToString(item.CostName)))
                    {
                        if (null == CostGroup || CostGroup.Count() == 0)
                        {
                            continue;
                        }
                        #region 分组后,取费用最早的开始时间,和最晚的结束时间
                        DateTime FeesStateDate, FeesEndDate;
                        FeesStateDate = DateTime.MaxValue;
                        FeesEndDate   = DateTime.MinValue;
                        string        CostID     = "";
                        string        CostName   = "";
                        List <string> FeesIDList = new List <string>();
                        foreach (var item in CostGroup)
                        {
                            FeesIDList.Add(Convert.ToString(item.FeesID));
                            if (string.IsNullOrEmpty(CostID))
                            {
                                CostID = Convert.ToString(item.CostID);
                            }
                            if (string.IsNullOrEmpty(CostName))
                            {
                                CostName = Convert.ToString(item.CostName);
                            }
                            if (DateTime.TryParse(Convert.ToString(item.FeesStateDate), out DateTime startTime))
                            {
                                if (FeesStateDate.CompareTo(startTime) > 0)
                                {
                                    FeesStateDate = startTime;
                                }
                            }
                            else
                            {
                                FeesStateDate = DateTime.Now;
                            }

                            if (DateTime.TryParse(Convert.ToString(item.FeesEndDate), out DateTime endTime))
                            {
                                if (FeesEndDate.CompareTo(endTime) < 0)
                                {
                                    FeesEndDate = endTime;
                                }
                            }
                            else
                            {
                                FeesEndDate = DateTime.Now;
                            }
                        }
                        #endregion
                        decimal Amount = conn.QueryFirstOrDefault <decimal>(@"SELECT ISNULL(SUM(ISNULL(Amount,0.00)),0.00) AS Amount FROM (
                                SELECT ISNULL(SUM(ISNULL(ISNULL(ChargeAmount,0.00) + ISNULL(LateFeeAmount,0.00),0.00)),0.00) AS Amount FROM Tb_HSPR_FeesDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID AND FeesID IN @FeesID
                                UNION
                                SELECT ISNULL(SUM(ISNULL(DueAmount,0.00)),0.00) AS Amount FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID AND FeesID IN @FeesID
                            ) AS a", new { ReceID, FeesID = FeesIDList.ToArray() });
                        FeesInfoList.Add(new Dictionary <string, string>
                        {
                            { "CostID", CostID },
                            { "CostName", CostName },
                            { "Amount", Amount.ToString() },
                            { "CostArea", FeesStateDate.ToString("yyyy-MM") + "至" + FeesEndDate.ToString("yyyy-MM") }
                        });
                    }
                }
                if (BillType == 2)
                {
                    dynamic info = conn.QueryFirstOrDefault("SELECT CostID,CostNames,PrecMemo FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID }).ToList();
                    if (null != info)
                    {
                        string CostID     = Convert.ToString(info.CostID);
                        string CostName   = Convert.ToString(info.CostNames);
                        string PrecMemo   = Convert.ToString(info.PrecMemo);
                        string PrecAmount = Convert.ToString(info.PrecAmount);
                        FeesInfoList.Add(new Dictionary <string, string>
                        {
                            { "CostID", CostID },
                            { "CostName", CostName },
                            { "Amount", PrecAmount },
                            { "CostArea", PrecMemo }
                        });
                    }
                }
                string ElectronicInvoice = string.Empty;
                if (conn.QueryFirstOrDefault <long>("SELECT isnull(object_id(N'Tb_HSPR_ElectronicInvoice',N'U'),0)") != 0)
                {
                    ElectronicInvoice = conn.QueryFirstOrDefault <string>("SELECT ImgUrl FROM Tb_HSPR_ElectronicInvoice WHERE CommID = @CommID AND ReceID = @ReceID", new { CommID, ReceID });
                }
                result.Add("Amount", BillsAmount);
                result.Add("Status", "交易成功");
                result.Add("PayType", ChargeMode);
                result.Add("CreateTime", BillsDate);
                result.Add("OrderSN", ReceID);
                result.Add("FeesDetail", FeesInfoList);
                result.Add("ElectronicInvoice", ElectronicInvoice);
                return(new ApiResult(true, result).toJson());
            }
        }
예제 #2
0
        /// <summary>
        /// 获取历史账单列表(显示票据号)
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private string GetPaymentHistory(DataRow row)
        {
            #region 获取参数并简单校验
            string CommID = string.Empty;
            if (!row.Table.Columns.Contains("CommID"))
            {
                return(new ApiResult(false, "小区编码不能为空").toJson());
            }
            CommID = row["CommID"].ToString();
            Tb_Community community = GetCommunity(CommID);
            if (null == community)
            {
                return(new ApiResult(false, "未查找到小区信息").toJson());
            }
            CommID = community.CommID;
            if (!row.Table.Columns.Contains("CustID") || !long.TryParse(row["CustID"].ToString(), out long CustID))
            {
                CustID = 0;
            }
            if (!row.Table.Columns.Contains("RoomID") || !long.TryParse(row["RoomID"].ToString(), out long RoomID))
            {
                RoomID = 0;
            }
            if (!row.Table.Columns.Contains("Page") || !int.TryParse(row["Page"].ToString(), out int Page))
            {
                Page = 1;
            }
            if (Page <= 0)
            {
                Page = 1;
            }
            if (!row.Table.Columns.Contains("Size") || !int.TryParse(row["Size"].ToString(), out int Size))
            {
                Size = 10;
            }
            if (Size <= 0)
            {
                Size = 10;
            }
            int Start = (Page - 1) * Size;
            int End   = Page * Size;

            PubConstant.tw2bsConnectionString  = Global_Fun.Tw2bsConnectionString(Global_Fun.GetNetType(community.DBServer));
            PubConstant.hmWyglConnectionString = GetConnectionStr(community);
            var chargeConnectionString = Global_Fun.BurstConnectionString(Convert.ToInt32(CommID), Global_Fun.BURST_TYPE_CHARGE);
            #endregion
            using (IDbConnection conn = new SqlConnection(chargeConnectionString))
            {
                List <Dictionary <string, object> > resultList = new List <Dictionary <string, object> >();
                DateTime BeginDate = new DateTime(1970, 1, 1);
                #region 查询可查询账单开始时间设置
                try
                {
                    dynamic setting = conn.QueryFirstOrDefault("SELECT ISNULL(IsShow, 1) AS IsShow,BeginDate FROM Tb_HSPR_FeeBundleSettings WHERE ISNULL(IsDelete,0) = 0 AND CommID = @CommID", new { CommID });
                    if (null != setting)
                    {
                        if (Convert.ToInt32(setting.IsShow) == 0)
                        {
                            return(new ApiResult(true, new { Data = resultList, Count = 0, Page = 0 }).toJson());
                        }
                        if (!DateTime.TryParse(Convert.ToString(setting.BeginDate), out BeginDate))
                        {
                            BeginDate = new DateTime(1970, 1, 1);
                        }
                    }
                }
                catch (Exception)
                {
                }
                #endregion
                conn.Execute("Proc_HSPR_CustomerBillSign_Cre", new { CommID, CustID, RoomID }, null, null, CommandType.StoredProcedure);
                int            Count    = conn.QueryFirstOrDefault <int>("SELECT COUNT(ReceID) FROM (SELECT * FROM view_HSPR_CustomerBillSign_Filter) AS a WHERE ISNULL(IsDelete,0) = 0 AND BillType IN (1,2) AND CommID = @CommID AND CustID = @CustID AND RoomID = @RoomID AND BillsDate > @BeginDate", new { CommID, CustID, RoomID, BeginDate });
                int            PageRes  = Count % Size > 0 ? (Count / Size) + 1 : Count / Size;
                int            CountRes = Count;
                List <dynamic> list     = conn.Query("SELECT ReceID,BillsDate,BillsSign,BillsAmount,ChargeMode,BillType FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY BillsDate DESC) AS RowId FROM view_HSPR_CustomerBillSign_Filter WHERE ISNULL(IsDelete,0) = 0 AND BillType IN (1,2) AND CommID = @CommID AND CustID = @CustID AND RoomID = @RoomID AND BillsDate > @BeginDate) AS a WHERE RowId BETWEEN @Start AND @End", new { CommID, CustID, RoomID, Start, End, BeginDate }).ToList();
                if (null != list && list.Count > 0)
                {
                    foreach (dynamic item in list)
                    {
                        try
                        {
                            int     BillType    = Convert.ToInt32(item.BillType);
                            string  ReceID      = Convert.ToString(item.ReceID);
                            string  BillsSign   = Convert.ToString(item.BillsSign);
                            string  BillsDate   = Convert.ToString(item.BillsDate);
                            decimal BillsAmount = Convert.ToDecimal(item.BillsAmount);
                            string  ChargeMode  = Convert.ToString(item.ChargeMode);
                            if (BillType == 1)
                            {
                                #region 实收查实收表,不能查预存
                                // 查询费项名称和区间
                                List <string>  FeesIDList = conn.Query <string>("SELECT FeesID FROM Tb_HSPR_FeesDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID UNION SELECT FeesID FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID }).ToList();
                                List <dynamic> CostList   = conn.Query("SELECT CostName,FeesStateDate,FeesEndDate FROM view_HSPR_Fees_SearchFilter WHERE CommID = @CommID AND ISNULL(FeesID, 0) != 0 AND FeesID IN @FeesIDList", new { CommID, FeesIDList }).ToList();
                                if (null == CostList || CostList.Count <= 0)
                                {
                                    continue;
                                }
                                DateTime FeesStateDate, FeesEndDate;
                                FeesStateDate = DateTime.MaxValue;
                                FeesEndDate   = DateTime.MinValue;
                                HashSet <string> CostNameList = new HashSet <string>();
                                foreach (dynamic costinfo in CostList)
                                {
                                    CostNameList.Add(Convert.ToString(costinfo.CostName));

                                    if (DateTime.TryParse(Convert.ToString(costinfo.FeesStateDate), out DateTime startTime))
                                    {
                                        if (FeesStateDate.CompareTo(startTime) > 0)
                                        {
                                            FeesStateDate = startTime;
                                        }
                                    }
                                    else
                                    {
                                        FeesStateDate = DateTime.Now;
                                    }
                                    if (DateTime.TryParse(Convert.ToString(costinfo.FeesEndDate), out DateTime endTime))
                                    {
                                        if (FeesEndDate.CompareTo(endTime) < 0)
                                        {
                                            FeesEndDate = endTime;
                                        }
                                    }
                                    else
                                    {
                                        FeesEndDate = DateTime.Now;
                                    }
                                }
                                resultList.Add(new Dictionary <string, object>
                                {
                                    { "OrderSN", ReceID },
                                    { "PayChannel", ChargeMode },
                                    { "PayTime", BillsDate },
                                    { "Amount", BillsAmount },
                                    { "BillsSign", BillsSign },
                                    { "CostName", string.Join(",", CostNameList.ToArray()) },
                                    { "CostArea", FeesStateDate.ToString("yyyy.MM.dd") + "-" + FeesEndDate.ToString("yyyy.MM.dd") }
                                });
                                #endregion
                            }
                            if (BillType == 2)
                            {
                                dynamic info = conn.QueryFirstOrDefault("SELECT CostNames,PrecMemo FROM Tb_HSPR_PreCostsDetail WHERE ISNULL(IsDelete,0) = 0 AND ReceID = @ReceID", new { ReceID }).ToList();
                                if (null == info)
                                {
                                    continue;
                                }
                                string CostName = Convert.ToString(info.CostNames);
                                string PrecMemo = Convert.ToString(info.PrecMemo);
                                // 预存查预存的费用信息
                                resultList.Add(new Dictionary <string, object>
                                {
                                    { "OrderSN", ReceID },
                                    { "PayChannel", ChargeMode },
                                    { "PayTime", BillsDate },
                                    { "Amount", BillsAmount },
                                    { "BillsSign", BillsSign },
                                    { "CostName", CostName },
                                    { "CostArea", PrecMemo }
                                });
                            }
                        }
                        catch (Exception)
                        {
                            continue;
                        }
                    }
                }
                return(new ApiResult(true, new { Data = resultList, Count = CountRes, Page = PageRes }).toJson());
            }
        }