lily的操作

来源：互联网发布：php反射机制编辑：程序博客网时间：2024/05/16 09:26

1.按照用户报表的格式创建一张excel报表，然后起类型设置为xml

2.修改lily的配置文件如下：

<?xml version="1.0" encoding="utf-8" ?>

- <Lily>

- <DataProviderPrototypes>

</DataProviderPrototypes>

- <Connections>

<Connection alias="YZXT">user id=yzxt_mes_80;password=yzxt;Data Source=ORADB</Connection>

<Connection alias="lims">data source=yzlims;user id=lims;password = lims</Connection>

</Connections>

- <DataProviders>

+ <DataProvider alias="ORADB" prototype="SqlDataProvider">

+ <DataProvider alias="lims" prototype="SqlDataProvider">

- <DataProvider alias="EquipRunning" prototype="SqlDataProvider">

</DataProvider>

- <DataProvider alias="DataSet" prototype="AssemblyDataProvider">

</DataProvider>

- <DataProvider alias="Grid" prototype="AssemblyDataProvider">

</DataProvider>

- <DataProvider alias="LIMSService" prototype="AssemblyDataProvider">

</DataProvider>

</DataProviders>

</Lily>

查询文件有四种：

1..sql查询其对应的配置为<DataProvider alias="ORADB" prototype="SqlDataProvider">

</DataProvider>

下面是查询文件

<?xml version="1.0" encoding="utf-8" ?>

- <SqlEntitySet>

- <SqlEntity id="GetProductPaperTagInfo" type="Text">

<Command>select 物料, 装置,tag 位号 from (select m.mat 物料, e.equipname 装置, e.innerid eid,m.innerid mid,m.sort from productpaper_mat m , productpaper_equipment e order by m.sort,e.innerid asc) t left join productpaper_mat_config c on c.equipid=t.eid and t.mid=c.matid order by t.sort,t.eid asc</Command>

</SqlEntity>

- <SqlEntity id="SetProductPaperTagInfo" type="Text">

<Command>MERGE INTO productpaper_mat_config p USING (select (select innerid from productpaper_mat where mat=:pmat) matid, (select innerid from PRODUCTPAPER_EQUIPMENT where equipname=:pequip) equipid, :ptag tag from dual)t ON (p.matid = t.matid and p.equipid=t.equipid) WHEN MATCHED THEN UPDATE SET p.tag = t.tag WHEN NOT MATCHED THEN INSERT (matid,equipid,tag) VALUES (t.matid,t.equipid,t.tag)</Command>

</SqlEntity>

- <SqlEntity id="GetProductDayReportInfo" type="Text">

<Command>select t.mat 物料,t.equipid 装置,sum.mouthsum 月累计,sum.dayavgsum 日均,sum.weekavgsum 七天日均,sum.yearsum 年累计,t.value 日累计 from (select m.mat,c.equipid,m.innerid,m.sort, ( case when e.value is null then ( select sum(sumvalue) from instrument_sum where tag=c.tag and begintime>=to_date(to_char(:Pbegtime,'yyyy-MM-dd')||' 10:00:00','yyyy-MM-dd HH24:mi:ss')-1 and endtime<=to_date(to_char(:Pendtime,'yyyy-MM-dd')||' 22:00:00','yyyy-MM-dd HH24:mi:ss')) else e.value end ) value from productpaper_mat m left join productpaper_mat_config c on m.innerid=c.matid left join productpaper_equipmentamount e on e.equitname=c.tag ) t left join (select s.mouthsum,s.dayavgsum,s.weekavgsum,s.yearsum,s.matid,s.productdate from productpaper_counting s) sum on t.innerid=sum.matid and sum.productdate>=to_date(to_char(:Pbegtime,'yyyy-MM-dd')||' 10:00:00','yyyy-MM-dd HH24:mi:ss')-1 and sum.productdate<=to_date(to_char(:Pendtime,'yyyy-MM-dd')||' 22:00:00','yyyy-MM-dd HH24:mi:ss') order by t.sort,t.equipid asc</Command>

</SqlEntity>

- <SqlEntity id="GetProductDayReportInfo1" type="Text">

