Пример #1
0
        public static Ui.DataGrid GetViewChargeSummaryGrid(int FeeType, int CategoryID, string SummaryType, int ContractID, string guid, string orderBy, long startRowIndex, int pageSize, string keywords)
        {
            long totalRows = 0;
            List <SqlParameter> parameters = new List <SqlParameter>();
            List <string>       conditions = new List <string>();

            conditions.Add("1=1");
            if (!string.IsNullOrEmpty(keywords))
            {
                conditions.Add("[Name] like @keywords");
                parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
            }
            if (FeeType > 0)
            {
                conditions.Add("[FeeType]=@FeeType");
                parameters.Add(new SqlParameter("@FeeType", FeeType));
            }
            if (CategoryID > 0)
            {
                conditions.Add("[CategoryID]=@CategoryID");
                parameters.Add(new SqlParameter("@CategoryID", CategoryID));
            }
            else if (CategoryID == 0)
            {
                conditions.Add("[CategoryID] not in (select ID from Category)");
            }
            if (ContractID > 0)
            {
                conditions.Add("[ID] in (select [ChargeID] from [Contract_ChargeSummary] where [ContractID]=@ContractID)");
                parameters.Add(new SqlParameter("@ContractID", ContractID));
            }
            else if (!string.IsNullOrEmpty(guid))
            {
                conditions.Add("[ID] in (select [ChargeID] from [Contract_ChargeSummary] where [GUID]=@GUID)");
                parameters.Add(new SqlParameter("@GUID", guid));
            }
            string fieldList = "[ViewChargeSummary].*,'" + SummaryType + "' as [SummaryType] ";
            string Statement = " from [ViewChargeSummary] where  " + string.Join(" and ", conditions.ToArray());

            ViewChargeSummary[] list = new ViewChargeSummary[] { };
            list = GetList <ViewChargeSummary>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
            DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
            dg.rows  = list;
            dg.total = totalRows;
            dg.page  = pageSize;
            return(dg);
        }
Пример #2
0
 public static bool InsertRoomFee(List <int> ProjectIDList, List <int> RoomIDList, ViewChargeSummary summary, string StartTime, string EndTime, int IsCycleFee = 0)
 {
     return(InsertRoomFee(ProjectIDList, RoomIDList, StartTime, EndTime, 0, summary.SummaryUnitPrice, string.Empty, summary, string.Empty, includenopeople: true, IsCycleFee: IsCycleFee));
 }
