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