ModuleMaster/pc/outstatus.aspx - main - ivan/ModMas

ModuleMaster/pc/outstatus.aspx
975 строк37 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="outstatus.aspx.vb" Inherits="PC_outstatus" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Outbound status</title>
    <script src="../Scripts/jquery-1.9.1.min.js"></script>
    <script src="../Scripts/bootstrap.js"></script>
    <script src="../Scripts/popover.js"></script>

    <style>
        #blink {
                -webkit-animation: blink 2s linear infinite;
                  animation: blink 2s linear infinite;
                }
                @-webkit-keyframes blink {
                  50% { color: rgb(255, 0, 0); }
                 }        
                @keyframes blink {
                  50% { color: rgb(255, 0, 0); }
                }
        @font-face {
            font-family: "Agency FB";
            src: url('../fonts/AGENCYB.TTF');
        }
        html, body, form {
            width: 100%;
            height: 100%;
            background-color: transparent;
            font-family: 'Agency FB', 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            font-size: 18pt !important;
            margin: 0;
        }
        table {
            width: 100%;
            height: 100%;
            text-align : center;
        }
        #ListView1_itemPlaceholderContainer td {
            width: 11%;
            font-size: 22pt;
        }
         #ListView2_itemPlaceholderContainer td {
            width: 11%;
            font-size: 22pt;
        }
        th{
            background-color: steelblue;
            color: white;
            font-size: 26pt;
        }

         .footer td{
            background-color: steelblue;
            color: white;
            font-size: 26pt;
        }

        caption
		{
			color: White;
			font-weight: bold;
			background-color: DarkGray;
		}
        .header{
            background-color: navy;
            color: white;
        }


        .simplepopup
		{
			background-color: Navy;
			color: White;
		}
		
		.tooltip
		{
			position: absolute;
			z-index: 1030;
			display: block;
			font-size: 12px;
			line-height: 1.4;
			visibility: visible;
			max-width: 1100px !important;
		}
		
		.tooltip-inner
		{
			max-width: 1000px;
			padding: 3px 8px;
			color: #000000;
			text-align: center;
			text-decoration: none;
			background-color: White;
			border-radius: 4px;
		}


        .detailTable td{
            font-size: 18pt !important;
        }




    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
<%-- 
FITST TABLE
-------------------------------------------------------------------------------------------------------                
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
--%>
            <asp:ListView ID="ListView1" runat="server">
                <AlternatingItemTemplate>
                    <tr style="background-color:#FFF8DC;">
                        <td>
                            <asp:Label ID="LineLabel" runat="server" Text='<%# Eval("Line") %>' />
                        </td>
                        <td>
                            <asp:Label ID="PlanLabel" runat="server" Text='<%# Eval("Plan") %>' />
                        </td>
                        <td>
                            <asp:Label ID="ResultLabel" runat="server" Text='<%# Eval("Result") %>' />
                        </td>
                        <td>
                            <asp:Label ID="PassLabel" runat="server" Text='<%# Eval("Pass") %>' />
                        </td>
                        <td>
                            <asp:Label ID="WaitLabel" runat="server" Text='<%# Eval("Wait") %>' />
                        </td>
                        <td>
                            <asp:Label ID="In_loading_processLabel" runat="server" Text='<%# Eval("[Today in loading process]") %>' />
                        </td>

                        <td>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("[In loading process]") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Factory_stockLabel" runat="server" Text='<%# Eval("[Today factory stock]") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Factory stock]") %>' />
                        </td>




                        <td id="SysStockTD" runat="server" visible="False">
                            <asp:Label ID="System_stockLabel" runat="server" Text='<%# Eval("[System stock]") %>' />
                        </td>
                    </tr>
                </AlternatingItemTemplate>
                <EmptyDataTemplate>
                    <table runat="server" style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;">
                        <tr>
                            <td>No plan data found.</td>
                        </tr>
                    </table>
                </EmptyDataTemplate>
                <ItemTemplate>
                    <tr style="background-color:#DCDCDC;color: #000000;">
                        <td>
                            <asp:Label ID="LineLabel" runat="server" Text='<%# Eval("Line") %>' />
                        </td>
                        <td>
                            <asp:Label ID="PlanLabel" runat="server" Text='<%# Eval("Plan") %>' />
                        </td>
                        <td>
                            <asp:Label ID="ResultLabel" runat="server" Text='<%# Eval("Result") %>' />
                        </td>
                        <td>
                            <asp:Label ID="PassLabel" runat="server" Text='<%# Eval("Pass") %>' />
                        </td>
                        <td>
                            <asp:Label ID="WaitLabel" runat="server" Text='<%# Eval("Wait") %>' />
                        </td>
                        <td>
                            <asp:Label ID="In_loading_processLabel" runat="server" Text='<%# Eval("[Today in loading process]") %>' />
                        </td>

                        <td>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("[In loading process]") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Factory_stockLabel" runat="server" Text='<%# Eval("[Today factory stock]") %>' />
                        </td>
                        <td>
                            <asp:Label ID="Label2" runat="server" Text='<%# Eval("[Factory stock]") %>' />
                        </td>
                        <td id="SysStockTD" runat="server" visible="False">
                            <asp:Label ID="System_stockLabel" runat="server" Text='<%# Eval("[System stock]") %>' />
                        </td>
                    </tr>
                </ItemTemplate>
                <LayoutTemplate>
                    <table runat="server">
                        <tr runat="server">
                            <td runat="server">
                                <table id="itemPlaceholderContainer" runat="server" border="1" 
                                    style="background-color: #FFFFFF;
                                    width: 100%;
                                    border-collapse: collapse;
                                    border-color: #999999;
                                    border-style:none;
                                    border-width:1px;">
                                    <tr runat="server" style="background-color:#DCDCDC;color: #000000;">
                                        <th runat="server" rowspan="2">Line</th>
                                        <th runat="server" colspan ="2">Production</th>
                                        <th runat="server" colspan ="2">OQC</th>
                                        <th runat="server" colspan ="4">Warehouse</th>
                                    </tr>
                                    <tr runat="server" style="background-color:#DCDCDC;color: #000000;">
                                        <th runat="server" class ="header">Plan</th>
                                        <th runat="server" class ="header">Result</th>
                                        <th runat="server" class ="header">Pass</th>
                                        <th runat="server" class ="header">Wait</th>
                                        <th runat="server" class ="header">In loading process Today</th>
                                        <th runat="server" class ="header">In loading process Weekly</th>
                                        <th runat="server" class ="header">Factory stock Today</th>
                                        <th runat="server" class ="header">Factory stock Weekly</th>
                                        <th runat="server" class ="header" visible="False">Not GI</th>
                                    </tr>
                                    <tr id="itemPlaceholder" runat="server">
                                    </tr>
                                </table>
                            </td>
                        </tr>
                        <%--<tr runat="server">
                            <td runat="server" style="text-align: center;background-color: #CCCCCC;font-family: Verdana, Arial, Helvetica, sans-serif;color: #000000;"></td>
                        </tr>--%>
                    </table>
                </LayoutTemplate>
            </asp:ListView>
