/**businesslibҵ߼ű*/
//õԤܶ
function getBudgetAmount(pwpno,period){
	var domainid = getDomainid();
	if(!pwpno) pwpno = getItemValue("pwpno");
	if(!pwpno) period = getItemValue("period");
	/*
	var sql = " select sum(t.item_amount) as item_amount,'"+domainid+"' as domainid from("
	+" select isnull(item_tmexpenses,0) as item_amount from tlk_tm_budget_expense where item_pwpno='"+pwpno+"'"
	+" union all"
	+" select isnull(d.item_transferamount,0) from tlk_tm_budget_change_in d,tlk_tm_budget m where d.parent=m.id and m.statelabel='' and d.item_pwpno='"+pwpno+"'"
	+" union all"
	+" select isnull(d.item_adjustamount,0) from tlk_tm_budget_change_out d,tlk_tm_budget m where d.parent=m.id and m.statelabel='' and d.item_pwpno='"+pwpno+"'"
	+" ) t";
	*/
	var sql = " select sum(t.item_amount) as item_amount,'"+domainid+"' as domainid from("
	+" select isnull(item_tmexpenses,0) as item_amount from tlk_tm_budget_expense where item_pwpno='"+pwpno+"' and item_period ='"+period+"'"
	+" union all"
	+" select isnull(d.item_transferamount,0) from tlk_tm_budget_change_in d,tlk_tm_budget_change m where d.parent=m.id and m.statelabel='ȷ' and d.item_pwpno='"+pwpno+"' and item_period ='"+period+"'"
	+" union all"
	+" select isnull(d.item_adjustamount,0) from tlk_tm_budget_change_out d,tlk_tm_budget_change m where d.parent=m.id and m.statelabel!='TM' and d.item_pwpno='"+pwpno+"' and item_period ='"+period+"'"
	+" ) t";

	var amount = 0;
	var datas = queryBySQL(sql);
	var iter = datas.iterator();
	if (iter.hasNext()) {
		var data = iter.next();
		amount = data.getItemValueAsDouble("amount");
	}
	return amount;
}

//õ
function getCommitmentAmount(pwpno){
	var domainid = getDomainid();
	if(!pwpno) pwpno = getItemValue("pwpno");

	//
	var sql1 = " select sum(cd.item_amount) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_commitment_detail cd,tlk_tm_commitment c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and c.statelabel<>'ȡ' and cd.item_pwpno='"+pwpno+"'";
	var count1 = 0;
	var data1 = queryBySQL(sql1);
	var iter1 = data1.iterator();
	if(iter1.hasNext()){
		var doc1 = iter1.next();
		count1 = doc1.getItemValueAsDouble("amount");
	}
	//
	var sql2 = " select sum(cd.item_ac) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_commitment_discount cd,tlk_tm_commitment c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and c.statelabel<>'ȡ' and cd.item_pwpno='"+pwpno+"'";
	var count2 = 0;
	var data2 = queryBySQL(sql2);
	var iter2 = data2.iterator();
	if(iter2.hasNext()){
		var doc2 = iter2.next();
		count2 = doc2.getItemValueAsDouble("amount");
	}
	//
	var sql3 = " select sum(cd.item_amount) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_commitment_others cd,tlk_tm_commitment c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and c.statelabel<>'ȡ' and cd.item_pwpno='"+pwpno+"'";
	var count3 = 0;
	var data3 = queryBySQL(sql3);
	var iter3 = data3.iterator();
	if(iter3.hasNext()){
		var doc3 = iter3.next();
		count3 = doc3.getItemValueAsDouble("amount");
	}
	//
	var sql4 = " select sum(cd.item_dayamount) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_commitment_sample cd,tlk_tm_commitment c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and c.statelabel<>'ȡ' and cd.item_pwpno='"+pwpno+"'";
	var count4 = 0;
	var data4 = queryBySQL(sql4);
	var iter4 = data4.iterator();
	if(iter4.hasNext()){
		var doc4 = iter4.next();
		count4 = doc4.getItemValueAsDouble("amount");
	}
	
    //Ƴ
	var sql5 = "select sum(item_adjustamount) as item_amount,'"+domainid+"' as domainid"
			 +" from tlk_tm_budget_change_out"
			 +" where statelabel = '' and item_pwpno = '"+pwpno+"'";
	var count5 = 0;
	var data5 = queryBySQL(sql5);
	var iter5 = data5.iterator();
	if(iter5.hasNext()){
	    var doc5 = iter5.next();
	    count5 = doc5.getItemValueAsDouble("amount");
	}
    //
	var sql6 = "select sum(item_transferamount) as item_amount,'"+domainid+"' as domainid"
			 +" from tlk_tm_budget_change_in"
			 +" where statelabel = '' and item_pwpno = '"+pwpno+"'";
	var count6 = 0;
	var data6 = queryBySQL(sql6);
	var iter6 = data6.iterator();
	if(iter6.hasNext()){
	    var doc6 = iter6.next();
	    count6 = doc6.getItemValueAsDouble("amount");
	}
    //Ӷ
	var sql7 = "select sum(item_amount) as item_amount,'"+domainid+"' as domainid"
			 +" from tlk_tm_expense_others o, tlk_tm_expense c"
			 +" where o.parent=c.id and c.statelabel <>'ҵ' and o.item_pwpno = '"+pwpno+"'";
	var count7 = 0;
	var data7 = queryBySQL(sql7);
	var iter7 = data7.iterator();
	if(iter7.hasNext()){
	    var doc7 = iter7.next();
	    count7 = doc7.getItemValueAsDouble("amount");
	}
    //º۵
	var sql8 = "select sum(cd.item_amount) as item_amount,'"+domainid+"' as domainid"
			 +" from tlk_tm_expense_discount cd ,tlk_tm_expense c"
			 +" where cd.parent=c.id and c.statelabel <>'ҵ' and cd.item_pwpno = '"+pwpno+"'";
	var count8 = 0;
	var data8 = queryBySQL(sql8);
	var iter8 = data8.iterator();
	if(iter8.hasNext()){
	    var doc8 = iter8.next();
	    count8 = doc8.getItemValueAsDouble("amount");
	}
      	//
	var sql9 = " select sum(cd.item_actual_quantity*cd.item_dayprice) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_commitment_sample_1 cd,tlk_tm_commitment c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and c.statelabel<>'ȡ' and cd.item_pwpno='"+pwpno+"'";
	var count9 = 0;
	var data9 = queryBySQL(sql9);
	var iter9 = data9.iterator();
	if(iter9.hasNext()){
		var doc9 = iter9.next();
		count9 = doc9.getItemValueAsDouble("amount");
	}
	var value = count1 + count2 + count3 + count4 - count5 - count6 + count7 + count8 + count9;
	return value;
}

