예제 #1
0
        public static void CreateChargeFeePriceRangeByImportFeeIDList(SqlHelper helper, List <int> ImportFeeIDList = null, List <int> RoomFeeIDList = null, int RoomFeeID = 0, int HistoryRoomFeeID = 0)
        {
            string cmdtext    = string.Empty;
            var    parameters = new List <SqlParameter>();

            if (HistoryRoomFeeID > 0 && RoomFeeID > 0)
            {
                RoomFeeHistory.UpdateRoomFeeHistoryID(helper, RoomFeeID: RoomFeeID, HistoryRoomFeeID: HistoryRoomFeeID);
                cmdtext  = "update [ChargeFeePriceRange] set [RoomFeeID]=@RoomFeeID where [RoomFeeID]=@HistoryRoomFeeID;";
                cmdtext += "update [RoomFeeHistory] set [ID]=@RoomFeeID where [ID]=@HistoryRoomFeeID;";
                parameters.Add(new SqlParameter("@RoomFeeID", RoomFeeID));
                parameters.Add(new SqlParameter("@HistoryRoomFeeID", HistoryRoomFeeID));
                helper.Execute(cmdtext, CommandType.Text, parameters);
                return;
            }
            if (ImportFeeIDList == null && RoomFeeIDList == null && RoomFeeID == 0)
            {
                return;
            }
            List <string> conditions = new List <string>();

            conditions.Add("RoomFee.ID not in (select RoomFeeID from [ChargeFeePriceRange])");
            conditions.Add("[RoomFee].ChargeID in (select SummaryID from [ChargePriceRange])");
            if (ImportFeeIDList != null && ImportFeeIDList.Count > 0)
            {
                conditions.Add("[RoomFee].[ImportFeeID] in (" + string.Join(",", ImportFeeIDList.ToArray()) + ")");
            }
            if (RoomFeeIDList != null && RoomFeeIDList.Count > 0)
            {
                conditions.Add("[RoomFee].[ID] in (" + string.Join(",", RoomFeeIDList.ToArray()) + ")");
            }
            if (RoomFeeID > 0)
            {
                conditions.Add("[RoomFee].ID=@RoomFeeID");
                parameters.Add(new SqlParameter("@RoomFeeID", RoomFeeID));
            }
            cmdtext = @"insert into [ChargeFeePriceRange]
           ([RoomFeeID]
           ,[ChargePriceRangeID]
           ,[SummaryID]
           ,[MinValue]
           ,[MaxValue]
           ,[BasePrice]
           ,[BaseType]
           ,[IsActive]
           ,[AddTime])
            select [RoomFee].ID as RoomFeeID,
            [ChargePriceRange].ID as ChargePriceRangeID,
            [ChargePriceRange].SummaryID,
            [ChargePriceRange].MinValue,[ChargePriceRange].MaxValue,
            [ChargePriceRange].BasePrice,
            [ChargePriceRange].BaseType,
            [ChargePriceRange].IsActive,
            getdate()
            from [RoomFee]
            left join [ChargePriceRange] on SummaryID=RoomFee.ChargeID
            where " + string.Join(" and ", conditions.ToArray());
            helper.Execute(cmdtext, CommandType.Text, parameters);
        }
예제 #2
0
        public static RoomFeeHistory[] GetInCorrectRoomFeeHistoryList()
        {
            List <SqlParameter> parameters = new List <SqlParameter>();
            string Statement = "select * from RoomFeeHistory where ID in (Select ID from RoomFee) and ChargeState=1 order by (Select DefaultOrder from Project where ID=RoomFeeHistory.RoomID) asc";

            RoomFeeHistory[] list = new RoomFeeHistory[] { };
            list = GetList <RoomFeeHistory>(Statement, parameters).ToArray();
            return(list);
        }
예제 #3
0
        public static RoomFeeHistory[] GetRoomFeeHistoryListByContractDivideIDList(List <int> DivideIDList)
        {
            List <SqlParameter> parameters = new List <SqlParameter>();
            List <string>       conditions = new List <string>();

            conditions.Add("[ChargeState] in (1,4)");
            if (DivideIDList.Count == 0)
            {
                return(new RoomFeeHistory[] { });
            }
            conditions.Add("isnull(ContractDivideID,0) in (" + string.Join(",", DivideIDList.ToArray()) + ")");
            string Statement = " select * from [RoomFeeHistory] where  " + string.Join(" and ", conditions.ToArray());

            RoomFeeHistory[] list = new RoomFeeHistory[] { };
            list = GetList <RoomFeeHistory>(Statement, parameters).ToArray();
            return(list);
        }