<%-- 
SECOND TABLE
-------------------------------------------------------------------------------------------------------                
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
--%>

           <div style ="height: 10px; background-color: black;"></div>
             <asp:ListView ID="ListView2" runat="server">
                <AlternatingItemTemplate>
                    <tr style="background-color:#FFF8DC;">
                        <td>
                            <asp:Label ID="LINELabel" runat="server" Text='<%# Eval("LINE") %>' />
                        </td>
                        <td>
                            <asp:Label ID="TOTAL_QTYLabel" runat="server" Text='<% # ProcessMyDataItem(Eval("DONE_QTY"), Eval("INPROGRESS_QTY"))%>' />
                        </td>
                        <td>
                            <asp:Label ID="DONE_QTYLabel" runat="server" Text='<%# Eval("DONE_QTY") %>' />
                        </td>
                        <td runat="server" id="Repair_Diff_TD">
                            <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                <asp:Label ID="INPROGRESS_QTYLabel" runat="server" Text='<%# Eval("INPROGRESS_QTY") %>' />
                            </div>
                        </td>
                        <td>
                            <asp:Label ID="REWORK_TOTAL" runat="server" Text='<%# ProcessMyDataItem(Eval("REWORK_IN_PROGRESS"), Eval("REWORK_DONE")) %>' />
                        </td>
                       <td runat="server" id="tooltip_rework_done">
                            <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                <asp:Label ID="REWORK_DONELabel" runat="server" Text='<%# Eval("REWORK_DONE") %>' />
                            </div>
                        </td>
                        <td runat="server" id="tooltip_rework_in_progress">
                            <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                <asp:Label ID="REWORK_IN_PROGRESSLabel" runat="server" Text='<%# Eval("REWORK_IN_PROGRESS") %>' />
                            </div>
                        </td>                       <td id="domestic_td" runat="server" >
                            <asp:Label ID="DLabel" runat="server" Text='<%# Eval("D") %>' />
                        </td>
                        <td id="export_td" runat="server" >
                            <asp:Label ID="ELabel" runat="server" Text='<%# Eval("E") %>' />
                        </td>
                    </tr>
                </AlternatingItemTemplate>
                <EmptyDataTemplate>
                    <table runat="server" style="background-color: #FFFFFF;border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px;">
                        <tr>
                            <td>No data was returned.</td>
                        </tr>
                    </table>
                </EmptyDataTemplate>
               
                <ItemTemplate>
                    <tr style="background-color:#DCDCDC;color: #000000;">
                        <td>
                            <asp:Label ID="LINELabel" runat="server" Text='<%# Eval("LINE") %>' />
                        </td>


                        <td>
                            <asp:Label ID="TOTAL_QTYLabel" runat="server" Text='<% # ProcessMyDataItem(Eval("DONE_QTY"), Eval("INPROGRESS_QTY"))%>' />
                        </td>


                        <td>
                            <asp:Label ID="DONE_QTYLabel" runat="server" Text='<%# Eval("DONE_QTY") %>' />
                        </td>
                        <td runat="server" id="Repair_Diff_TD">
                            <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                <asp:Label ID="INPROGRESS_QTYLabel" runat="server" Text='<%# Eval("INPROGRESS_QTY") %>' />
                            </div>
                        </td>
                        <td>
                            <asp:Label ID="REWORK_TOTAL" runat="server" Text='<%# ProcessMyDataItem(Eval("REWORK_IN_PROGRESS"), Eval("REWORK_DONE")) %>' />
                        </td>
                        <td runat="server" id="tooltip_rework_done">
                            <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                <asp:Label ID="REWORK_DONELabel" runat="server" Text='<%# Eval("REWORK_DONE") %>' />
                            </div>
                        </td>
                        <td runat="server" id="tooltip_rework_in_progress">
                            <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                <asp:Label ID="REWORK_IN_PROGRESSLabel" runat="server" Text='<%# Eval("REWORK_IN_PROGRESS") %>' />
                            </div>
                        </td>
                        <td id="domestic_td" runat="server" >
                                <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                    <asp:Label ID="DLabel" runat="server" Text='<%# Eval("D") %>' style="font-size: 50pt"/>
                                </div> 
                        </td>
                        <td id="export_td" runat="server" >
                            <div data-toggle="tooltip" data-placement="auto" data-html="true" data-original-title="@TEMPLATE"
                                style="cursor: pointer;">
                                <asp:Label ID="ELabel" runat="server" Text='<%# Eval("E") %>' style="font-size: 50pt" />
                            </div> 
                        </td>
                    </tr>
                </ItemTemplate>
                <LayoutTemplate>
                    <table runat="server">
                        <tr runat="server">
                            <td runat="server">
                                <table id="itemPlaceholderContainer" runat="server" border="1" style="background-color: #FFFFFF;