<Command>select t.equipid 装置,t.sort 值,t.mat 物料 from (select m.mat,c.equipid,m.innerid,m.sort from productpaper_mat m left join productpaper_mat_config c on m.innerid=c.matid left join productpaper_equipmentamount e on e.equitname=c.tag ) t order by t.sort,t.equipid asc</Command>

</SqlEntity>

- <SqlEntity id="GetProductDayTankReport" type="Text">

<Command>select a.name||'('||a.remark||')' 罐号,p.heighttag 库存 from area a left join PHYTANK p on a.ID=p.id where a.type='PHYTANK'</Command>

</SqlEntity>

- <SqlEntity id="GetProductDayReportTank" type="Text">

<Command>SELECT C.stocksvalue,(C.stocksvalue-R.stocksvalue) FROM (select stocksvalue from PRODUCTMONING_TANK_RESULT R INNER JOIN AREA A ON R.phytankid=A.ID AND A.type='PHYTANK' where to_char(R.producttime,'yyyy-mm-dd')=to_char(:producttime,'yyyy-mm-dd') and A.NAME=:NAME) C, ( select stocksvalue from PRODUCTMONING_TANK_RESULT R INNER JOIN AREA A ON R.phytankid=A.ID AND A.type='PHYTANK' where to_char(R.producttime,'yyyy-mm-dd')=to_char(:producttime-1/24,'yyyy-mm-dd') and A.NAME=:NAME) R</Command>

</SqlEntity>

- <SqlEntity id="GetProductDayReportRunningTemValue" type="Text">

<Command>select TEMvalue from AREA u left join PRODUCTMONING_RUNNING_RESULT e on U.ID=E.MEASUREPOINTID AND to_char(E.PRODUCTTIME,'yyyy-mm-dd')=to_char(:PRODUCTTIME,'yyyy-mm-dd') where u.NAME=:unit and u.type='UNIT'</Command>

</SqlEntity>

- <SqlEntity id="GetProductDayReportElseValue" type="Text">

<Command>select r.value from productmoning_config c left join productmoning_result r on c.innerid=r.parentid and to_char(r.producttime,'yyyy-mm-dd')=to_char(:producttime,'yyyy-mm-dd') WHERE c.project=:project and (c.samplepoint=:samplepoint OR c.SAMPLEPOINT is null) and c.type=:type</Command>

</SqlEntity>

- <SqlEntity id="GetProductPaperLog" type="Text">

<Command>SELECT LOG FROM PRODUCTPAPER_RUNNINGEQUIP E LEFT JOIN PRODUCTPAPER_RUNNINGLOG L ON E.INNERID=L.RUNNINGEQUIPID AND to_char(L.LOGTIME,'yyyy-mm-dd')=to_char(:LOGTIME,'yyyy-mm-dd') WHERE E.EQUIPMENT=:EQUIPMENT</Command>

</SqlEntity>

- <SqlEntity id="GetProductPaperYXLog" type="Text">

<Command>select '从今天起到年底还有'||d.day1||'天，今后日产乙烯 '||to_char((y.yearplan-s.yearsum)/d.day1,'9999999.99')||'吨可完成年产乙烯'||y.yearplan||'吨任务' from ( select yearplan from productpaper_yearplan where year=to_char(:TIME,'yyyy') ) y, ( select (to_date(((to_char(:TIME,'yyyy')+1)|| '01-01'),'yyyy-mm-dd')-:TIME) day1 from dual ) d, ( select decode(yearsum,null,0,yearsum) yearsum from PRODUCTPAPER_MAT m left join PRODUCTPAPER_COUNTING c on m.innerid=c.matid where mat='乙烯' and rownum<=1 order by c.productdate desc ) s</Command>

</SqlEntity>

- <SqlEntity id="GetProductPaperMonthSum" type="Text">

<Command>select c.monthplan from productpaper_mat m left join productpaper_counting c on m.INNERID=c.matid and to_char(c.productdate,'yyyy-mm-dd')=to_char(:productdate,'yyyy-mm-dd') where m.mat=:mat</Command>

</SqlEntity>

- <SqlEntity id="GetProductPaperSum" type="Text">

