public List <CommonSplitDto> GetCommonSplit(string sourceDepartId, int month, int year) { var sql = $@" SELECT DISTINCT sp.SourceDepartID AS SourceDepartID ,cc.TargetDepart AS FromDepartID ,vaFrom.NamePath AS FromDepartName ,sp.DepartID AS ToDepartID ,vaTo.NamePath AS ToDepartName , sp.SplitRate FROM dbo.CommonCastVersion cc INNER JOIN BudgetTargetLeaderCostSplitVersion sp ON cc.TargetCode=sp.TargetCode AND sp.Month=@month AND sp.Year=@year LEFT JOIN dbo.DepartmentVersion vaFrom ON vaFrom.DepartID=cc.TargetDepart AND vaFrom.Month=@month AND vaFrom.Year=@year LEFT JOIN dbo.DepartmentVersion vaTo ON vaTo.DepartID=sp.DepartID AND vaTo.Month=@month AND vaTo.Year=@year WHERE cc.IsDelete=0 AND cc.Month=@month AND cc.Year=@year AND sp.SourceDepartID=@SourceDepartID AND (SELECT COUNT(*) FROM dbo.DepartMentVersion WHERE month=@month AND year=@year and DepartID=sp.DepartID AND Pid=cc.TargetDepart AND Status=1)=0 "; //圈定一定范围 var models = _costSplitVersionRepository.QueryBySql <CommonSplitDto>(sql, new { SourceDepartID = sourceDepartId, month, year }).ToList(); var ss2 = models.Where(s => s.FromDepartID == sourceDepartId && s.SplitRate != 0).ToList(); var yy = new List <CommonSplitDto>(); tt(models, ss2, yy); return(yy); }
public bool AddPurchaseRequirementDeptShareRatioVersionRepository(string poCode) { var sql = $@" SELECT distinct po.POCode ,po.Period,po.POLineNum ,pr.RequirementCode as PRCode ,pe.ExecutionCode as PECode ,prDR.DeptID as DepartId ,(select NamePath from v_DepartMent where DepartID = prDR.DeptID) as DepartNamePath ,ISNULL(prDR.ShareProportion,0) ShareProportion FROM CRM2009.dbo.PurchaseOrderLineConfirmHistory po inner JOIN CRM2009.dbo.PurchaseExecution pe ON pe.POCode=po.POCode and pe.Status=0 inner JOIN CRM2009.dbo.PurchaseRequirement pr ON pr.RequirementCode=pe.RequireCode and pr.status = 0 inner JOIN CRM2009.dbo.PurchaseRequirementDeptShareRatio prDR ON prdr.PRCode=pe.RequireCode AND prDR.State=0 WHERE 1=1 --AND po.POCode='PO0102878' AND po.Period= convert(varchar(7), DATEADD(MONTH,-1,GETDATE()),120)--上个月 "; var entities = _purchaseRequirementDeptShareRatioVersionRepository.QueryBySql <PurchaseRequirementDeptShareRatioVersion>(sql, param: null).ToList(); if (!string.IsNullOrEmpty(poCode)) { entities = entities.Where(s => s.POCode == poCode).ToList(); } var num = _purchaseRequirementDeptShareRatioVersionRepository.Add(entities); return(num > 0); }
public PagedResultDto <SaleAchievementListOutPut> GePageList(SaleQueryInput query) { //var dt = DateTime.Now; //var cou = _saleAchDapperRepository.Count(s => s.CreateTime < dt); //var saleAchievements = _saleAchDapperRepository.GetAllPaged(s => s.CreateTime < dt, query.CurrentPage - 1, query.PageSize, false, t => t.CreateTime); //return new PagedResultDto<SaleAchievement>(Convert.ToInt32(cou), saleAchievements.ToList()); #region 原有Sql var originalSql = @" select s.*,ui.TrueName as SaleManName,ui1.TrueName as CreateUserName,ui2.TrueName as LastUpdateUserName, ci.CustName, p.ProjectName, a.FileName, a.FilePath from SaleAchievement s LEFT JOIN v_CustsAndFriends ci on ci.CustID=s.CustID LEFT JOIN Project p on p.ProjectCode =s.ProjectID left join AttachFile a on s.FileID = a.RecID LEFT JOIN v_userinfo ui on ui.UserID=s.SaleManID LEFT JOIN v_userinfo ui1 on ui1.UserID=s.CreateUserID LEFT JOIN v_userinfo ui2 on ui2.UserID=s.[LastUpdateUserID] where s.ID like '%'+'" + IsNullorEmpty(query.ID) + "'+'%' and s.SaleManID like '" + IsNullorEmpty(query.SaleManID) + "' and s.CustID like '" + IsNullorEmpty(query.CustID) + "' and s.ProjectID like '" + IsNullorEmpty(query.ProjectID) + "' and ui1.TrueName like '%'+'" + IsNullorEmpty(query.CreateUserName) + "'+'%' and ui2.TrueName like '%'+'" + IsNullorEmpty(query.LastUpdateUserName) + "'+'%'"; if (!String.IsNullOrEmpty(query.AchievementStartTime)) { originalSql += " and s.AchievementStartTime = '" + IsNullorEmpty(query.AchievementStartTime) + "' "; } if (!String.IsNullOrEmpty(query.AchievementEndTime)) { originalSql += " and s.AchievementEndTime = '" + IsNullorEmpty(query.AchievementEndTime) + "' "; } #endregion var count = _saleAchDapperRepository.QueryBySql <int>($"select count(1) as count from ({originalSql}) tem ", null).FirstOrDefault(); var pagerSql = GetPagerSql(originalSql, "CustID", query.CurrentPage, query.PageSize); var models = _saleAchDapperRepository.QueryBySql <SaleAchievementListOutPut>(pagerSql, null); return(new PagedResultDto <SaleAchievementListOutPut>(Convert.ToInt32(count), models.ToList())); }
/// <summary> /// 获取一级菜单 /// </summary> /// <param name="userId">用户ID</param> /// <param name="sysId">系统ID</param> /// <returns></returns> public List <NavigationInfo> GetParentModuleInfoByUserId(int userId, string sysId) { string sqlstr = $@" select moduleinfo.* ,DomainInfo.Domain FROM SysRightsManager.dbo.moduleinfo LEFT JOIN SysRightsManager.dbo.DomainInfo on moduleinfo.DomainCode=DomainInfo.DomainCode where moduleinfo.moduleid in (select distinct pid from moduleinfo where moduleid in(SELECT DISTINCT moduleid FROM RoleModule WHERE Status = 0 and sysid=@sysId and roleid IN(select roleid from userrole where userid=@userId and sysid=@sysId and status=0)) and status=0 and sysid=@sysId and [level]=2) and moduleinfo.status=0 and moduleinfo.sysid=@sysId order by moduleinfo.OrderNum"; var navigationInfos = _navigationDapperRepository.QueryBySql <NavigationInfo>(sqlstr, new { userId = userId, sysId = sysId }).ToList(); return(navigationInfos); }