Files
KIN/project/punching_fcs_send_ok.asp
2025-09-29 17:55:08 +09:00

867 lines
35 KiB
Plaintext
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<!-- #include virtual = "lib/config.asp" -->
<!------------------><!------------------><!------------------>
<!-- ·Îµù¹Ù -->
<div id="loadingBar" style="display: block;">
<br><br><br><br><br><br><br><br><br><br>
<table border=0 cellspacing="0" align="center" >
<tr>
<td align="center">
<img src="image/loadingBar.gif">
</td>
</tr>
</table>
</div>
<% Response.Flush %>
<!------------------><!------------------><!------------------>
<%
SET rsa = server.CreateObject("ADODB.RECORDSET")
SET rsp = server.CreateObject("ADODB.RECORDSET")
SET rsm = server.CreateObject("ADODB.RECORDSET")
'-- response.write "<br>"
'-- For Each s In request.form
'-- response.write s &":"&request.form(s)&"<br>"Prod) - 1) EXEC (' select A.oid,A.tid,A.pc_prjcode,A.pc_prjcode_sub1,A.pc_prjcode_sub2, case when left(A.ffusercode,2)=''1-'' then A.ffusercode else isnull(''1-''+REPLICATE(''0'',4 - LEN(A.ffusercode))+cast(A.ffusercode as varchar(10)),'''') end as ffusercode , B.* from ( select max(id) as oid, tid,pc_prjcode,pc_prjcode_sub1,pc_prjcode_sub2,ffusercode,ffid from [project2].[dbo].[punching_data] where pc_prjcode=''1904033_C01_A'' and pc_prjcode_sub1=''A'' and ff_ing=''1'' and ffid in ( select ttelnum from [punching].[dbo].[tb_response] where tprocode=''1904033_C01_A'' and char(ttype+65)=''A'' ) group by tid,pc_prjcode,pc_prjcode_sub1,pc_prjcode_sub2,ffusercode,ffid ) A left join ( select * from (SELECT ffid,ffque, case when ffans=''-1'' then '''' else ffans end as ffans FROM [project2].[dbo].[punching_data] where pc_prjcode=''1904033_C01_A'' and pc_prjcode_sub1=''A'' and ff_ing=''1'' ) Q PIVOT ( max(ffans) FOR ffque IN (' + @Prod + ') ) AS PVT ) B on A.ffid=B.ffid order by A.oid asc ')
'-- next
dim x_save : x_save=request.form("x_save") '-- ÀúÀå(save_ok)
dim xprint : xprint="" '-- sql Ãâ·Â
dim xck_num : xck_num=0
dim pn_cd : pn_cd=request.form("pn_cd")
dim pu_prjcode_type : pu_prjcode_type=request.form("pu_prjcode_type")
dim pu_prjcode_area : pu_prjcode_area=request.form("pu_prjcode_area")
dim pu_fcs_type : pu_fcs_type=request.form("pu_fcs_type") '-- fcs ŸÀÔ
dim fcs_type__ss : fcs_type__ss=request.form("fcs_type__ss") '-- fcs ŸÀÔ ÆäÀÌÆÛ ¾ÆÀ̵ð ¹üÀ§(~ºÎÅÍ)
dim fcs_type__ee : fcs_type__ee=request.form("fcs_type__ee") '-- fcs ŸÀÔ ÆäÀÌÆÛ ¾ÆÀ̵ð ¹üÀ§(~±îÁö)
dim ckday : ckday=request.form("ckday")
dim sql, isql, co_sql
dim tsql
'-- fcs À̰ü µ¥ÀÌÅÍ Á¤¸®(Çǹþ)
isql=fun_sql("")
if xprint="1" then response.Write isql&"<br><br>"
rsp.Open isql, adoConn, 1
'-- °¡»óÅ×À̺í¸í
dim co_table : co_table="#temp_"&pn_cd&"_"&pu_prjcode_type
'-- °¡»óÅ×ÀÌºí »ý¼º
co_sql=""
co_sql=co_sql&" CREATE TABLE "&co_table&" ( "&vbCrLf
co_sql=co_sql&" id int IDENTITY (1, 1) primary key NOT Null , "&vbCrLf
For i=0 To rsp.Fields.Count-1
'--response.write rsp.Fields(i).name&"<br>"
select case rsp.Fields(i).name
case "oid" : co_sql=co_sql&" "&rsp.Fields(i).name&" int , "&vbCrLf '-- Á¤·Ä
case "tid" : co_sql=co_sql&" "&rsp.Fields(i).name&" varchar (60) default '', "&vbCrLf '-- ÆÝó¾ÆÀ̵ð
case "pc_prjcode" : co_sql=co_sql&" "&rsp.Fields(i).name&" varchar (30) default '', "&vbCrLf '-- FCS/ÆÝĪ ÇÁ·ÎÁ§Æ®ÄÚµå
case "pc_prjcode_sub1" : co_sql=co_sql&" "&rsp.Fields(i).name&" varchar (4) default '', "&vbCrLf '-- ÆÝĪ ÇÁ·ÎÁ§Æ®ÄÚµå (ŸÀÔ)
case "pc_prjcode_sub2" : co_sql=co_sql&" "&rsp.Fields(i).name&" varchar (4) default '', "&vbCrLf '-- ÆÝĪ ÇÁ·ÎÁ§Æ®ÄÚµå (ŸÀÔ) "pc_prjcode_sub1" °ú µ¿ÀÏÇÔ
case "ffusercode" : co_sql=co_sql&" "&rsp.Fields(i).name&" varchar (20) default '', "&vbCrLf '-- ¸éÁ¢¿ø ¾ÆÀ̵ð
case "ffid" : co_sql=co_sql&" "&rsp.Fields(i).name&" int , "&vbCrLf '-- ¼³¹®Áö ¾ÆÀ̵ð
case else : co_sql=co_sql&" ["&rsp.Fields(i).name&"] varchar(200) default '' , "&vbCrLf '-- °ËÁõ¹®Ç×
end select
Next
rsp.Close
co_sql=co_sql&" r_error int , "&vbCrLf '-- ¿¡·¯Ã¼Å©¿ë
co_sql=co_sql&" m_error int , "&vbCrLf '-- ¿¡·¯Ã¼Å©¿ë
co_sql=co_sql&" s_error int , "&vbCrLf '-- ¿¡·¯Ã¼Å©¿ë
co_sql=co_sql&" q_error int , "&vbCrLf '-- ¿¡·¯Ã¼Å©¿ë
co_sql=co_sql&" insert_date datetime default getdate() ) "&vbCrLf
if xprint="1" then response.write co_sql&"<br><br>"
adoconn.execute co_sql
'-- °¡»óÅ×ÀÌºí¿¡ µ¥ÀÌÅÍ insert
co_sql=fun_sql(co_table)
if xprint="1" then response.write co_sql&"<br><br>"
adoconn.execute co_sql
'-- ¸éÁ¢¿ø Äڵ庯°æ ó¸® update
co_sql=" update "&co_table&" set "&vbCrLf
co_sql=co_sql&" ffusercode=B.ffusercode_change "&vbCrLf
co_sql=co_sql&" from "&co_table&" as A "&vbCrLf
co_sql=co_sql&" inner join "&vbCrLf
co_sql=co_sql&" ( "&vbCrLf
co_sql=co_sql&" select ffusercode, ffusercode_change from [dbo].[T_RES_FIELD_USER_CHANGE] "&vbCrLf
co_sql=co_sql&" where ting='1' "&vbCrLf
co_sql=co_sql&" and len(ffusercode)=6 "&vbCrLf '-- Á¤»óÀûÀÎ ¸éÁ¢¿øÄÚµå ´Â 6ÀÚ¸®ÀÓ
co_sql=co_sql&" and pu_child_sn='"&pn_cd&"' "&vbCrLf
co_sql=co_sql&" and pu_type='"&pu_prjcode_type&"' "&vbCrLf
co_sql=co_sql&" ) B "&vbCrLf
co_sql=co_sql&" on A.ffusercode=B.ffusercode "&vbCrLf
if xprint="1" then response.write co_sql&"<br><br>"
adoconn.execute co_sql
'--[Á¤Çü±â] »ç¿ëÀº ÇÏÁö ¾ÊÁö¸¸ ÀÏ´Ü À¯Áö
'##### ERR ó¸®(s_error) #########################################################
'--s_error : [dbo].[T_AREA_SID] ±âÁØ ¹üÀ§¸¦ ¹þ¾î³­ ¸®½ºÆ®¾ÆÀ̵ðÀÎ °æ¿ì "1" ·Î Ç¥½ÃÇÔ
co_sql=" update "&co_table&" set "&vbCrLf
co_sql=co_sql&" s_error='1' "&vbCrLf
co_sql=co_sql&" where (ffid<'"&fcs_type__ss&"' or ffid>'"&fcs_type__ee&"') "
if xprint="1" then response.write co_sql&"<br><br>"
adoconn.execute co_sql
'--[Á¤Çü±â] ÇöÀç ¼³¹®Áö ID´Â ¼³Á¤ÁßÀ̹ǷΠÀ¯Áö
'###############################################################################
'-- ÆÝε¥ÀÌÅÍÁß Áߺ¹ ÆäÀÌÆÛ ¾ÆÀ̵ð - s_error ó¸®
co_sql=" update "&co_table&" set "&vbCrLf
co_sql=co_sql&" s_error='1' "&vbCrLf
co_sql=co_sql&" where id not in ( "&vbCrLf
co_sql=co_sql&" select max(id) from "&co_table&" "&vbCrLf
co_sql=co_sql&" group by ffid "&vbCrLf
co_sql=co_sql&" ) "&vbCrLf
if xprint="1" then response.write co_sql&"<br><br>"
adoconn.execute co_sql
'--[Á¤Çü±â] ÆÝε¥ÀÌÅÍ´Â ÇÁ·Î±×·¥ ÀÚüÀûÀ¸·Î Áߺ¹À» Çã¿ëÇÏÁö ¾ÊÀ½
'################################################################################
'-- r_error : ÀÀ´äÀÚ Áߺ¹¿¡·¯(¸ðµç FFÇÁ·ÎÁ§Æ® °Ë»ö±â°£(ckday) ³» Áߺ¹½Ã)
'-- ÀÀ´äÀÚÀ̸§, ¿¬¶ôó1,¿¬¶ôó2 °¡Á®¿À±â
dim co_name, co_tel1, co_tel2
co_name="" : co_tel1="" : co_tel2=""
co_sql=" select * from [project2].[dbo].[T_RES_SQ_QUE_LAB] with(nolock) "
co_sql=co_sql&" where ting='1' " '-- »ç¿ë(1)
co_sql=co_sql&" and pu_child_sn='"&pn_cd&"' "
co_sql=co_sql&" and pu_type='"&pu_prjcode_type&"' "
'-- fcsÁö¿ª co_sql=co_sql&" and fcs_area='"&pu_prjcode_area&"' "
co_sql=co_sql&" and pu_que_type in ('2','3') " '-- ÀÀ´äÀÚ(3), ¿¬¶ôó(2)
co_sql=co_sql&" order by tuser asc, tphone_1 asc, tphone_2 asc "
if xprint="1" then response.write co_sql&"<br><br>"
rsp.Open co_sql, adoConn, 1
while not rsp.Eof
'-- ÀÀ´äÀÚ
if rsp("tuser_name")<>"" then co_name=rsp("pu_que")
'-- ÈÞ´ëÆù
if rsp("tphone_1")<>"" then co_tel1=rsp("pu_que")
'-- ÀϹݹøÈ£
if rsp("tphone_2")<>"" then co_tel2=rsp("pu_que")
rsp.movenext
Wend
rsp.Close
dim xxsql : xxsql="" '-- µ¥ÀÌÅÍ À̰üÀü FCSµ¥ÀÌÅÍ¿¡¼­ Áߺ¹ üũ¿ë
dim in_cksql : in_cksql="" '-- ÆÝε¥ÀÌÅÍ ÀÀ´äÀÚ Áߺ¹ üũ¿ë
if co_tel2<>"" then
xxsql=" on 1=1 "
xxsql=xxsql&" and replace(replace(isnull(A.["&co_name&"],''),'-',''),' ','')=B.r_name "
xxsql=xxsql&" and replace(replace(isnull(A.["&co_tel2&"],''),'-',''),' ','')=B.r_tel "
'-----------------
in_cksql=" replace(replace(isnull(["&co_name&"],''),'-',''),' ',''), replace(replace(isnull(["&co_tel2&"],''),'-',''),' ','') "
elseif co_tel1<>"" then
xxsql=" on 1=1 "
xxsql=xxsql&" and replace(replace(isnull(A.["&co_name&"],''),'-',''),' ','')=B.r_name "
xxsql=xxsql&" and replace(replace(isnull(A.["&co_tel1&"],''),'-',''),' ','')=B.r_phone "
'-----------------
in_cksql=" replace(replace(isnull(["&co_name&"],''),'-',''),' ',''), replace(replace(isnull(["&co_tel1&"],''),'-',''),' ','') "
else
xxsql=" on 1=1 "
xxsql=xxsql&" and replace(replace(isnull(A.["&co_name&"],''),'-',''),' ','')=B.r_name "
xxsql=xxsql&" where (replace(replace(isnull(A.["&co_tel2&"],''),'-',''),' ','')=B.r_tel or replace(replace(isnull(A.["&co_tel1&"],''),'-',''),' ','')=B.r_phone) "
'-----------------
in_cksql=" replace(replace(isnull(["&co_name&"],''),'-',''),' ',''), replace(replace(isnull(["&co_tel2&"],''),'-',''),' ',''), replace(replace(isnull(["&co_tel1&"],''),'-',''),' ','') "
end if
'##### ERR ó¸®(r_error) #########################################################
'-- r_error : ÀÀ´äÀÚ Áߺ¹¿¡·¯ ó¸®(º»À롂 Áߺ¹Á¦¿Ü: Á¤º¸¾÷µ¥ÀÌÆ® ÇÒ¼ö ÀÖµµ·Ï!!)
co_sql=" DECLARE @dblck_id int "&vbCrLf
co_sql=co_sql&" DECLARE cur_dblck CURSOR FOR "&vbCrLf
co_sql=co_sql&" select A.ffid from "&vbCrLf
co_sql=co_sql&" ( select * from "&co_table&" with(nolock) ) A "&vbCrLf
co_sql=co_sql&" inner join "&vbCrLf
co_sql=co_sql&" ( "&vbCrLf
co_sql=co_sql&" select h.r_name,h.r_tel,h.r_phone "&vbCrLf
co_sql=co_sql&" from ( "&vbCrLf
co_sql=co_sql&" select "&vbCrLf
co_sql=co_sql&" p_sn, s_id, "&vbCrLf
co_sql=co_sql&" replace(replace(isnull(r_name,''),'-',''),' ','') as r_name, "&vbCrLf
co_sql=co_sql&" replace(replace(isnull(r_tel,''),'-',''),' ','') as r_tel, "&vbCrLf
co_sql=co_sql&" replace(replace(isnull(r_phone,''),'-',''),' ','') as r_phone "&vbCrLf
co_sql=co_sql&" from [dbo].[res] with(nolock) "&vbCrLf
co_sql=co_sql&" where DelGubun='N' "&vbCrLf
co_sql=co_sql&" and replace(r_date,'-','')>=replace('"&ckday&"','-','') "&vbCrLf
co_sql=co_sql&" ) h "&vbCrLf
co_sql=co_sql&" where h.p_sn+':'+cast(h.s_id as varchar(10)) not in ( "&vbCrLf
co_sql=co_sql&" select pc_prjcode+':'+cast(ffid as varchar(10)) from "&co_table&" with(nolock) "&vbCrLf
co_sql=co_sql&" ) "&vbCrLf
co_sql=co_sql&" group by h.r_name,h.r_tel,h.r_phone "&vbCrLf
co_sql=co_sql&" ) B "&vbCrLf
co_sql=co_sql&xxsql
co_sql=co_sql&" OPEN cur_dblck "&vbCrLf
co_sql=co_sql&" FETCH NEXT FROM cur_dblck INTO @dblck_id "&vbCrLf
co_sql=co_sql&" WHILE @@FETCH_STATUS = 0 "&vbCrLf
co_sql=co_sql&" BEGIN "&vbCrLf
co_sql=co_sql&" update "&co_table&" set "&vbCrLf
co_sql=co_sql&" r_error='1' "&vbCrLf
co_sql=co_sql&" where ffid=@dblck_id "&vbCrLf
co_sql=co_sql&" FETCH NEXT FROM cur_dblck INTO @dblck_id "&vbCrLf
co_sql=co_sql&" END "&vbCrLf
co_sql=co_sql&" CLOSE cur_dblck "&vbCrLf
co_sql=co_sql&" DEALLOCATE cur_dblck "&vbCrLf
if xprint="1" then response.write co_sql&"<br><br>"
adoconn.execute co_sql
'##### ERR ó¸®(m_error) #########################################################
'-- m_error : ¸éÁ¢¿ø Äڵ尡 ¾ø°Å³ª, "Ȱµ¿"ÀÌ ¾Æ´Ñ ¸éÁ¢¿øÀÏ °æ¿ì - ÀÎÆ®¶ó³Ý¿¡ ¾ø´ÂÄÚµå
co_sql=" select A.ffid from "&vbCrLf
co_sql=co_sql&" ( "&vbCrLf
co_sql=co_sql&" select "&vbCrLf
co_sql=co_sql&" case when left(ffusercode,2)='1-' then ffusercode else '1-'+ffusercode end as ff "&vbCrLf
co_sql=co_sql&" ,ffusercode, ffid from "&co_table&" "&vbCrLf
co_sql=co_sql&" ) A "&vbCrLf
co_sql=co_sql&" left join "&vbCrLf
co_sql=co_sql&" ( "&vbCrLf
co_sql=co_sql&" select new_id from [project2].[dbo].[man] with(nolock) "&vbCrLf
co_sql=co_sql&" where m_activity='Ȱµ¿' "&vbCrLf
co_sql=co_sql&" and left(new_id,2)='1-' "&vbCrLf
co_sql=co_sql&" ) B "&vbCrLf
co_sql=co_sql&" on A.ff=B.new_id "&vbCrLf
co_sql=co_sql&" where B.new_id is null "&vbCrLf '-- man Å×ÀÌºí¿¡ ¸éÁ¢¿ø Á¤º¸°¡ ¾ø´Â°æ¿ì Err ó¸®
po_sql=" update "&co_table&" set "&vbCrLf
po_sql=po_sql&" m_error='1' "&vbCrLf
po_sql=po_sql&" from "&co_table&" as AA "&vbCrLf
po_sql=po_sql&" inner join "&vbCrLf
po_sql=po_sql&" ( "&vbCrLf
po_sql=po_sql&co_sql
po_sql=po_sql&" ) BB "&vbCrLf
po_sql=po_sql&" on AA.ffid=BB.ffid "&vbCrLf
if xprint="1" then response.write po_sql&"<br><br>"
adoconn.execute po_sql
'##### ERR ó¸®(q_error) #########################################################
'-- q_error : FCS¿¡¼­ 1¹øÀÌ¶óµµ °ËÁõÀ» ÁøÇàÇѰæ¿ì (¼³¹®ÁöID ±âÁØ)
'-- [Á¤Çü±â] °ËÁõµ¥ÀÌÅÍ¿¡ Á¸ÀçÇÏ´Â °æ¿ì °¡»óÅ×ÀÌºí »èÁ¦
po_sql=po_sql&" DELETE "&vbCrLf
po_sql=po_sql&" FROM "&co_table&" "&vbCrLf
po_sql=po_sql&" WHERE ffid in "&vbCrLf
po_sql=po_sql&" ( "&vbCrLf
po_sql=po_sql&" select s_id from [project2].[dbo].[T_INSPECTION_RES] with(nolock) "&vbCrLf
po_sql=po_sql&" where p_sn='"&pn_cd&"' "&vbCrLf
po_sql=po_sql&" ) "&vbCrLf
if xprint="1" then response.write po_sql&"<br><br>"
adoconn.execute po_sql
'-- q_error : ÇöÀç À̰üµ¥ÀÌÅÍ·Î Á¸ÀçÇÏ´Â °æ¿ì (¼³¹®ÁöID ±âÁØ)
'-- [Á¤Çü±â] À̰üµ¥ÀÌÅÍ¿¡ Á¸ÀçÇÏ´Â °æ¿ì °¡»óÅ×ÀÌºí »èÁ¦
po_sql=po_sql&" DELETE "&vbCrLf
po_sql=po_sql&" FROM "&co_table&" "&vbCrLf
po_sql=po_sql&" WHERE ffid in "&vbCrLf
po_sql=po_sql&" ( "&vbCrLf
po_sql=po_sql&" select s_id from [project2].[dbo].[RES] with(nolock) "&vbCrLf
po_sql=po_sql&" where p_sn='"&pn_cd&"' "&vbCrLf
po_sql=po_sql&" and DelGubun = 'N' "&vbCrLf
po_sql=po_sql&" ) "&vbCrLf
if xprint="1" then response.write po_sql&"<br><br>"
adoconn.execute po_sql
'###### ÆÝε¥ÀÌÅͳ» ÀÀ´äÁߺ¹ÀÚ (r_error) ó¸® ######################################
po_sql=" update "&co_table&" set "&vbCrLf
po_sql=po_sql&" r_error='1' "&vbCrLf
po_sql=po_sql&" from "&co_table&" as AA "&vbCrLf
po_sql=po_sql&" inner join ( "&vbCrLf
po_sql=po_sql&" select A.id from "&vbCrLf
po_sql=po_sql&" (select * from "&co_table&" ) A "&vbCrLf
po_sql=po_sql&" left join "&vbCrLf
po_sql=po_sql&" ( "&vbCrLf
po_sql=po_sql&" select max(id) as id2 from "&co_table&" "&vbCrLf
po_sql=po_sql&" where (isnull(r_error,'0')+isnull(m_error,'0')+isnull(s_error,'0')+isnull(q_error,'0'))=0 "&vbCrLf
po_sql=po_sql&" group by "&in_cksql&" "&vbCrLf '-- ÀÀ´äÀÚ Áߺ¹¹®Ç׿¡ ´ëÇÑ group by
po_sql=po_sql&" ) B "&vbCrLf
po_sql=po_sql&" on A.id=B.id2 "&vbCrLf
po_sql=po_sql&" where (isnull(A.r_error,'0')+isnull(A.m_error,'0')+isnull(A.s_error,'0')+isnull(A.q_error,'0'))=0 "&vbCrLf
po_sql=po_sql&" and B.id2 is null "&vbCrLf
po_sql=po_sql&" ) BB "&vbCrLf
po_sql=po_sql&" on AA.id=BB.id "&vbCrLf
if xprint="1" then response.write po_sql&"<br><br>"
adoconn.execute po_sql
%>
<HTML>
<HEAD>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="Web Survey System">
<meta name="author" content="KRC">
<TITLE>KRC</TITLE>
<style>
td,body,input {font-face:±¼¸²Ã¼;font-size:13px;line-height:150%;}
</style>
</HEAD>
<BODY id='body' onclick="javascript:try{if(winObj.window.focus()){}}catch(exception){}" ondblclick="javascript:try{if(winObj.window.focus()){}}catch(exception){}" <%if x_save="" then %> onload="javascript:js_init();" <%end if%> >
<form name='xform' method='post' >
<div id="divBody" style="display: none">
<p id="f_main"></p>
<%
'-- µ¥ÀÌÅÍ Á¤¸®ÈÄ - Ãâ·Â(¶óº§)
tsql=" select "&vbCrLf
tsql=tsql&" top 1 "
tsql=tsql&" isnull(B.new_id,'Á¤º¸¾øÀ½') as ¸éÁ¢¿øÄÚµå, "&vbCrLf
tsql=tsql&" isnull(B.m_name,'Á¤º¸¾øÀ½') as ¸éÁ¢¿øÀ̸§, "&vbCrLf
tsql=tsql&" isnull(B.m_jumin,'Á¤º¸¾øÀ½') as »ý³â¿ùÀÏ, "&vbCrLf
tsql=tsql&" A.* "&vbCrLf
tsql=tsql&" from "&vbCrLf
tsql=tsql&" ( "&vbCrLf
tsql=tsql&" select "&vbCrLf
tsql=tsql&" (isnull(r_error,'0')+isnull(m_error,'0')+isnull(s_error,'0')+isnull(q_error,'0')) as t, "&vbCrLf
tsql=tsql&" case when left(ffusercode,2)='1-' then ffusercode else '1-'+ffusercode end as ff, "&vbCrLf
tsql=tsql&" * "&vbCrLf
tsql=tsql&" from "&co_table&" "&vbCrLf
tsql=tsql&" ) A "&vbCrLf
tsql=tsql&" left join "&vbCrLf
tsql=tsql&" ( "&vbCrLf
tsql=tsql&" select new_id, m_name, left(m_jumin,6) as m_jumin from [project2].[dbo].[man] with(nolock) "&vbCrLf
tsql=tsql&" where m_activity='Ȱµ¿' "&vbCrLf
tsql=tsql&" and left(new_id,2)='1-' "&vbCrLf
tsql=tsql&" ) B "&vbCrLf
tsql=tsql&" on A.ff=B.new_id "&vbCrLf
tsql=tsql&" order by A.ffid asc "&vbCrLf
'-- tsql=tsql&" order by B.new_id asc, A.t desc "&vbCrLf
if xprint="1" then response.write tsql&"<br><br>"
rsp.Open tsql, adoConn, 1
'-----------------------------------------------------------------------------------------------
dim xfield : xfield=""
dim xTable : xTable=""
dim Twidth : Twidth=0
select case x_save
case "save_ok" '-- ÆÝĪ µ¥ÀÌÅÍ À̰ü
rsp.close
' '############################################## call fn_fcs_input_data() '-- fcs·Î µ¥ÀÌÅÍ À̰ü
'############################################## '############################################## '##############################################
'############################################## '############################################## '##############################################
'############################################## '############################################## '##############################################
'############################################## '############################################## '##############################################
%>
<!-- #include file = "fcs_input_data.asp" -->
<%
'############################################## '############################################## '##############################################
'############################################## '############################################## '##############################################
'############################################## '############################################## '##############################################
'############################################## '############################################## '##############################################
'##############################################
' '-- °¡»èÅ×ÀÌºí »èÁ¦
' po_sql=" drop table "&co_table&" "
' if xprint="1" then response.write po_sql&"<br><br>"
' adoconn.execute po_sql
'##############################################
%>
</div>
</form>
</BODY>
</HTML>
<script language="javascript">
alert("µ¥ÀÌÅÍ Ã³¸® µÇ¾ú½À´Ï´Ù ");
// ¹ÌÀû¿ë opener.parent.parent_function();
window.close();
</script>
<%response.end %>
<%
case else '-- µ¥ÀÌÅÍ ¸®½ºÆ® Ãâ·Â
xTable=xTable&"<span id=""pu_table"" >"
xTable=xTable&"<table border=""1"" bordercolordark='#FFFFFF' cellspacing='0' cellpadding='5' align=center style=""width:[Twidth]px;word-break:break-all;"" >"
xTable=xTable&"<tr bgcolor='#E2E2E2' >"
'-----------------------------------------------------------------------------------------------
'-- Default ----------------------
if not rsp.eof then
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:100px;word-break:break-all;"" align=""center"" >¼³¹®Áö¾ÆÀ̵ð<br>(ÀÔ·Â)</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&"[ffid]"
end if
For i=0 To rsp.Fields.Count-1
Select Case rsp.Fields(i).name
Case "t", "ff", "id", "oid","pc_prjcode_sub2","ffid" '-- Á¦¿Ü
case "pc_prjcode"
Twidth=cdbl(Twidth)+120
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:120px;word-break:break-all;"" align=""center"" >ÆÝĪ/FCS ÇÁ·ÎÁ§Æ®ÄÚµå</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&"["&rsp.Fields(i).name&"]"
case "pc_prjcode_sub1"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:100px;word-break:break-all;"" align=""center"" >ÆÝĪŸÀÔ</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&"["&rsp.Fields(i).name&"]"
case "ffusercode"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:100px;word-break:break-all;"" align=""center"" >¸éÁ¢¿øÄÚµå</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&"["&rsp.Fields(i).name&"]"
case "ffid"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:100px;word-break:break-all;"" align=""center"" >¼³¹®ÁöID</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&"["&rsp.Fields(i).name&"]"
case "r_error"
Twidth=cdbl(Twidth)+120
xTable=xTable&"<td bgcolor=""#dbe9f2"" style=""width:120px;word-break:break-all;"" align=""center"" >ÀÀ´äÀÚÁߺ¹ ¿¡·¯<br>(¾÷·ÎµåºÒ°¡)</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" case when A."&rsp.Fields(i).name&"='1' then '¿¡·¯' else '' end as xr_error "
case "m_error"
Twidth=cdbl(Twidth)+120
xTable=xTable&"<td bgcolor=""#dbe9f2"" style=""width:120px;word-break:break-all;"" align=""center"" >¸éÁ¢¿øÄÚµå ¿¡·¯<br>(¾÷·ÎµåºÒ°¡)</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" case when A."&rsp.Fields(i).name&"='1' then '¿¡·¯' else '' end as xm_error "
case "s_error"
Twidth=cdbl(Twidth)+130
xTable=xTable&"<td bgcolor=""#dbe9f2"" style=""width:130px;word-break:break-all;"" align=""center"" >¸®½ºÆ®ID ¿¡·¯<br>(¾÷·ÎµåºÒ°¡)</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" case when A."&rsp.Fields(i).name&"='1' then '¿¡·¯' else '' end as xs_error "
case "q_error"
Twidth=cdbl(Twidth)+90
xTable=xTable&"<td bgcolor=""#dbe9f2"" style=""width:90px;word-break:break-all;"" align=""center"" >À̰ü ¹× °ËÁõÁøÇà<br>(¾÷·ÎµåºÒ°¡)</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" case when A."&rsp.Fields(i).name&"='1' then '¿¡·¯' else '' end as xq_error "
case "tid"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:100px;word-break:break-all;"" align=""center"" >ÆÝóÁ¤º¸</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&"["&rsp.Fields(i).name&"]"
case "insert_date"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:100px;word-break:break-all;"" align=""center"" >FCS À̰üó¸®ÀÏ</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" case when A.t=0 then convert(nvarchar(10),"&rsp.Fields(i).name&",120) else '<font color=red>FCS À̰üºÒ°¡</font>' end as tdate "
case "¸éÁ¢¿øÄÚµå"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#66CCFF"" style=""width:100px;word-break:break-all;"" align=""center"" >¸éÁ¢¿øÄÚµå</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" isnull(B.new_id,'Á¤º¸¾øÀ½') as ¸éÁ¢¿øÄÚµå "
case "¸éÁ¢¿øÀ̸§"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#66CCFF"" style=""width:100px;word-break:break-all;"" align=""center"" >¸éÁ¢¿øÀ̸§</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" isnull(B.m_name,'Á¤º¸¾øÀ½') as ¸éÁ¢¿øÀ̸§ "
case "»ý³â¿ùÀÏ"
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#66CCFF"" style=""width:100px;word-break:break-all;"" align=""center"" >»ý³â¿ùÀÏ</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&" isnull(B.m_jumin,'Á¤º¸¾øÀ½') as »ý³â¿ùÀÏ "
Case Else '-- Ãâ·Â
Twidth=cdbl(Twidth)+100
xTable=xTable&"<td bgcolor=""#E7CB5F"" style=""width:100px;word-break:break-all;"" align=""center"" >"&rsp.Fields(i).name&"</td>"
if xfield<>"" then xfield=xfield&","
xfield=xfield&"["&rsp.Fields(i).name&"]"
End Select
next
rsp.Close
xTable=xTable&"</tr>"
response.Write replace(xTable,"[Twidth]",Twidth)
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'-- µ¥ÀÌÅÍ Á¤¸®ÈÄ - Ãâ·Â(µ¥ÀÌÅÍ)
tsql=" select A.t, "&xfield&" "&vbCrLf
tsql=tsql&" from "&vbCrLf
tsql=tsql&" ( "&vbCrLf
tsql=tsql&" select "&vbCrLf
tsql=tsql&" (isnull(r_error,'0')+isnull(m_error,'0')+isnull(s_error,'0')+isnull(q_error,'0')) as t, "&vbCrLf
tsql=tsql&" case when left(ffusercode,2)='1-' then ffusercode else '1-'+ffusercode end as ff, "&vbCrLf
tsql=tsql&" * "&vbCrLf
tsql=tsql&" from "&co_table&" "&vbCrLf
tsql=tsql&" ) A "&vbCrLf
tsql=tsql&" left join "&vbCrLf
tsql=tsql&" ( "&vbCrLf
tsql=tsql&" select new_id, m_name, left(m_jumin,6) as m_jumin from [project2].[dbo].[man] with(nolock) "&vbCrLf
tsql=tsql&" where m_activity='Ȱµ¿' "&vbCrLf
tsql=tsql&" and left(new_id,2)='1-' "&vbCrLf
tsql=tsql&" ) B "&vbCrLf
tsql=tsql&" on A.ff=B.new_id "&vbCrLf
tsql=tsql&" order by A.ffid asc "&vbCrLf
'-- tsql=tsql&" order by B.new_id asc, A.t desc "&vbCrLf
if xprint="1" then response.write tsql&"<br><br>"
rsp.Open tsql, adoConn, 1
dim k : k=1
xck_num=0
'###########################################################################
if rsp.Eof then '-- À̰ü µ¥ÀÌÅÍ ¾øÀ½
response.write "<font color=red>¡Ø À̰üÇÏ½Ç µ¥ÀÌÅͰ¡ ¾ø½À´Ï´Ù </font>"
rsp.close
'##############################################
'-- °¡»èÅ×ÀÌºí »èÁ¦
po_sql=" drop table "&co_table&" "
if xprint="1" then response.write po_sql&"<br><br>"
adoconn.execute po_sql
'##############################################
%>
</body>
<script language="javascript">
function js_init() {
document.getElementById("loadingBar").style.display = "none";
document.getElementById("divBody").style.display = "";
}
</script>
<%
response.end
end if
'###########################################################################
Response.Buffer = True '----- ¹öÆÛ»ç¿ë
dim ck_data : ck_data=0
while not rsp.Eof
if cdbl(rsp("t"))>0 then xck_num=1
'-- µ¥ÀÌÅÍÃâ·Â ¡é
response.write "<tr>"
For i=1 To rsp.Fields.Count-1
if isnull(rsp(i)) or trim(rsp(i))="" then
response.write "<td style=""mso-number-format:'\@';"" align=""right"" >&nbsp;</td>"
else
response.write "<td style=""mso-number-format:'\@';"" align=""right"" >"&rsp(i)&"</td>"
'response.write "<td style=""mso-number-format:'\@';"" align=""right"" >"&rsp(i)&":"& trim(i)&":"&trim(rsp.Fields.Count-1)&":"&instr(rsp(i),"FCS À̰üºÒ°¡")&":"&ck_data&"</td>"
End If
'------------------------------------------------
if trim(i)=trim(rsp.Fields.Count-1) then
if instr(rsp(i),"FCS À̰üºÒ°¡")=0 then ck_data=1 '-- À̰üÇÒ µ¥ÀÌÅÍ ÀÖÀ½
end if
'------------------------------------------------
Next
response.write "</tr>"
'-- µ¥ÀÌÅÍÃâ·Â ¡è
if k mod 500=0 then Response.flush ' 500¹øÂ°¸´Ù ¹öÆÛ³»¿ë Ãâ·Â...
k=k+1
rsp.movenext
wend
rsp.close
Response.flush ' ÃÖÁ¾ ¹öÆÛ¿¡¼­ Ãâ·Â.
Response.Clear '¹öÆÛ ³»¿ë ÃʱâÈ­
response.write "</tr>"
response.write "</table>"
response.write "</span>"
end select
%>
</div>
<% For Each s In request.form %><input type="hidden" name="<%=s%>" id="<%=s%>" value="<%=request.form(s)%>" /><% next %>
<input type="hidden" name="x_temp" id="x_temp" value="" />
<input type="hidden" name="x_save" id="x_save" value="" />
</form>
</BODY>
</HTML>
<script language="javascript">
// document.getElementById("f_main").innerHTML="<table border=1 width=100% ><tr><td>aaa <input type='button' value='sss' name='tbtn' onclick=""javascript:alert('aaa');"" style=\"background-color:#999999;color:#ffffff;width:130px;height:20px;cursor:hand;\" ></td></tr></table><br>";
function js_init() {
document.getElementById("loadingBar").style.display = "none";
document.getElementById("divBody").style.display = "";
var ck_data = "<%=ck_data%>";
if (ck_data == "0") {
alert("FCS·Î À̰üÇϽǼö ÀÖ´Â µ¥ÀÌÅͰ¡ ¾ø½À´Ï´Ù ");
return;
}
var b1 = "<%=xck_num%>";
if (b1 == "0") {
alert("À̰üÇÏ½Ç ¸®½ºÆ®¸¦ È®ÀÎÇÏ¿© ÁÖ¼¼¿ä ");
document.getElementById("f_main").innerHTML = "<table border=0 width=100% ><tr><td><font color=blue>¡Ø ÆÝĪÇÁ·Î±×·¥¿¡¼­ °ËÁõÀ̰ü ó¸®µÈ µ¥ÀÌÅ͸¸ FCS·Î À̰üµÇ¸ç, ¿¡·¯°¡ üũµÈ µ¥ÀÌÅÍ´Â À̰üó¸® µÇÁö ¾Ê½À´Ï´Ù(¿¡·¯µ¥ÀÌÅÍ´Â ¾Æ·¡ ¹öư Ŭ¸¯½Ã ÀÚµ¿À¸·Î ÆÝĪÇÁ·Î±×·¥¿¡ [°ËÁõ¿¡·¯] 󸮰¡ µË´Ï´Ù<br><br></font><input type=\'button\' value=\'¾Æ·¡ ³»¿ëÀ» È®ÀÎÇÏ½Ã°í Æ¯ÀÌ»çÇ×ÀÌ ¾øÀ¸½Ã¸é, ÇØ´ç¹öưÀ» Ŭ¸¯ÇÏ¿© ÁÖ¼¼¿ä. FCS·Î ¾Æ·¡ µ¥ÀÌÅ͸¦ À̰ü ÇÕ´Ï´Ù (GO!!) ¢¹¢º \' name=\'tbtn\' onclick=\"javascript:js_Usubmit(xform);\" style=\"background-color:#999999;color:#ffffff;width:750px;height:35px;cursor:hand;\" >&nbsp;&nbsp;&nbsp;<input type=\'button\' value=\'EXCEL ´Ù¿î·Îµå ¢¹¢º \' name=\'tbtn\' onclick=\"javascript:js_Excel(xform);\" style=\"background-color:#999999;color:#ffffff;height:35px;cursor:hand;\" ></td></tr></table><br>";
}
if (b1 == "1") {
alert("¸®½ºÆ®³»¿¡ ¿¡·¯ ³»¿ëÀÌ ÀÖ½À´Ï´Ù. È®ÀÎÇϽñ⠹ٶø´Ï´Ù ");
document.getElementById("f_main").innerHTML = "<table border=0 width=100% ><tr><td><font color=blue>¡Ø ÆÝĪÇÁ·Î±×·¥¿¡¼­ °ËÁõÀ̰ü ó¸®µÈ µ¥ÀÌÅ͸¸ FCS·Î À̰üµÇ¸ç, ¿¡·¯°¡ üũµÈ µ¥ÀÌÅÍ´Â À̰üó¸® µÇÁö ¾Ê½À´Ï´Ù(¿¡·¯µ¥ÀÌÅÍ´Â ¾Æ·¡ ¹öư Ŭ¸¯½Ã ÀÚµ¿À¸·Î ÆÝĪÇÁ·Î±×·¥¿¡ [°ËÁõ¿¡·¯] 󸮰¡ µË´Ï´Ù<br><br></font><input type=\'button\' value=\'¿¡·¯³»¿ëÀ» Á¦¿ÜÇϰí FCS¿¡ ¾÷·ÎµåÇϸç, ¿¡·¯ ¸®½ºÆ®´Â ÆÝε¥ÀÌÅÍ¿¡ [°ËÁõºÒ·®]À¸·Î ó¸® ÇÕ´Ï´Ù(GO!!) ¢¹¢º \' name=\'tbtn\' onclick=\"javascript:js_Usubmit(xform);\" style=\"background-color:#999999;color:#ffffff;width:750px;height:35px;cursor:hand;\" >&nbsp;&nbsp;&nbsp;<input type=\'button\' value=\'EXCEL ´Ù¿î·Îµå ¢¹¢º \' name=\'tbtn\' onclick=\"javascript:js_Excel(xform);\" style=\"background-color:#999999;color:#ffffff;height:35px;cursor:hand;\" ></td></tr></table><br>";
}
/*
if( (parseInt(document.getElementById('body').offsetHeight)- parseInt(window.document.body.clientHeight)) > 20 ) {
window.document.body.scroll = "auto";
window.resizeBy(18,0);
*/
}
function js_Usubmit(x) {
if (!confirm("µ¥ÀÌÅ͸¦ FCS·Î À̰üÇϽðڽÀ´Ï±î? À̰üÈÄ¿¡´Â Ãë¼ÒµÇÁö ¾Ê½À´Ï´Ù ")) { return; }
alert("½Ã°£ÀÌ ´Ù¼Ò °É¸±¼ö ÀÖ»ç¿À´Ï, 󸮽ÿ¡´Â Àá½Ã ±â´Ù·Á Áֽñ⠹ٶø´Ï´Ù ");
x.x_save.value = "save_ok"
x.target = "";
x.action = "punching_fcs_send_ok.asp";
x.submit();
}
function js_Excel(x) {
x.target = "";
x.x_temp.value = document.getElementById("pu_table").innerHTML;
alert("´Ù¿î·Îµå ÈÄ ExcelÀ» ´Ù¸¥ À̸§À¸·Î ÀúÀå(xlsx) ÈÄ ÀÌ¿ëÇϼ¼¿ä.");
x.action = "punching_fcs_exceldown.asp";
x.submit();
}
</script>
<%
'##############################################
'-- °¡»óÅ×ÀÌºí »èÁ¦
po_sql=" drop table "&co_table&" "
if xprint="1" then response.write po_sql&"<br><br>"
adoconn.execute po_sql
'##############################################
%>
<%
function fun_sql(tmode)
dim tval
dim sql
dim sqArr
sql=" DECLARE @Prod varchar(max) "&vbCrLf
sql=sql&" SET @Prod = '' "&vbCrLf
sql=sql&" SELECT @Prod = @Prod + '' + pu_que + ',' "&vbCrLf
sql=sql&" FROM ( "&vbCrLf
sql=sql&" select id, pu_que from [project2].[dbo].[T_RES_SQ_QUE_LAB] "&vbCrLf
sql=sql&" where ting='1' "&vbCrLf
sql=sql&" and pu_child_sn='"&pn_cd&"' "&vbCrLf
sql=sql&" and pu_type='"&pu_prjcode_type&"' "&vbCrLf
sql=sql&" and pu_que_type in ('0','2','3') "&vbCrLf '-- °ËÁõ¹®Ç×(0), °ËÁõÀüÈ­¹øÈ£(2), À̸§(3)
sql=sql&" ) A "&vbCrLf
sql=sql&" order by A.id asc "&vbCrLf
sql=sql&" SET @Prod = LEFT(@Prod, LEN(@Prod) - 1) "&vbCrLf
sql=sql&" select @Prod as 'que' "&vbCrLf
rsm.Open sql, adoConn, 1 , 1
sqArr = split(rsm("que"),",")
sql=" DECLARE @Prod varchar(max) "&vbCrLf
sql=sql&" SET @Prod = '' "&vbCrLf
sql=sql&" SELECT @Prod = @Prod + '[' + pu_que + '],' "&vbCrLf
sql=sql&" FROM ( "&vbCrLf
sql=sql&" select id, pu_que from [project2].[dbo].[T_RES_SQ_QUE_LAB] "&vbCrLf
sql=sql&" where ting='1' "&vbCrLf
sql=sql&" and pu_child_sn='"&pn_cd&"' "&vbCrLf
sql=sql&" and pu_type='"&pu_prjcode_type&"' "&vbCrLf
'-- fcsÁö¿ª sql=sql&" and fcs_area='"&pu_prjcode_area&"' "&vbCrLf
sql=sql&" and pu_que_type in ('0','2','3') "&vbCrLf '-- °ËÁõ¹®Ç×(0), °ËÁõÀüÈ­¹øÈ£(2), À̸§(3)
sql=sql&" ) A "&vbCrLf
sql=sql&" order by A.id asc "&vbCrLf
sql=sql&" SET @Prod = LEFT(@Prod, LEN(@Prod) - 1) "&vbCrLf
sql=sql&" EXEC (' "
if tmode<>"" then
sql=sql&" insert into "&tmode&" "
end if
sql=sql&" select "&vbCrLf
sql=sql&" A.oid,A.tid,A.pc_prjcode,A.pc_prjcode_sub1,A.pc_prjcode_sub2, "&vbCrLf
sql=sql&" case when left(A.ffusercode,2)=''1-'' then A.ffusercode else isnull(''1-''+REPLICATE(''0'',4 - LEN(A.ffusercode))+cast(A.ffusercode as varchar(10)),'''') end as ffusercode , "&vbCrLf '-- ¸éÁ¢¿øÄÚµå Æ÷¸ä ÀÚ¸´¼ö ä¿öÁÜ
sql=sql&" B.* "&vbCrLf
if tmode<>"" then
sql=sql&" , null as r_error, null as m_error,null as s_error,null as q_error, getdate() as insert_date "
end if
sql=sql&" from "&vbCrLf
sql=sql&" ( "&vbCrLf
sql=sql&" select max(id) as oid, "&vbCrLf
sql=sql&" tid,pc_prjcode,pc_prjcode_sub1,pc_prjcode_sub2,ffusercode,ffid "&vbCrLf
sql=sql&" from [project2].[dbo].[punching_data] "&vbCrLf
sql=sql&" where pc_prjcode=''"&pn_cd&"'' "&vbCrLf
sql=sql&" and pc_prjcode_sub1=''"&pu_prjcode_type&"'' "&vbCrLf
sql=sql&" and ff_ing=''1'' "&vbCrLf
'--- °ËÁõÀ̰ü µ¥ÀÌÅÍ --------------------------------------------------------------------
sql=sql&" and ffid in ( "&vbCrLf
sql=sql&" select ttelnum from [punching].[dbo].[tb_response] "&vbCrLf
sql=sql&" where tprocode=''"&pn_cd&"'' "&vbCrLf
sql=sql&" and char(ttype+65)=''"&pu_prjcode_type&"'' "&vbCrLf
sql=sql&" ) "&vbCrLf
'--------------------------------------------------------------------------------------
sql=sql&" group by tid,pc_prjcode,pc_prjcode_sub1,pc_prjcode_sub2,ffusercode,ffid "&vbCrLf
sql=sql&" ) A "&vbCrLf
sql=sql&" left join "&vbCrLf
sql=sql&" ( "&vbCrLf
sql=sql&" select * from "&vbCrLf
sql=sql&" (SELECT ffid,ffque, ffans FROM [project2].[dbo].[punching_data] "&vbCrLf
sql=sql&" where pc_prjcode=''"&pn_cd&"'' "&vbCrLf
sql=sql&" and pc_prjcode_sub1=''"&pu_prjcode_type&"'' "&vbCrLf
sql=sql&" and ff_ing=''1'' "&vbCrLf
sql=sql&" ) Q "&vbCrLf
sql=sql&" PIVOT ( max(ffans) FOR ffque IN (' + @Prod + ') "&vbCrLf
sql=sql&" ) AS PVT "&vbCrLf
sql=sql&" ) B "&vbCrLf
sql=sql&" on A.ffid=B.ffid "&vbCrLf
sql=sql&" where 1=1 "&vbCrLf
for i = 0 to UBound(sqArr)
sql=sql&" and " & sqArr(i) & " <> ''-1'' "&vbCrLf
next
sql=sql&" order by A.oid asc ') "&vbCrLf
tval=sql
fun_sql=tval
rsm.Close
end function
%>