setconditiondatediff.asp

学生档案与成绩管理（1）学生档案管理：记录每个学生的档案信息

<style type="text/css">
	#FieldToConditionSelect {position:absolute;visibility:hidden;left:0;top:0;width:0;height:0;z-index:-1}
	#AllConditionDetail {position:absolute;visibility:hidden;left:0;top:0;width:0;height:0;z-index:-1}
</style>    
<!-- #include file = "../../../include/asphead.asp" -->
<!-- #include file = "../../../include/function.asp" -->
<!-- #include file = "../../../include/calendar.asp" -->
<%CheckUser()%>
<link href="../../../include/main.css" rel="stylesheet" type="text/css">
<script src="../../../include/function.js" type="text/javascript"></script>
<script src="../../../include/formulaedit.js" type="text/javascript"></script>
<script ID="clientEventHandlersJS" LANGUAGE="javascript">
<!--

	var strDateFrom,strDateTo,unit;

	//根据公式数据，初始化公式设置的界面，FieldToDateFrom选择框，FieldToDateTo选择框,DateDiffUnit选择框
	//并设置其selectedIndex值
	function InitFormula() 
	{
		var FormulaStr;	//公式内部结构串，Condition ItemSep SQLDesc ItemSep Divisor
						//其中的Condition，采用与查询条件类似的结构：Fldxxx ConItemSep Value GroupSep Fldxxx ConItemSep Value
		var FieldID,pos1,pos2;
		if (window.dialogArguments != null)
		{
			FormulaStr=JTrim(window.dialogArguments[0]);	//公式内部结构串
			//初始化界面上的FieldToDateFrom选择框，FieldToDateTo选择框,DateDiffUnit选择框
			pDateFrom.innerHTML =window.dialogArguments[1];
			pDateTo.innerHTML =window.dialogArguments[2];
			FieldToConditionSelect.innerHTML =window.dialogArguments[3];
			ConditionDataType =window.dialogArguments[4].split(",");
			AllConditionDetail.innerHTML =window.dialogArguments[5];
		}
		
		//初始化各选择框的selectedIndex
		frmData.Condition.value=''
		frmData.ConditionDesc.value=''
		frmData.FieldToDateFrom.selectedIndex=0;		//缺省设为系统日期
		frmData.FieldToDateTo.selectedIndex=0;			//缺省设为系统日期
		frmData.DateDiffUnit.selectedIndex=0;				//缺省设为年
		if(FormulaStr!='')
		{
			var parts,SQLParts;
			parts=FormulaStr.split(ItemSep);
			if(parts.length>1)
			{
				//初始化条件部分
				if(!isEmpty(parts[0]))
				{
					frmData.Condition.value=parts[0];
					frmData.ConditionDesc.value=FormatConditionToCaption(parts[0]);
				}
				//初始化公式内容
				//parts[1]为SQLDesc，即datediff("d",[Fld1],[Fld2])
				//解析datediff("d",[Fld1],[Fld2])
				SQLParts=parts[1].split(",");
				//判断时间单位
				if(SQLParts[0].lastIndexOf("yyyy")>=SQLParts[0].length-5)
					//最后5个字符中包含yyyy，则单位为年
					frmData.DateDiffUnit.selectedIndex =0;
				else if(SQLParts[0].lastIndexOf("m")>=SQLParts[0].length-2)
					//最后2个字符中包含m，则单位为月
					frmData.DateDiffUnit.selectedIndex =1;
				else if(SQLParts[0].lastIndexOf("d")>=SQLParts[0].length-2)
					//最后2个字符中包含d，则单位为日
					frmData.DateDiffUnit.selectedIndex =2;
				else if(SQLParts[0].lastIndexOf("h")>=SQLParts[0].length-2)
					//最后2个字符中包含h，则单位为小时
					frmData.DateDiffUnit.selectedIndex =3;
				else if(SQLParts[0].lastIndexOf("n")>=SQLParts[0].length-2)
					//最后2个字符中包含n，则单位为分
					frmData.DateDiffUnit.selectedIndex =4;

				if(parts[1].lastIndexOf("+iif(")>0)
				{
					//要计算整年数、整月数或整天数
					frmData.DateDiffUnit.selectedIndex =frmData.DateDiffUnit.selectedIndex+5;
				}

				//判断FieldToDateFrom，如果公式中出现now()，表明当前选中的是系统
				//日期；如果出现[Fld]字符串，表明当前选中的是某字段；其它，可认为是指定日期
				if(SQLParts[1].indexOf("now()")>=0)
					frmData.FieldToDateFrom.selectedIndex=0;		//从，为系统日期
				else 
				{
					pos1=SQLParts[1].indexOf("[Fld");
					if(pos1>=0)
					{
						//从，为某日期字段
						pos2=SQLParts[1].lastIndexOf("]");
						if(pos2>pos1+4)
						{
							FieldID=SQLParts[1].substr(pos1+4,pos2-(pos1+4))
							SetIndex(frmData.FieldToDateFrom,FieldID)
						}
					}
					else
					{
						frmData.FieldToDateFrom.selectedIndex=1;		//从，为某指定日期
						frmData.DedicateFrom.value=replace(SQLParts[1],"cdate(","")	//去掉其中的cdate( 
						frmData.DedicateFrom.value=replace(frmData.DedicateFrom.value,")","")	//去掉其中的右括弧 
						frmData.DedicateFrom.value=replace(frmData.DedicateFrom.value,"'","")	//去掉其中的单引号
					}
				}
				if(SQLParts[2].indexOf("+iif(")>0)
				{
					//计算整年整月整日时，公式中还含有偏移量的修正，去掉之
					SQLParts[2]=SQLParts[2].substr(0,SQLParts[2].indexOf("+iif("));
				}
				if(SQLParts[2].indexOf("now()")>=0)
					frmData.FieldToDateTo.selectedIndex=0;		//至，为系统日期
				else 
				{
					pos1=SQLParts[2].indexOf("[Fld");
					if(pos1>=0)
					{
						//至，为某日期字段
						pos2=SQLParts[2].lastIndexOf("]");
						if(pos2>pos1+4)
						{
							FieldID=SQLParts[2].substr(pos1+4,pos2-(pos1+4))
							SetIndex(frmData.FieldToDateTo,FieldID)
						}
					}
					else
					{
						frmData.FieldToDateTo.selectedIndex=1;		//至，为某指定日期
						frmData.DedicateTo.value=replace(SQLParts[2],"cdate(","")	//去掉其中的cdate( 
						frmData.DedicateTo.value=replace(frmData.DedicateTo.value,")","")	//去掉其中的右括弧 
						frmData.DedicateTo.value=replace(frmData.DedicateTo.value,"'","")	//去掉其中的单引号
					}
				}
			}			
		}
	}	

	//表达式中的字段id还原成字段名称[XXX]，用于在页面中显示
	function FormatToCaption(Desc) 
	{
		var Count,Caption,FieldID;
		Count=FieldToCondition.length
		for(var i=1;i<=Count-1;i++)	//抛弃了最前面的空选项
		{
			Caption=FieldToCondition[i].text;
			Caption='['+Caption+']';
			FieldID=FieldToCondition[i].value;
			FieldID='[Fld'+FieldID+']';
			Desc=replace(Desc,FieldID,Caption);
		}
		return(Desc);
	}

	//设置公式条件
	function PopSetCondition() 
	{
		var ret,CurCondition;
		CurCondition=frmData.Condition.value;
		CurCondition=replace(CurCondition,GroupSep,JoinChar)	//GroupSep不能通过QueryString传递，暂时用JoinChar代替
		ret=window.showModalDialog ("condition.asp?CurCondition="+CurCondition,"Setting","dialogwidth:470px;dialogheight:445px;Status:0;center:1;resizable=1;");
		if(typeof(ret)!="undefined" && ret!=false)
		{
			if(ret.charAt(0)=="1")
			{
				var parts
				ret=ret.substr(2,ret.length-2);
				frmData.Condition.value=ret;
				frmData.ConditionDesc.value=FormatConditionToCaption(ret);
			}
		}
	}

	//日期设定方式发生变化，disable或enable日期输入框
	function ChangeStyle(styleCtrl,dateCtrl)
	{
		var btnCalName;		//日期文本框右边的button名称
		btnCalName="btnFor" + dateCtrl.name;
		if(styleCtrl.selectedIndex!=1)	//只要不是指定日期，文本框要灰掉
		{
			dateCtrl.value="";
			dateCtrl.style.backgroundColor="#e0e0e0";
			dateCtrl.disabled=true;
			eval("frmData."+btnCalName+".disabled=true;");
		}
		else
		{
			dateCtrl.style.backgroundColor="white";
			dateCtrl.disabled=false;
			eval("frmData."+btnCalName+".disabled=false;");
		}
	}

	//公式数据的合法性检查(Data Check)
	function FormulaDC() 
	{
		if(frmData.FieldToDateFrom.selectedIndex==0 && frmData.FieldToDateTo.selectedIndex==0)
		{
			alert("日期区间的从、至都是系统时间，是无意义的，请修改。")
			return false;
		}
		if (frmData.FieldToDateFrom.selectedIndex==1)
		{
			//如果选择的是指定日期，则需要验证日期填写值的合法性
			var tmp;
			tmp=JTrim(frmData.DedicateFrom.value);
			if(tmp=='')
			{
				alert("日期区间(从)的指定日期\\时间未填写，请填写。")
				return false;
			}
			else
			{
				if(!isValidDate(tmp) && !isValidDateAndTime(tmp))
				{
					alert("日期区间(从)输入不正确，正确的日期\\时间格式为YYYY-MM-DD，或YYYY-MM-DD hh:mm:ss。")
					return(false);	
				}
			}
		}
		if (frmData.FieldToDateTo.selectedIndex==1)
		{
			tmp=JTrim(frmData.DedicateTo.value);
			if(tmp=='')
			{
				alert("日期区间(至)的指定日期\\时间未填写，请填写。")
				return false;
			}
			else
			{
				if(!isValidDate(tmp) && !isValidDateAndTime(tmp))
				{
					alert("日期区间(至)输入不正确，正确的日期\\时间格式为YYYY-MM-DD，或YYYY-MM-DD hh:mm:ss。")
					return(false);	
				}
			}
		}
		return true;
	}
	
	//获取时间间隔的计算公式
	function GetSQLDesc()
	{
		//确定时间单位
		if(frmData.DateDiffUnit.selectedIndex==0 || frmData.DateDiffUnit.selectedIndex==5)
			unit="yyyy"
		else if(frmData.DateDiffUnit.selectedIndex==1 || frmData.DateDiffUnit.selectedIndex==6)
			unit="m";
		else if(frmData.DateDiffUnit.selectedIndex==2 || frmData.DateDiffUnit.selectedIndex==7)
			unit="d";
		else if(frmData.DateDiffUnit.selectedIndex==3 || frmData.DateDiffUnit.selectedIndex==8)
			unit="h";
		else if(frmData.DateDiffUnit.selectedIndex==4 || frmData.DateDiffUnit.selectedIndex==9)
			unit="n";
		//从值
		if(frmData.FieldToDateFrom.selectedIndex==0)
			strDateFrom="now()"
		else
		{
			if(frmData.FieldToDateFrom.selectedIndex==1)
				strDateFrom="cdate('" + JTrim(frmData.DedicateFrom.value) + "')" 
			else
				strDateFrom="[Fld" + JTrim(frmData.FieldToDateFrom.value) + "]" 
		}
		//至值
		if(frmData.FieldToDateTo.selectedIndex==0)
			strDateTo="now()"
		else 
		{
			if(frmData.FieldToDateTo.selectedIndex==1)
				strDateTo="cdate('" + JTrim(frmData.DedicateTo.value) + "')" 
			else
				strDateTo="[Fld" + JTrim(frmData.FieldToDateTo.value) + "]" 
		}
		//拼写计算公式
		var SQLDesc;
		SQLDesc="datediff('" + unit + "'," + strDateFrom + "," + strDateTo + ")";
		if(frmData.DateDiffUnit.selectedIndex>=5)
		{
			//要计算整年数、整月数或整天数
			var Span;	//跨越数量
			var DateFromThenSpan;	//起始日期加跨越数量
			var Adjust,Adjust1,Adjust2;	//调整量
			Span=SQLDesc;
			DateFromThenSpan="dateadd('" + unit + "'," + Span + "," + strDateFrom + ")";
			Adjust1="iif(" + DateFromThenSpan + ">" + strDateTo + ",-1,0)";
			Adjust2="iif(" + DateFromThenSpan + "<" + strDateTo + ",1,0)";
			Adjust="iif(" + strDateFrom + "<=" + strDateTo + "," + Adjust1 + "," + Adjust2 + ")"
			SQLDesc=Span + "+" + Adjust; 
		}
		return(SQLDesc);
	}
	
	function Check()
	{
		if (FormulaDC()==true)
		{
			alert("检查通过。")
			return true;
		}
	}

	function Save()
	{
		var Condition,SQLDesc,Divisor

		if (FormulaDC()==false)
			return false;

		Condition=frmData.Condition.value;
		SQLDesc=GetSQLDesc();
		Divisor="";
		window.returnValue = "1,"+Condition+ItemSep+SQLDesc+ItemSep+Divisor;
		window.close();

	}
	
	function Cancel()
	{
		window.returnValue = "0";
		window.close();
	}

	function Init()
	{
		InitFormula();
		ChangeStyle(frmData.FieldToDateFrom,frmData.DedicateFrom);
		ChangeStyle(frmData.FieldToDateTo,frmData.DedicateTo);
	}

	function HasChange()
	{
		
	}