border-collapse: collapse;border-color: #999999;border-style:none;border-width:1px; ">
                                    <tr runat="server" style="background-color:#DCDCDC;color: #000000;">
                                       <th runat="server" rowspan="3">Line</th>
                                        <th runat="server" colspan="8" class="header">ISSUES</th>
                                    </tr>
                                    <tr runat="server" style="background-color:#DCDCDC;color: #000000;">
                                        <th runat="server" colspan="3">Repair</th>
                                        <th runat="server"  colspan="3">Reworks/Rejects</th>
                                        <th runat="server" colspan="2"><div id="blink">Truck pending</div></th>
                                    </tr>
                                    
                                    <tr runat="server"  style="background-color:Navy;color: #000000;">
                                        <th runat="server" class="header">Total</th>
                                        <th runat="server" class="header">Done</th>
                                        <th runat="server" class="header">Difference</th>
                                        <th runat="server" class="header">Total</th>
                                        <th runat="server" class="header">Done</th>
                                        <th runat="server" class="header">Difference</th>
                                        <th runat="server" class="header">Domestic</th>
                                        <th runat="server" class="header">Export</th>
                                    </tr>

                                    <tr id="itemPlaceholder" runat="server">
                                    </tr>
                                </table>
                            </td>
                        </tr>
                    </table>
                </LayoutTemplate>
               
            </asp:ListView>
            
            <asp:SqlDataSource ID="sdsPlanStatus" runat="server" ConnectionString="<%$ ConnectionStrings:CATVConnectionString %>" SelectCommand="
