Esempio n. 1
0
        public static void GetContractFeeSummary(ContractDetail[] list, out ContractDetail footeritem, bool outfooter = false)
        {
            List <int> ContractIDList   = list.Select(p => p.ID).ToList();
            var        fee_list         = RoomFeeAnalysis.GetRoomFeeAnalysisListByContractIDList(ContractIDList);
            var        feehistory_list  = ViewRoomFeeHistory.GetViewRoomFeeHistoryListByContractIDList(ContractIDList);
            var        fee_weiyue_list  = RoomFeeAnalysis.GetContractFeeWeiYueListByContractIDList(ContractIDList);
            decimal    totalRestCost    = 0;
            decimal    totalChargedCost = 0;
            decimal    totalPreCost     = 0;
            decimal    totalDepositCost = 0;
            decimal    totalBreakCost   = 0;
            decimal    totalTotalCost   = 0;

            foreach (var item in list)
            {
                item.RestCost     = fee_list.Where(p => p.ContractID == item.ID && p.TotalCost > 0).Sum(p => p.TotalCost);
                item.ChargedCost  = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0).Sum(p => p.RealCost);
                item.PreCost      = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0 && p.CategoryID == 4).Sum(p => p.RealCost);
                item.DepositCost  = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0 && p.CategoryID == 3).Sum(p => p.RealCost);
                item.TotalCost    = item.RestCost + item.ChargedCost;
                item.BreakCost    = 0;
                totalRestCost    += item.RestCost;
                totalChargedCost += item.ChargedCost;
                totalPreCost     += item.PreCost;
                totalDepositCost += item.DepositCost;
                totalBreakCost   += fee_weiyue_list.Where(p => p.RelatedFeeID == item.ID && p.TotalCost > 0).Sum(p => p.TotalCost);
                totalTotalCost   += item.TotalCost;
            }
            footeritem             = new ContractDetail();
            footeritem.ContractNo  = "合计";
            footeritem.RestCost    = totalRestCost;
            footeritem.ChargedCost = totalChargedCost;
            footeritem.PreCost     = totalPreCost;
            footeritem.DepositCost = totalDepositCost;
            footeritem.TotalCost   = totalTotalCost;
            footeritem.BreakCost   = totalBreakCost;
        }
Esempio n. 2
0
        public static int GetALLWaringingContractsCount(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
        {
            List <string> conditions = new List <string>();

            if (ProjectIDList.Count > 0)
            {
                List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: false, UserID: UserID);
                conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
            }
            if (RoomIDList.Count > 0)
            {
                List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]");
                conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
            }
            List <SqlParameter> parameters = new List <SqlParameter>();

            parameters.Add(new SqlParameter("@ContractStatus", Utility.EnumModel.ContractStatus.tongguo.ToString()));
            conditions.Add("[ContractStatus]=@ContractStatus");
            var list = GetList <ContractDetail>("select [ID],[ContractStatus],[RentEndTime] from [Contract] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();

            ContractDetail.SetContractWarningCount(list);
            list = list.Where(p => p.IsRentExpire).ToArray();
            return(list.Length);
        }
Esempio n. 3
0
        public static Ui.DataGrid GetContractDetailGridByKeywords(string Keywords, string ContractStatus, List <int> RoomIDList, List <int> ProjectIDList, DateTime StartTime, DateTime EndTime, DateTime RentStartTime, DateTime RentEndTime, string orderBy, long startRowIndex, int pageSize, int onlyexpired = 0, bool canexport = false)
        {
            long totalRows = 0;
            List <SqlParameter> parameters = new List <SqlParameter>();
            List <string>       conditions = new List <string>();

            conditions.Add("1=1");
            #region 关键字查询
            string cmd = string.Empty;
            if (!string.IsNullOrEmpty(Keywords))
            {
                string[] keywords = Keywords.Trim().Split(' ');
                for (int i = 0; i < keywords.Length; i++)
                {
                    if (string.IsNullOrEmpty(keywords[i].ToString()))
                    {
                        continue;
                    }
                    if (i + 1 == keywords.Length)
                    {
                        if (keywords.Length == 1)
                        {
                            cmd += "  and  ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%')";
                        }
                        else
                        {
                            cmd += "  ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%'))";
                        }
                    }
                    else if (i == 0)
                    {
                        cmd += " and (([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%') or";
                    }
                    else
                    {
                        cmd += "  ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%') or ";
                    }
                }
            }
            #endregion
            if (RentStartTime > DateTime.MinValue)
            {
                conditions.Add("[RentEndTime]>=@RentStartTime");
                parameters.Add(new SqlParameter("@RentStartTime", RentStartTime));
            }
            if (RentEndTime > DateTime.MinValue)
            {
                conditions.Add("[RentEndTime]<=@RentEndTime");
                parameters.Add(new SqlParameter("@RentEndTime", RentEndTime));
            }
            if (!string.IsNullOrEmpty(ContractStatus))
            {
                conditions.Add("[ContractStatus]=@ContractStatus");
                parameters.Add(new SqlParameter("@ContractStatus", ContractStatus));
            }
            if (ProjectIDList.Count > 0)
            {
                List <string> cmdlist = new List <string>();
                foreach (var ProjectID in ProjectIDList)
                {
                    cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [ID] =" + ProjectID + ")");
                }
                conditions.Add("ID in (select ContractID from Contract_Room where [RoomID] in (select [ID] from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
            }
            if (RoomIDList.Count > 0)
            {
                conditions.Add("ID in (select ContractID from Contract_Room where [RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
            }
            if (StartTime > DateTime.MinValue)
            {
                conditions.Add("[SignTime]>=@StartTime");
                parameters.Add(new SqlParameter("@StartTime", StartTime));
            }
            if (EndTime > DateTime.MinValue)
            {
                conditions.Add("[SignTime]<=@EndTime");
                parameters.Add(new SqlParameter("@EndTime", EndTime));
            }
            string fieldList = "A.*";
            string Statement = @" from (select *,(select sum(isnull(ContractArea,0)) from [RoomBasic] where ([RoomID] 
in (select [RoomID] from [Contract_RoomCharge] where [ContractID]=[Contract].ID)
or ([RoomID] in (select [RoomID] from [Contract_Room] where [ContractID]=[Contract].ID
and exists(select 1 from [Contract_RoomCharge] where [ContractID]=[Contract].ID and [RoomID]=0)
))
)) as TotalContractArea from [Contract]) A where  " + string.Join(" and ", conditions.ToArray()) + cmd;
            var    list      = GetList <ContractDetail>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
            SetContractWarningCount(list);
            if (onlyexpired == 1)
            {
                string ContractStatusDesc = Utility.EnumModel.ContractStatus.tongguo.ToString();
                list = list.Where(p => p.IsRentExpire).ToArray();
            }
            if (!canexport)
            {
                list = list.Skip((int)startRowIndex).Take(pageSize).ToArray();
            }
            totalRows = list.Length;
            var footeritem = new ContractDetail();
            GetContractFeeSummary(list, out footeritem, outfooter: true);
            DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
            dg.footer = new ContractDetail[] { footeritem };
            dg.rows   = list;
            dg.total  = totalRows;
            dg.page   = pageSize;
            return(dg);
        }