//-->
</script>

<html>
	<head>
		<title>设置公式</title>
		<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
	</head>
	<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" LANGUAGE=javascript onload="return Init()">
		<form METHOD="post" name="frmData">
			<table width="100%" border=0 align=center CELLSPACING=0 CELLPADDING="0">
				<tr height="35">
					<td colspan=5>
						<b>
							设置条件日期间隔项
						</b>
					</td>
				</tr>
				<tr height=105 valign="top">
					<td colspan=5>
						<table width="95%" border=0 CELLSPACING="1" CELLPADDING="0">
							<tr>
								<td bgcolor="#e0e0e0" width="15%">
									<p align="left">
										计算条件
									</p>
								</td>
								<td bgcolor="white" colspan=2 width="85%">
									<p align="left">
										<TEXTAREA rows=3 cols=80 readOnly=true style="FONT-SIZE:10pt;" NAME=ConditionDesc value=[ConditionDesc] style="WIDTH: 335px"></TEXTAREA>
										<input TYPE="hidden" NAME=Condition>
										<INPUT type='button' value='v' onclick=PopSetCondition() id='button' name='button'>
									</p>
								</td>
							</tr>
							<tr>
								<td rowspan=2 bgcolor="#e0e0e0">
									<p align="left">日期区间</p>
								</td>
								<td bgcolor="#e0e0e0" width="5%">
									<p align="left">从</p>
								</td>
								<td width="80%">
									<p align="left" id=pDateFrom>
									</p>
								</td>
							</tr>
							<tr>
								<td bgcolor="#e0e0e0">
									<p align="left">至</p>
								</td>
								<td width="80%">
									<p align="left" id=pDateTo>
									</p>
								</td>
							</tr>
							<tr>
								<td bgcolor="#e0e0e0" colspan=2>
									<p align="left">
										间隔单位
									</p>
								</td>
								<td bgcolor="white">
									<p align="left">
										<select name=DateDiffUnit style="WIDTH:130px">
											<option value=1>跨年份数</option>
											<option value=2>跨月份数</option>
											<option value=3>跨日数</option>
											<option value=4>跨小时数</option>
											<option value=5>跨分钟数</option>
											<option value=6>整年数</option>
											<option value=7>整月数</option>
											<option value=8>整日数</option>
											<option value=9>整小时数</option>
											<option value=10>整分钟数</option>
										</select>
									</p>
								</td>
							</tr>
						</table>
					</td>
				</tr>
				<tr height=40 valign=bottom>
					<td colspan="5">
						<table width="100%">
							<tr>
								<td width="20%"></td>
								<td width="20%"><input id="btnDC" type="button" value="检查" name="btnDC" style="cursor:hand; WIDTH: 50px; HEIGHT: 24px" LANGUAGE="javascript" onclick="return Check();"></td>
								<td width="20%"><input id="btnSave" type="button" value="确定" name="btnSave" style="cursor:hand; WIDTH: 50px; HEIGHT: 24px" LANGUAGE="javascript" onclick="return Save()"></td>
								<td width="20%"><input id="btnCancel" type="button" value="取消" name="btnCancel" style="cursor:hand; WIDTH: 50px; HEIGHT: 24px" LANGUAGE="javascript" onclick="return Cancel()"></td>
								<td width="20%"></td>
							</tr>
						</table>
					</td>
				</tr>
			</table>
			<input TYPE="hidden" NAME="SQLDesc">
			<input TYPE="hidden" NAME="Divisor">
		</form>
		<div id=FieldToConditionSelect>
		</div>
		<div id=AllConditionDetail>
		</div>
	</body>                                                                                                           
</html>