//õѱ
function getExpenseAmount(pwpno){
	var domainid = getDomainid();
	if(!pwpno) pwpno = getItemValue("pwpno");

	//ϸۿۡӶ
	var sql1 = " select sum(cd.item_amount) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_expense_detail cd,tlk_tm_expense c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and cd.item_pwpno='"+pwpno+"'";
	var count1 = 0;
	var data1 = queryBySQL(sql1);
	var iter1 = data1.iterator();
	if(iter1.hasNext()){
		var doc1 = iter1.next();
		count1 = doc1.getItemValueAsDouble("amount");
	}
	//
	var sql2 = " select sum(cd.item_amount) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_expense_discount cd,tlk_tm_expense c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and cd.item_pwpno='"+pwpno+"'";
	var count2 = 0;
	var data2 = queryBySQL(sql2);
	var iter2 = data2.iterator();
	if(iter2.hasNext()){
		var doc2 = iter2.next();
		count2 = doc2.getItemValueAsDouble("amount");
	}
	//
	var sql3 = " select sum(cd.item_amount) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_expense_others cd,tlk_tm_expense c"
		+" where cd.parent=c.id and c.statelabel <> 'ҵ' and cd.item_pwpno='"+pwpno+"'";
	var count3 = 0;
	var data3 = queryBySQL(sql3);
	var iter3 = data3.iterator();
	if(iter3.hasNext()){
		var doc3 = iter3.next();
		count3 = doc3.getItemValueAsDouble("amount");
	}
	//
/*	var sql4 = " select sum(item_accountingamount) as item_amount,'"+domainid+"' as domainid"
		+" from tlk_tm_commitment_sample cd,tlk_tm_commitment c"
		+" where cd.parent=c.id and c.statelabel <> 'ȡ' and cd.item_pwpno='"+pwpno+"'";
	var count4 = 0;
	var data4 = queryBySQL(sql4);
	var iter4 = data4.iterator();
	if(iter4.hasNext()){
		var doc4 = iter4.next();
		count4 = doc4.getItemValueAsDouble("amount");
	}
	var value = count1 + count2 + count3 + count4;
	*/
	var value = count1 + count2 + count3;
	return value;
}

/**ȡͻ
* customerno ͻ
* startDate ڣݴڻȡ
* isLastYear Ƿ
* quarter ȣ1/2/3/4
*/
function getCustomerSales(customerno, startDate, isLastYear, quarter) {
	var year = "";
	var strDate = "";
	var iyear = 0; //var startDate = getItemValueAsDate("startdate");
	if (startDate != null) {
		strDate = format(startDate, "yyyy-MM-dd");
	}
	var sql = "select * from TLK_CALENDAR where '" + strDate + "' between ITEM_FROM and ITEM_TO";
	var cal = findBySQL(sql);
	year = cal.getItemValueAsString("year");
	if (year != null && year.length() > 3) {
		iyear = parseInt(year.substring(2, year.length()));
		if (isLastYear) iyear = iyear - 1;
	}
	var syear = "FY" + iyear;
	var sqlQuarter = "";
	if (quarter) {
		sqlQuarter = " and item_quarter=" + quarter;
	} //var customerno = getItemValue("customerno");
	var countSql = "select SUM(item_sales) from tlk_customersales  where ITEM_customerno='" + customerno + "'" 
		+ " and ITEM_SALESSTARTDATE >= (select MIN(item_from) from TLK_CALENDAR where ITEM_YEAR='" + syear + "'" + sqlQuarter + ")" 
		+ " and ITEM_SALEENDDATE <= (select MAX(item_to) from TLK_CALENDAR where ITEM_YEAR='" + syear + "'" + sqlQuarter + ")";
	var value = sumBySQL(countSql);
	return value;
}

