package com.yc.sdk.shopping.service.balance;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.context.annotation.Scope;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Service;

import com.yc.sdk.shopping.entity.BalanceDetailEntity;
import com.yc.sdk.shopping.entity.BalanceEntity;
import com.yc.service.BaseService;

@Service("BalanceImpl")
@Scope("prototype")
public class BalanceImpl  extends BaseService implements BalanceIfc {
	
	@Override
	public BalanceEntity getSummaryForHomePage(String openId) {
		String sql = " set nocount on ; \n"
				+ " declare @OpenId varchar(200) = ? ,@HeXiaoCount int = 0 ,@OrderCount int = 0 \n"  //可核销订单数量，订单次数
				+ " declare @MyVisiteTraceCount int = 0 , @MyPanicBuyingTraceCount int = 0 \n"   //我的足迹，客户轨迹 
				+ " declare @MyFeedbackUnreadTimes int = 0 \n"   //意见反馈，还未查看的已回复次数
				//可核销订单数量，订单次数
				+ " select @HeXiaoCount = sum(case when isnull(a.Digit,0) <> 0 and isnull(a.HeXiaoDigit,0) <> isnull(a.Digit,0) then 1 else 0 end ),  \n"
				+ "    @OrderCount = count(1) \n"
				+ " from  t710806H a \n"
				+ " where a.OpenId = @OpenId  \n"
				+ " and a.DocStatus = 100 \n"
				
				//我的足迹
				+ " select @MyVisiteTraceCount = count(1) \n"
				+ " from t710805 a where a.OpenId = @OpenId \n"
				
				//客户轨迹 
				+ " select @MyPanicBuyingTraceCount = count(1) \n"
				+ " from t710805 a  \n"
				+ " where a.RefOpenId = @OpenId and a.ReadTimesByPanicBuyingAuthor = 0 \n"
				+ " and a.OpenId <> @OpenId \n"
				
				//意见反馈
				+ " select @MyFeedbackUnreadTimes = sum(isnull(UnreadTimes,0)) \n"
				+ " from t710819H a \n"
				+ " where a.OpenId = @OpenId \n"
				
				//输出
				+ " select isnull(@HeXiaoCount,0) as HeXiaoCount,isnull(@OrderCount,0) as OrderCount, \n"
				+ "   isnull(@MyVisiteTraceCount,0) as MyVisiteTraceCount,\n"
				+ "   isnull(@MyPanicBuyingTraceCount,0) as MyPanicBuyingTraceCount, \n"
				+ "   isnull(@MyFeedbackUnreadTimes,0) as MyFeedbackUnreadTimes \n";
				
		BalanceEntity balanceEntity = new BalanceEntity() ;
		balanceEntity.setCltCode(openId);
		Map<String,Object> map = null ;
		try {
			map = this.jdbcTemplate.queryForMap(sql,new Object[]{openId});
			if (map != null) {
				
				//balanceEntity.setBalance(map.get("Balance") == null ? 0: (Double.parseDouble(map.get("Balance").toString())));
				//balanceEntity.setAccumulationAmount(map.get("AccumulationAmount") == null ? 0: (Double.parseDouble(map.get("AccumulationAmount").toString())));
				//balanceEntity.setCurentPeriodId(map.get("CurentPeriodId") == null ? "": (String)map.get("CurentPeriodId"));
				balanceEntity.setHeXiaoCount(map.get("HeXiaoCount") == null ? 0: (Integer)map.get("HeXiaoCount"));
				balanceEntity.setOrderCount(map.get("OrderCount") == null ? 0: (Integer)map.get("OrderCount"));
				balanceEntity.setMyVisiteTraceCount(map.get("MyVisiteTraceCount") == null ? 0: (Integer)map.get("MyVisiteTraceCount"));
				balanceEntity.setMyPanicBuyingTraceCount(map.get("MyPanicBuyingTraceCount") == null ? 0: (Integer)map.get("MyPanicBuyingTraceCount"));
				balanceEntity.setMyFeedbackUnreadTimes(map.get("MyFeedbackUnreadTimes") == null ? 0: (Integer)map.get("MyFeedbackUnreadTimes"));
			}
		}catch(DataAccessException e ) {
			if (e instanceof EmptyResultDataAccessException){
				return balanceEntity ;
			}else {
				e.printStackTrace();
				 throw e;
			}
		}catch(Exception e){
			e.printStackTrace();
		    throw e;
		}
		return balanceEntity;
	}
	
