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.Calendar;
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 GgSignContractReportFacadeControllerBean extends AbstractGgSignContractReportFacadeControllerBean
{
    private static Logger logger =
        Logger.getLogger("com.kingdee.eas.custom.gggl.app.GgSignContractReportFacadeControllerBean");
    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 {
		Calendar calendar=Calendar.getInstance();
		int year=0;
		year=calendar.get(Calendar.YEAR);
		
		RptTableHeader header = new RptTableHeader();
		RptTableColumn col = null;


		initColoum(header, col, "HTNUM", 80, false);
		initColoum(header, col, "SNW", 80, false);
		initColoum(header, col, "NAME", 80, false);
		initColoum(header, col, "CFKJCC", 80, false);
		initColoum(header, col, "CFAREA", 80, false);
		initColoum(header, col, "CFBZPRICE", 80, false);
		initColoum(header, col, "year2", 80, false);
		initColoum(header, col, "year1", 80, false);
		initColoum(header, col, "ZLZQ", 80, false);
		initColoum(header, col, "pp", 80, false);
		initColoum(header, col, "BEIZ", 80, false);
		initColoum(header, col, "CFZHLX", 80, false);
		initColoum(header, col, "01", 80, false);
		initColoum(header, col, "02", 80, false);
		initColoum(header, col, "03", 80, false);
		initColoum(header, col, "04", 80, false);
		initColoum(header, col, "05", 80, false);
		initColoum(header, col, "06", 80, false);
		initColoum(header, col, "07", 80, false);
		initColoum(header, col, "08", 80, false);
		initColoum(header, col, "09", 80, false);
		initColoum(header, col, "10", 80, false);
		initColoum(header, col, "11", 80, false);
		initColoum(header, col, "12", 80, false);
		initColoum(header, col, "13", 80, false);
		initColoum(header, col, "ZLSTARTDATE", 120, false);
		initColoum(header, col, "ZLENDDATE", 120, false);
		initColoum(header, col, "cydays", 70, false);
		initColoum(header, col, "DATEDIF", 70, false);
		initColoum(header, col, "adID", 70, true);
		initColoum(header, col, "id", 70, true);
		initColoum(header, col, "zlAmount", 70, false);
		
		List<String> firstHead0 = new ArrayList<String>();
		List<String> firstHead = new ArrayList<String>();
		List<String> firstHead2 = new ArrayList<String>();
		String[] fixedHead0 =  new String[]{year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表",year+"年已签约租赁明细表"};
		String[] fixedHead1 =  new String[]{"新编号","室内/户外","位置","可见尺寸","面积","标准定价",year-1+"计划单价",year+"计划单价","周期","品牌","备注","租户/非租户","1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月","总计","租赁开始日期","最后租赁期日","天数","到期天数","广告位ID","合同ID","租赁金额"};
		String[] fixedHead2 =  new String[]{"新编号","室内/户外","位置","可见尺寸","面积","标准定价","(RMB/月/个)","(RMB/月/个 )","周期","品牌","现有租户","租户/非租户","1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月","总计","租赁开始日期","最后租赁期日","天数","到期天数","广告位ID","合同ID","租赁金额"};
		initFixeHeader(firstHead0, fixedHead0);
		initFixeHeader(firstHead, fixedHead1);
		initFixeHeader(firstHead2, fixedHead2);
		Object[] fhead0 = firstHead0.toArray();
		Object[] fhead = firstHead.toArray();
		Object[] fhead2 = firstHead2.toArray();
		header.setLabels(new Object[][]{ 
				fhead0,fhead,fhead2
		},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) {
		StringBuilder sql = new StringBuilder("");
		sql.append("SELECT DISTINCT");
		sql.append("  E1_GGW.fname_l2 AS HTNUM,");
		sql.append(" case when E1_GGW.CFSNW='sn' then '室内' else '户外 ' end AS SNW,E1_GGW.FName_l2 AS NAME,E1_GGW.CFKJCC AS CFKJCC,E1_GGW.CFAREA as CFAREA,");
		sql.append("  E1_GGW.CFBZPRICE AS CFBZPRICE,");
		sql.append("  '' price1,");
		sql.append("  '' price2,");
		sql.append("  E1.CFZlzq AS ZLZQ,SALECONTRACT.CFPP as pp,cus.fname_l2  customer,");
		sql.append("  case when SALECONTRACT.CFZHLX='zh' then '租户' else '非租户' end AS CFZHLX,");
		sql.append("  null AS M01,");
		sql.append("  null AS M02,");
		sql.append("  null AS M03,");
		sql.append("  null AS M04,");
		sql.append("  null AS M05,");
		sql.append("  null AS M06,");
		sql.append("  null AS M07,");
		sql.append("  null AS M08,");
		sql.append("  null AS M09,");
		sql.append("  null AS M10,");
		sql.append("  null AS M11,");
		sql.append("  null AS M12,");
		sql.append("  null AS M13,");
		sql.append(" to_char(E1.CFZlStartDate,'YYYY-MM-DD') AS ZLSTARTDATE,");
		sql.append(" to_char(E1.CFZlEndDate,'YYYY-MM-DD')  AS ZLENDDATE, ");
		sql.append(" DATEDIFF(DAY,E1.CFZlStartDate,  E1.CFZlEndDate)+1 as cyDays, ");
		sql.append(" DATEDIFF(DAY, GETDATE(),  E1.CFZlEndDate) as DATEDIF, ");
		sql.append("  E1_GGW.fid AS adID,");
		sql.append("  SALECONTRACT.fid AS id,");
		sql.append("  E1.CFZlAmount AS zlAmount");
		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 year(E1.CFZlStartDate)<=DateName(year,GetDate())  and  year(E1.CFZlEndDate)>=DateName(year,GetDate())   and SALECONTRACT.FSTATUS=4 ");//租赁开始日期小于等于当前年，租赁截至日期大于等于当前年
		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  ");
	 return sql.toString();
	 
	 
	}
}