<Command>select c.monthsum,c.dayavgsum,c.weekavgsum,c.yearsum from productpaper_mat m left join productpaper_counting c on m.INNERID=c.matid and to_char(c.productdate,'yyyy-mm-dd')=to_char(:productdate,'yyyy-mm-dd') where m.mat=:mat</Command>

</SqlEntity>

- <SqlEntity id="GetProductPaperEquipValue" type="Text">

<Command>select value from productpaper_mat m left join PRODUCTPAPER_EQUIPMENTAMOUNT e on m.innerid=e.matid and to_char(e.productdate,'yyyy-mm-dd')=to_char(:productdate,'yyyy-mm-dd') and e.equipid=:equipid where m.mat=:mat</Command>

</SqlEntity>

- <SqlEntity id="GetProductPager_MatList" type="Text">

<Command>select innerid,mat from PRODUCTPAPER_MAT</Command>

</SqlEntity>

- <SqlEntity id="GetProductPager_EquipnameList" type="Text">

<Command>select null innerid,'全部' equipname from dual union all select innerid,equipname from PRODUCTPAPER_EQUIPMENT</Command>

</SqlEntity>

- <SqlEntity id="GetProductPager_EquipnameResultList" type="Text">

<Command>select d.productdate productdate,(select mat from PRODUCTPAPER_MAT where innerid=:matid) mat,sum(decode(e.value,null,0,e.value)) value from (select (:starttime+rownum-1) as productdate from user_objects where rownum<=:endtime-:starttime+ 1) d left join (select mat,e.productdate,e.value,e.equipid from PRODUCTPAPER_EQUIPMENTAMOUNT e,PRODUCTPAPER_MAT m where e.matid=:matid and e.equipid like '%'||:equipid||'%' and e.productdate>=:starttime and e.productdate<=:endtime and e.matid=m.innerid)e on d.productdate=e.productdate group by mat,d.productdate order by d.productdate</Command>

</SqlEntity>

- <SqlEntity id="GetXTCRunningWeekProductResult" type="Text">

<Command>select c.sumt 累计量,round(c.avgt,2) 日平均,t.monthplan 月计划,t.monthsum 月累计,t.wcl 完成率,t.yearsum 年累计 from (select sum(value) sumt,avg(value) avgt from PRODUCTPAPER_MAT m left join PRODUCTPAPER_EQUIPMENTAMOUNT e on m.innerid=e.matid where m.mat=:mat and productdate between :starttime and :endtime) c, ( select monthplan,monthsum,round((monthsum/monthplan)*100,2) wcl,yearsum from PRODUCTPAPER_MAT m left join PRODUCTPAPER_COUNTING c on m.innerid=c.matid and m.mat=:mat where productdate<=:endtime and rownum<=1 and productdate>=:starttime order by productdate desc) t</Command>

</SqlEntity>

- <SqlEntity id="GetXTCRunningWeekProductValue" type="Text">

<Command>select sum(value) sumt from PRODUCTPAPER_MAT m left join PRODUCTPAPER_EQUIPMENTAMOUNT e on m.innerid=e.EQUIPID where m.mat=:mat and productdate between :starttime and :endtime</Command>

</SqlEntity>

</SqlEntitySet>

（2）程序集查询：对应的文件为： <Property name="assembly" value="D:/扬子烯烃厂项目/系统发布版本/lily/MES.Common.Lily2/MES.Common.Lily.Web/bin/Supcon.MES.PetroSuite.dll" />

</DataProvider>

</DataProviders>

其中：Supcon.MES.PetroSuite.dll 是要引用的程序集 Supcon.MES.PetroSuite.Business.LimsService 方法所在的命名空间

3. 在excel中添加公式

#ORADB_GetXTCRunningWeekProductResult('乙烯',开始时间,结束时间)--对应的为sql查询

#LimsService_getTechnologyIndexCardHGL('1＃乙烯装置','',开始时间,结束时间)--对应的为程序集查询

getTechnologyIndexCardHGL为Supcon.MES.PetroSuite.Business.LimsService的方法：

public string getTechnologyIndexCardHGL(string Equipment, string INDICATORSNAME, DateTime StartTime, DateTime EndTime)