DIVIDE BY ZERO ERROR, WHEN RUN FROM ASP PAGE

12/04/2007 - 17:03 por norrisinc | Informe spam
Here is the scenario, i have a stored procedure, it calculates
commissions and runs fine in SQL. However when the SP is run through
my asp page it will come back with a divide by zero error, but only
for one sales person... I have been through this code a hundred times
and can't find any errors. Any help would be much appreciated.

SP:

ALTER PROCEDURE [dbo].[SP_COMMISSIONS]
@SALESMAN VARCHAR(50),
@ACTION VARCHAR(50)

AS


IF @ACTION='SUMPAID'
SELECT COALESCE(SUM(TOTALPAID),0) AS SUMPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN


IF @ACTION='PAIDREPORT'
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATECOMPL) AS PDMONTH,
YEAR(DATECOMPL) AS PDYEAR, TOTAL_PROJ,
MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN AND
TOTALPAID!=0
ORDER BY YEAR(DATECOMPL) DESC, MONTH(DATECOMPL) DESC






IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
SELECT A.PERSON, A.NUMBER, A.DESCRIPTIO, MONTH(A.DATECOMPL) AS
PDMONTH, YEAR(A.DATECOMPL) AS PDYEAR, A.TOTAL_PROJ,
ROUND(((A.TOTAL_PROJ-A.TOTALCOST)/A.TOTAL_PROJ*100),0) AS MARGIN,
COALESCE(B.RATE, 0) AS RATE,
(A.TOTAL_PROJ*.035) AS BASEPAID,
(COALESCE(B.RATE, 0)*A.TOTAL_PROJ/100) AS MARGINPAID,
((COALESCE(B.RATE, 0)*A.TOTAL_PROJ/100)+(A.TOTAL_PROJ*.035)) AS
TOTALPAID
INTO #TEMPCOMM
FROM
(SELECT PERSON, NUMBER, DESCRIPTIO, DATECOMPL, (TOTAL_PART
+TOTAL_LBR) AS TOTAL_PROJ, TOTALCOST
FROM PROJMASTSQL
WHERE NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)
AND STATUS='D'
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'
AND TOTAL_PROJ!=0
AND PERSON = @SALESMAN) A
LEFT JOIN
(SELECT MARGIN, RATE
FROM COMMMARGINRATES) B
ON (ROUND(((A.TOTAL_PROJ-A.TOTALCOST)/A.TOTAL_PROJ*100),0)=B.MARGIN)
ORDER BY A.PERSON, YEAR(A.DATECOMPL) DESC, MONTH(A.DATECOMPL) DESC

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAIDºSEPAID
WHERE MARGIN<40 AND TOTAL_PROJ<2000
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'
IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET MARGINPAID=0
WHERE MARGIN<40 AND TOTAL_PROJ<2000
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ
FROM PROJMASTSQL
WHERE STATUS IN ('G')
AND TOTAL_PROJ!=0
AND PERSON=@SALESMAN
AND RIGHT(NUMBER,LEN(NUMBER)-6) NOT IN ('IV', 'FI', 'UI')
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAIDP
WHERE RATE IS NULL

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAIDP
WHERE RATE IS NULL


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ
FROM PROJMASTSQL
WHERE STATUS IN ('G')
AND TOTAL_PROJ!=0
AND PERSON=@SALESMAN
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAID=(TOTAL_PROJ*.07)
WHERE RATE IS NULL
AND BASEPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=(TOTAL_PROJ*.07)
WHERE RATE IS NULL
AND TOTALPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ, MARGIN)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ,
((TOTAL_PROJ-COALESCE(TOTALCOST,0))/TOTAL_PROJ*100) AS MARGIN
FROM PROJMASTSQL
WHERE STATUS IN ('D')
AND PERSON=@SALESMAN
AND TOTAL_PROJ!=0
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=(TOTAL_PROJ*.01)
WHERE RATE IS NULL
AND TOTALPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND MARGIN>16.5


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAID=(TOTAL_PROJ*.01)
WHERE RATE IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND MARGIN>16.5



IF @ACTION='NOTPAIDREPORT'
SELECT LTRIM(NUMBER) AS NUMBER, PERSON, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ, MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM #TEMPCOMM
IF @ACTION='NOTPAIDREPORT'
DROP TABLE #TEMPCOMM

