NetSuite 会计期间查询

使用SuiteQL封装会计期间查询常用函数

/**
	 * 获取日期所在会计期间
	 * @param {String} date 日期
	 * @returns {Number} 会计期间内部ID
	 */
	function getPeriodIdByDate(date) {
		var sql =
			"SELECT id FROM accountingPeriod WHERE (TO_DATE('" +
			date +
			"', 'YYYY-MM-DD' ) BETWEEN startdate AND enddate) AND isQuarter = 'F' AND isYear = 'F'";

		var qryResults = query.runSuiteQL(sql).asMappedResults();
		return qryResults[0] && qryResults[0].id;
	}

	/**
	 * 根据会计期间ID获取日期范围
	 * @param {Number} id 会计期间内部ID
	 * @returns {Object} 包含会计期间开始日期、结束日期的对象
	 */
	function getPeriodDateRangeById(id) {
		var data = {
			startDate: '',
			endDate: ''
		};

		var sql = 'SELECT startdate, enddate FROM accountingPeriod WHERE id = ' + id;
		var qryResults = query.runSuiteQL(sql).asMappedResults();
		if (qryResults[0]) {
			data.startDate = qryResults[0].startdate;
			data.endDate = qryResults[0].enddate;
		}

		return data;
	}

	/**
	 * 根据日期范围获取会计期间
	 * 注意：当endDate晚于startDate时，两者会交换
	 * @param {String} startDate 开始日期
	 * @param {String} endDate 截止日期
	 * @returns {Array} 会计期间内部ID
	 */
	function getPeriodIdByDateRange(startDate, endDate) {
		var ids = [];
		var sql =
			"SELECT id FROM accountingperiod WHERE ((TO_DATE ('" +
			startDate +
			"', 'YYYY-MM-DD') BETWEEN startdate AND enddate) or (TO_DATE ('" +
			endDate +
			"', 'YYYY-MM-DD') BETWEEN startdate AND enddate) or (TO_DATE ('" +
			startDate +
			"', 'YYYY-MM-DD') <= startdate AND TO_DATE ('" +
			endDate +
			"', 'YYYY-MM-DD') >= enddate)) AND isQuarter = 'F'	AND isYear = 'F'";
		var qryResults = query.runSuiteQL(sql).asMappedResults();
		for (var i = 0; i < qryResults.length; i++) {
			ids.push(qryResults[i].id);
		}
		return ids;
	}