[Ext.Net]易飞IQC来料批次分析与MsChart 使用双轴显示
涉及到ERP系统数据分析：三个数据分析表，三个图形展示。前台EXT.NET1.5 +MSCHART
IQC来料情况表:
存储过程:
-- ============================================= -- Author: <David Gong> -- Create date: <2013-03-08> -- Description: <IQC来料批次情况表> -- =============================================ALTER Proc [dbo].[UF_ZEIQCReport]( @year as varchar(4) )as begin--IQC检验情况--创建临时表create table #Tmp --创建临时表#Tmp( Item varchar(20), Jan decimal(9,2), Feb decimal(9,2), Mar decimal(9,2), Apr decimal(9,2), May decimal(9,2), Jun decimal(9,2), Jul decimal(9,2), Aug decimal(9,2), Sep decimal(9,2), Oct decimal(9,2), Nov decimal(9,2), [Dec] decimal(9,2) )declare @Rate as decimal(10,2)select @Rate=Goal from OA.dbo.IQCGoal where Company='ZE' AND [Year]=@yearInsert into #Tmp(Item,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) select * from(SELECT '检验批数' AS Item,sum(case when substring(TA004,5,2)='01' then 1 else 0 end) as Jan,sum(case when substring(TA004,5,2)='02' then 1 else 0 end) as Feb,sum(case when substring(TA004,5,2)='03' then 1 else 0 end) as Mar,sum(case when substring(TA004,5,2)='04' then 1 else 0 end) as Apr,sum(case when substring(TA004,5,2)='05' then 1 else 0 end) as May,sum(case when substring(TA004,5,2)='06' then 1 else 0 end) as Jun,sum(case when substring(TA004,5,2)='07' then 1 else 0 end) as Jul,sum(case when substring(TA004,5,2)='08' then 1 else 0 end) as Aug,sum(case when substring(TA004,5,2)='09' then 1 else 0 end) as Sep,sum(case when substring(TA004,5,2)='10' then 1 else 0 end) as Oct,sum(case when substring(TA004,5,2)='11' then 1 else 0 end) as Nov,sum(case when substring(TA004,5,2)='12' then 1 else 0 end) as DecFROM QMSTAwhere left(TA004,4)=@yearUNION ALLSELECT '合格批数' AS Item,sum(case when substring(TA004,5,2)='01' then 1 else 0 end) as Jan,sum(case when substring(TA004,5,2)='02' then 1 else 0 end) as Feb,sum(case when substring(TA004,5,2)='03' then 1 else 0 end) as Mar,sum(case when substring(TA004,5,2)='04' then 1 else 0 end) as Apr,sum(case when substring(TA004,5,2)='05' then 1 else 0 end) as May,sum(case when substring(TA004,5,2)='06' then 1 else 0 end) as Jun,sum(case when substring(TA004,5,2)='07' then 1 else 0 end) as Jul,sum(case when substring(TA004,5,2)='08' then 1 else 0 end) as Aug,sum(case when substring(TA004,5,2)='09' then 1 else 0 end) as Sep,sum(case when substring(TA004,5,2)='10' then 1 else 0 end) as Oct,sum(case when substring(TA004,5,2)='11' then 1 else 0 end) as Nov,sum(case when substring(TA004,5,2)='12' then 1 else 0 end) as DecFROM QMSTAwhere left(TA004,4)=@year AND TA011='2'UNION ALLSELECT '不合格批数' AS Item,sum(case when substring(TA004,5,2)='01' then 1 else 0 end) as Jan,sum(case when substring(TA004,5,2)='02' then 1 else 0 end) as Feb,sum(case when substring(TA004,5,2)='03' then 1 else 0 end) as Mar,sum(case when substring(TA004,5,2)='04' then 1 else 0 end) as Apr,sum(case when substring(TA004,5,2)='05' then 1 else 0 end) as May,sum(case when substring(TA004,5,2)='06' then 1 else 0 end) as Jun,sum(case when substring(TA004,5,2)='07' then 1 else 0 end) as Jul,sum(case when substring(TA004,5,2)='08' then 1 else 0 end) as Aug,sum(case when substring(TA004,5,2)='09' then 1 else 0 end) as Sep,sum(case when substring(TA004,5,2)='10' then 1 else 0 end) as Oct,sum(case when substring(TA004,5,2)='11' then 1 else 0 end) as Nov,sum(case when substring(TA004,5,2)='12' then 1 else 0 end) as DecFROM QMSTAwhere left(TA004,4)=@year AND TA011 in ('3','4')UNION ALLSELECT '退货' AS Item,sum(case when substring(TA004,5,2)='01' then 1 else 0 end) as Jan,sum(case when substring(TA004,5,2)='02' then 1 else 0 end) as Feb,sum(case when substring(TA004,5,2)='03' then 1 else 0 end) as Mar,sum(case when substring(TA004,5,2)='04' then 1 else 0 end) as Apr,sum(case when substring(TA004,5,2)='05' then 1 else 0 end) as May,sum(case when substring(TA004,5,2)='06' then 1 else 0 end) as Jun,sum(case when substring(TA004,5,2)='07' then 1 else 0 end) as Jul,sum(case when substring(TA004,5,2)='08' then 1 else 0 end) as Aug,sum(case when substring(TA004,5,2)='09' then 1 else 0 end) as Sep,sum(case when substring(TA004,5,2)='10' then 1 else 0 end) as Oct,sum(case when substring(TA004,5,2)='11' then 1 else 0 end) as Nov,sum(case when substring(TA004,5,2)='12' then 1 else 0 end) as DecFROM QMSTAwhere left(TA004,4)=@year AND TA011='3'UNION ALLSELECT '特采' AS Item,sum(case when substring(TA004,5,2)='01' then 1 else 0 end) as Jan,sum(case when substring(TA004,5,2)='02' then 1 else 0 end) as Feb,sum(case when substring(TA004,5,2)='03' then 1 else 0 end) as Mar,sum(case when substring(TA004,5,2)='04' then 1 else 0 end) as Apr,sum(case when substring(TA004,5,2)='05' then 1 else 0 end) as May,sum(case when substring(TA004,5,2)='06' then 1 else 0 end) as Jun,sum(case when substring(TA004,5,2)='07' then 1 else 0 end) as Jul,sum(case when substring(TA004,5,2)='08' then 1 else 0 end) as Aug,sum(case when substring(TA004,5,2)='09' then 1 else 0 end) as Sep,sum(case when substring(TA004,5,2)='10' then 1 else 0 end) as Oct,sum(case when substring(TA004,5,2)='11' then 1 else 0 end) as Nov,sum(case when substring(TA004,5,2)='12' then 1 else 0 end) as DecFROM QMSTAwhere left(TA004,4)=@year AND TA011='4'UNION ALL--SELECT '批次合格率' AS Item,--sum(case when substring(TA004,5,2)='01' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='01' then 1.00 else 0.00 end)*100 as Jan,--sum(case when substring(TA004,5,2)='02' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='02' then 1.00 else 0.00 end)*100 as Feb,--sum(case when substring(TA004,5,2)='03' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='03' then 1.00 else 0.00 end)*100 as Mar,--sum(case when substring(TA004,5,2)='04' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='04' then 1.00 else 0.00 end)*100 as Apr,--sum(case when substring(TA004,5,2)='05' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='05' then 1.00 else 0.00 end)*100 as May,--sum(case when substring(TA004,5,2)='06' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='06' then 1.00 else 0.00 end)*100 as Jun,--sum(case when substring(TA004,5,2)='07' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='07' then 1.00 else 0.00 end)*100 as Jul,--sum(case when substring(TA004,5,2)='08' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='08' then 1.00 else 0.00 end)*100 as Aug,--sum(case when substring(TA004,5,2)='09' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='09' then 1.00 else 0.00 end)*100 as Sep,--sum(case when substring(TA004,5,2)='10' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='10' then 1.00 else 0.00 end)*100 as Oct,--sum(case when substring(TA004,5,2)='11' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='11' then 1.00 else 0.00 end)*100 as Nov,--sum(case when substring(TA004,5,2)='12' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='12' then 1.00 else 0.00 end)*100 as Dec--FROM QMSTASELECT '批次合格率' AS Item, case when sum(case when substring(TA004,5,2)='01' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='01' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='01' then 1.00 else 0.00 end)*100 end as Jan,case when sum(case when substring(TA004,5,2)='02' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='02' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='02' then 1.00 else 0.00 end)*100 end as Feb,case when sum(case when substring(TA004,5,2)='03' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='03' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='03' then 1.00 else 0.00 end)*100 end as Mar,case when sum(case when substring(TA004,5,2)='04' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='04' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='04' then 1.00 else 0.00 end)*100 end as Apr,case when sum(case when substring(TA004,5,2)='05' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='05' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='05' then 1.00 else 0.00 end)*100 end as May,case when sum(case when substring(TA004,5,2)='06' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='06' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='06' then 1.00 else 0.00 end)*100 end as Jun,case when sum(case when substring(TA004,5,2)='07' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='07' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='07' then 1.00 else 0.00 end)*100 end as Jul,case when sum(case when substring(TA004,5,2)='08' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='08' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='08' then 1.00 else 0.00 end)*100 end as Aug,case when sum(case when substring(TA004,5,2)='09' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='09' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='09' then 1.00 else 0.00 end)*100 end as Sep,case when sum(case when substring(TA004,5,2)='10' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='10' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='10' then 1.00 else 0.00 end)*100 end as Oct,case when sum(case when substring(TA004,5,2)='11' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='11' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='11' then 1.00 else 0.00 end)*100 end as Nov,case when sum(case when substring(TA004,5,2)='12' then 1.00 else 0.00 end)=0 then 0.00 elsesum(case when substring(TA004,5,2)='12' and TA011='2' then 1.00 else 0.00 end)/sum(case when substring(TA004,5,2)='12' then 1.00 else 0.00 end)*100 end as DecFROM QMSTAwhere left(TA004,4)=@year AND TA011 IN ('2','3','4')UNION ALLselect '目标' as Item,@Rate*100 as Jan,@Rate*100 as Feb,@Rate*100 as Mar,@Rate*100 as Apr,@Rate*100 as May,@Rate*100 as Jun,@Rate*100 as Jul,@Rate*100 as Aug,@Rate*100 as Sep,@Rate*100 as Oct,@Rate*100 as Nov,@Rate*100 as Dec) Aselect * from #Tmpselect * from #Tmpwhere Item in ('检验批数','合格批数','批次合格率','目标')drop table #Tmp --删除临时表#Tmpend-- ============================================= -- Author: <David Gong> -- Create date: <2013-03-08> -- Description: <供应商来料批次情况表> -- =============================================ALTER Proc [dbo].[UF_ZEIQCVendor]( @start as varchar(8), @end as varchar(8) )as begin--供应商来料批次select * from (SELECT TG005 供应商编码,MA002 供应商简称,sum(case when TA011 in ('2','3','4') then 1 else 0 end) as 来料总批次,sum(case when TA011 in ('3','4') then 1 else 0 end) as 不合格批数,CONVERT(decimal(9,2), sum(case when TA011 in ('3','4') then 1.00 else 0.00 end)/sum(case when TA011 in ('2','3','4') then 1.00 else 0.00 end)*100) as 不合格占比率FROM QMSTA LEFT JOIN PURTG ON TA001=TG001 AND TA002=TG002 LEFT JOIN PURMA ON TG005=MA001where TA004>=@start and TA004<=@endGROUP BY TG005,MA002) bwhere b.不合格批数<>0order by b.不合格批数 desc,b.不合格占比率 descselect top 10 * from (SELECT TG005 供应商编码,sum(case when TA011 in ('2','3','4') then 1 else 0 end) as 来料总批次,sum(case when TA011 in ('3','4') then 1 else 0 end) as 不合格批数,CONVERT(decimal(9,2), sum(case when TA011 in ('3','4') then 1.00 else 0.00 end)/sum(case when TA011 in ('2','3','4') then 1.00 else 0.00 end)*100) as 不合格占比率FROM QMSTA LEFT JOIN PURTG ON TA001=TG001 AND TA002=TG002 LEFT JOIN PURMA ON TG005=MA001where TA004>=@start and TA004<=@endGROUP BY TG005) border by b.不合格批数 desc,b.不合格占比率 descend-- ============================================= -- Author: <David Gong> -- Create date: <2013-03-08> -- Description: <物料类别来料批次情况表> -- =============================================ALTER Proc [dbo].[UF_ZEIQCType]( @start as varchar(8), @end as varchar(8) )as begin--类别select * from (SELECT A.MA003 as 类别,sum(case when TA011 in ('2','3','4') then 1 else 0 end) as 来料总批次,sum(case when TA011 in ('3','4') then 1 else 0 end) as 不合格批数,CONVERT(decimal(9,2), sum(case when TA011 in ('3','4') then 1.00 else 0.00 end)/sum(case when TA011 in ('2','3','4') then 1.00 else 0.00 end)*100) as 不合格占比率FROM QMSTA LEFT JOIN PURTH ON TA001=TH001 AND TA002=TH002 AND TA003=TH003left join INVMB on TH004=MB001LEFT JOIN (SELECT MA002,MA003 from INVMA WHERE MA001='2') A ON A .MA002=MB006 where TA004>=@start and TA004<=@endGROUP BY A.MA003) bwhere b.不合格批数<>0order by b.不合格批数 desc,b.不合格占比率 descselect top 10 * from (SELECT A.MA003 as 类别,sum(case when TA011 in ('2','3','4') then 1 else 0 end) as 来料总批次,sum(case when TA011 in ('3','4') then 1 else 0 end) as 不合格批数,CONVERT(decimal(9,2), sum(case when TA011 in ('3','4') then 1.00 else 0.00 end)/sum(case when TA011 in ('2','3','4') then 1.00 else 0.00 end)*100) as 不合格占比率FROM QMSTA LEFT JOIN PURTH ON TA001=TH001 AND TA002=TH002 AND TA003=TH003left join INVMB on TH004=MB001LEFT JOIN (SELECT MA002,MA003 from INVMA WHERE MA001='2') A ON A .MA002=MB006 where TA004>=@start and TA004<=@endGROUP BY A.MA003) border by b.不合格批数 desc,b.不合格占比率 descendaspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ZEIQCReport.aspx.cs" Inherits="ERP_QA_ZEIQCReport" %><%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %><!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 runat="server"> <title>IQC综合报表</title> <script type="text/javascript" src="../../js/MyExtJs.js"></script> <script type="text/javascript"> var Mychanges = function (value, cellmeta, r) { var template = '<span style="color:{0};">{1}</span>'; if (r.data.Item == "批次合格率" || r.data.Item == "目标") { return String.format(template, (value > 0) ? "green" : "red", value + "%").toString(); } else { return value; } } </script></head><body> <form id="form1" runat="server"> <ext:ResourceManager ID="ResourceManager1" runat="server" AjaxAspnetControls="ChartIQC,ChartVendor,ChartType" /> <div> <ext:Store ID="StoreIQCReport" runat="server"> <Reader> <ext:JsonReader> <Fields> <ext:RecordField Name="Item" /> <ext:RecordField Name="Jan" Type="Float" /> <ext:RecordField Name="Feb" Type="Float" /> <ext:RecordField Name="Mar" Type="Float" /> <ext:RecordField Name="Apr" Type="Float" /> <ext:RecordField Name="May" Type="Float" /> <ext:RecordField Name="Jun" Type="Float" /> <ext:RecordField Name="Jul" Type="Float" /> <ext:RecordField Name="Aug" Type="Float" /> <ext:RecordField Name="Sep" Type="Float" /> <ext:RecordField Name="Oct" Type="Float" /> <ext:RecordField Name="Nov" Type="Float" /> <ext:RecordField Name="Dec" Type="Float" /> </Fields> </ext:JsonReader> </Reader> </ext:Store> <ext:Store ID="StoreVendor" runat="server"> <Reader> <ext:JsonReader> <Fields> <ext:RecordField Name="供应商编码" /> <ext:RecordField Name="供应商简称" /> <ext:RecordField Name="来料总批次" Type="Float" /> <ext:RecordField Name="不合格批数" Type="Float" /> <ext:RecordField Name="不合格占比率" Type="Float" /> </Fields> </ext:JsonReader> </Reader> </ext:Store> <ext:Store ID="StoreType" runat="server"> <Reader> <ext:JsonReader> <Fields> <ext:RecordField Name="类别" /> <ext:RecordField Name="来料总批次" Type="Float" /> <ext:RecordField Name="不合格批数" Type="Float" /> <ext:RecordField Name="不合格占比率" Type="Float" /> </Fields> </ext:JsonReader> </Reader> </ext:Store> <ext:GridPanel ID="GPIQC" runat="server" StoreID="StoreIQCReport" Title="IQC检验情况表" Width="1024" Height="240" StripeRows="true"> <TopBar> <ext:Toolbar ID="Toolbar1" runat="server"> <Items> <ext:TextField ID="txtYear" runat="server" Width="150" FieldLabel="请输入年份"> </ext:TextField> <ext:Button ID="btnsubmit" runat="server" Icon="Zoom" Text="查询" EnableAjax="false"> <DirectEvents> <Click OnEvent="Query"> <EventMask ShowMask="true" Msg="正在提交数据，请稍候..." MinDelay="1000" /> </Click> </DirectEvents> </ext:Button> </Items> </ext:Toolbar> </TopBar> <ColumnModel ID="ColumnModel1" runat="server"> <Columns> <ext:RowNumbererColumn Locked="true" /> <ext:Column Header="项目" DataIndex="Item" Width="100" Locked="true" /> <ext:Column Header="一月" DataIndex="Jan" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="二月" DataIndex="Feb" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="三月" DataIndex="Mar" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="四月" DataIndex="Apr" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="五月" DataIndex="May" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="六月" DataIndex="Jun" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="七月" DataIndex="Jul" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="八月" DataIndex="Aug" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="九月" DataIndex="Sep" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="十月" DataIndex="Oct" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="十一月" DataIndex="Nov" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> <ext:Column Header="十二月" DataIndex="Dec" Width="70" Align="Right"> <Renderer Fn="Mychanges" /> </ext:Column> </Columns> </ColumnModel> <SelectionModel> <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" /> </SelectionModel> <LoadMask ShowMask="true" /> <View> <ext:LockingGridView ID="LockingGridView1" runat="server" /> </View> </ext:GridPanel> <ext:Panel ID="ContentPanel1" runat="server" BodyPadding="5px" EnableBackgroundColor="false" ShowBorder="true" ShowHeader="false" AutoScroll="true"> <Content> <asp:Chart ID="ChartIQC" runat="server" BackColor="LightSteelBlue" BackGradientStyle="TopBottom" BackSecondaryColor="AliceBlue" EnableTheming="False" EnableViewState="True"> <Titles> <asp:Title Font="微软雅黑, 16pt" Text="IQC检验情况表" Alignment="TopCenter"> </asp:Title> <asp:Title Docking="Bottom" Font="Trebuchet MS, 8.25pt" Text="Zettlercn Corporation" Alignment="MiddleRight"> </asp:Title> </Titles> <Legends> <asp:Legend BackColor="Transparent" Alignment="Center" Docking="Bottom" Font="Trebuchet MS, 8.25pt, style=Bold" IsTextAutoFit="False" Name="Default" LegendStyle="Table"> </asp:Legend> </Legends> <Series> <asp:Series Name="Series1"> </asp:Series> <asp:Series Name="Series2"> </asp:Series> <asp:Series Name="Series3"> </asp:Series> <asp:Series Name="Series4"> </asp:Series> </Series> <ChartAreas> <asp:ChartArea Name="ChartArea1"> </asp:ChartArea> </ChartAreas> </asp:Chart> </Content> </ext:Panel> <ext:GridPanel ID="GPVendor" runat="server" StoreID="StoreVendor" Title="供应商来料批次情况" Width="1024" Height="290" StripeRows="true"> <TopBar> <ext:Toolbar ID="Toolbar2" runat="server"> <Items> <ext:DateField ID="dvstart" runat="server" FieldLabel="检验日期" Width="200" AllowBlank="false"> </ext:DateField> <ext:DateField ID="dvend" runat="server" FieldLabel="至日期" Width="200" AllowBlank="false"> </ext:DateField> <ext:Button ID="btnQuery2" runat="server" Icon="Zoom" Text="查询"> <DirectEvents> <Click OnEvent="VendorQuery"> <EventMask ShowMask="true" Msg="正在提交数据，请稍候..." MinDelay="1000" /> </Click> </DirectEvents> </ext:Button> </Items> </ext:Toolbar> </TopBar> <ColumnModel ID="ColumnModel2" runat="server"> <Columns> <ext:RowNumbererColumn Locked="true" /> <ext:Column Header="供应商编码" DataIndex="供应商编码" Width="150" Locked="true" /> <ext:Column Header="供应商简称" DataIndex="供应商简称" Width="150" Locked="true" /> <ext:Column Header="来料总批次" DataIndex="来料总批次" Width="100" Align="Right"> </ext:Column> <ext:Column Header="不合格批数" DataIndex="不合格批数" Width="100" Align="Right"> </ext:Column> <ext:Column Header="不合格占比率" DataIndex="不合格占比率" Width="100" Align="Right"> <Renderer Fn="pctChange" /> </ext:Column> </Columns> </ColumnModel> <SelectionModel> <ext:RowSelectionModel ID="RowSelectionModel2" runat="server" /> </SelectionModel> <LoadMask ShowMask="true" /> <View> <ext:LockingGridView ID="LockingGridView2" runat="server"> <Templates> <Header Visible="False" ID="ctl66"> </Header> </Templates> </ext:LockingGridView> </View> </ext:GridPanel> <asp:Chart ID="ChartVendor" runat="server" BackColor="LightSteelBlue" BackGradientStyle="TopBottom" BackSecondaryColor="AliceBlue" EnableTheming="False" EnableViewState="True"> <Titles> <asp:Title Font="微软雅黑, 16pt" Alignment="TopCenter"> </asp:Title> <asp:Title Docking="Bottom" Font="Trebuchet MS, 8.25pt" Text="Zettlercn Corporation" Alignment="MiddleRight"> </asp:Title> </Titles> <Legends> <asp:Legend BackColor="Transparent" Alignment="Center" Docking="Bottom" Font="Trebuchet MS, 8.25pt, style=Bold" IsTextAutoFit="False" Name="Default" LegendStyle="Table"> </asp:Legend> </Legends> <Series> <asp:Series Name="Series1"> </asp:Series> <asp:Series Name="Series2"> </asp:Series> <asp:Series Name="Series3"> </asp:Series> </Series> <ChartAreas> <asp:ChartArea Name="ChartArea1"> </asp:ChartArea> </ChartAreas> </asp:Chart> <ext:GridPanel ID="GPType" runat="server" StoreID="StoreType" Title="物料类别来料批次情况" Width="1024" Height="300" StripeRows="true"> <TopBar> <ext:Toolbar ID="Toolbar3" runat="server"> <Items> <ext:DateField ID="dtstart" runat="server" FieldLabel="检验日期" Width="200" AllowBlank="false"> </ext:DateField> <ext:DateField ID="dtend" runat="server" FieldLabel="至日期" Width="200" AllowBlank="false"> </ext:DateField> <ext:Button ID="Button1" runat="server" Icon="Zoom" Text="查询"> <DirectEvents> <Click OnEvent="TypeQuery"> <EventMask ShowMask="true" Msg="正在提交数据，请稍候..." MinDelay="1000" /> </Click> </DirectEvents> </ext:Button> </Items> </ext:Toolbar> </TopBar> <ColumnModel ID="ColumnModel3" runat="server"> <Columns> <ext:RowNumbererColumn Locked="true" /> <ext:Column Header="物料名称" DataIndex="类别" Width="150" Locked="true" /> <ext:Column Header="来料总批次" DataIndex="来料总批次" Width="100" Align="Right"> </ext:Column> <ext:Column Header="不合格批数" DataIndex="不合格批数" Width="100" Align="Right"> </ext:Column> <ext:Column Header="不合格占比率" DataIndex="不合格占比率" Width="100" Align="Right"> <Renderer Fn="pctChange" /> </ext:Column> </Columns> </ColumnModel> <SelectionModel> <ext:RowSelectionModel ID="RowSelectionModel3" runat="server" /> </SelectionModel> <LoadMask ShowMask="true" /> <View> <ext:LockingGridView ID="LockingGridView3" runat="server"> <Templates> <Header Visible="False" ID="Header1"> </Header> </Templates> </ext:LockingGridView> </View> </ext:GridPanel> <asp:Chart ID="ChartType" runat="server" BackColor="LightSteelBlue" BackGradientStyle="TopBottom" BackSecondaryColor="AliceBlue" EnableTheming="False" EnableViewState="True"> <Titles> <asp:Title Font="微软雅黑, 16pt" Alignment="TopCenter"> </asp:Title> <asp:Title Docking="Bottom" Font="Trebuchet MS, 8.25pt" Text="Zettlercn Corporation" Alignment="MiddleRight"> </asp:Title> </Titles> <Legends> <asp:Legend BackColor="Transparent" Alignment="Center" Docking="Bottom" Font="Trebuchet MS, 8.25pt, style=Bold" IsTextAutoFit="False" Name="Default" LegendStyle="Table"> </asp:Legend> </Legends> <Series> <asp:Series Name="Series1"> </asp:Series> <asp:Series Name="Series2"> </asp:Series> <asp:Series Name="Series3"> </asp:Series> </Series> <ChartAreas> <asp:ChartArea Name="ChartArea1"> </asp:ChartArea> </ChartAreas> </asp:Chart> <br /> </div> </form></body></html>
CS
using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using DzOpenPlat.Utility;using System.Xml;using System.Xml.Xsl;using System.IO;using System.Text;using System.Drawing;using System.Web.UI.DataVisualization.Charting;using SqlServerDAL;using Ext.Net;public partial class ERP_QA_ZEIQCReport : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (!X.IsAjaxRequest) { this.txtYear.SetValue(DateTime.Now.Year.ToString()); this.dvend.SetValue(DateTime.Now.ToShortDateString()); this.dvstart.SetValue(DateTime.Now.Year.ToString() + "-01-01"); this.dtend.SetValue(DateTime.Now.ToShortDateString()); this.dtstart.SetValue(DateTime.Now.Year.ToString() + "-01-01"); GPbind(); GPVendorbind(); GPTypebind(); } } } /// <summary> /// IQC来料批次查询 /// <param name="sender"></param> /// <param name="e"></param> protected void Query(object sender, DirectEventArgs e) { GPbind(); } /// <summary> /// 供应商来料批次情况查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void VendorQuery(object sender, DirectEventArgs e) { GPVendorbind(); } /// <summary> /// 物料类别来料批次情况查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void TypeQuery(object sender, DirectEventArgs e) { GPTypebind(); } /// <summary> /// IQC数据绑定 /// </summary> private void GPbind() { if (txtYear.Text.Trim() != "") { SqlParameter[] parms = new SqlParameter[1]; parms[0] = new SqlParameter("@year", SqlDbType.VarChar, 4); parms[0].Value = txtYear.Text.Trim(); DBCon db = new DBCon("ZE"); this.StoreIQCReport.DataSource = SqlHelper.ExecuteDataset(db.StringConnection(), CommandType.StoredProcedure, "UF_ZEIQCReport", parms).Tables[0]; this.StoreIQCReport.DataBind(); DataTable dt = new DataTable(); dt = DataTableConvert.Transpose(SqlHelper.ExecuteDataset(db.StringConnection(), CommandType.StoredProcedure, "UF_ZEIQCReport", parms).Tables[1], "Item"); MutiColumn(dt); } else { X.Msg.Alert("友情提醒","请填写年份").Show(); } } /// <summary> /// 供应商数据绑定 /// </summary> private void GPVendorbind() { DateTime startdate; DateTime enddate; startdate = Convert.ToDateTime(this.dvstart.Text.Trim()); enddate = Convert.ToDateTime(this.dvend.Text.Trim()); SqlParameter[] parms = new SqlParameter[2]; parms[0] = new SqlParameter("@start", SqlDbType.VarChar, 8); parms[1] = new SqlParameter("@end", SqlDbType.VarChar, 8); parms[0].Value = string.Format("{0:yyyyMMdd}", startdate); parms[1].Value = string.Format("{0:yyyyMMdd}", enddate); DBCon db = new DBCon("ZE"); DataTable dt = new DataTable(); dt = SqlHelper.ExecuteDataset(db.StringConnection(), CommandType.StoredProcedure, "UF_ZEIQCVendor", parms).Tables[0]; this.StoreVendor.DataSource = dt; this.StoreVendor.DataBind(); MutiColumnVendor( SqlHelper.ExecuteDataset(db.StringConnection(), CommandType.StoredProcedure, "UF_ZEIQCVendor", parms).Tables[1]); } /// <summary> /// 物料类别数据绑定 /// </summary> private void GPTypebind() { DateTime startdate; DateTime enddate; startdate = Convert.ToDateTime(this.dtstart.Text.Trim()); enddate = Convert.ToDateTime(this.dtend.Text.Trim()); SqlParameter[] parms = new SqlParameter[2]; parms[0] = new SqlParameter("@start", SqlDbType.VarChar, 8); parms[1] = new SqlParameter("@end", SqlDbType.VarChar, 8); parms[0].Value = string.Format("{0:yyyyMMdd}", startdate); parms[1].Value = string.Format("{0:yyyyMMdd}", enddate); DBCon db = new DBCon("ZE"); DataTable dt = new DataTable(); dt = SqlHelper.ExecuteDataset(db.StringConnection(), CommandType.StoredProcedure, "UF_ZEIQCType", parms).Tables[0]; this.StoreType.DataSource = dt; this.StoreType.DataBind(); MutiColumnType(SqlHelper.ExecuteDataset(db.StringConnection(), CommandType.StoredProcedure, "UF_ZEIQCType", parms).Tables[1]); } /// <summary> /// 列名是否包含 /// </summary> /// <param name="dt"></param> /// <param name="ColumnName"></param> /// <returns></returns> private bool ExistColumn(DataTable dt, string ColumnName) { bool flag = false; foreach (DataColumn dc in dt.Columns) { if (dc.ColumnName == ColumnName) { flag = true; } } return flag; } /// <summary> /// 第一列所有含是否包含 /// </summary> /// <param name="dt"></param> /// <param name="ColumnName"></param> /// <returns></returns> private bool ExistRow(DataTable dt, string RowName) { bool flag = false; foreach (DataRow dr in dt.Rows) { if (dr[0].ToString() == RowName) { flag = true; } } return flag; } /// <summary> /// IQC综合情况表 /// </summary> /// <param name="dt"></param> private void MutiColumn(DataTable dt) { this.ChartIQC.Width = 1024; this.ChartIQC.Height = 350; this.ChartIQC.DataSource = dt; this.ChartIQC.DataBind(); this.ChartIQC.Series["Series1"].ChartType = SeriesChartType.Column; if (ExistColumn(dt, "检验批数")) { this.ChartIQC.Series["Series1"].XValueMember = "Item"; this.ChartIQC.Series["Series1"].YValueMembers = "检验批数"; this.ChartIQC.Series["Series1"].XAxisType = AxisType.Primary; this.ChartIQC.Series["Series1"].YAxisType = AxisType.Primary; } if (ExistColumn(dt, "合格批数")) { this.ChartIQC.Series["Series2"].XValueMember = "Item"; this.ChartIQC.Series["Series2"].YValueMembers = "合格批数"; this.ChartIQC.Series["Series2"].XAxisType = AxisType.Primary; this.ChartIQC.Series["Series2"].YAxisType = AxisType.Primary; } if (ExistColumn(dt, "批次合格率")) { this.ChartIQC.Series["Series3"].ChartType = SeriesChartType.Line; this.ChartIQC.Series["Series3"].XValueMember = "Item"; this.ChartIQC.Series["Series3"].YValueMembers = "批次合格率"; this.ChartIQC.Series["Series3"].XAxisType = AxisType.Secondary; this.ChartIQC.Series["Series3"].YAxisType = AxisType.Secondary; this.ChartIQC.Series["Series3"].ToolTip = "合格批数:#VALY"+"%"; } if (ExistColumn(dt, "目标")) { this.ChartIQC.Series["Series4"].ChartType = SeriesChartType.Line; this.ChartIQC.Series["Series4"].XValueMember = "Item"; this.ChartIQC.Series["Series4"].YValueMembers = "目标"; this.ChartIQC.Series["Series4"].XAxisType = AxisType.Secondary; this.ChartIQC.Series["Series4"].YAxisType = AxisType.Secondary; this.ChartIQC.Series["Series4"].ToolTip = "目标:#VALY" + "%"; } //图例数据 ChartIQC.Series["Series3"].IsValueShownAsLabel = true; ChartIQC.Series["Series4"].IsValueShownAsLabel = true; //图例区域标题 this.ChartIQC.ChartAreas["ChartArea1"].AxisY.Title = "检验批次"; this.ChartIQC.ChartAreas["ChartArea1"].AxisY2.Title = "合格率%"; //图例文字 this.ChartIQC.Legends[0].Enabled = true; this.ChartIQC.Series["Series1"].LegendText = "检验批数"; this.ChartIQC.Series["Series2"].LegendText = "合格批数"; this.ChartIQC.Series["Series3"].LegendText = "批次合格率"; this.ChartIQC.Series["Series4"].LegendText = "目标"; //图列坐标轴字体,坐标轴起始值 ChartIQC.ChartAreas[0].AxisY.TitleFont = new Font("微软雅黑", 12); ChartIQC.ChartAreas[0].AxisY2.TitleFont = new Font("微软雅黑", 12); ChartIQC.ChartAreas["ChartArea1"].AxisY2.Minimum = 80; //背景色设置 this.ChartIQC.ChartAreas["ChartArea1"].ShadowColor = Color.Transparent; this.ChartIQC.ChartAreas["ChartArea1"].BackColor = Color.FromArgb(209, 237, 254); //该处设置为了由天蓝到白色的逐渐变化 this.ChartIQC.ChartAreas["ChartArea1"].BackGradientStyle = GradientStyle.TopBottom; this.ChartIQC.ChartAreas["ChartArea1"].BackSecondaryColor = Color.White; //中间X,Y线条的颜色设置 this.ChartIQC.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64); this.ChartIQC.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64); //X.Y轴数据显示间隔 this.ChartIQC.ChartAreas["ChartArea1"].AxisX.Interval = 1; this.ChartIQC.ChartAreas["ChartArea1"].AxisX2.Interval = 100; //ChartAnnualSales2.ChartAreas["ChartArea1"].AxisX.MajorGrid.Enabled = false;//仅不显示x轴方向的网格线 //ChartAnnualSales2.ChartAreas["ChartArea1"].AxisY.MajorGrid.Enabled = false;//仅不显示y轴方向的网格线 ChartIQC.ChartAreas["ChartArea1"].AxisX2.MajorGrid.Enabled = false;//仅不显示x轴方向的网格线 ChartIQC.ChartAreas["ChartArea1"].AxisY2.MajorGrid.Enabled = false; //ChartIQC.ChartAreas["ChartArea1"].AxisX.Enabled = AxisEnabled.False;//不显示x轴，以及x轴方向的网格线 //ChartIQC.ChartAreas["ChartArea1"].AxisY.Enabled = AxisEnabled.False;//不显示y轴，以及y轴 } /// <summary> /// 供应商来料情况 /// </summary> /// <param name="dt"></param> private void MutiColumnVendor(DataTable dt) { this.ChartVendor.Width = 1024; this.ChartVendor.Height = 350; this.ChartVendor.DataSource = dt; this.ChartVendor.DataBind(); this.ChartVendor.Titles[0].Text = "供应商来料情况表(TOP10)"; this.ChartVendor.Series["Series1"].ChartType = SeriesChartType.Column; if (ExistColumn(dt, "来料总批次")) { this.ChartVendor.Series["Series1"].XValueMember = "供应商编码"; this.ChartVendor.Series["Series1"].YValueMembers = "来料总批次"; this.ChartVendor.Series["Series1"].XAxisType = AxisType.Primary;//第一X轴 this.ChartVendor.Series["Series1"].YAxisType = AxisType.Primary;//第一Y轴 } if (ExistColumn(dt, "不合格批数")) { this.ChartVendor.Series["Series2"].XValueMember = "供应商编码"; this.ChartVendor.Series["Series2"].YValueMembers = "不合格批数"; this.ChartVendor.Series["Series2"].XAxisType = AxisType.Primary; this.ChartVendor.Series["Series2"].YAxisType = AxisType.Primary; } if (ExistColumn(dt, "不合格占比率")) { this.ChartVendor.Series["Series3"].ChartType = SeriesChartType.Line; this.ChartVendor.Series["Series3"].XValueMember = "供应商编码"; this.ChartVendor.Series["Series3"].YValueMembers = "不合格占比率"; this.ChartVendor.Series["Series3"].XAxisType = AxisType.Secondary;//第二X轴 this.ChartVendor.Series["Series3"].YAxisType = AxisType.Secondary;//第二Y轴 // Set series and legend tooltips this.ChartVendor.Series["Series3"].ToolTip = "不合格占比率:#VALY" + "%"; } //图例数据 ChartVendor.Series["Series3"].IsValueShownAsLabel = true; //图例区域标题 this.ChartVendor.ChartAreas["ChartArea1"].AxisY.Title = "检验批次"; this.ChartVendor.ChartAreas["ChartArea1"].AxisY2.Title = "不合格占比率%"; //图例文字 this.ChartVendor.Legends[0].Enabled = true; this.ChartVendor.Series["Series1"].LegendText = "来料总批次"; this.ChartVendor.Series["Series2"].LegendText = "不合格批数"; this.ChartVendor.Series["Series3"].LegendText = "不合格占比率"; //图列坐标轴字体,坐标轴起始值 ChartVendor.ChartAreas[0].AxisY.TitleFont = new Font("微软雅黑", 12); ChartVendor.ChartAreas[0].AxisY2.TitleFont = new Font("微软雅黑", 12); //ChartVendor.ChartAreas["ChartArea1"].AxisY2.Minimum = 80; //背景色设置 this.ChartVendor.ChartAreas["ChartArea1"].ShadowColor = Color.Transparent; this.ChartVendor.ChartAreas["ChartArea1"].BackColor = Color.FromArgb(209, 237, 254); //该处设置为了由天蓝到白色的逐渐变化 this.ChartVendor.ChartAreas["ChartArea1"].BackGradientStyle = GradientStyle.TopBottom; this.ChartVendor.ChartAreas["ChartArea1"].BackSecondaryColor = Color.White; //中间X,Y线条的颜色设置 this.ChartVendor.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64); this.ChartVendor.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64); //X.Y轴数据显示间隔 this.ChartVendor.ChartAreas["ChartArea1"].AxisX.Interval = 1; this.ChartVendor.ChartAreas["ChartArea1"].AxisX2.Interval = 100; ChartVendor.ChartAreas["ChartArea1"].AxisX2.MajorGrid.Enabled = false;//仅不显示x轴方向的网格线 ChartVendor.ChartAreas["ChartArea1"].AxisY2.MajorGrid.Enabled = false; } /// <summary> /// 物料类别来料情况 /// </summary> /// <param name="dt"></param> private void MutiColumnType(DataTable dt) { this.ChartType.Width = 1024; this.ChartType.Height = 350; this.ChartType.DataSource = dt; this.ChartType.DataBind(); this.ChartType.Titles[0].Text = "物料类别来料批次情况表(TOP10)"; this.ChartType.Series["Series1"].ChartType = SeriesChartType.Column; if (ExistColumn(dt, "来料总批次")) { this.ChartType.Series["Series1"].XValueMember = "类别"; this.ChartType.Series["Series1"].YValueMembers = "来料总批次"; this.ChartType.Series["Series1"].XAxisType = AxisType.Primary; this.ChartType.Series["Series1"].YAxisType = AxisType.Primary; } if (ExistColumn(dt, "不合格批数")) { this.ChartType.Series["Series2"].XValueMember = "类别"; this.ChartType.Series["Series2"].YValueMembers = "不合格批数"; this.ChartType.Series["Series2"].XAxisType = AxisType.Primary; this.ChartType.Series["Series2"].YAxisType = AxisType.Primary; } if (ExistColumn(dt, "不合格占比率")) { this.ChartType.Series["Series3"].ChartType = SeriesChartType.Line; this.ChartType.Series["Series3"].XValueMember = "类别"; this.ChartType.Series["Series3"].YValueMembers = "不合格占比率"; this.ChartType.Series["Series3"].XAxisType = AxisType.Secondary; this.ChartType.Series["Series3"].YAxisType = AxisType.Secondary; // Set series and legend tooltips this.ChartType.Series["Series3"].ToolTip = "不合格占比率:#VALY" + "%"; } //图例数据 ChartType.Series["Series3"].IsValueShownAsLabel = true; //图例区域标题 this.ChartType.ChartAreas["ChartArea1"].AxisY.Title = "检验批次"; this.ChartType.ChartAreas["ChartArea1"].AxisY2.Title = "不合格占比率%"; //图例文字 this.ChartType.Legends[0].Enabled = true; this.ChartType.Series["Series1"].LegendText = "来料总批次"; this.ChartType.Series["Series2"].LegendText = "不合格批数"; this.ChartType.Series["Series3"].LegendText = "不合格占比率"; //图列坐标轴字体,坐标轴起始值 ChartType.ChartAreas[0].AxisY.TitleFont = new Font("微软雅黑", 12); ChartType.ChartAreas[0].AxisY2.TitleFont = new Font("微软雅黑", 12); //ChartVendor.ChartAreas["ChartArea1"].AxisY2.Minimum = 80; //背景色设置 this.ChartType.ChartAreas["ChartArea1"].ShadowColor = Color.Transparent; this.ChartType.ChartAreas["ChartArea1"].BackColor = Color.FromArgb(209, 237, 254); //该处设置为了由天蓝到白色的逐渐变化 this.ChartType.ChartAreas["ChartArea1"].BackGradientStyle = GradientStyle.TopBottom; this.ChartType.ChartAreas["ChartArea1"].BackSecondaryColor = Color.White; //中间X,Y线条的颜色设置 this.ChartType.ChartAreas["ChartArea1"].AxisX.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64); this.ChartType.ChartAreas["ChartArea1"].AxisY.MajorGrid.LineColor = Color.FromArgb(64, 64, 64, 64); //X.Y轴数据显示间隔 this.ChartType.ChartAreas["ChartArea1"].AxisX.Interval = 1; this.ChartType.ChartAreas["ChartArea1"].AxisX2.Interval = 100; ChartType.ChartAreas["ChartArea1"].AxisX2.MajorGrid.Enabled = false;//仅不显示x轴方向的网格线 ChartType.ChartAreas["ChartArea1"].AxisY2.MajorGrid.Enabled = false; }}页面
![[Ext.Net]易飞IQC来料批次分析与MsChart 施用双轴显示](http://img.reader8.com/uploadfile/jiaocheng/201401106/2811/2014012822115813241.jpg)
![[Ext.Net]易飞IQC来料批次分析与MsChart 施用双轴显示](http://img.reader8.com/uploadfile/jiaocheng/201401106/2811/2014012822115813242.jpg)
![[Ext.Net]易飞IQC来料批次分析与MsChart 施用双轴显示](http://img.reader8.com/uploadfile/jiaocheng/201401106/2811/2014012822115813243.jpg)