declare @FactoryStock TABLE (plant varchar(10), line varchar(10), temp_stock numeric, FactoryStock numeric, SystemStock numeric, current_temp_stock numeric, current_FactoryStock numeric)
insert into @FactoryStock
select plant
,line
,temp_stock
,FactoryStock
,SystemStock
,current_temp_stock
,current_FactoryStock
 from openquery (GMES20,'
-- ------------ TOTAL REPORT --------------
SELECT
NVL(NVL(PROD.PLANT_CODE, TEMP.PLANT_CODE), OUT.PLANT_CODE) as PLANT
,NVL(TEMP.QTY,0) as temp_stock
,NVL(NVL(PROD.LINE_CODE, TEMP.LINE_CODE), OUT.LINE_CODE) as LINE
,NVL(PROD.qty, 0) - NVL(TEMP.QTY,0) - NVL(OUT.QTY,0) as FactoryStock
,NVL(PROD.qty, 0) - NVL(OUT.QTY,0) as SystemStock

,NVL(current_TEMP.QTY,0) as current_temp_stock
,NVL(current_PROD.qty, 0) - NVL(current_TEMP.QTY,0) - NVL(current_OUT.QTY,0) as current_FactoryStock


from
(
--PROD
(select 
plant_code
,LINE_CODE
,sum (set_qty) as qty
from TBP_PM_PACK_ACRS
where ACRS_YMD &gt; to_char(sysdate-21,''yyyyMMdd'') 
and del_yn = ''N'' 
and use_yn = ''Y'' 
GROUP BY  LINE_CODE, plant_code
) PROD
full join
(--TEMP
select 
plant_code
,line_code
,sum(set_qty) as qty
from TBP_PM_PACK_ACRS
where ACRS_YMD &gt; to_char(sysdate-21,''yyyyMMdd'') 
AND OUT_STATUS_CODE = 40  AND STRG_ID &lt;&gt; ''FL01''
and del_yn = ''N'' 
and use_yn = ''Y'' 
GROUP BY line_code, plant_code
) TEMP
ON PROD.PLANT_CODE = TEMP.PLANT_CODE and PROD.LINE_CODE = TEMP.LINE_CODE 
FULL JOIN
(
--OUT
select 
plant_code
,LINE_CODE
,sum (set_qty) as qty
from TBP_PM_PACK_ACRS
where ACRS_YMD &gt; to_char(sysdate-21,''yyyyMMdd'') 
AND OUT_STATUS_CODE = 50  
and del_yn = ''N'' 
and use_yn = ''Y'' 
GROUP BY LINE_CODE, plant_code
) OUT
ON (nvl(PROD.PLANT_CODE,TEMP.PLANT_CODE) = OUT.PLANT_CODE and NVL(PROD.LINE_CODE, TEMP.LINE_CODE ) = OUT.LINE_CODE) 
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
full join
--PROD
(select 
plant_code
,LINE_CODE
,sum (set_qty) as qty
from TBP_PM_PACK_ACRS
where ACRS_YMD = to_char(sysdate,''yyyyMMdd'') 
and del_yn = ''N'' 
and use_yn = ''Y'' 
GROUP BY  LINE_CODE, plant_code
) current_PROD
ON (nvl(nvl(PROD.PLANT_CODE,TEMP.PLANT_CODE), OUT.PLANT_CODE) = current_PROD.PLANT_CODE and nvl(NVL(PROD.LINE_CODE, TEMP.LINE_CODE ), OUT.LINE_CODE) = current_PROD.LINE_CODE) 
full join
(--TEMP
select 
plant_code
,line_code
,sum(set_qty) as qty
from TBP_PM_PACK_ACRS
where ACRS_YMD = to_char(sysdate,''yyyyMMdd'') 
AND OUT_STATUS_CODE = 40  AND STRG_ID &lt;&gt; ''FL01''
and del_yn = ''N'' 
and use_yn = ''Y'' 
GROUP BY line_code, plant_code
) current_TEMP
ON current_PROD.PLANT_CODE = current_TEMP.PLANT_CODE and current_PROD.LINE_CODE = current_TEMP.LINE_CODE 
FULL JOIN
(
--OUT
select 
plant_code
,LINE_CODE
,sum (set_qty) as qty
from TBP_PM_PACK_ACRS
where ACRS_YMD = to_char(sysdate,''yyyyMMdd'') 
AND OUT_STATUS_CODE = 50  
and del_yn = ''N'' 
and use_yn = ''Y'' 
GROUP BY LINE_CODE, plant_code
) current_OUT
ON (nvl(current_PROD.PLANT_CODE,current_TEMP.PLANT_CODE) = current_OUT.PLANT_CODE and NVL(current_PROD.LINE_CODE, current_TEMP.LINE_CODE ) = current_OUT.LINE_CODE) 
)
'
)


SELECT 
CATV.[dbo].[TB_COM_MAIN_LINE].LINE AS Line
, [PLAN] as [Plan]
, PROD as Result
, Case QC_OK when '0' then NULL else QC_OK end  as Pass
, CAST(PROD as NUMERIC) - CAST(QC_OK as NUMERIC) as Wait
--, Case CAST(STUFFING as NUMERIC)  + CAST(TEMP as NUMERIC) WHEN 0 then NULL ELSE CAST(STUFFING as NUMERIC)  + CAST(TEMP as NUMERIC) END  as [In loading process]
--, Case BALANCE WHEN '0' THEN NULL ELSE BALANCE END as [Factory stock]
,FactoryStock.temp_stock as [In loading process]
,FactoryStock.factorystock as [Factory stock]
,SystemStock.systemstock as [System stock]
,FactoryStock.current_temp_stock as [Today in loading process]
,FactoryStock.current_FactoryStock as [Today factory stock]