Esempio n. 4
0
        public static void GetContractFeeSummary(ContractDetail[] list)
        {
            ContractDetail footeritem = null;

            GetContractFeeSummary(list, out footeritem, outfooter: true);
        }
Esempio n. 5
0
        public static Ui.DataGrid GetContractGridByKeywords(string Keywords, string ContractStatus, List <int> RoomIDList, List <int> ProjectIDList, bool ShowALL, DateTime StartTime, DateTime EndTime, DateTime RentStartTime, DateTime RentEndTime, string orderBy, long startRowIndex, int pageSize, bool IsDivideOn = false)
        {
            long totalRows = 0;
            List <SqlParameter> parameters = new List <SqlParameter>();
            List <string>       conditions = new List <string>();

            conditions.Add("1=1");
            #region 关键字查询
            string cmd = string.Empty;

            if (!string.IsNullOrEmpty(Keywords))
            {
                string[] keywords = Keywords.Trim().Split(' ');
                for (int i = 0; i < keywords.Length; i++)
                {
                    if (string.IsNullOrEmpty(keywords[i].ToString()))
                    {
                        continue;
                    }
                    if (i + 1 == keywords.Length)
                    {
                        if (keywords.Length == 1)
                        {
                            cmd += "  and  ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%')";
                        }
                        else
                        {
                            cmd += "  ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%'))";
                        }
                    }
                    else if (i == 0)
                    {
                        cmd += " and (([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%') or";
                    }
                    else
                    {
                        cmd += "  ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%') or ";
                    }
                }
            }
            #endregion
            if (RentStartTime > DateTime.MinValue)
            {
                conditions.Add("[RentEndTime]>=@RentStartTime");
                parameters.Add(new SqlParameter("@RentStartTime", RentStartTime));
            }
            if (RentEndTime > DateTime.MinValue)
            {
                conditions.Add("[RentEndTime]<=@RentEndTime");
                parameters.Add(new SqlParameter("@RentEndTime", RentEndTime));
            }
            if (!string.IsNullOrEmpty(ContractStatus))
            {
                conditions.Add("[ContractStatus]=@ContractStatus");
                parameters.Add(new SqlParameter("@ContractStatus", ContractStatus));
            }
            //else
            //{
            //    conditions.Add("[ContractStatus]<>@ContractStatus");
            //    parameters.Add(new SqlParameter("@ContractStatus", Utility.EnumModel.ContractStatus.deleted.ToString()));
            //}
            if (ProjectIDList.Count > 0)
            {
                List <string> cmdlist = new List <string>();
                foreach (var ProjectID in ProjectIDList)
                {
                    cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [ID] =" + ProjectID + ")");
                }
                if (ShowALL)
                {
                    conditions.Add("([ID] in (select [ContractID] from [Contract_Room] where [RoomID] in (select [ID] from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + "))) or not exists (select * from [Contract_Room] where ContractID=[Contract].ID))");
                }
                else
                {
                    conditions.Add("[ID] in (select [ContractID] from [Contract_Room] where [RoomID] in (select [ID] from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
                }
            }
            if (RoomIDList.Count > 0)
            {
                conditions.Add("[ID] in (select [ContractID] from [Contract_Room] where [RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
            }
            if (StartTime > DateTime.MinValue)
            {
                conditions.Add("[SignTime]>=@StartTime");
                parameters.Add(new SqlParameter("@StartTime", StartTime));
            }
            if (EndTime > DateTime.MinValue)
            {
                conditions.Add("[SignTime]<=@EndTime");
                parameters.Add(new SqlParameter("@EndTime", EndTime));
            }
            if (IsDivideOn)
            {
                conditions.Add("[IsDivideOn]=1");
            }
            string fieldList = "[Contract].*";
            string Statement = " from [Contract] where  " + string.Join(" and ", conditions.ToArray()) + cmd;
            var    list      = GetList <ContractDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
            ContractDetail.SetContractWarningCount(list);
            DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
            dg.rows  = list;
            dg.total = totalRows;
            dg.page  = pageSize;
            return(dg);
        }