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.ctrl.kdf.table.util.KDTableUtil;
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;
import com.kingdee.eas.util.client.MsgBox;

public class ZtAnalyseReportFacdeControllerBean extends AbstractZtAnalyseReportFacdeControllerBean
{
    private static Logger logger =
        Logger.getLogger("com.kingdee.eas.custom.gggl.app.ZtAnalyseReportFacdeControllerBean");
    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", 120, false);
		initColoum(header, col, "HTMC", 100, false);
		initColoum(header, col, "CUNAME", 200, false);
		initColoum(header, col, "GGWNAME", 80, false);
		initColoum(header, col, "cfrectype", 80, false);
		initColoum(header, col, "cfrecdate", 100, false);
		initColoum(header, col, "skamount", 100, false);
		initColoum(header, col, "recamount", 100, false);
		initColoum(header, col, "skdate", 100, false);
		initColoum(header, col, "ceAmount", 120, 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[]{"合同号","合同名称","客户","广告位","收款类型","应收日期","应收金额","实际收款金额","实际日期","差额"};

		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 startDate = (String) params.getObject("startDate");
		String endDate = (String) params.getObject("endDate");
		StringBuilder sql = new StringBuilder("");
		sql.append("SELECT SALECONTRACT.CFHtNum AS HTNUM,SALECONTRACT.CFHtmc AS HTMC,  ");
		sql.append(" CUSTOMER.FName_l2 AS CUNAME, E1_GGW.FName_l2 AS GGWNAME, ");
		sql.append(" SKTYPE.FName_l2 AS cfrectype,to_char(E2.cfskdate,'YYYY-MM-DD') as cfrecdate,isNULL(E2.cfskamount,0) as skamount,");
		
		sql.append(" isNULL(E2.cfrecamount,0) as recamount,to_char(E2.cfrecdate,'YYYY-MM-DD') as skdate ,");
		sql.append(" isNULL(E2.cfskamount,0)- isNULL(E2.cfrecamount,0)  as ceAmount");
		sql.append(" FROM CT_GGL_SaleContract AS SALECONTRACT");
		
		sql.append(" LEFT OUTER JOIN CT_GGL_SaleContractE2 AS E2");
		sql.append(" ON SALECONTRACT.FID = E2.FParentID");
		
		sql.append(" LEFT OUTER JOIN CT_GGL_ReceType AS SKTYPE");
		sql.append(" ON E2.cfrectypeid = SKTYPE.FID  ");
		
		sql.append(" LEFT OUTER JOIN CT_GGL_Advert AS E1_GGW");
		sql.append(" ON E2.CFGgwID = E1_GGW.FID");
		sql.append(" LEFT OUTER JOIN T_BD_Customer AS CUSTOMER");
		sql.append(" ON SALECONTRACT.CFCustomerID = CUSTOMER.FID");
		

		
		sql.append(" LEFT OUTER JOIN T_ORG_Company AS COMPANY");
		sql.append(" ON SALECONTRACT.CFCompanyID = COMPANY.FID");
		sql.append(" where year(SALECONTRACT.fcreatetime)=DateName(year,GetDate())  and SALECONTRACT.FSTATUS=4 ");
		sql.append(" and SALECONTRACT.cfdjtype=2");
		sql.append(" and  to_char(E2.cfskdate,'YYYY-MM-DD')>='"+startDate+"'  and  to_char(E2.cfskdate,'YYYY-MM-DD')<='"+endDate+"' ");
		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  SALECONTRACT.CFHtNum, to_char(E2.cfskdate,'YYYY-MM-DD'),E1_GGW.FName_l2 ");
//		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();
	 
	 
	}
}