FROM CATV.[dbo].[TB_COM_MAIN_LINE]
left join [UserModules].[user].[CATV_LINE_LIST] on CATV.[dbo].[TB_COM_MAIN_LINE].LINE Collate Cyrillic_General_CI_AS = [UserModules].[user].[CATV_LINE_LIST].LINE
left join @FactoryStock FactoryStock
on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE Collate Cyrillic_General_CI_AI = FactoryStock.line
left join 
(
select 
case when plant = 'P901' then
	(select top 1 line_code from UserModules.[user].CATV_LINE_LIST where UserModules.[user].CATV_LINE_LIST.ACTIVE ='Y' order by LINE)
	else 'M040'
end as line_code
,sum(cast(unrestricted as numeric) + cast(quality as numeric )) as systemstock
from [UserModules].[user].[system_stock]
--left join UserModules.[user].CATV_LINE_LIST
--on UserModules.[user].CATV_LINE_LIST.ACTIVE ='Y'
where material &lt;&gt; '' and material is not null
group by plant
) as SystemStock
on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE = SystemStock.line_code
WHERE CATV.[dbo].[TB_COM_MAIN_LINE].LINE &lt;&gt; 'TOTAL' and CATV.[dbo].[TB_COM_MAIN_LINE].line &lt;&gt; 'W'


UNION ALL 
SELECT 
'Total VD' as [Line]
, SUM(CAST([PLAN]  as NUMERIC)) as [Plan]
, SUM(CAST( PROD  as NUMERIC) ) as Result
, SUM(CAST(QC_OK  as NUMERIC)) as Pass
, SUM(CAST(PROD as NUMERIC) - CAST(QC_OK as NUMERIC)) as Wait
--, SUM( CAST(STUFFING  as NUMERIC) + CAST(TEMP  as NUMERIC)) as [In loading process]
, sum (cast (FactoryStock.temp_stock as numeric)) as [In loading process]
, SUM(CAST(FactoryStock.factorystock as numeric)) as [Factory stock]
, SUM(CAST(FactoryStock.systemstock as numeric)) as [System stock]

, sum(cast(FactoryStock.current_temp_stock as numeric)) as [Today in loading process]
,sum(cast(FactoryStock.current_FactoryStock as numeric)) as [Today factory stock]

FROM CATV.[dbo].[TB_COM_MAIN_LINE]
left join [UserModules].[user].[CATV_LINE_LIST] on CATV.[dbo].[TB_COM_MAIN_LINE].LINE Collate Cyrillic_General_CI_AS = [UserModules].[user].[CATV_LINE_LIST].LINE
left join @FactoryStock FactoryStock
on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE Collate Cyrillic_General_CI_AI = FactoryStock.line
WHERE CATV.[dbo].[TB_COM_MAIN_LINE].LINE &lt;&gt; 'TOTAL' and CATV.[dbo].[TB_COM_MAIN_LINE].line &lt;&gt; 'W'


-- ------------ WASHING MACHINE --------------
UNION ALL

SELECT 
CATV.[dbo].[TB_COM_MAIN_LINE].LINE AS Line
, [PLAN] as [Plan]
, PROD as Result
, Case QC_OK when '0' then NULL else QC_OK end  as Pass
, CAST(PROD as NUMERIC) - CAST(QC_OK as NUMERIC) as Wait
--, Case CAST(STUFFING as NUMERIC)  + CAST(TEMP as NUMERIC) WHEN 0 then NULL ELSE CAST(STUFFING as NUMERIC)  + CAST(TEMP as NUMERIC) END  as [In loading process]
--, Case BALANCE WHEN '0' THEN NULL ELSE BALANCE END as [Factory stock]
,FactoryStock.temp_stock as [In loading process]
,FactoryStock.factorystock as [Factory stock]
,SystemStock.systemstock as [System stock]
,FactoryStock.current_temp_stock as [Today in loading process]
,FactoryStock.current_FactoryStock as [Today factory stock]
FROM CATV.[dbo].[TB_COM_MAIN_LINE]
left join [UserModules].[user].[CATV_LINE_LIST] on CATV.[dbo].[TB_COM_MAIN_LINE].LINE Collate Cyrillic_General_CI_AS = [UserModules].[user].[CATV_LINE_LIST].LINE
left join @FactoryStock FactoryStock
on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE Collate Cyrillic_General_CI_AI = FactoryStock.line
left join 
(
select 
case when plant = 'P901' then
	(select top 1 line_code from UserModules.[user].CATV_LINE_LIST where UserModules.[user].CATV_LINE_LIST.ACTIVE ='Y' order by LINE)
	else 'M040'
end as line_code
,sum(cast(unrestricted as numeric) + cast(quality as numeric )) as systemstock
from [UserModules].[user].[system_stock]
--left join UserModules.[user].CATV_LINE_LIST
--on UserModules.[user].CATV_LINE_LIST.ACTIVE ='Y'
where material &lt;&gt; '' and material is not null
group by plant
) as SystemStock
on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE = SystemStock.line_code
WHERE CATV.[dbo].[TB_COM_MAIN_LINE].line = 'W' and (change_group = '1' or change_group is null)

