package com.kingdee.eas.custom.gggl.app;

import org.apache.log4j.Logger;
import javax.ejb.*;
import java.rmi.RemoteException;
import java.util.ArrayList;
import java.util.List;

import com.kingdee.bos.*;
import com.kingdee.bos.util.BOSObjectType;
import com.kingdee.bos.metadata.IMetaDataPK;
import com.kingdee.bos.metadata.rule.RuleExecutor;
import com.kingdee.bos.metadata.MetaDataPK;
//import com.kingdee.bos.metadata.entity.EntityViewInfo;
import com.kingdee.bos.framework.ejb.AbstractEntityControllerBean;
import com.kingdee.bos.framework.ejb.AbstractBizControllerBean;
//import com.kingdee.bos.dao.IObjectPK;
import com.kingdee.bos.dao.IObjectValue;
import com.kingdee.bos.dao.IObjectCollection;
import com.kingdee.bos.service.ServiceContext;
import com.kingdee.bos.service.IServiceContext;

import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.framework.report.app.CommRptBaseControllerBean;
import com.kingdee.eas.framework.report.util.RptParams;
import com.kingdee.eas.framework.report.util.RptRowSet;
import com.kingdee.eas.framework.report.util.RptTableColumn;
import com.kingdee.eas.framework.report.util.RptTableHeader;
import com.kingdee.eas.util.app.ContextUtil;

public class GgLeaseStatusReportFacadeControllerBean extends AbstractGgLeaseStatusReportFacadeControllerBean
{
    private static Logger logger =
        Logger.getLogger("com.kingdee.eas.custom.gggl.app.GgLeaseStatusReportFacadeControllerBean");
    protected RptParams _init(Context ctx, RptParams params)
	throws BOSException, EASBizException {
		RptParams pp = new RptParams();
		return pp;
	}
    private void initColoum(RptTableHeader header,RptTableColumn col,String name,int width,boolean isHide){
		col= new RptTableColumn(name);
		col.setWidth(width);
		col.setHided(isHide);
		header.addColumn(col);
	}
	protected RptParams _createTempTable(Context ctx, RptParams params)
	throws BOSException, EASBizException {
		RptTableHeader header = new RptTableHeader();
		RptTableColumn col = null;


		initColoum(header, col, "HTNUM", 120, false);
		
		initColoum(header, col, "SNW", 60, false);
		initColoum(header, col, "CFAddress", 150, false);
		initColoum(header, col, "ZLZQ", 80, false);
		initColoum(header, col, "customer", 200, false);
		initColoum(header, col, "pp", 100, false);
		initColoum(header, col, "CFZLAMOUNT", 100, false);
		initColoum(header, col, "ZLSTARTDATE", 100, false);
		initColoum(header, col, "ZLENDDATE", 100, false);
		
		initColoum(header, col, "CFHTAMOUNT", 100, false);
		initColoum(header, col, "CFZLAMOUNT", 100, false);
		initColoum(header, col, "CFZHLX", 120, false);
		
		initColoum(header, col, "REMAK", 100, false);
		List<String> firstHead = new ArrayList<String>();
		List<String> firstHead2 = new ArrayList<String>();
		List<String> firstHead3 = new ArrayList<String>();
		
		String[] fixedHead1 =  new String[]{"实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表","实际租赁情况表"};
		String[] fixedHead2 =  new String[]{"编号","室内/户外","位置","租赁周期","备注","品牌","计划租赁总价","租赁期限","租赁期限","大合同租金","" +
				"租金合计","租户/非租户","备注"};
		String[] fixedHead3 =  new String[]{"编号","室内/户外","位置","(月)","现有租户","品牌","(RMB/个)","开始时间","结束时间","大合同租金","租金合计","租户/非租户","备注"};
		initFixeHeader(firstHead, fixedHead1);
		initFixeHeader(firstHead2, fixedHead2);
		initFixeHeader(firstHead3, fixedHead3);
		Object[] fhead = firstHead.toArray();
		Object[] fhead2 = firstHead2.toArray();
		Object[] fhead3 = firstHead3.toArray();
		header.setLabels(new Object[][]{ 
				fhead,fhead2,fhead3
		},true);
		params.setObject("header", header);
		return params;
	}
	private void initFixeHeader(List<String> list, String[] array) {
		for (String s : array) {
			list.add(s);
		}
	}
	protected RptParams _query(Context ctx, RptParams params) throws BOSException, EASBizException {
		RptRowSet rowSet = executeQuery(getSql(ctx,params), null, ctx);
		
		if(rowSet!=null){
		params.setObject("rowSet", rowSet);
		}
		return params;
		
	}
	

