原创

[易飞]按产品型号开票金额TOP20同上月涨幅

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://david.blog.csdn.net/article/details/52594212

接到用户需求:按产品型号开票金额TOP20,并实现当月金额的柱状图。
同上月差异涨幅。
存储过程:

-- =============================================      
-- Author: <David Gong>      
-- Create date: <2016-09-20>      
-- Description: <每月开票金额TOP20及上月涨幅>      
-- =============================================  
ALTER Procedure [dbo].[DZ_InvoiceByPrd]
(
     @YEAR AS CHAR(4),
     @MONTH AS CHAR(2)
)
AS BEGIN
--SET @YEAR='2016'
--SET @MONTH='08'
DECLARE @C_YM AS CHAR(6)
DECLARE @L_YM AS CHAR(6)
SET @C_YM=@YEAR+@MONTH
SET @L_YM=CONVERT(CHAR(6),DATEADD(MONTH,-1,@YEAR+@MONTH+'01'),112)
;WITH CTE AS(
SELECT TB039,SUM(CASE WHEN  TA079='1' THEN TB019 ELSE -(TB019) END) AS C_Account, SUM(TB022)  AS C_Qty
FROM ACRTA LEFT join ACRTB   ON TA001=TB001 AND TA002=TB002 
where TB004 in ('1','2') AND LEFT(TA001,3)<>'61B'  and LEFT(TA038,6)=@C_YM
GROUP BY TB039), 
CTE1 AS(
SELECT TB039,SUM(CASE WHEN  TA079='1' THEN TB019 ELSE -(TB019) END) AS L_Account, SUM(TB022)  AS L_Qty
FROM ACRTA LEFT join ACRTB   ON TA001=TB001 AND TA002=TB002 
where TB004 in ('1','2') AND LEFT(TA001,3)<>'61B'  and LEFT(TA038,6)=@L_YM
GROUP BY TB039)   

SELECT TOP 20 MB002,MB003,C_Account,ISNULL(L_Account,0) L_Account,C_Qty,ISNULL(L_Qty,0) L_Qty,(C_Account-ISNULL(L_Account,0))/C_Account as DIFF  FROM CTE LEFT JOIN CTE1 ON CTE.TB039=CTE1.TB039

LEFT JOIN INVMB ON CTE.TB039=MB001 ORDER BY C_Account DESC 

END