IF @ACTION='POSTCOMMISSIONS'
INSERT INTO COMMLOG (PERSON, NUMBER, DESCRIPTIO, TOTAL_PROJ, MARGIN,
RATE, BASEPAID, MARGINPAID, TOTALPAID, PAIDDATE)
SELECT A.PERSON, A.NUMBER, A.DESCRIPTIO, A.TOTAL_PROJ, A.MARGIN,
A.RATE, A.BASEPAID, A.MARGINPAID, A.TOTALPAID, GETDATE() AS PAIDDATE
FROM #TEMPCOMM A

IF @ACTION='POSTCOMMISSIONS'
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATECOMPL) AS PDMONTH,
YEAR(DATECOMPL) AS PDYEAR, TOTAL_PROJ,
MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN AND
TOTALPAID!=0 AND
MONTH(PAIDDATE)=MONTH(GETDATE()) AND
YEAR(PAIDDATE)=YEAR(GETDATE()) AND
DAY(PAIDDATE)ÚY(GETDATE())
ORDER BY YEAR(DATECOMPL) DESC, MONTH(DATECOMPL) DESC

IF @ACTION='POSTCOMMISSIONS'
DROP TABLE #TEMPCOMM



IF @ACTION='SUMNOTPAID'
SELECT COALESCE(SUM(TOTALPAID),0) AS SUMNOTPAID
FROM #TEMPCOMM
WHERE PERSON=@SALESMAN

IF @ACTION='SUMNOTPAID'
DROP TABLE #TEMPCOMM


The ASP PAGE IS:

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master"
AutoEventWireup="false" CodeFile="COMMISSIONS.aspx.vb"
Inherits="Default2" title="Commissions Report" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<atlas:ScriptManager ID="ScriptManager1" runat="server">
</atlas:ScriptManager>
<atlas:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<table style="width: 880px; background-color: #DCDCDC">
<tr>
<td colspan="4" style="font-weight: bold; text-
decoration: underline">
Commission Report</td>
</tr>
<tr>
<td style="width: 10px">
</td>
<td style="width: 100px">
Salesman:</td>
<td style="width: 230px">
<asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SALESMEN" DataTextField="PERSON"
DataValueField="PERSON">
</asp:DropDownList></td>
<td rowspan="4" style="border-right: black 1px
solid; padding-right: 5px; border-top: black 1px solid;
padding-left: 5px; font-size: small; padding-
bottom: 5px; border-left: black 1px solid;
padding-top: 5px; border-bottom: black 1px
solid; background-color: #ffffff">
<span>Instructions:<br />
<br />
Currently only the Not Paid Report is
functional check back soon for the historical
paid report.</span></td>
</tr>
<tr>
<td style="width: 10px">
</td>
<td style="width: 100px">
Report Type:</td>
<td style="width: 230px">
<asp:RadioButtonList ID="RadioButtonList1" runat="server">
<asp:ListItem Value="PAIDREPORT">PAID REPORT</asp:ListItem>
<asp:ListItem Value="NOTPAIDREPORT">NOT PAID REPORT</
asp:ListItem>
</asp:RadioButtonList></td>
</tr>
<tr>
<td colspan="1" style="width: 10px">
</td>
<td colspan="2">
<asp:Button ID="Button1" runat="server"
Text="Button" /></td>
</tr>
<tr>
<td colspan="3" style="text-align: center">
<atlas:UpdateProgress ID="UpdateProgress1" runat="server">
<ProgressTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl="~/
IMAGES/ajax-loader[1].gif" /><br />
<span style="color: #ff0000">Please Wait...</span>
</ProgressTemplate>
</atlas:UpdateProgress>

