public DataTable getContrattiConProblemi()
{
string query = @"--select * from tablename where oldtable='service'
--select * from colname where oldcol='rec770kind'
--più di un parasubcontract non conguagliato per lo stesso creditore
select distinct ce1.idcon
from payroll ce1
join parasubcontract co1 on ce1.flagbalance='S' and ce1.idcon=co1.idcon and ce1.disbursementdate is null and fiscalyear=" + GetSys("esercizio") + @"
join payroll ce2 on ce2.flagbalance='S' and ce1.idpayroll<>ce2.idpayroll and ce2.disbursementdate is null and ce2.fiscalyear=ce1.fiscalyear
join parasubcontract co2 on ce2.idcon=co2.idcon and co1.idreg=co2.idreg
union
--contratti con cud che puntano allo stesso altro parasubcontract
select cud1.idcon
from exhibitedcud cud1
join parasubcontractyear on parasubcontractyear.idcon=cud1.idcon and ayear=" + QHS.quote(GetSys("esercizio")) + @"
join exhibitedcud cud2
on cud1.idlinkedcon=cud2.idlinkedcon and (cud1.idcon<>cud2.idcon or cud1.idexhibitedcud<>cud2.idexhibitedcud)
union
--contratti che non puntano ad un parasubcontract precedente
select c1.idcon
from parasubcontract c1
join parasubcontractyear ic1 on c1.idcon=ic1.idcon and ic1.ayear=" + QHS.quote(GetSys("esercizio")) + @"
join payroll on flagbalance='S' and disbursementdate is not null and fiscalyear=ic1.ayear and payroll.idcon<>c1.idcon
JOIN service ON service.idser = c1.idser
join parasubcontract c2 on c2.idcon=payroll.idcon and c2.idreg=c1.idreg
where not exists (select * from exhibitedcud where exhibitedcud.idlinkedcon=payroll.idcon)
AND ISNULL(service.certificatekind,'') = 'U'
and (not exists (select * from payroll c2 where c2.idcon=c1.idcon and flagbalance='S' and disbursementdate is not null)
or exists (select * from exhibitedcud cp where cp.idcon=c1.idcon))
union
--contratti conguagliati che hanno cud che non puntano a nulla
select distinct exhibitedcud.idcon
from exhibitedcud
where idlinkedcon is null and exhibitedcud.fiscalyear=" + QHS.quote(GetSys("esercizio")) + @"
and (cfotherdeputy is null or cfotherdeputy=
(select cf from license))
union
--contratti dello stesso creditore tutti conguagliati e senza cud
select c1.idcon from parasubcontract c1
join parasubcontractyear i1 on c1.idcon=i1.idcon and ayear=" + QHS.quote(GetSys("esercizio")) + @"
where not exists (
select * from parasubcontract c2 join payroll
on payroll.idcon=c2.idcon and payroll.fiscalyear=i1.ayear and c2.idreg=c1.idreg and payroll.flagbalance='S'
and (payroll.disbursementdate is null or exists (select * from exhibitedcud where exhibitedcud.idcon=c2.idcon))
) and (select count(*) from parasubcontract c3 join parasubcontractyear i3 on c3.idcon=i3.idcon and i3.ayear=i1.ayear and c3.idreg=c1.idreg)>1
union
--contratti di creditori che erano stati pagati col wizard
select distinct idcon from parasubcontract
join expense on parasubcontract.idreg=expense.idreg
join service on expense.idser=service.idser and rec770kind='G'
join expenseyear on expense.idexp=expenseyear.idexp and expenseyear.ayear=" + GetSys("esercizio") + @"
join expensetax on expense.idexp=expensetax.idexp
and not exists (select * from expensepayroll where expense.idexp like idexp+'%')" ;
return(Conn.SQLRunner(query));
}