页面:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="InvoiceByPrd.aspx.cs" Inherits="DzPlatForm.ERP.Mgr.InvoiceByPrd" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
   <title>产品接单金额TOP 20</title>
    <script type="text/javascript" src="../../Scripts/My97DatePicker/WdatePicker.js"></script>
    <link href="../../Styles/bulelight.css"  type="text/css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table border="0" cellpadding="5" cellspacing="1" class="table" style="width: 1024px;
                text-align: center">
                <tr>
                    <td class="hback" colspan="4" style="color: #666666; text-align: left">
                        温馨提示: ①任何的删除操作都是不可逆的,执行删除时请慎重
                    </td>
                </tr>
                <tr>
                    <td class="forumRowHighlight">
                        <b>管理操作选项</b>
                    </td>
                    <td class="forumRowHighlight" style="text-align: left; height: 25px;">
                            年份:<asp:TextBox ID="txtyear" runat="server" Width="74px"></asp:TextBox>
                    </td>
                    <td class="forumRowHighlight" style="text-align: left; height: 25px;">
                         月份:<asp:DropDownList ID="ddlMonth" runat="server" Width="100px">
                             <asp:ListItem Value="01">一月</asp:ListItem>
                             <asp:ListItem Value="02">二月</asp:ListItem>
                             <asp:ListItem Value="03">三月</asp:ListItem>
                             <asp:ListItem Value="04">四月</asp:ListItem>
                             <asp:ListItem Value="05">五月</asp:ListItem>
                             <asp:ListItem Value="06">六月</asp:ListItem>
                             <asp:ListItem Value="07">七月</asp:ListItem>
                             <asp:ListItem Value="08">八月</asp:ListItem>
                             <asp:ListItem Value="09">九月</asp:ListItem>
                             <asp:ListItem Value="10">十月</asp:ListItem>
                             <asp:ListItem Value="11">十一月</asp:ListItem>
                             <asp:ListItem Value="12">十二月</asp:ListItem>
                        </asp:DropDownList></td>

                    <td class="forumRowHighlight" style="text-align: left; height: 25px;">
                        <asp:Button ID="btnQuery" runat="server" class="btn1" OnClick="btnQuery_Click" Text="查询"
                            Width="70px" />
                        <asp:Button ID="btnExcel" runat="server" onclick="btnExcel_Click" 
                            Text="导出excel" Width="65px" />
                        </td>
                </tr>
                </table>
            <table border="0" cellpadding="5" cellspacing="1" class="table" style="width: 1024px;
                text-align: center">
                <asp:Repeater ID="rptInvoicePrd" runat="server" >
                    <HeaderTemplate>
                        <tr style="font-weight: bold;color: #cc3300;">
                            <td class="hback" style="width: 5%; ">
                                序号
                            </td>
                            <td class="hback" style="width: 15%; ">
                                品名
                            </td>
                            <td class="hback" style="width: 25%; ">
                                规格
                            </td>
                            <td class="hback" style="width: 10%; ">
                                当月金额
                            </td>
                            <td class="hback" style="width: 10%; ">
                                上月金额
                            </td>
                             <td class="hback" style="width: 10%; ">
                                当月数量
                            </td>
                            <td class="hback" style="width: 10%; ">
                                上月数量
                            </td>
                            <td class="hback" style="width: 10%; ">
                                涨幅
                            </td>
                        </tr>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr style="font-weight:normal">
                            <td class="hback">
                                <%#Container.ItemIndex+1%>
                            </td>
                            <td class="hback">
                                <%#DataBinder.Eval(Container.DataItem, "MB002")%>
                            </td>
                            <td class="hback">
                                <%#DataBinder.Eval(Container.DataItem, "MB003")%>
                            </td>
                            <td class="hback">
                                <%#DataBinder.Eval(Container.DataItem, "C_Account", "{0:N2}")%>
                            </td>
                            <td class="hback">
                                <%#DataBinder.Eval(Container.DataItem, "L_Account", "{0:N2}")%>
                            </td>
                            <td class="hback">
                                <%#DataBinder.Eval(Container.DataItem, "C_Qty", "{0:N2}")%>
                            </td>
                            <td class="hback">
                                <%#DataBinder.Eval(Container.DataItem, "L_Qty", "{0:N2}")%>
                            </td>
                            <td class="hback">
                                <%#DataBinder.Eval(Container.DataItem, "DIFF", "{0:N4}")%>
                            </td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>


                    </FooterTemplate>
                </asp:Repeater>

            </table>

                <asp:Chart ID="ChartSeries" runat="server" BackColor="LightSteelBlue" BackGradientStyle="TopBottom"
                BackSecondaryColor="AliceBlue" EnableTheming="False" EnableViewState="True" Visible="false">
                <Titles>
                    <asp:Title Font="微软雅黑, 16pt" Text="当月接单金额柱状图" Alignment="TopCenter">
                    </asp:Title>
                    <asp:Title Docking="Bottom" Font="微软雅黑, 8.25pt" Text="Zettlercn Corporation"
                        Alignment="MiddleRight">
                    </asp:Title>
                </Titles>
                <Legends>
                    <asp:Legend BackColor="Transparent" Alignment="Center" Docking="Bottom" Font="微软雅黑, 8.25pt, style=Bold"
                        IsTextAutoFit="False" Name="Default" LegendStyle="Table">
                    </asp:Legend>
                </Legends>
                <Series>
                    <asp:Series Name="Series1">
                    </asp:Series>
                </Series>
                <ChartAreas>
                    <asp:ChartArea Name="ChartArea1">
                    </asp:ChartArea>
                </ChartAreas>
            </asp:Chart>
               <asp:Chart ID="ChartDIFF" runat="server" BackColor="LightSteelBlue" BackGradientStyle="TopBottom"
                BackSecondaryColor="AliceBlue" EnableTheming="False" EnableViewState="True" Visible="false">
                     <Titles>
                    <asp:Title Font="微软雅黑, 16pt" Text="当月上月环比涨幅" Alignment="TopCenter">
                    </asp:Title>
                    <asp:Title Docking="Bottom" Font="微软雅黑, 8.25pt" Text="Zettlercn Corporation"
                        Alignment="MiddleRight">
                    </asp:Title>
                </Titles>
                <Legends>
                    <asp:Legend BackColor="Transparent" Alignment="Center" Docking="Bottom" Font="微软雅黑, 8.25pt, style=Bold"
                        IsTextAutoFit="False" Name="Default" LegendStyle="Table">
                    </asp:Legend>
                </Legends>
                <Series>
                    <asp:Series Name="Series1">
                    </asp:Series>
                </Series>
                <ChartAreas>
                    <asp:ChartArea Name="ChartArea1">
                    </asp:ChartArea>
                </ChartAreas>
            </asp:Chart>
        </div>
    </form>
</body>
</html>

CS:

