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