/**ȡĳ¿ͻ
* customerno ͻ
* startDate ڣݴڻȡ
* isLastYear Ƿ
* beginNum ʼ·
* endNum ·
*/
function getCustomerSalesByMonths(customerno, startDate, isLastYear, beginNum, endNum) {
	var year = "";
	var strDate = "";
	var iyear = 0; //var startDate = getItemValueAsDate("startdate");
	if (startDate != null) {
		strDate = format(startDate, "yyyy-MM-dd");
	}
	var sql = "select * from TLK_CALENDAR where '" + strDate + "' between ITEM_FROM and ITEM_TO";
	var cal = findBySQL(sql);
	year = cal.getItemValueAsString("year");
	if (year != null && year.length() > 3) {
		iyear = parseInt(year.substring(2, year.length()));
		if (isLastYear) iyear = iyear - 1;
	}
	var syear = "FY" + iyear;

	 //var customerno = getItemValue("customerno");
	var countSql = "select SUM(item_sales) from tlk_customersales  where ITEM_customerno='" + customerno + "'" 
		+ " and ITEM_SALESSTARTDATE >= (select MIN(item_from) from TLK_CALENDAR where ITEM_YEAR='" + syear + "' and item_num between "+beginNum+" and "+endNum+")" 
		+ " and ITEM_SALEENDDATE <= (select MAX(item_to) from TLK_CALENDAR where ITEM_YEAR='" + syear + "' and item_num between "+beginNum+" and "+endNum+")";
	var value = sumBySQL(countSql);
	return value;
}

/**жϵǰ¼ûǷȨ޴Ӷֶ
* stateLabels ɼ״̬  ַ
* nodeNames ַ,ӵ,öŸ
* isParent Ƿøĵ,Ϊĵ
*/
function hiddenField(stateLabels,nodeNames,isParent){
	var value = true;
	var domainid = getDomainid();
	var user = getWebUser();
	var doc;
	if(isParent){ 
		doc = getParentDocument();
	} else {
		doc = getCurrentDocument();
	}
	var userList = doc.getAuditorList();
	var count = 1;
	if(stateLabels.constructor == Array){
		count = stateLabels.length;
	}
	for(var i=0; i<count; i++){
		var stateLabel;
		if(stateLabels.constructor == Array){
			stateLabel = stateLabels[i];
		} else {
			stateLabel = stateLabels;
		}

		if(stateLabel.equals(doc.getStateLabel())){
			if (userList.indexOf(user.getId()) > 0){
				value = false;
			}
			break;
		}
	}
	if (value == true){
		var sql ="select '"+domainid+"' as domainid, AUDITOR as ITEM_AUDITOR,PROCESSTIME as ITEM_PROCESSTIME "
			+" from t_relationhis  where ENDNODENAME in ("+nodeNames+")"
			+" and DOCID='"+doc.getId()+"' and auditor='"+user.getId()+"'";
		var datas = queryBySQL(sql);
		if(datas != null && datas.size() > 0){
			value = false;
		}
	}
	return value;
}


/**ȡһĳͻϢ
*--customername ͻ
customerno ͻ
*varlue ҪȡĿͻϢ
*
*/
function getContractCustomer(customername, varlue, contractdate) {
	var name = "";
	name = getItemValueAsString(customername);
	var strDate = "";
	var iyear = 0;
	if (contractdate != null) {
		strDate = format(contractdate, "yyyy");
		iyear = parseInt(strDate) - 1;
	}
	//var sql = "select * from tlk_tm_contract_customer where datepart(yy ,item_contractdate) =" + iyear + " and item_customername ='" + name + "'";
	var sql = "select * from tlk_tm_contract_customer where datepart(yy ,item_contractdate) =" + iyear + " and item_customerno ='" + name + "'";
	var doc = findBySQL(sql);
	var value = doc.getItemValueAsDouble(varlue);
	return value;
}

function getWhereSQL(itemNames,conditions,fieldNames){
	var sqlWhere = "";
	for (var i = 0; i < itemNames.length; i++) {
		var itemValue = getItemValue(itemNames[i]);
		if (itemValue != null && itemValue.trim().length() > 0) {
			if (conditions[i] == "like") itemValue = "%" + itemValue + "%";
			var item = fieldNames[i];
			//for Oracle
			/*if (conditions[i] == "<=" || conditions[i] == ">=") {
				item = "to_char(" + item + ",'yyyy-MM-dd')";
			}*/
			sqlWhere += " and " + item + " " + conditions[i] + " '" + itemValue + "'";
		}
	}
	return sqlWhere;
}