UNION ALL 
SELECT 
'Total' as [Line]
, SUM(CAST([PLAN]  as NUMERIC)) as [Plan]
, SUM(CAST( PROD  as NUMERIC) ) as Result
, SUM( CAST(QC_OK  as NUMERIC)) as Pass
, SUM(CAST(PROD as NUMERIC) - CAST(QC_OK as NUMERIC)) as Wait
--, SUM( CAST(STUFFING  as NUMERIC) + CAST(TEMP  as NUMERIC)) as [In loading process]
,sum(cast(FactoryStock.temp_stock as numeric)) as [In loading process]
, SUM( CAST(FactoryStock.factorystock as numeric)) as [Factory stock]
,SUM( CAST(SystemStock.systemstock as numeric)) as [System stock]
, sum(cast(FactoryStock.current_temp_stock as numeric)) as [Today in loading process]
,sum(cast(FactoryStock.current_FactoryStock as numeric)) as [Today factory stock]
FROM CATV.[dbo].[TB_COM_MAIN_LINE]
left join [UserModules].[user].[CATV_LINE_LIST] on CATV.[dbo].[TB_COM_MAIN_LINE].LINE Collate Cyrillic_General_CI_AS = [UserModules].[user].[CATV_LINE_LIST].LINE
left join @FactoryStock FactoryStock
on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE Collate Cyrillic_General_CI_AI = FactoryStock.line

left join 
(
select 
case when plant = 'P901' then
	(select top 1 line_code from UserModules.[user].CATV_LINE_LIST where UserModules.[user].CATV_LINE_LIST.ACTIVE ='Y' order by LINE)
	else 'M040'
end as line_code
,sum(cast(unrestricted as numeric) + cast(quality as numeric )) as systemstock
from [UserModules].[user].[system_stock]
--left join UserModules.[user].CATV_LINE_LIST
--on UserModules.[user].CATV_LINE_LIST.ACTIVE ='Y'
where material &lt;&gt; '' and material is not null
group by plant
) as SystemStock
on [UserModules].[user].[CATV_LINE_LIST].LINE_CODE = SystemStock.line_code
WHERE CATV.[dbo].[TB_COM_MAIN_LINE].LINE &lt;&gt; 'TOTAL' and (change_group = '1' or change_group is null)
"></asp:SqlDataSource>
        </div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CATVConnectionString %>" SelectCommand="
SELECT 
T0.LINE
, T1.DONE_QTY
, T1.INPROGRESS_QTY
, TRework_inprogress.REWORK_IN_PROGRESS as REWORK_IN_PROGRESS
, TRework_done.REWORK_DONE as REWORK_DONE
, T2.D
, T2.E

FROM
UserModules.[user].CATV_LINE_LIST T0 
LEFT JOIN
(SELECT LINE
, SUM(case when REPAIR_FLAG = 'Y' then 1 else 0 end ) as DONE_QTY
, SUM(case when REPAIR_FLAG = 'Y' then 0 else 1 end ) as INPROGRESS_QTY
FROM 
GMESData.[dbo].[TB_VWP_QMS_QUALITY] 
WHERE DEFECT_DATE = convert(char(8),cast(getdate() as date), 112)
AND  (SECT = 'P001' OR SECT = 'P008')
--AND DEFECT_DECISION = 'R'
GROUP BY LINE
) T1
ON T0.LINE = T1.LINE Collate Cyrillic_General_CI_AS

LEFT JOIN (
-- -------------------------TRUCKS PENDING
select top 1 D
,E
,line_code
from 
(
select 
distinct remark
,case 
	when [destination] = 'MEDIAMARKT_CPFR' or [destination] = 'SERC' then 'D'
	else 'E'
end as Dest
--,count(distinct remark) as D
from [UserModules].[user].[LOADING_ID_FROM_SAP] 
where LOAD_ID &lt;&gt; '' and destination &lt;&gt; ''  and status = 'X'
) as truck
PIVOT (COUNT (remark) FOR Dest IN ([D],[E])) as pvt
left join UserModules.[user].CATV_LINE_LIST
on UserModules.[user].CATV_LINE_LIST.ACTIVE ='Y'
order by line_code
) T2
ON T0.LINE_CODE = T2.LINE_CODE

-- -------------------------Rework in progress qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_IN_PROGRESS
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] is null or [REWORK_DATE] = '')
group by line
) TRework_inprogress
on T0.LINE = TRework_inprogress.line
-- -------------------------Rework done qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_DONE
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] = cast(getdate() as date))
group by line
) as TRework_done
on T0.LINE = TRework_done.line