	@Override
	public BalanceEntity getBalance(String currency,String cltCode) {
		String sql = " set nocount on ; \n"
				+ " declare @CompanyId varchar(20)  \n"
				+ " declare @AccountsReceivableAcctCode varchar(20) \n"
				+ " declare @Currency varchar(20) = ? \n"
				+ " declare @CltCode varchar(20) = ?  \n"
				+ " declare @Balance money,@AccumulationAmount money \n"
				+ " select @AccountsReceivableAcctCode = AccountsReceivableAcctCode from t111601 \n"
				+ " if isnull(@AccountsReceivableAcctCode,'') = '' \n"
				+ " begin \n"
				+ "    raiserror('请在 111601 中预先维护【应收帐款科目】',16,1) \n"
				+ "    return \n"
				+ " end \n"
				+ " select @CompanyId = CompanyId \n"
				+ " from t110203 a where a.CltCode = @CltCode \n"
				+ " if isnull(@CompanyId,'') = '' \n"
				+ " begin \n"
				+ "     select top 1 @CompanyId = CompanyId from oCompany \n"
				+ " end \n"
				
				+ " select @Balance = a.Balance,@AccumulationAmount = AccumulationCredit from t150103 a \n"
				+ " where a.CompanyId = @CompanyId and a.AcctCode = @AccountsReceivableAcctCode \n"
				+ " and a.Currency = @Currency and a.cv1 = @CltCode \n"
				+ " select isnull(@Balance,0) as Balance,@AccumulationAmount as AccumulationAmount,convert(varchar(7),getdate(),120) as CurrentPeriodId \n";
		BalanceEntity balanceEntity = new BalanceEntity() ;
		balanceEntity.setCltCode(cltCode);
		Map<String,Object> map = null ;
		try {
			map = this.jdbcTemplate.queryForMap(sql,new Object[]{currency,cltCode});
			if (map != null) {
				
				balanceEntity.setBalance(map.get("Balance") == null ? 0: (Double.parseDouble(map.get("Balance").toString())));
				balanceEntity.setAccumulationAmount(map.get("AccumulationAmount") == null ? 0: (Double.parseDouble(map.get("AccumulationAmount").toString())));
				balanceEntity.setCurrentPeriodId(map.get("CurrentPeriodId") == null ? "": (String)map.get("CurrentPeriodId"));
			}
		}catch(DataAccessException e ) {
			if (e instanceof EmptyResultDataAccessException){
				return balanceEntity ;
			}else {
				e.printStackTrace();
				 throw e;
			}
		}catch(Exception e){
			e.printStackTrace();
		    throw e;
		}
		return balanceEntity;
	}
	
