<%@ Page Language="vb" AutoEventWireup="false" MasterPageFile="~/Admin.Master" CodeBehind="Integrity.aspx.vb" Inherits="Superbowl.IntegrityTest" 
    title="Untitled Page" EnableEventValidation="false" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">

  <script language="javascript" type="text/javascript">

</script>

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="body" runat="server">
  <table width="100%" cellpadding="2">
    <tr>
        <td align="center" class="headings" colspan="4">Data Warehouse Integrity</td>
    </tr>
    <tr>
        <td width="15%">&nbsp;</td>
        <td width="25%">&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
    </tr>
    <tr>
        <td class="redBold" colspan="2" >Data Warehouse Summary Report:</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
    </tr>
    <tr>
        <td align="left" colspan="4" style="border: 1px solid maroon">
			  <asp:GridView ID="gvSummary" runat="server" Width="100%" CellPadding="2" 
            AllowSorting="True" AutoGenerateColumns="False" 
				 PageSize="25" HeaderStyle-CssClass="gvHeader" ShowFooter="True" 
            DataSourceID="sdsSummary">	  					
					<HeaderStyle CssClass="gvHeader"/>				  
				  <AlternatingRowStyle CssClass="lightergrey" />
				   <PagerSettings PageButtonCount="20" Mode="NumericFirstLast" FirstPageText="<B>&lt;&lt;</B>" LastPageText="<B>&gt;&gt;</B>" />
				   <Columns>
             <asp:BoundField DataField="Entity" HeaderText="Entity" ReadOnly="True" 
               SortExpression="Entity" />
             <asp:BoundField DataField="Master" HeaderText="Master (Current)" ReadOnly="True" 
               SortExpression="Master" NullDisplayText="0">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:BoundField DataField="Data Warehouse" HeaderText="Data Warehouse (Current)" 
               ReadOnly="True" SortExpression="Data Warehouse" NullDisplayText="0">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:BoundField DataField="Current Month" HeaderText="Current Month" ReadOnly="True" 
               SortExpression="Current Month" NullDisplayText="0">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:BoundField DataField="Previous Month" HeaderText="Previous Month" ReadOnly="True" 
               SortExpression="Previous Month" NullDisplayText="0">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
          </Columns>
				   <FooterStyle CssClass="gvHeader" />
				  <RowStyle CssClass="gvItem" />		
			  </asp:GridView>	
		  </td>
    </tr>
    <tr>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
    </tr>
    <tr>
        <td class="redBold" colspan="4" >Records the Customer Master Missing from the Data 
          Warehouse:</td>
    </tr>
    <tr>
        <td align="left" colspan="4" style="border: 1px solid maroon">
			  <asp:GridView ID="gvCustomerMaster" runat="server" Width="100%" CellPadding="2" 
            AllowSorting="True" AutoGenerateColumns="False" 
				 PageSize="25" HeaderStyle-CssClass="gvHeader" ShowFooter="True" 
            DataSourceID="sdsCustomerMaster" DataKeyNames="strCustomerNo">	  					
					<HeaderStyle CssClass="gvHeader"/>				  
				  <AlternatingRowStyle CssClass="lightergrey" />
				   <PagerSettings PageButtonCount="20" Mode="NumericFirstLast" FirstPageText="<B>&lt;&lt;</B>" LastPageText="<B>&gt;&gt;</B>" />
				   <Columns>
             <asp:BoundField DataField="strCustomerNo" HeaderText="Customer Number" ReadOnly="True" 
               SortExpression="strCustomerNo" >
             <HeaderStyle Width="1%" />
             </asp:BoundField>
             <asp:BoundField DataField="strCustomerName" HeaderText="Customer Name" 
               SortExpression="strCustomerName">
             </asp:BoundField>
             <asp:BoundField DataField="strBranch" HeaderText="Branch" 
               ReadOnly="True" SortExpression="strBranch">
             </asp:BoundField>
             <asp:BoundField DataField="strRep" HeaderText="Rep" ReadOnly="True" 
               SortExpression="strRep"
               NullDisplayText="&lt;div align=center&gt;&lt;img src='redExclamation24.png'/&gt;&lt;/div&gt;">
             </asp:BoundField>
             <asp:BoundField DataField="strSegment" HeaderText="Segment" 
               SortExpression="strSegment"
               NullDisplayText="&lt;div align=center&gt;&lt;img src='redExclamation24.png'/&gt;&lt;/div&gt;">
             </asp:BoundField>
             <asp:BoundField DataField="strChannel" HeaderText="Channel" 
               SortExpression="strChannel" 
               NullDisplayText="&lt;div align=center&gt;&lt;img src='redExclamation24.png'/&gt;&lt;/div&gt;"/>
             <asp:CheckBoxField DataField="blnActive" HeaderText="Active" 
               SortExpression="blnActive">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Center" />
             </asp:CheckBoxField>
             <asp:BoundField DataField="Sales" HeaderText="Sales" SortExpression="Sales">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:TemplateField HeaderText="Select">
               <HeaderTemplate>
                 <asp:Button ID="btnCMst" runat="server" BorderStyle="None" 
                   CausesValidation="False"  Text="Select All" 
                   UseSubmitBehavior="true" onclick="btnCMst_Click" />
               </HeaderTemplate>
               <ItemTemplate>
                 <asp:CheckBox ID="chkCMst" runat="server" />
               </ItemTemplate>
               <HeaderStyle Width="1%" />
               <ItemStyle HorizontalAlign="Center" />
             </asp:TemplateField>
          </Columns>
				   <FooterStyle CssClass="gvHeader" />
				  <RowStyle CssClass="gvItem" />		
			  </asp:GridView>								  
			    

        </td>
    </tr>
    <tr>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td align="right" ><asp:Button ID="btnCustomerMst" runat="server" 
            Text="Add to Data Warehouse" /> </td>
    </tr>    
    <tr>
        <td class="redBold" colspan="4" >Records the Customer Data Warehouse Missing from 
          the Master:</td>
    </tr>
    <tr>
        <td align="left" colspan="4" style="border: 1px solid maroon">
			  <asp:GridView ID="gvCustomerDW" runat="server" Width="100%" CellPadding="2" 
            AllowSorting="True" AutoGenerateColumns="False" 
				 PageSize="25" HeaderStyle-CssClass="gvHeader" ShowFooter="True" 
            DataSourceID="sdsCustomerDW" DataKeyNames="strCustomerNo">	  					
					<HeaderStyle CssClass="gvHeader"/>				  
				  <AlternatingRowStyle CssClass="lightergrey" />
				   <PagerSettings PageButtonCount="20" Mode="NumericFirstLast" FirstPageText="<B>&lt;&lt;</B>" LastPageText="<B>&gt;&gt;</B>" />
				   <Columns>
             <asp:BoundField DataField="strCustomerNo" HeaderText="Customer Number" ReadOnly="True" 
               SortExpression="strCustomerNo" >
             <HeaderStyle Width="1%" />
             </asp:BoundField>
             <asp:BoundField DataField="strCustomerName" HeaderText="Customer Name" 
               SortExpression="strCustomerName">
             </asp:BoundField>
             <asp:BoundField DataField="strBranch" HeaderText="Branch" 
               ReadOnly="True" SortExpression="strBranch">
             </asp:BoundField>
             <asp:BoundField DataField="strRep" HeaderText="Rep" ReadOnly="True" 
               SortExpression="strRep"
               NullDisplayText="&lt;div align=center&gt;&lt;img src='redExclamation24.png'/&gt;&lt;/div&gt;">
             </asp:BoundField>
             <asp:BoundField DataField="strSegment" HeaderText="Segment" 
               SortExpression="strSegment"
               NullDisplayText="&lt;div align=center&gt;&lt;img src='redExclamation24.png'/&gt;&lt;/div&gt;">
             </asp:BoundField>
             <asp:BoundField DataField="strChannel" HeaderText="Channel" 
               SortExpression="strChannel" 
               NullDisplayText="&lt;div align=center&gt;&lt;img src='redExclamation24.png'/&gt;&lt;/div&gt;"/>
             <asp:CheckBoxField DataField="blnActive" HeaderText="Active" 
               SortExpression="blnActive">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Center" />
             </asp:CheckBoxField>
             <asp:BoundField DataField="Sales" HeaderText="Sales" SortExpression="Sales">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:TemplateField HeaderText="Select">
               <HeaderTemplate>
                 <asp:Button ID="btnCDW" runat="server" BorderStyle="None" 
                   CausesValidation="False"  Text="Select All" 
                   UseSubmitBehavior="true" onclick="btnCDW_Click" />
               </HeaderTemplate>
               <ItemTemplate>
                 <asp:CheckBox ID="chkCDW" runat="server" />
               </ItemTemplate>
               <ItemStyle HorizontalAlign="Center" Width="1%" />
             </asp:TemplateField>
          </Columns>
				   <FooterStyle CssClass="gvHeader" />
				  <RowStyle CssClass="gvItem" />		
			  </asp:GridView>			
	    
			  </td>
    </tr>    
    <tr>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td align="right" ><asp:Button ID="btnCustomerDW" runat="server" 
            Text="Add to Master" /> </td>
    </tr>
    <tr>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
    </tr>
    <tr>
        <td class="redBold" colspan="4" >Records the Product Master Missing from the Data 
          Warehouse:</td>
    </tr>
    <tr>
        <td align="left" colspan="4" style="border: 1px solid maroon">
			  <asp:GridView ID="gvPMst" runat="server" Width="100%" CellPadding="2" 
            AllowSorting="True" AutoGenerateColumns="False" 
				 PageSize="25" HeaderStyle-CssClass="gvHeader" ShowFooter="True" 
            DataSourceID="sdsProductMst" DataKeyNames="Prod Code">	  					
					<HeaderStyle CssClass="gvHeader"/>				  
				  <AlternatingRowStyle CssClass="lightergrey" />
				   <PagerSettings PageButtonCount="20" Mode="NumericFirstLast" FirstPageText="<B>&lt;&lt;</B>" LastPageText="<B>&gt;&gt;</B>" />
				   <Columns>
             <asp:BoundField DataField="Prod Code" HeaderText="Prod Code" ReadOnly="True" 
               SortExpression="Prod Code" >
             <HeaderStyle Width="1%" />
             </asp:BoundField>
             <asp:BoundField DataField="Description" HeaderText="Description" 
               SortExpression="Description">
             </asp:BoundField>
             <asp:BoundField DataField="Brand" HeaderText="Brand" SortExpression="Brand">
             </asp:BoundField>
             <asp:BoundField DataField="Package" HeaderText="Package" 
               SortExpression="Package">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:BoundField DataField="Size" HeaderText="Size" 
               SortExpression="Size">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:BoundField DataField="FlatCase" HeaderText="FlatCase" 
               SortExpression="FlatCase">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:CheckBoxField DataField="Active" HeaderText="Active" 
               SortExpression="Active">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Center" />
             </asp:CheckBoxField>
             <asp:BoundField DataField="Sales" HeaderText="Sales" SortExpression="Sales" 
               ReadOnly="True">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:TemplateField HeaderText="Select">
               <HeaderTemplate>
                 <asp:Button ID="btnPMst" runat="server" BorderStyle="None" 
                   CausesValidation="False"  Text="Select All" 
                   UseSubmitBehavior="true" onclick="btnPMst_Click" />
               </HeaderTemplate>
               <ItemTemplate>
                 <asp:CheckBox ID="chkPMst" runat="server" />
               </ItemTemplate>
               <ItemStyle HorizontalAlign="Center" Width="1%" />
             </asp:TemplateField>
          </Columns>
				   <FooterStyle CssClass="gvHeader" />
				  <RowStyle CssClass="gvItem" />		
			  </asp:GridView>		
        </td>
    </tr>
    <tr>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td align="right" >
          <asp:Button ID="btnPMst" runat="server" 
            Text="Add to Data Warehouse" /> </td>
    </tr>
    <tr>
        <td class="redBold" colspan="4" >Records the Product Data Warehouse Missing from 
          the Master:</td>
    </tr>
    <tr>
        <td align="left" colspan="4" style="border: 1px solid maroon">
			  <asp:GridView ID="gvPDW" runat="server" Width="100%" CellPadding="2" 
            AllowSorting="True" AutoGenerateColumns="False" 
				 PageSize="25" HeaderStyle-CssClass="gvHeader" ShowFooter="True" 
            DataSourceID="sdsProductDW" DataKeyNames="Prod Code">	  					
					<HeaderStyle CssClass="gvHeader"/>				  
				  <AlternatingRowStyle CssClass="lightergrey" />
				   <PagerSettings PageButtonCount="20" Mode="NumericFirstLast" FirstPageText="<B>&lt;&lt;</B>" LastPageText="<B>&gt;&gt;</B>" />
				   <Columns>
             <asp:BoundField DataField="Prod Code" HeaderText="Prod Code" ReadOnly="True" 
               SortExpression="Prod Code" >
             <HeaderStyle Width="1%" />
             </asp:BoundField>
             <asp:BoundField DataField="Description" HeaderText="Description" 
               SortExpression="Description">
             </asp:BoundField>
             <asp:BoundField DataField="Brand" HeaderText="Brand" SortExpression="Brand">
             </asp:BoundField>
             <asp:BoundField DataField="Package" HeaderText="Package" 
               SortExpression="Package">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:BoundField DataField="Size" HeaderText="Size" 
               SortExpression="Size">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:BoundField DataField="FlatCase" HeaderText="FlatCase" 
               SortExpression="FlatCase">
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:CheckBoxField DataField="Active" HeaderText="Active" 
               SortExpression="Active">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Center" />
             </asp:CheckBoxField>
             <asp:BoundField DataField="Sales" HeaderText="Sales" SortExpression="Sales" 
               ReadOnly="True">
             <HeaderStyle Width="1%" />
             <ItemStyle HorizontalAlign="Right" />
             </asp:BoundField>
             <asp:TemplateField HeaderText="Select">
               <HeaderTemplate>
                 <asp:Button ID="btnPDW" runat="server" BorderStyle="None" 
                   CausesValidation="False" Text="Select All" 
                   UseSubmitBehavior="true" onclick="btnPDW_Click" />
               </HeaderTemplate>
               <ItemTemplate>
                 <asp:CheckBox ID="chkPDW" runat="server" />
               </ItemTemplate>
               <ItemStyle HorizontalAlign="Center" Width="1%" />
             </asp:TemplateField>
          </Columns>
				   <FooterStyle CssClass="gvHeader" />
				  <RowStyle CssClass="gvItem" />		
			  </asp:GridView>		
        </td>
    </tr>
    <tr>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td align="right" >
          <asp:Button ID="btnPDW" runat="server" 
            Text="Add to Master" /> </td>
    </tr>
    <tr>
        <td colspan="4" >
          <asp:Label ID="lblMessage" runat="server" CssClass="redBold"></asp:Label>
        </td>
    </tr>
    <tr>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
        <td >&nbsp;</td>
    </tr>