protected void btnQuery_Click(object sender, EventArgs e)
        {
            if (txtyear.Text.Trim() != "" || ddlMonth.SelectedItem.Text.Trim() != "--请选择--")
            {

                DataTable dt = YiFeiDAL.InvoiceByPrdList(company, txtyear.Text.Trim(), ddlMonth.SelectedItem.Value.Trim());

                rptInvoicePrd.DataSource = dt;
                rptInvoicePrd.DataBind();
                ChartBind(dt);
                this.ChartSeries.Visible = true;
                this.ChartDIFF.Visible = true;


            }
            else
            {
                JScript.Alert("请输入年份!", this.Page);
            }
        }
        private void ChartBind(DataTable dt)
        {

            this.ChartSeries.Width = 1024;
            this.ChartSeries.Height = 500;
            this.ChartSeries.DataSource = dt;
            this.ChartSeries.DataBind();
            this.ChartSeries.Series["Series1"].ChartType = SeriesChartType.Column;
            this.ChartSeries.Series["Series1"].XValueMember = "MB002";
            this.ChartSeries.Series["Series1"].YValueMembers = "C_Account";

            this.ChartSeries.ChartAreas["ChartArea1"].AxisX.Title = "型号";
            this.ChartSeries.ChartAreas["ChartArea1"].AxisY.Title = "当月接单金额";
            //显示类别
            this.ChartSeries.Legends[0].Enabled = true;
            this.ChartSeries.Series["Series1"].LegendText = "当月接单金额";

            //背景色设置
            this.ChartSeries.ChartAreas["ChartArea1"].ShadowColor = Color.Transparent;
            this.ChartSeries.ChartAreas["ChartArea1"].BackColor = Color.FromArgb(209, 237, 254);         //该处设置为了由天蓝到白色的逐渐变化
            this.ChartSeries.ChartAreas["ChartArea1"].BackGradientStyle = GradientStyle.TopBottom;
            this.ChartSeries.ChartAreas["ChartArea1"].BackSecondaryColor = Color.White;

            //中间X,Y线条的颜色设置
            this.ChartSeries.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64);
            this.ChartSeries.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64);
            //X.Y轴数据显示间隔
            this.ChartSeries.ChartAreas["ChartArea1"].AxisX.Interval = 1;



            this.ChartDIFF.Width = 1024;
            this.ChartDIFF.Height = 500;
            this.ChartDIFF.DataSource = dt;
            this.ChartDIFF.DataBind();
            this.ChartDIFF.Series["Series1"].ChartType = SeriesChartType.Spline;
            this.ChartDIFF.Series["Series1"].XValueMember = "MB002";
            this.ChartDIFF.Series["Series1"].YValueMembers = "DIFF";


            this.ChartDIFF.ChartAreas["ChartArea1"].AxisX.Title = "型号";
            this.ChartDIFF.ChartAreas["ChartArea1"].AxisY.Title = "涨幅";
            //显示类别
            this.ChartDIFF.Legends[0].Enabled = true;
            this.ChartDIFF.Series["Series1"].LegendText = "涨幅";

            //背景色设置
            this.ChartDIFF.ChartAreas["ChartArea1"].ShadowColor = Color.Transparent;
            this.ChartDIFF.ChartAreas["ChartArea1"].BackColor = Color.FromArgb(209, 237, 254);         //该处设置为了由天蓝到白色的逐渐变化
            this.ChartDIFF.ChartAreas["ChartArea1"].BackGradientStyle = GradientStyle.TopBottom;
            this.ChartDIFF.ChartAreas["ChartArea1"].BackSecondaryColor = Color.White;

            //中间X,Y线条的颜色设置
            this.ChartDIFF.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64);
            this.ChartDIFF.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64);
            //X.Y轴数据显示间隔
            this.ChartDIFF.ChartAreas["ChartArea1"].AxisX.Interval = 0.2;


        }
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            this.rptInvoicePrd.RenderControl(hw);

            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.Charset = "";
            rptInvoicePrd.Page.EnableViewState = true;
            Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode("产品-开票金额TOP20(" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ").xls", System.Text.Encoding.UTF8) + "\"");
            Response.Write("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=UTF8\"><title> 产品-开票金额TOP20</title></head><body><table><tr><td> 序号</td><td>品名</td><td>规格</td><td>当月金额</td><td>上月金额</td><td>当月数量</td><td>上月数量</td><td>涨幅</td></tr>");
            Response.Write(sw.ToString());
            Response.Write("</table></body></html>");
            Response.End();
        }

效果图:
这里写图片描述
这里写图片描述
这里写图片描述

文章最后发布于: 2016-09-20 11:26:12
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 鲸 设计师: meimeiellie

分享到微信朋友圈

×

扫一扫,手机浏览