Пример #3
0
        public static bool InsertRoomFee(List <int> ProjectIDList, List <int> RoomIDList, string StartTime, string EndTime, int ContractID, decimal UnitPrice, string Remark, ViewChargeSummary summary, string NewEndTime, decimal TotalCost = 0, decimal UseCount = 0, int Contract_RoomChargeID = 0, bool includenopeople = true, int IsCycleFee = 0)
        {
            string sqltext = string.Empty;
            List <SqlParameter> parameters = new List <SqlParameter>();

            if (string.IsNullOrEmpty(StartTime))
            {
                StartTime = summary.SummaryStartTime == DateTime.MinValue ? "NULL" : "'" + summary.SummaryStartTime.ToString("yyyy-MM-dd") + "'";
            }
            else
            {
                StartTime = "'" + StartTime + "'";
            }
            if (string.IsNullOrEmpty(EndTime))
            {
                EndTime = "NULL";
            }
            else
            {
                EndTime = "'" + EndTime + "'";
            }
            if (string.IsNullOrEmpty(NewEndTime))
            {
                NewEndTime = "NULL";
            }
            else
            {
                NewEndTime = "'" + NewEndTime + "'";
            }
            TotalCost = TotalCost > 0 ? TotalCost : 0;
            UseCount  = UseCount > 0 ? UseCount : 0;
            string AddUserName = "******" + User.GetCurrentUserName() + "'";

            if (RoomIDList.Contains(0))
            {
                sqltext += @"INSERT INTO [dbo].[RoomFee]
               ([RoomID]
               ,[UseCount]
               ,[StartTime]
               ,[EndTime]
               ,[NewEndTime]
               ,[Cost]
               ,[Remark]
               ,[AddTime]
               ,[IsCharged]
               ,[ChargeFeeID]
               ,[ChargeID]
               ,[IsStart]
               ,[UnitPrice]
               ,[ChargeFee]
               ,[ContractID]
               ,[Contract_RoomChargeID]
               ,[IsCycleFee]
               ,[AddUserName])
                values(
                0," + UseCount + "," + StartTime + "," + EndTime + "," + NewEndTime + "," + TotalCost + ",'" + Remark + @"',getdate(),0,0," + summary.ID + ",1,'" + UnitPrice + "',0," + ContractID + "," + Contract_RoomChargeID + "," + IsCycleFee + "," + AddUserName + ");";
                RoomIDList.Remove(0);
            }
            List <string> conditions = new List <string>();

            conditions.Add("[isParent]=0");
            conditions.Add("isnull([IsLocked],0)=0");
            if (!includenopeople && new Utility.SiteConfig().CheckOwnerInStatus)
            {
                List <string> cmdlist = new List <string>();
                cmdlist.Add("[ID] in (select [RoomID] from [RoomPhoneRelation])");
                cmdlist.Add("[ID] in (select [RoomID] from [RoomRelation] where [GUID] in (select [GUID] from [RoomRelation] where [RoomID] in (select [RoomID] from [RoomPhoneRelation])))");
                conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
            }
            if (ProjectIDList.Count > 0)
            {
                List <string> cmdlist = new List <string>();
                cmdlist.Add("[ID] in (" + string.Join(",", ProjectIDList.ToArray()) + ")");
                foreach (var ProjectID in ProjectIDList)
                {
                    cmdlist.Add("[AllParentID] like '%," + ProjectID + ",%'");
                }
                conditions.Add("((" + string.Join(" or ", cmdlist.ToArray()) + "))");
            }
            if (RoomIDList.Count > 0)
            {
                conditions.Add("[ID] in (" + string.Join(",", RoomIDList.ToArray()) + ")");
            }
            string cmdcolumn = ",(select [IsLocked] from [RoomBasic] where [RoomID]=[Project].ID) as IsLocked";

            if (ContractID > 0)
            {
                cmdcolumn += ",(select top 1 ID from [RoomPhoneRelation] where [RoomID]=[Project].ID and [ContractID]=@ContractID) as RelationID,(select top 1 RelationName from [RoomPhoneRelation] where [RoomID]=[Project].ID and [ContractID]=@ContractID) as RelationName";
                parameters.Add(new SqlParameter("@ContractID", ContractID));
            }
            else
            {
                cmdcolumn += ",(select top 1 ID from [RoomPhoneRelation] where [RoomID]=[Project].ID and IsDefault = 1) as RelationID,(select top 1 RelationName from [RoomPhoneRelation] where [RoomID]=[Project].ID and IsDefault = 1) as RelationName";
            }
            if (RoomIDList.Count > 0 || ProjectIDList.Count > 0)
            {
                sqltext += @"INSERT INTO [dbo].[RoomFee]
               ([RoomID]
               ,[UseCount]
               ,[StartTime]
               ,[EndTime]
               ,[NewEndTime]
               ,[Cost]
               ,[Remark]
               ,[AddTime]
               ,[IsCharged]
               ,[ChargeFeeID]
               ,[ChargeID]
               ,[IsStart]
               ,[UnitPrice]
               ,[ChargeFee]
               ,[ContractID]
               ,[Contract_RoomChargeID]
               ,[IsCycleFee]
               ,[AddUserName]
               ,[DefaultChargeManID]
               ,[DefaultChargeManName])
                select [ID]," + UseCount + "," + StartTime + "," + EndTime + "," + NewEndTime + "," + TotalCost + ",'" + Remark + @"',getdate(),0,0," + summary.ID + ",1,'" + UnitPrice + "',0," + ContractID + "," + Contract_RoomChargeID + "," + IsCycleFee + "," + AddUserName + ",A.RelationID,A.RelationName from (select [ID],[AllParentID],[isParent]" + cmdcolumn + " from [Project])A where " + string.Join(" and ", conditions.ToArray()) + ";";
            }
            if (!string.IsNullOrEmpty(sqltext))
            {
                using (SqlHelper helper = new SqlHelper())
                {
                    try
                    {
                        helper.BeginTransaction();
                        helper.Execute(sqltext, CommandType.Text, parameters);
                        helper.Commit();
                        return(true);
                    }
                    catch (Exception)
                    {
                        helper.Rollback();
                        return(false);
                    }
                }
            }
            return(false);
        }