</td>
</tr>
<tr>
<td colspan="4" style="text-align: center">
<asp:FormView ID="FormView1" runat="server"
BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"
CellPadding="2" DataSourceID="SUMNOTPAID"
ForeColor="Black">
<FooterStyle BackColor="Tan" />
<EditRowStyle BackColor="DarkSlateBlue"
ForeColor="GhostWhite" />
<PagerStyle BackColor="PaleGoldenrod"
ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<ItemTemplate>
Total:&nbsp;
<asp:TextBox ID="TextBox1" runat="server" Text='<%#
Bind("SUMNOTPAID", "{0:C}") %>'></asp:TextBox>
</ItemTemplate>
</asp:FormView>
</td>
</tr>
</table>
<asp:GridView ID="GridView1" runat="server"
BackColor="White" BorderColor="#DEDFDE"
BorderStyle="None" BorderWidth="1px" CellPadding="4"
DataSourceID="COMMISSIONS"
ForeColor="Black" GridLines="Vertical"
AutoGenerateColumns="False" Font-Size="X-Small" Width="860px"
AllowSorting="True">
<FooterStyle BackColor="#CCCC99" />
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True"
ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black"
HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:HyperLinkField DataNavigateUrlFields="NUMBER"
DataTextField="NUMBER" DataNavigateUrlFormatString="~/
PROJECTDETAIL.ASPX?PROJECT={0}" SortExpression="NUMBER" />
<asp:BoundField DataField="DESCRIPTIO"
HeaderText="Person" SortExpression="PERSON" />
<asp:boundfield DataField="PDMONTH"
HeaderText="Month" SortExpression="PDMONTH" />
<asp:BoundField DataField="PDYEAR"
HeaderText="Year" SortExpression="PDYEAR" />
<asp:BoundField DataField="TOTAL_PROJ"
HeaderTEXT="Total" SortExpression="TOTAL_PROJ" HtmlEncode="False"
DataFormatString="{0:C}" />
<asp:BoundField DataField="MARGIN"
HeaderText="Margin" SortExpression="MARGIN" HtmlEncode="False"
DataFormatString="{0:#.##}" />
<asp:boundField DataField="RATE" HeaderText="Rate"
SortExpression="RATE" HtmlEncode="False" DataFormatString="{0:#.##}" /




<asp:BoundField DataField="BASEPAID"
HeaderText="Base" SortExpression="BASEPAID" HtmlEncode="False"
DataFormatString="{0:c}" />
<asp:BoundField DataField="MARGINPAID"
HeaderText="Margin" SortExpression="MARGINPAID" HtmlEncode="False"
DataFormatString="{0:c}" />
<asp:BoundField DataField="TOTALPAID"
HeaderText="Total" SortExpression="TOTALPAID" HtmlEncode="False"
DataFormatString="{0:c}" />
</Columns>
</asp:GridView>

</ContentTemplate>
</atlas:UpdatePanel>

<asp:SqlDataSource ID="COMMISSIONS" runat="server"
ConnectionString="<%$ ConnectionStrings:PROJECTSQL %>"
SelectCommand="SP_COMMISSIONS"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1"
Name="SALESMAN" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="RadioButtonList1"
Name="ACTION" PropertyName="SelectedValue"
Type="String" DefaultValue="" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SUMNOTPAID" runat="server"
ConnectionString="<%$ ConnectionStrings:PROJECTSQL %>"
SelectCommand="SP_COMMISSIONS_SUMS"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1"
Name="SALESMAN" PropertyName="SelectedValue"
Type="String" />
<asp:Parameter DefaultValue="SUMNOTPAID" Name="ACTION"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SALESMEN" runat="server" ConnectionString="<
%$ ConnectionStrings:PROJECTSQL %>"
SelectCommand="SELECT DISTINCT(PERSON) AS PERSON&#13;&#10;FROM
PROJMASTSQL&#13;&#10;ORDER BY PERSON">
</asp:SqlDataSource>
</asp:Content>

Preguntas similare

Leer las respuestas

#1 Gustavo Larriera (MVP)
12/04/2007 - 17:56 | Informe spam
Lo más sensato es que ejecutes el SP en tu Query Analyzer (si usas SS2000) o
en el management Studio (si usas SS2005) donde te informará en qué línea se
produjo el error.


Gustavo Larriera, MVP
http://aspnet2.com/mvp.ashx?GustavoLarriera
http://blogs.solidq.com/ES/glarriera
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"norrisinc" wrote:

Here is the scenario, i have a stored procedure, it calculates
commissions and runs fine in SQL. However when the SP is run through
my asp page it will come back with a divide by zero error, but only
for one sales person... I have been through this code a hundred times
and can't find any errors. Any help would be much appreciated.