WHERE T0.Active = 'Y' and T0.Line &lt;&gt; 'W'
-- -------------------------TOTAL VD LINE 
UNION ALL
select 
'Total VD' as LINE
,sum(DONE_QTY) as done_qty
,sum(INPROGRESS_QTY) as INPROGRESS_QTY
,sum(REWORK_IN_PROGRESS) as REWORK_IN_PROGRESS
,sum(REWORK_DONE) as REWORK_DONE
,sum(D) as D
,sum(E) as E
from
(
SELECT 
T0.LINE
, T1.DONE_QTY
, T1.INPROGRESS_QTY
, TRework_inprogress.REWORK_IN_PROGRESS as REWORK_IN_PROGRESS
, TRework_done.REWORK_DONE as REWORK_DONE
, T2.D
, T2.E

FROM
UserModules.[user].CATV_LINE_LIST T0 
LEFT JOIN
(SELECT LINE
, SUM(case when REPAIR_FLAG = 'Y' then 1 else 0 end ) as DONE_QTY
, SUM(case when REPAIR_FLAG = 'Y' then 0 else 1 end ) as INPROGRESS_QTY
FROM 
GMESData.[dbo].[TB_VWP_QMS_QUALITY] 
WHERE DEFECT_DATE = convert(char(8),cast(getdate() as date), 112)
AND  (SECT = 'P001' OR SECT = 'P008')
--AND DEFECT_DECISION = 'R'
GROUP BY LINE
) T1
ON T0.LINE = T1.LINE Collate Cyrillic_General_CI_AS

LEFT JOIN (
-- -------------------------TRUCKS PENDING
SELECT
*
FROM OPENQUERY (GMES20,'

SELECT 
 DISTINCT 
 PL.DOM_EXP_GUBUN_CODE
, PL.LINE_CODE 
, T2.TRUCK_CODE

 FROM
TBP_PM_PACK_ACRS ACRS 
LEFT JOIN TBM_PM_PO_PLAN PL
ON ACRS.PO_NO = PL.PO_NO
LEFT JOIN TBP_PM_OUT_STATUS T2
ON ACRS.OUT_PALL_NO = T2.PACK_NO
AND ACRS.OUT_STATUS_CODE = T2.OUT_STATUS_CODE
AND ACRS.MODEL_CODE = T2.MODEL_CODE
WHERE 
ACRS.LUP_YMD &gt;= to_char(sysdate-5,''yyyyMMdd'') 
AND ACRS.QC_DECI_PASS_YN IS NULL
AND T2.TRUCK_CODE is not NULL

ORDER BY TRUCK_CODE
')
PIVOT (COUNT (TRUCK_CODE) FOR DOM_EXP_GUBUN_CODE IN ([D],[E])) as pvt
) T2
ON T0.LINE_CODE = T2.LINE_CODE Collate Cyrillic_General_CI_AS

-- -------------------------Rework in progress qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_IN_PROGRESS
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] is null or [REWORK_DATE] = '')
group by line
) TRework_inprogress
on T0.LINE = TRework_inprogress.line
-- -------------------------Rework done qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_DONE
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] = cast(getdate() as date))
group by line
) as TRework_done
on T0.LINE = TRework_done.line
WHERE T0.Active = 'Y' and T0.Line &lt;&gt; 'W') as original
-- -------------------------Washing machine
Union ALL
SELECT 
T0.LINE
, T1.DONE_QTY
, T1.INPROGRESS_QTY
, TRework_inprogress.REWORK_IN_PROGRESS as REWORK_IN_PROGRESS
, TRework_done.REWORK_DONE as REWORK_DONE
, T2.D
, T2.E

FROM
UserModules.[user].CATV_LINE_LIST T0 
LEFT JOIN
(SELECT LINE
, SUM(case when REPAIR_FLAG = 'Y' then 1 else 0 end ) as DONE_QTY
, SUM(case when REPAIR_FLAG = 'Y' then 0 else 1 end ) as INPROGRESS_QTY
FROM 
GMESData.[dbo].[TB_VWP_QMS_QUALITY] 
WHERE DEFECT_DATE = convert(char(8),cast(getdate() as date), 112)
AND  (SECT = 'P001' OR SECT = 'P008')
--AND DEFECT_DECISION = 'R'
GROUP BY LINE
) T1
ON T0.LINE = T1.LINE Collate Cyrillic_General_CI_AS