	@Override
	public List<BalanceDetailEntity> getBalanceDetail(String currency,String cltCode) {
		String sql = " set nocount on ; \n"
				+ " declare @CompanyId varchar(20)  \n"
				+ " declare @AccountsReceivableAcctCode varchar(20) ,@OperatingRevenueAcctCode varchar(20) \n"
				+ " declare @Currency varchar(20) = ? \n"
				+ " declare @CltCode varchar(20) = ?  \n"
				+ " declare @WxPayAcctCode varchar(20) \n"
				+ " declare @table table (DocCode varchar(20),FormId int ,FormName varchar(80),InsertTime datetime,"
				+ "    BalanceAmount money,AmountDebit money,\n"
				+ "    AmountCredit money,SubCltName varchar(80)) \n"
				+ " select @AccountsReceivableAcctCode = AccountsReceivableAcctCode, \n"
				+ "    @OperatingRevenueAcctCode = OperatingRevenueAcctCode \n"
				+ " from t111601 \n"
				+ " if isnull(@AccountsReceivableAcctCode,'') = '' \n"
				+ " begin \n"
				+ "    raiserror('请在 111601 中预先维护【应收帐款科目】',16,1) \n"
				+ "    return \n"
				+ " end \n"
				+ " select @CompanyId = CompanyId from t110203 a where a.CltCode = @CltCode \n"
				+ " if isnull(@CompanyId,'') = '' \n"
				+ " begin \n"
				+ "     select top 1 @CompanyId = CompanyId from oCompany \n"
				+ " end \n"
				+ " insert into @table(DocCode,FormId,FormName,InsertTime,BalanceAmount,AmountDebit,AmountCredit,SubCltName) \n"
				+ " select a.DocCode,a.FormId,b.FormName,a.InsertTime,\n"
				+ "		sum(isnull(a.AmountDebit,0) - isnull(a.AmountCredit,0)) as BalanceAmount,  --借方减贷方 \n"
				+ "		sum(isnull(a.AmountDebit,0)) as AmountDebit,\n"
				+ "		sum(isnull(a.AmountCredit,0)) as AmountCredit, \n"
				+ "		'' as SubCltName \n"
				+ "	from t150102 a  \n"
				+ "	join gform b on a.FormId = b.FormId \n"
				+ "	where a.CompanyId = @CompanyId and a.AcctCode = @AccountsReceivableAcctCode   \n"
				+ "	and a.Currency = @Currency and a.cv1 = @CltCode --and a.formid not in (120230,120406) \n"
				+ "	group by a.DocCode,a.FormId,b.FormName,a.InsertTime \n"
				+ "	having sum(isnull(a.AmountDebit,0) - isnull(a.AmountCredit,0)) <> 0.0 \n"
				
				
				/*
				+ " insert into @table(DocCode,FormId,FormName,InsertTime,BalanceAmount,AmountDebit,AmountCredit,SubCltName) \n"
				+ " select a.DocCode,a.FormId,b.FormName,a.InsertTime, \n"
				+ "   sum(isnull(a.AmountDebit,0) - isnull(a.AmountCredit,0)) as BalanceAmount,  --借方减贷方   \n"
				+ "   sum(isnull(a.AmountDebit,0)) as AmountDebit,sum(isnull(a.AmountCredit,0)) as AmountCredit, \n"
				//+ " case when isnull(a.MatName,'') <> '' then a.MatName else a.CltName end as SubCltName,"
				+ "   '' as SubCltName \n"
				+ " from t150102 a  \n"
				+ " join gform b on a.FormId = b.FormId \n"
				+ " where a.CompanyId = @CompanyId and a.AcctCode = @AccountsReceivableAcctCode  \n" 
				+ " and a.Currency = @Currency and a.cv1 = @CltCode and a.formid not in (120230,120406) \n"
				+ " group by a.DocCode,a.FormId,b.FormName,a.InsertTime \n"
				+ " having sum(isnull(a.AmountDebit,0) - isnull(a.AmountCredit,0)) <> 0.0 \n"
				//+ " order by a.InsertTime desc \n"
				
				+ " insert into @table(DocCode,FormId,FormName,InsertTime,BalanceAmount,AmountDebit,AmountCredit,SubCltName) \n"
				+ " select a.DocCode,a.FormId,b.FormName,null as InsertTime, \n"
				+ "   sum(isnull(a.AmountCredit,0) - isnull(a.AmountDebit,0) ) as BalanceAmount,  --贷方减借方 \n"
				+ "   sum(isnull(a.AmountDebit,0)) as AmountDebit,sum(isnull(a.AmountCredit,0)) as AmountCredit, \n"
				//+ " case when isnull(a.MatName,'') <> '' then a.MatName else a.CltName end as SubCltName,"
				+ "   '' as SubCltName \n"
				+ " from t150102 a  \n"
				+ " join gform b on a.FormId = b.FormId \n"
				+ " where a.CompanyId = @CompanyId and a.AcctCode = @OperatingRevenueAcctCode  \n" 
				+ " and a.Currency = @Currency and a.CltCode = @CltCode and a.formid = 120230 \n"
				+ " group by a.DocCode,a.FormId,b.FormName \n"
				+ " having sum(isnull(a.AmountDebit,0) - isnull(a.AmountCredit,0)) <> 0.0 \n"
				
				+ " select @WxPayAcctCode = WxPayAcctCode from t714001  \n"
				+ " insert into @table(DocCode,FormId,FormName,InsertTime,BalanceAmount,AmountDebit,AmountCredit,SubCltName) \n"
				+ " select a.DocCode,a.FormId,b.FormName,null as InsertTime, \n"
				+ "   sum(isnull(a.AmountDebit,0) - isnull(a.AmountCredit,0)) as BalanceAmount,  --借方减贷方 \n"
				+ "   sum(isnull(a.AmountDebit,0)) as AmountDebit,sum(isnull(a.AmountCredit,0)) as AmountCredit, \n"
				//+ " case when isnull(a.MatName,'') <> '' then a.MatName else a.CltName end as SubCltName,"
				+ "   '' as SubCltName \n"
				+ " from t150102 a  \n"
				+ " join gform b on a.FormId = b.FormId \n"
				+ " where a.CompanyId = @CompanyId and a.AcctCode = @WxPayAcctCode  \n" 
				+ " and a.Currency = @Currency and a.CltCode = @CltCode and a.formid = 120406 \n"
				+ " group by a.DocCode,a.FormId,b.FormName \n"
				+ " having sum(isnull(a.AmountDebit,0) - isnull(a.AmountCredit,0)) <> 0.0 \n"
				*/
				
				+ " update a set InsertTime = case when b.PostDate is null then b.ModifyDate else b.PostDate end \n"
				+ " from @table a join t120406H b on a.doccode = b.doccode  \n"
				+ " where a.formid = 120406 \n"
				+ " update a set InsertTime = case when b.PostDate is null then b.ModifyDate else b.PostDate end \n"  
				+ " from @table a join t120201H b on a.doccode = b.doccode  \n" 
				+ " where a.formid = 120230 \n"
				
				+ " select a.DocCode,a.FormId,a.FormName,a.InsertTime,sum(isnull(a.BalanceAmount,0)) as BalanceAmount,\n" 
				+ "	sum(isnull(a.AmountDebit,0)) as AmountDebit,\n" 
				+ "	sum(isnull(a.AmountCredit,0)) as AmountCredit,a.SubCltName \n" 
				+ " from @table a \n" 
				+ " group by a.DocCode,a.FormId,a.FormName,a.InsertTime,a.SubCltName\n"
				+ " having sum(isnull(a.BalanceAmount,0)) <> 0.0 \n"  
				+ " order by a.InsertTime desc " ;
				//+ " order by a.InsertTime desc \n" ;
				
		List<BalanceDetailEntity> balanceDetailList = new ArrayList<BalanceDetailEntity>() ;
		List<Map<String,Object>> list = null ;
		try {
			list = this.jdbcTemplate.queryForList(sql,new Object[]{currency,cltCode});
			for (int i =0;list != null && i < list.size();i++) {
				Map<String,Object> map = list.get(i) ;
				BalanceDetailEntity balanceDetailEntity = new BalanceDetailEntity() ;
				balanceDetailEntity.setFormName(map.get("FormName")== null?"":(String)map.get("FormName"));
				balanceDetailEntity.setInsertTime(map.get("InsertTime") == null ? null: (Date)map.get("InsertTime"));	
				balanceDetailEntity.setBalanceAmount(map.get("BalanceAmount") == null ? 0: (Double.parseDouble(map.get("BalanceAmount").toString())));
				balanceDetailEntity.setAmountDebit(map.get("AmountDebit") == null ? 0: (Double.parseDouble(map.get("AmountDebit").toString())));
				balanceDetailEntity.setAmountCredit(map.get("AmountCredit") == null ? 0: (Double.parseDouble(map.get("AmountCredit").toString())));
				balanceDetailEntity.setSubCltName(map.get("SubCltName")== null?"":(String)map.get("SubCltName"));
				balanceDetailEntity.setDocCode(map.get("DocCode")== null?"":(String)map.get("DocCode"));
				balanceDetailEntity.setFormId(map.get("FormId")== null?null:(Integer)map.get("FormId"));
				
				balanceDetailList.add(balanceDetailEntity);
			}
			return balanceDetailList ;
		}catch(DataAccessException e ) {
			if (e instanceof EmptyResultDataAccessException){
				return balanceDetailList ;
			}else {
				e.printStackTrace();
				 throw e;
			}
		}catch(Exception e){
			e.printStackTrace();
		    throw e;
		}
	}

}