</table>

<asp:SqlDataSource ID="sdsSummary" runat="server" 
ConnectionString="<%$ ConnectionStrings:csStockControl %>" 
SelectCommand="SELECT     strType AS Entity, MST AS Master, DWD AS [Data Warehouse], date1 as [Current Month], date2 as [Previous Month]
FROM         (SELECT     'Customer' AS strType,
(SELECT     COUNT(strCustomerNo) AS Expr1
FROM          mstCustomer) AS MST,
(SELECT     COUNT(strCustomerNo)
FROM          dwdCustomer) AS DWD,
(SELECT     intCustomers
FROM          dlyDataWarehouse AS dlyDataWarehouse_2
WHERE      ([strDate] = #date1)) AS date1,
(SELECT     intCustomers
FROM          dlyDataWarehouse
WHERE      ([strDate] = #date2)) AS date2
UNION
SELECT     TOP (100) PERCENT 'Product' AS strType,
(SELECT     COUNT(strProdCode) AS Expr1
FROM          mstProduct) AS MST,
(SELECT     COUNT(strProdCode)
FROM          dwdProduct) AS DWD,
(SELECT     intProducts
FROM          dlyDataWarehouse AS dlyDataWarehouse_2
WHERE      ([strDate] = #date1)) AS date1,
(SELECT     intProducts
FROM          dlyDataWarehouse AS dlyDataWarehouse_3
WHERE      ([strDate] = #date2)) AS date2
ORDER BY strType) AS derivedtbl_1">

</asp:SqlDataSource>

<asp:SqlDataSource ID="sdsCustomerMaster" runat="server" 
  ConnectionString="<%$ ConnectionStrings:csStockControl %>" 
  SelectCommand="SELECT mstCustomer.strCustomerNo, mstCustomer.strCustomerName, mstBranch.strBranch + ' - ' + mstBranch.strCode AS strBranch, mstRep.strRepName + ' - ' + mstRep.RepID AS strRep, mstSegment.strSegment, mstChannel.strChannel, mstCustomer.blnActive, COUNT(dwfSales.refCustomerNo) AS Sales FROM dwfSales RIGHT OUTER JOIN mstCustomer ON dwfSales.refCustomerNo = mstCustomer.strCustomerNo LEFT OUTER JOIN mstChannel LEFT OUTER JOIN mstSegment ON mstChannel.refSegmentID = mstSegment.SegmentID ON mstCustomer.refChannelID = mstChannel.ChannelID LEFT OUTER JOIN mstRep ON mstCustomer.refRepID = mstRep.RepID LEFT OUTER JOIN mstBranch ON mstCustomer.refBranchID = mstBranch.BranchID LEFT OUTER JOIN dwdCustomer ON mstCustomer.strCustomerNo = dwdCustomer.strCustomerNo WHERE (dwdCustomer.strCustomerNo IS NULL) GROUP BY mstCustomer.strCustomerNo, mstCustomer.strCustomerName, mstBranch.strBranch + ' - ' + mstBranch.strCode, mstRep.strRepName + ' - ' + mstRep.RepID, mstSegment.strSegment, mstChannel.strChannel, mstCustomer.blnActive">
</asp:SqlDataSource>

<asp:SqlDataSource ID="sdsCustomerDW" runat="server" 
  ConnectionString="<%$ ConnectionStrings:csStockControl %>" 
  SelectCommand="SELECT dwdCustomer.strCustomerNo, dwdCustomer.strCustomerName, dwdCustomer.strRepName + ' - ' + dwdCustomer.refRepID AS strRep, dwdCustomer.strSegment, dwdCustomer.strChannel, dwdCustomer.strBranch + ' - ' + dwdCustomer.strCode AS strBranch, dwdCustomer.blnActive, COUNT(dwfSales.refCustomerNo) AS Sales FROM dwdCustomer LEFT OUTER JOIN dwfSales ON dwdCustomer.strCustomerNo = dwfSales.refCustomerNo LEFT OUTER JOIN mstCustomer ON dwdCustomer.strCustomerNo = mstCustomer.strCustomerNo WHERE (mstCustomer.strCustomerNo IS NULL) GROUP BY dwdCustomer.strCustomerNo, dwdCustomer.strCustomerName, dwdCustomer.strRepName + ' - ' + dwdCustomer.refRepID, dwdCustomer.strSegment, dwdCustomer.strChannel, dwdCustomer.strBranch + ' - ' + dwdCustomer.strCode, dwdCustomer.blnActive ORDER BY dwdCustomer.strCustomerName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="sdsProductMst" runat="server" 
  ConnectionString="<%$ ConnectionStrings:csStockControl %>" 
  SelectCommand="SELECT mstProduct.strProdCode AS 'Prod Code', mstProduct.strLongProdDesc AS Description, mstBrand.strBrand AS Brand, mstProduct.intPack AS Package, mstProduct.intSize AS Size, ROUND(mstProduct.dblFlat, 4) AS FlatCase, mstProduct.blnActive AS Active, COUNT(dwfSales.refProdCode) AS Sales FROM mstBrand INNER JOIN mstProduct ON mstBrand.BrandID = mstProduct.refBrandID LEFT OUTER JOIN dwdProduct ON mstProduct.strProdCode = dwdProduct.strProdCode LEFT OUTER JOIN dwfSales ON mstProduct.strProdCode = dwfSales.refProdCode WHERE (dwdProduct.strProdCode IS NULL) GROUP BY mstProduct.strProdCode, mstProduct.strLongProdDesc, mstProduct.intPack, mstProduct.intSize, mstProduct.dblFlat, mstProduct.blnActive, mstBrand.strBrand">
</asp:SqlDataSource>

<asp:SqlDataSource ID="sdsProductDW" runat="server" 
  ConnectionString="<%$ ConnectionStrings:csStockControl %>" 
  
    SelectCommand="SELECT dwdProduct.strProdCode AS [Prod Code], dwdProduct.strLongProdDesc AS Description, dwdProduct.strBrand AS Brand, dwdProduct.intPack AS Package, dwdProduct.intSize AS Size, Round(dwdProduct.dblFlat,4) AS FlatCase, dwdProduct.blnActive AS Active, COUNT(dwfSales.refProdCode) AS Sales FROM dwdProduct LEFT OUTER JOIN dwfSales ON dwdProduct.strProdCode = dwfSales.refProdCode LEFT OUTER JOIN mstProduct ON dwdProduct.strProdCode = mstProduct.strProdCode WHERE (mstProduct.strProdCode IS NULL) GROUP BY dwdProduct.strProdCode, dwdProduct.strLongProdDesc, dwdProduct.intPack, dwdProduct.intSize, dwdProduct.dblFlat, dwdProduct.blnActive, dwdProduct.strBrand">
</asp:SqlDataSource>
      
</asp:Content>
