public bool AddExpenseLineConfirmHistory(string expenseCode = "") { var sql = $@" ----项目费用 WITH etcExpMain AS ( SELECT p.CostDepartID,e.CRMProjectCode,p.ProjectName,p.ProjectType ,e.ExpenseCode,e.EHRCompanyCode,e.EHRCompanyName FROM dbo.Expense e LEFT JOIN Project p ON e.CRMProjectCode = p.ProjectCode WHERE e.CRMProjectCode IS NOT NULL UNION ALL --部门费用 SELECT e.CRMDepartID,NULL,NULL,NULL ,e.ExpenseCode,e.EHRCompanyCode,e.EHRCompanyName FROM dbo.Expense e WHERE e.CRMProjectCode IS NULL AND e.CRMDepartID IS NOT NULL ) INSERT INTO [dbo].[ExpenseLineConfirmHistory] ([ExpenseCode],[ExpenseLineNum],[DepartID],[DepartNamePath] ,[ConfirmDate],[Period],[Amount] ,[Status],[MaterialClassCode],[MaterialClassName],[MaterialCode],[MaterialName] ,[ExpenseItem],[ExpenseItemName] ,[ProjectCode],[ProjectName],[ProjectType],[CompanyCode],[CompanyName] ,[CreateTime],[CreateUserID],[CreateUserName],[LastUpdateTime],[LastUpdateUserID],[LastUpdateUserName]) SELECT main.ExpenseCode,exLine.LineID AS ExpenseLineNum, dv.DepartID AS DepartId,dv.NamePath AS DepartNamePath ,exLine.ConfirmDate,convert(varchar(7), CONVERT(DATE,exLine.ConfirmDate) ,120) AS Period,exLine.Amount ,exLine.Status,ma.ProductCatID AS MaterialClassCode,ma.Name AS MaterialClassName, vp.MaterialCode,exLine.MaterialName ,exLine.ExpenseItem,exLine.ExpenseItemName ,main.CRMProjectCode AS ProjectCode,main.ProjectName,main.ProjectType,main.EHRCompanyCode AS CompanyCode, main.EHRCompanyName AS CompanyName ,GETDATE(),NULL,NULL,NULL,NULL,NULL FROM etcExpMain main LEFT JOIN dbo.ExpenseLineStatement exLine ON exLine.ExpenseCode = main.ExpenseCode AND exLine.Status=0 LEFT JOIN dbo.DepartMentVersion dv ON (dv.DepartID=main.CostDepartID OR dv.HRDepartID=main.CostDepartID) AND (main.CostDepartID !='') AND dv.Period= CONVERT(varchar(7), DATEADD(MONTH,-1,GETDATE()),120) LEFT JOIN dbo.v_MaterialAll vp ON vp.MaterialCode=exLine.MaterialCode AND vp.Status = 0 LEFT JOIN AC2010..v_ProductCategory ma ON ma.ProductCatID=vp.MaterialClassCode "; if (!string.IsNullOrEmpty(expenseCode)) { sql += $@" WHERE main.ExpenseCode='{expenseCode}' AND dv.Period= CONVERT(varchar(7), DATEADD(MONTH,-1,GETDATE()),120) AND exLine.Status = 0"; } else { sql += $@" WHERE convert(varchar(7), CONVERT(DATE,exLine.ConfirmDate) ,120)=CONVERT(varchar(7), DATEADD(MONTH,-1,GETDATE()),120) AND dv.Period= CONVERT(varchar(7), DATEADD(MONTH,-1,GETDATE()),120) AND exLine.Status = 0"; } var b = _expenseHistoryRepository.ExecuteSql(sql); return(b); }