SP:

ALTER PROCEDURE [dbo].[SP_COMMISSIONS]
@SALESMAN VARCHAR(50),
@ACTION VARCHAR(50)

AS


IF @ACTION='SUMPAID'
SELECT COALESCE(SUM(TOTALPAID),0) AS SUMPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN


IF @ACTION='PAIDREPORT'
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATECOMPL) AS PDMONTH,
YEAR(DATECOMPL) AS PDYEAR, TOTAL_PROJ,
MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN AND
TOTALPAID!=0
ORDER BY YEAR(DATECOMPL) DESC, MONTH(DATECOMPL) DESC






IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
SELECT A.PERSON, A.NUMBER, A.DESCRIPTIO, MONTH(A.DATECOMPL) AS
PDMONTH, YEAR(A.DATECOMPL) AS PDYEAR, A.TOTAL_PROJ,
ROUND(((A.TOTAL_PROJ-A.TOTALCOST)/A.TOTAL_PROJ*100),0) AS MARGIN,
COALESCE(B.RATE, 0) AS RATE,
(A.TOTAL_PROJ*.035) AS BASEPAID,
(COALESCE(B.RATE, 0)*A.TOTAL_PROJ/100) AS MARGINPAID,
((COALESCE(B.RATE, 0)*A.TOTAL_PROJ/100)+(A.TOTAL_PROJ*.035)) AS
TOTALPAID
INTO #TEMPCOMM
FROM
(SELECT PERSON, NUMBER, DESCRIPTIO, DATECOMPL, (TOTAL_PART
+TOTAL_LBR) AS TOTAL_PROJ, TOTALCOST
FROM PROJMASTSQL
WHERE NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)
AND STATUS='D'
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'
AND TOTAL_PROJ!=0
AND PERSON = @SALESMAN) A
LEFT JOIN
(SELECT MARGIN, RATE
FROM COMMMARGINRATES) B
ON (ROUND(((A.TOTAL_PROJ-A.TOTALCOST)/A.TOTAL_PROJ*100),0)=B.MARGIN)
ORDER BY A.PERSON, YEAR(A.DATECOMPL) DESC, MONTH(A.DATECOMPL) DESC

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAIDºSEPAID
WHERE MARGIN<40 AND TOTAL_PROJ<2000
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'
IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET MARGINPAID=0
WHERE MARGIN<40 AND TOTAL_PROJ<2000
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ
FROM PROJMASTSQL
WHERE STATUS IN ('G')
AND TOTAL_PROJ!=0
AND PERSON=@SALESMAN
AND RIGHT(NUMBER,LEN(NUMBER)-6) NOT IN ('IV', 'FI', 'UI')
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAIDP
WHERE RATE IS NULL

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAIDP
WHERE RATE IS NULL


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ
FROM PROJMASTSQL
WHERE STATUS IN ('G')
AND TOTAL_PROJ!=0
AND PERSON=@SALESMAN
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAID=(TOTAL_PROJ*.07)
WHERE RATE IS NULL
AND BASEPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=(TOTAL_PROJ*.07)
WHERE RATE IS NULL
AND TOTALPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ, MARGIN)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ,
((TOTAL_PROJ-COALESCE(TOTALCOST,0))/TOTAL_PROJ*100) AS MARGIN
FROM PROJMASTSQL
WHERE STATUS IN ('D')
AND PERSON=@SALESMAN
AND TOTAL_PROJ!=0
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=(TOTAL_PROJ*.01)
WHERE RATE IS NULL
AND TOTALPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND MARGIN>16.5


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAID=(TOTAL_PROJ*.01)
WHERE RATE IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND MARGIN>16.5



IF @ACTION='NOTPAIDREPORT'
SELECT LTRIM(NUMBER) AS NUMBER, PERSON, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ, MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM #TEMPCOMM
IF @ACTION='NOTPAIDREPORT'
DROP TABLE #TEMPCOMM

