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);
}