예제 #4
0
        public static RoomFeeHistory GetPreRoomFeeHistory(int RoomFeeID, DateTime StartTime, SqlHelper helper, out RoomFeeHistory nextRoomFeeHistory)
        {
            List <SqlParameter> parameters = new List <SqlParameter>();
            List <string>       conditions = new List <string>();
            string cmdwhere1 = string.Empty;
            string cmdwhere2 = string.Empty;

            conditions.Add("[ID]=@RoomFeeID");
            conditions.Add("[IsCuiShou]=1");
            conditions.Add("[ChargeState]=5");
            if (StartTime > DateTime.MinValue)
            {
                cmdwhere1 = " and [StartTime]<@StartTime";
                cmdwhere2 = " and [StartTime]>@StartTime";
                parameters.Add(new SqlParameter("@StartTime", StartTime));
            }
            parameters.Add(new SqlParameter("@RoomFeeID", RoomFeeID));
            nextRoomFeeHistory = GetOne <RoomFeeHistory>("select top 1 * from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + cmdwhere2 + " order by [StartTime] asc", parameters, helper);
            return(GetOne <RoomFeeHistory>("select top 1 * from [RoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + cmdwhere1 + " order by [StartTime] desc", parameters, helper));
        }
예제 #5
0
        public static ImportFee GetOrCreateImportFeeByID(int ID, SqlHelper helper, bool CanCreate = true)
        {
            var data = ImportFee.GetImportFee(ID, helper);

            if (!CanCreate)
            {
                return(data);
            }
            if (data != null)
            {
                return(data);
            }
            #region  除备份还原
            List <SqlParameter> parameters = new List <SqlParameter>();
            parameters.Add(new SqlParameter("@ID", ID));
            var bak_data = GetOne <ImportFeeBak>("select * from [ImportFeeBak] where [ID] = @ID", parameters, helper);
            if (bak_data != null)
            {
                string cmdtext = string.Empty;
                cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
                cmdtext += @"
                    insert into [ImportFee]
                    ([ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,[AddTime]
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo])
                    select 
                    [ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,getdate()
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo]
                    from [ImportFeeBak] where ID=@ID;";
                cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
                int count = helper.Execute(cmdtext, CommandType.Text, parameters);
                if (count > 0)
                {
                    data = ImportFee.GetImportFee(ID, helper);
                }
            }
            #endregion
            #region 账单明细还原
            if (data == null)
            {
                var roomfee = RoomFee.GetRoomFeeByImportFeeID(ID, helper);
                if (roomfee != null)
                {
                    decimal EndPoint          = roomfee.UseCount > 0 ? roomfee.UseCount : 0;
                    decimal UnitPrice         = roomfee.UnitPrice > 0 ? roomfee.UnitPrice : 0;
                    decimal RealCost          = roomfee.RealCost > 0 ? roomfee.RealCost : 0;
                    string  WriteDate         = "'" + (roomfee.RoomFeeWriteDate > DateTime.MinValue ? roomfee.RoomFeeWriteDate.ToString("yyyy-MM-dd") : DateTime.Now.ToString("yyyy-MM-dd")) + "'";
                    string  StartTime         = roomfee.StartTime > DateTime.MinValue ? "'" + roomfee.StartTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    string  EndTime           = roomfee.EndTime > DateTime.MinValue ? "'" + roomfee.EndTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    decimal ImportCoefficient = roomfee.RoomFeeCoefficient > 0 ? roomfee.RoomFeeCoefficient : 0;
                    string  cmdtext           = string.Empty;
                    cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
                    cmdtext += @"insert into [ImportFee]
                    ([ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,[AddTime]
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo])
                    values( 
                     " + ID + @"
                    ," + roomfee.RoomID + @"
                    ,NULL
                    ," + roomfee.ChargeID + @"
                    ,0
                    ," + EndPoint + @"
                    ," + EndPoint + @"
                    ," + UnitPrice + @"
                    ," + RealCost + @"
                    ," + WriteDate + @"
                    ," + StartTime + @"
                    ," + EndTime + @"
                    ,getdate()
                    ,0
                    ," + ImportCoefficient + @"
                    ,NULL
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    );";
                    cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
                    int count = helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
                    if (count > 0)
                    {
                        data = ImportFee.GetImportFee(ID, helper);
                    }
                }
            }
            #endregion
            #region 历史单据还原
            if (data == null)
            {
                var roomfee = RoomFeeHistory.GetRoomFeeHistoryByImportFeeID(ID, helper);
                if (roomfee != null)
                {
                    decimal EndPoint          = roomfee.UseCount > 0 ? roomfee.UseCount : 0;
                    decimal UnitPrice         = roomfee.UnitPrice > 0 ? roomfee.UnitPrice : 0;
                    decimal RealCost          = roomfee.RealCost > 0 ? roomfee.RealCost : 0;
                    string  WriteDate         = "'" + (roomfee.RoomFeeWriteDate > DateTime.MinValue ? roomfee.RoomFeeWriteDate.ToString("yyyy-MM-dd") : DateTime.Now.ToString("yyyy-MM-dd")) + "'";
                    string  StartTime         = roomfee.StartTime > DateTime.MinValue ? "'" + roomfee.StartTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    string  EndTime           = roomfee.EndTime > DateTime.MinValue ? "'" + roomfee.EndTime.ToString("yyyy-MM-dd") + "'" : "NULL";
                    decimal ImportCoefficient = roomfee.RoomFeeCoefficient > 0 ? roomfee.RoomFeeCoefficient : 0;
                    string  cmdtext           = string.Empty;
                    cmdtext += "SET IDENTITY_INSERT [ImportFee] ON;";
                    cmdtext += @"insert into [ImportFee]
                    ([ID]
                    ,[RoomID]
                    ,[ChargeDate]
                    ,[ChargeID]
                    ,[StartPoint]
                    ,[EndPoint]
                    ,[TotalPoint]
                    ,[UnitPrice]
                    ,[TotalPrice]
                    ,[WriteDate]
                    ,[StartTime]
                    ,[EndTime]
                    ,[AddTime]
                    ,[ChargeStatus]
                    ,[ImportCoefficient]
                    ,[ImportBiaoCategory]
                    ,[ImportBiaoName]
                    ,[ChargeBiaoID]
                    ,[ProjectBiaoID]
                    ,[ImportBiaoGuiGe]
                    ,[ImportRate]
                    ,[ImportReducePoint]
                    ,[ImportChargeRoomNo])
                    values( 
                     " + ID + @"
                    ," + roomfee.RoomID + @"
                    ,NULL
                    ," + roomfee.ChargeID + @"
                    ,0
                    ," + EndPoint + @"
                    ," + EndPoint + @"
                    ," + UnitPrice + @"
                    ," + RealCost + @"
                    ," + WriteDate + @"
                    ," + StartTime + @"
                    ," + EndTime + @"
                    ,getdate()
                    ,0
                    ," + ImportCoefficient + @"
                    ,NULL
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    ,0
                    ,0
                    ,NULL
                    );";
                    cmdtext += @"SET IDENTITY_INSERT [ImportFee] OFF;";
                    int count = helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
                    if (count > 0)
                    {
                        data = ImportFee.GetImportFee(ID, helper);
                    }
                }
            }
            #endregion
            return(data);
        }
예제 #6
0
        public static Ui.DataGrid GetViewContractDivideGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList, int ContractID, bool canexport = false)
        {
            ReSetParams();
            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))
            {
                conditions.Add("([ContractNo] like @Keywords or [ContractName] like @Keywords)");
                parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
            }
            #endregion
            if (StartTime > DateTime.MinValue)
            {
                conditions.Add("[WriteDate]>=@StartTime");
                parameters.Add(new SqlParameter("@StartTime", StartTime));
            }
            if (EndTime > DateTime.MinValue)
            {
                conditions.Add("[WriteDate]<=@EndTime");
                parameters.Add(new SqlParameter("@EndTime", EndTime));
            }
            if (ContractID > 0)
            {
                conditions.Add("[ContractID]=@ContractID");
                parameters.Add(new SqlParameter("@ContractID", ContractID));
            }
            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("[ContractID] 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("[ContractID] in (select [ContractID] from [Contract_Room] where [RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
            }
            string fieldList          = "[ViewContractDivide].*";
            string Statement          = " from [ViewContractDivide] where  " + string.Join(" and ", conditions.ToArray()) + cmd;
            ViewContractDivide[] list = new ViewContractDivide[] { };
            if (canexport)
            {
                list = GetList <ViewContractDivide>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
            }
            else
            {
                list = GetList <ViewContractDivide>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
            }
            if (list.Length > 0)
            {
                var idlist = list.Where(p => p.ChargeStatus != 2).Select(p => p.ID).ToList();
                feelist     = RoomFeeAnalysis.GetRoomFeeAnalysisListByContractDivideIDList(idlist);
                historylist = RoomFeeHistory.GetRoomFeeHistoryListByContractDivideIDList(idlist);
                int MinContractID = list.Min(p => p.ContractID);
                int MaxContractID = list.Max(p => p.ContractID);
                var contractList  = GetList <Contract>("select ID,ContractBasicRentCost from [Contract] where ID between " + MinContractID + " and " + MaxContractID, new List <SqlParameter>()).ToArray();
                foreach (var item in list)
                {
                    var myContract = contractList.FirstOrDefault(p => p.ID == item.ContractID);
                    if (myContract != null)
                    {
                        item.ContractBasicRentCost = myContract.ContractBasicRentCost;
                    }
                }
            }
            DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
            dg.rows  = list;
            dg.total = totalRows;
            dg.page  = pageSize;
            return(dg);
        }