	private String getSql(Context ctx,RptParams params) {
		String number = params.getString("number");
		StringBuilder sql = new StringBuilder("");

		sql.append("SELECT E1_GGW.fname_l2 AS HTNUM,");
		sql.append(" CASE WHEN E1_GGW.CFSNW='sn' THEN '室内' ELSE '室外 ' END AS SNW,");
		sql.append(" E1_GGW.CFAddress AS CFAddress,E1.CFZlzq AS ZLZQ,cus.fname_l2  customer,");
		sql.append("  SALECONTRACT.CFPP as pp,E1.CFZlAmount AS ZLAMOUNT,");
		sql.append("  to_char(E1.CFZlStartDate,'YYYY-MM-DD')  AS ZLSTARTDATE,to_char(E1.CFZlEndDate,'YYYY-MM-DD') AS ZLENDDATE, ");
		 
		sql.append("  SALECONTRACT.CFHTAMOUNT AS CFHTAMOUNT,  ");
		sql.append("  E1.CFZLAMOUNT AS CFZLAMOUNT, ");
		sql.append("  case when SALECONTRACT.CFZHLX='zh' then '租户' else '非租户' end AS CFZHLX,");
		
		sql.append(" E1.CFRemak AS REMAK");
		sql.append(" FROM CT_GGL_SaleContract AS SALECONTRACT");
		sql.append(" LEFT OUTER JOIN t_bd_customer  cus on  cus.fid=SALECONTRACT.CFCustomerID ");
		sql.append(" LEFT OUTER JOIN CT_GGL_SaleContractE1 AS E1");
		sql.append(" ON SALECONTRACT.FID = E1.FParentID");
		sql.append(" LEFT OUTER JOIN CT_GGL_Advert AS E1_GGW");
		sql.append(" ON E1.CFGgwID = E1_GGW.FID");

		
		sql.append(" LEFT OUTER JOIN T_ORG_Company AS COMPANY");
		sql.append(" ON SALECONTRACT.CFCompanyID = COMPANY.FID");
		sql.append(" where 1=1 and SALECONTRACT.FSTATUS=4 ");//year(SALECONTRACT.fcreatetime)=DateName(year,GetDate()) 
		sql.append(" and SALECONTRACT.cfdjtype=1");
		if(ContextUtil.getCurrentFIUnit(ctx).getNumber()!=null){
			if(!ContextUtil.getCurrentFIUnit(ctx).getNumber().toString().equals("HY")){
				sql.append(" and  COMPANY.FNUMBER ='"+ContextUtil.getCurrentFIUnit(ctx).getNumber()+"'");
			}
		}
		sql.append(" order by E1_GGW.fname_l2,to_char(E1.CFZlStartDate,'YYYY-MM-DD')  ");
		
//		if(number!=null && ContextUtil.getCurrentFIUnit(ctx).getNumber()==null){
//			sql.append(" and  E1.FNUMBER ='"+number+"'");
//		}else if(number==null && ContextUtil.getCurrentFIUnit(ctx).getNumber()!=null){
//			sql.append(" and  COMPANY.FNUMBER ='"+ContextUtil.getCurrentFIUnit(ctx).getNumber()+"'");
//		}else if(number!=null &&ContextUtil.getCurrentFIUnit(ctx).getNumber()!=null){
//			sql.append(" and  COMPANY.FNUMBER ='"+ContextUtil.getCurrentFIUnit(ctx).getNumber()+"'");
//			sql.append(" and  E1_GGW.FNUMBER ='"+number+"'");
//		}
	 return sql.toString();
	 
	 
	}
}