Transfer Requisition: expenseTransfer.asp - how do I have the page save the negative and positive record and show it in tblExpense
See Below:
<%@ Language=VBScript %>
<% Option Explicit %>
<%
Response.Expires = -5
Dim intFY, intQTR, strBCat, intBCatID, strAmount, strNotes, intCaseID, intExpenseID, strCaseName
Dim strSQL, Conn, rs
Dim intReqType, strReqNum, strReqMod, strReqDte, strRAmt, strObDte, strOAmt, intTransferNo
Dim strSumRAmt, strSumOAmt
intExpenseID = Request.QueryString("EID")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open Session("ConnStr")
Set rs = Server.CreateObject("ADODB.Recordset")
If Request.Form("sSave") = "SAVE" Then
'*** SAVE TRANSFER TO NEW CASE
intCaseID = Request.Form("txtTrCase")
intFY = Request.Form("txtFY")
intQTR = Request.Form("txtQTR")
strBCat = Request.Form("txtBCat")
intReqType = 7
strReqNum = Request.Form("txtReqNum")
strReqMod = Request.Form("txtReqMod")
strReqDte = Request.Form("txtReqDte")
strRAmt = Replace(Request.Form("txtRAmt"),",","")
strObDte = Request.Form("txtObDte")
strOAmt = Replace(Request.Form("txtOAmt"),",","")
strNotes = Replace(Request.Form("txtNotes"),"'","''")
strSQL = "AddExpenseInfo " & intCaseID & ","
If intFY & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & intFY & ","
If intQTR & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & intQTR & ","
If strBCat & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & strBCat & ","
If intReqType & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & intReqType & ","
If strReqNum & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & "'" & strReqNum & "'" & ","
If strReqMod & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & "'" & strReqMod & "'" & ","
If strReqDte & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & "'" & strReqDte & "'" & ","
If strRAmt & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & strRAmt & ","
If strObDte & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & "'" & strObDte & "'" & ","
If strOAmt & "" = "" Then strSQL = strSQL & "NULL," Else strSQL = strSQL & strOAmt & ","
If strNotes & "" = "" Then strSQL = strSQL & "NULL" Else strSQL = strSQL & "'" & strNotes & "'"
Response.write strSQL
'Conn.Execute strSQL
'*** SAVE TRANSFER TO ORIGINAL CASE - BE SURE TO PUT THE NEGATIVE VAULE
End If
%>
<HTML>
<head><TITLE>NLO BUDGET TRACKING SYSTEM</title>
<SCRIPT LANGUAGE="JavaScript">
function ValidateForm(FY,QTR,BCID,RT){
var dt=document.frmUpdate.txtObDte
if (FY.selectedIndex < 1) {
window.alert('Please select a Fiscal Year');
frmUpdate.txtFY.style.background = 'yellow';
return false;
}
if (QTR.selectedIndex < 1) {
window.alert('Please select a Quarter');
frmUpdate.txtQTR.style.background = 'yellow';
return false;
}
if (BCID.selectedIndex < 1) {
window.alert('Please select a Category');
frmUpdate.txtBCat.style.background = 'yellow';
return false;
}
if (RT.selectedIndex < 1) {
window.alert('Please select a Request Type');
frmUpdate.txtReqType.style.background = 'yellow';
return false;
}
return true
}
function currencyFormat(fld, milSep, decSep, e) {
var sep = 0;
var key = '';
var i = j = 0;
var len = len2 = 0;
var strCheck = '-0123456789';
var aux = aux2 = '';
var whichCode = (window.Event) ? e.which : e.keyCode;
if (whichCode == 13) return true; // Enter
key = String.fromCharCode(whichCode); // Get key value from key code
if (strCheck.indexOf(key) == -1) return false; // Not a valid key
len = fld.value.length;
for(i = 0; i < len; i++)
if ((fld.value.charAt(i) != '0') && (fld.value.charAt(i) != decSep)) break;
aux = '';
for(; i < len; i++)
if (strCheck.indexOf(fld.value.charAt(i))!=-1) aux += fld.value.charAt(i);
aux += key;
len = aux.length;
if (len == 0) fld.value = '';
if (len == 1) fld.value = '0'+ decSep + '0' + aux;
if (len == 2) fld.value = '0'+ decSep + aux;
if (len > 2) {
aux2 = '';
for (j = 0, i = len - 3; i >= 0; i--) {
if (j == 3) {
aux2 += milSep;
j = 0;
}
aux2 += aux.charAt(i);
j++;
}
fld.value = '';
len2 = aux2.length;
for (i = len2 - 1; i >= 0; i--)
fld.value += aux2.charAt(i);
fld.value += decSep + aux.substr(len - 2, len);
}
return false;
}
function LoadURL(url)
{
opener.location.href=url
};
function changeColor(th) {
th.style.background = "white";
}
var sSep= "/";
var minYear=1900;
var maxYear=2100;
function isInteger(s){
var i;
for (i = 0; i < s.length; i++){
// Check that current character is number.
var c = s.charAt(i);
if (((c < "0") || (c > "9"))) return false;
}
// All characters are numbers.
return true;
}
function stripChars(s, bag){
var i;
var returnString = "";
// Search through string's characters one by one.
// If character is not in bag, append to returnString.
for (i = 0; i < s.length; i++){
var c = s.charAt(i);
if (bag.indexOf(c) == -1) returnString += c;
}
return returnString;
}
function daysInFebruary (year){
// February has 29 days in any year evenly divisible by four,
// EXCEPT for centurial years which are not also divisible by 400.
return (((year % 4 == 0) && ( (!(year % 100 == 0)) || (year % 400 == 0))) ? 29 : 28 );
}
function DaysArray(n) {
for (var i = 1; i <= n; i++) {
this[i] = 31
if (i==4 || i==6 || i==9 || i==11) {this[i] = 30}
if (i==2) {this[i] = 29}
}
return this
}
function isDate(dtStr){
var daysInMonth = DaysArray(12)
var pos1=dtStr.indexOf(sSep)
var pos2=dtStr.indexOf(sSep,pos1+1)
var strMonth=dtStr.substring(0,pos1)
var strDay=dtStr.substring(pos1+1,pos2)
var strYear=dtStr.substring(pos2+1)
strYr=strYear
if (strDay.charAt(0)=="0" && strDay.length>1) strDay=strDay.substring(1)
if (strMonth.charAt(0)=="0" && strMonth.length>1) strMonth=strMonth.substring(1)
for (var i = 1; i <= 3; i++) {
if (strYr.charAt(0)=="0" && strYr.length>1) strYr=strYr.substring(1)
}
month=parseInt(strMonth)
day=parseInt(strDay)
year=parseInt(strYr)
if (pos1==-1 || pos2==-1){
alert("The date format should be : mm/dd/yyyy")
return false
}
if (strMonth.length<1 || month<1 || month>12){
alert("Please enter a valid month")
return false
}
if (strDay.length<1 || day<1 || day>31 || (month==2 && day>daysInFebruary(year)) || day > daysInMonth[month]){
alert("Please enter a valid day")
return false
}
if (strYear.length != 4 || year==0 || year<minYear || year>maxYear){
alert("Please enter a valid 4 digit year between "+minYear+" and "+maxYear)
return false
}
if (dtStr.indexOf(sSep,pos2+1)!=-1 || isInteger(stripChars(dtStr, sSep))==false){
alert("Please enter a valid date")
return false
}
return true
}
// End -->
</script>
</head>
<BODY bgcolor="#c0c0c0">
<div align="center">
<form action="expenseTransfer.asp?EID=<%= intExpenseID %>" method="post" name="frmUpdate" >
<div align="center">
<%
'*** GET EXPENSE ROW INFORMATION
strSQL = "GetExpenseRow " & intExpenseID
rs.Open strSQL, Conn
If NOT rs.EOF Then
strCaseName = rs("CaseName")
intCaseID = rs("CaseID")
intFY = rs("FY")
intQTR = rs("Qtr")
strBCat = rs("BudgetCategory")
intBCatID = rs("BudgetCatID")
strReqNum = rs("ReqNum")
strReqMod = rs("ReqMod")
End If
rs.Close
'*** GET REMAINING INFORMATION
strSQL = "SELECT SUM(dbo.tblExpense.AmtRequested) AS AmtRequested, SUM(dbo.tblExpense.AmtObligated) AS AmtObligated, COUNT(dbo.tblExpense.TransferID) AS TransferCount FROM tblExpense WHERE CASEID = " & intCaseID & " AND REQNUM = '" & strReqNum & "'"
If strReqMod & "" = "" Then strSQL = strSQL & " AND REQMOD IS NULL" Else strSQL = strSQL & " AND REQMOD = '" & strReqMod & "'"
'Response.write strSQL
'Response.end
rs.Open strSQL, Conn
If NOT rs.EOF Then
strSumRAmt = rs("AmtRequested")
strSumOAmt = rs("AmtObligated")
intTransferNo = rs("TransferCount")
If intTransferNo & "" = "" Then intTransferNo = 0
End If
rs.Close
%>
<table border="0" bgcolor="#c0c0c0" align=left>
<tr><td COLSPAN=3>Transfer from <%= strCaseName %></td></tr>
<tr>
<td ><B>FY</td><td ><%= intFY %></td>
<td> </td>
<td><B>TRANSFER TO CASE</td>
<%
'*** CASE DYNAMIC DROP DOWN
strSQL = "GetCaseName "
rs.Open strSQL, Conn
%>
<td>
<select name="txtTrCase" STYLE='FONT-FAMILY: Verdana, Arial;FONT-WEIGHT: heavy;FONT-SIZE: 10pt;COLOR: black;' onClick="changeColor(this);">
<% If not rs.EOF Then %>
<option value=""></option>
<% Do Until rs.EOF %>
<option value='<%= rs("CaseID") %>'><%= rs("CaseName") %></option>
<% rs.MoveNext %>
<% Loop %>
<% End IF %>
</select>
</td>
</tr>
<tr>
<td ><B>QTR</td><td ><%= intQTR %></td>
<td> </td>
<td><B>TRANSFER TO QTR</td>
<td width="82"><select name='txtQTR' STYLE='FONT-FAMILY: Verdana, Arial;FONT-WEIGHT: heavy;FONT-SIZE: 10pt;COLOR: black;' onClick="changeColor(this);">
<option></option>
<option <% if intQTR=1 Then Response.write "selected" %>>1</option>
<option <% if intQTR=2 Then Response.write "selected" %>>2</option>
<option <% if intQTR=3 Then Response.write "selected" %>>3</option>
<option <% if intQTR=4 Then Response.write "selected" %>>4</option>
</select>
</td>
</tr>
<tr>
<td ><B>CATEGORY</td><td ><%= strBCat %></td>
<td> </td>
<td><B>TRANSFER TO CATEGORY</td>
<%
rs.Close
'*** BUDGET CATEGORY DYNAMIC DROP DOWN
strSQL = "sp_GetBudgetCats "
rs.Open strSQL, Conn
%>
<td>
<select name="txtBCat" STYLE='FONT-FAMILY: Verdana, Arial;FONT-WEIGHT: heavy;FONT-SIZE: 10pt;COLOR: black;' onClick="changeColor(this);">
<% If not rs.EOF Then %>
<option value=""></option>
<% Do Until rs.EOF %>
<option value='<%= rs("BudgetCatID") %>'
<% If intBCatID = rs("BudgetCatID") Then Response.write " selected " %>
><%= rs("BudgetCategory") %></option>
<% rs.MoveNext %>
<% Loop %>
<% End IF %>
</select>
</td>
</tr>
<tr>
<td ><B>REQ. NUM</td><td ><%= strReqNum %></td>
<td> </td>
<td><B>REQUEST DATE</td>
<td width="13%" height="29"><input type="text" onClick='changeColor(this);' name="txtReqDte" size="15"></td>
</tr>
<tr>
<td ><B>REQ. MOD</td><td ><%= strReqMod %></td>
<td> </td>
<td><B>AMOUNT REQUESTED</td>
<td width="2%" height="29"><input type="text" name="txtRAmt" value="<%= FormatCurrency(0.00) %>" onKeyPress="return(currencyFormat(this,',','.',eve nt))" size="15"></td>
</tr>
<tr>
<td ><B>REMAINING AMT REQ'D</td><td ><%= FormatCurrency(strSumRAmt) %></td>
<td> </td>
<td><B>DATE OBLIGATED</td>
<td width="15%" height="29"><input type="text" onClick='changeColor(this);' name="txtObDte" size="15"></td>
</tr>
<tr>
<td ><B>REMAINING AMT OBL'D</td>
<td ><%= FormatCurrency(strSumOAmt) %></td>
<td> </td>
<td><B>AMOUNT OBLIGATED</td>
<td width="17%" height="29"><input type="text" name="txtOAmt" value="<%= FormatCurrency(0.00) %>" onKeyPress="return(currencyFormat(this,',','.',eve nt))" size="15"></td>
</tr>
<tr>
<td ><B>TRANSFER ID</td><td ><%= GetAlpha(intTransferNo + 1) %></td>
<td> </td>
<td></td>
<td width="17%" height="29"></td>
</tr>
<TR>
<td colspan=5>
<p align="center">
<INPUT TYPE=HIDDEN NAME='hCaseID' value='<%= intCaseID %>'>
<INPUT TYPE=Submit STYLE='FONT-FAMILY: Verdana, Arial;FONT-WEIGHT: heavy;FONT-SIZE: 10pt;COLOR: NAVY;WIDTH: 75px;' NAME='sSave' VALUE='SAVE'><BR>
<p align="center"><a href="javascript
:lockU=0;LoadURL('expense.asp?CID= <%= intCaseID %>');self.close()">CLOSE WINDOW</a>
</td>
</tr>
</table>
</div>
</form>
<%
rs.Close
%>
</BODY>
</HTML>
amdba33
Data Engineer
Expense Transfer problem ASP / SQL Help needed?