LEFT JOIN (
-- -------------------------TRUCKS PENDING
SELECT
*
FROM OPENQUERY (GMES20,'
SELECT 
 DISTINCT 
 PL.DOM_EXP_GUBUN_CODE
, PL.LINE_CODE 
, T2.TRUCK_CODE

 FROM
TBP_PM_PACK_ACRS ACRS 
LEFT JOIN TBM_PM_PO_PLAN PL
ON ACRS.PO_NO = PL.PO_NO
LEFT JOIN TBP_PM_OUT_STATUS T2
ON ACRS.OUT_PALL_NO = T2.PACK_NO
AND ACRS.OUT_STATUS_CODE = T2.OUT_STATUS_CODE
AND ACRS.MODEL_CODE = T2.MODEL_CODE
WHERE 
ACRS.LUP_YMD &gt;= to_char(sysdate-5,''yyyyMMdd'') 
AND ACRS.QC_DECI_PASS_YN IS NULL
AND T2.TRUCK_CODE is not NULL

ORDER BY TRUCK_CODE
')
PIVOT (COUNT (TRUCK_CODE) FOR DOM_EXP_GUBUN_CODE IN ([D],[E])) as pvt
) T2
ON T0.LINE_CODE = T2.LINE_CODE Collate Cyrillic_General_CI_AS

-- -------------------------Rework in progress qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_IN_PROGRESS
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] is null or [REWORK_DATE] = '')
group by line
) TRework_inprogress
on T0.LINE = TRework_inprogress.line
-- -------------------------Rework done qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_DONE
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] = cast(getdate() as date))
group by line
) as TRework_done
on T0.LINE = TRework_done.line
WHERE T0.Active = 'Y' and T0.Line = 'W'
-- -------------------------TOTAL LINE 
UNION ALL
select 
'Total' as LINE
,sum(DONE_QTY) as done_qty
,sum(INPROGRESS_QTY) as INPROGRESS_QTY
,sum(REWORK_IN_PROGRESS) as REWORK_IN_PROGRESS
,sum (REWORK_DONE) as REWORK_DONE
,sum(D) as D
,sum(E) as E
from
(
SELECT 
T0.LINE
, T1.DONE_QTY
, T1.INPROGRESS_QTY
, TRework_inprogress.REWORK_IN_PROGRESS as REWORK_IN_PROGRESS
, TRework_done.REWORK_DONE as REWORK_DONE
, T2.D
, T2.E

FROM
UserModules.[user].CATV_LINE_LIST T0 
LEFT JOIN
(SELECT LINE
, SUM(case when REPAIR_FLAG = 'Y' then 1 else 0 end ) as DONE_QTY
, SUM(case when REPAIR_FLAG = 'Y' then 0 else 1 end ) as INPROGRESS_QTY
FROM 
GMESData.[dbo].[TB_VWP_QMS_QUALITY] 
WHERE DEFECT_DATE = convert(char(8),cast(getdate() as date), 112)
AND  (SECT = 'P001' OR SECT = 'P008')
--AND DEFECT_DECISION = 'R'
GROUP BY LINE
) T1
ON T0.LINE = T1.LINE Collate Cyrillic_General_CI_AS

LEFT JOIN (
-- -------------------------TRUCKS PENDING
SELECT
*
FROM OPENQUERY (GMES20,'

SELECT 
 DISTINCT 
 PL.DOM_EXP_GUBUN_CODE
, PL.LINE_CODE 
, T2.TRUCK_CODE

 FROM
TBP_PM_PACK_ACRS ACRS 
LEFT JOIN TBM_PM_PO_PLAN PL
ON ACRS.PO_NO = PL.PO_NO
LEFT JOIN TBP_PM_OUT_STATUS T2
ON ACRS.OUT_PALL_NO = T2.PACK_NO
AND ACRS.OUT_STATUS_CODE = T2.OUT_STATUS_CODE
AND ACRS.MODEL_CODE = T2.MODEL_CODE
WHERE 
ACRS.LUP_YMD &gt;= to_char(sysdate-5,''yyyyMMdd'') 
AND ACRS.QC_DECI_PASS_YN IS NULL
AND T2.TRUCK_CODE is not NULL

ORDER BY TRUCK_CODE
')
PIVOT (COUNT (TRUCK_CODE) FOR DOM_EXP_GUBUN_CODE IN ([D],[E])) as pvt
) T2
ON T0.LINE_CODE = T2.LINE_CODE Collate Cyrillic_General_CI_AS

-- -------------------------Rework in progress qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_IN_PROGRESS
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] is null or [REWORK_DATE] = '')
group by line
) TRework_inprogress
on T0.LINE = TRework_inprogress.line
-- -------------------------Rework done qty
left join(
select line
,sum(cast (qty as numeric)) as REWORK_DONE
from [UserModules].[user].[REWORK]
where ([DETECTION_DATE] is not null and [DETECTION_DATE] &lt;&gt; '')
and ([REWORK_DATE] = cast(getdate() as date))
group by line
) as TRework_done
on T0.LINE = TRework_done.line
WHERE T0.Active = 'Y') as original
"></asp:SqlDataSource>

        <script type="text/javascript">
			$(document).ready(function () {
				$("[data-toggle=popover]").popover();
				$("[data-toggle=tooltip]").tooltip();
				//startTime();
			});
		</script>

        <asp:Timer ID="Timer1" runat="server" Interval="60000">
        </asp:Timer>
         <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>

    </form>
</body>
</html>