IF @ACTION='POSTCOMMISSIONS'
INSERT INTO COMMLOG (PERSON, NUMBER, DESCRIPTIO, TOTAL_PROJ, MARGIN,
RATE, BASEPAID, MARGINPAID, TOTALPAID, PAIDDATE)
SELECT A.PERSON, A.NUMBER, A.DESCRIPTIO, A.TOTAL_PROJ, A.MARGIN,
A.RATE, A.BASEPAID, A.MARGINPAID, A.TOTALPAID, GETDATE() AS PAIDDATE
FROM #TEMPCOMM A

IF @ACTION='POSTCOMMISSIONS'
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATECOMPL) AS PDMONTH,
YEAR(DATECOMPL) AS PDYEAR, TOTAL_PROJ,
MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN AND
TOTALPAID!=0 AND
MONTH(PAIDDATE)=MONTH(GETDATE()) AND
YEAR(PAIDDATE)=YEAR(GETDATE()) AND
DAY(PAIDDATE)ÚY(GETDATE())
ORDER BY YEAR(DATECOMPL) DESC, MONTH(DATECOMPL) DESC

IF @ACTION='POSTCOMMISSIONS'
DROP TABLE #TEMPCOMM



IF @ACTION='SUMNOTPAID'
SELECT COALESCE(SUM(TOTALPAID),0) AS SUMNOTPAID
FROM #TEMPCOMM
WHERE PERSON=@SALESMAN

IF @ACTION='SUMNOTPAID'
DROP TABLE #TEMPCOMM


The ASP PAGE IS:

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master"
AutoEventWireup="false" CodeFile="COMMISSIONS.aspx.vb"
Inherits="Default2" title="Commissions Report" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<atlas:ScriptManager ID="ScriptManager1" runat="server">
</atlas:ScriptManager>
<atlas:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<table style="width: 880px; background-color: #DCDCDC">
<tr>
<td colspan="4" style="font-weight: bold; text-
decoration: underline">
Commission Report</td>
</tr>
<tr>
<td style="width: 10px">
</td>
<td style="width: 100px">
Salesman:</td>
<td style="width: 230px">
<asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SALESMEN" DataTextField="PERSON"
DataValueField="PERSON">
</asp:DropDownList></td>
<td rowspan="4" style="border-right: black 1px
solid; padding-right: 5px; border-top: black 1px solid;
padding-left: 5px; font-size: small; padding-
bottom: 5px; border-left: black 1px solid;
padding-top: 5px; border-bottom: black 1px
solid; background-color: #ffffff">
<span>Instructions:<br />
<br />
Currently only the Not Paid Report is
functional check back soon for the historical
paid report.</span></td>
</tr>
<tr>
<td style="width: 10px">
</td>
<td style="width: 100px">
Report Type:</td>
<td style="width: 230px">
<asp:RadioButtonList ID="RadioButtonList1" runat="server">
<asp:ListItem Value="PAIDREPORT">PAID REPORT</asp:ListItem>
<asp:ListItem Value="NOTPAIDREPORT">NOT PAID REPORT</
asp:ListItem>
</asp:RadioButtonList></td>
</tr>
<tr>
<td colspan="1" style="width: 10px">
</td>
<td colspan="2">
<asp:Button ID="Button1" runat="server"
Text="Button" /></td>
</tr>
<tr>
<td colspan="3" style="text-align: center">
<atlas:UpdateProgress ID="UpdateProgress1" runat="server">
<ProgressTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl="~/
IMAGES/ajax-loader[1].gif" /><br />
<span style="color: #ff0000">Please Wait...</span>
</ProgressTemplate>
</atlas:UpdateProgress>

</td>
</tr>
<tr>
<td colspan="4" style="text-align: center">
<asp:FormView ID="FormView1" runat="server"
BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"
CellPadding="2" DataSourceID="SUMNOTPAID"
ForeColor="Black">
<FooterStyle BackColor="Tan" />
<EditRowStyle BackColor="DarkSlateBlue"
ForeColor="GhostWhite" />
<PagerStyle BackColor="PaleGoldenrod"
ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<ItemTemplate>
Total:
<asp:TextBox ID="TextBox1" runat="server" Text='<%#
Bind("SUMNOTPAID", "{0:C}") %>'></asp:TextBox>
email Siga el debate Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaRespuesta Tengo una respuesta
Search Busqueda sugerida