private void GVBind()
{
switch (Session["gline"].ToString().Trim())
{
case "N112":
if (Global.query_cprj != null)
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,RTRIM(tec.t_dsca) as position,t1.nloc,emno,t2.usdtim,t4.typedesc,t3.errdsca,case convert(nchar(1),t2.[workstop]) when '1' then '是' else '否' end as workstop,t2.uclose,t2.errno,t2.puid,t2.pdsca,t5.t_name from (ecsfc929_memb as t1 inner join (ecsfc930_pud as t2 inner join (ecsfc932_ud as t3 inner join ecsfc931_ud as t4 on t3.[type]=t4.[type]) on t2.errno=t3.errno) on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)inner join ecpsl010 as t5 on t1.emno=t5.t_empl inner join rp3440.baan.baan.tecsfc915230 as tec on t1.cprj=tec.t_cprj and t1.line=tec.t_line where t1.cprj='" + Global.query_cprj + "' and t1.line='" + Global.query_line + "' and t1.nopr='" + Global.query_nopr + "'");
GridView1.DataBind();
}
else
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,RTRIM(tec.t_dsca) as position,t1.nloc,emno,t2.usdtim,t4.typedesc,t3.errdsca,case convert(nchar(1),t2.[workstop]) when '1' then '是' else '否' end as workstop,t2.uclose,t2.errno,t2.puid,t2.pdsca,t5.t_name from (ecsfc929_memb as t1 inner join (ecsfc930_pud as t2 inner join (ecsfc932_ud as t3 inner join ecsfc931_ud as t4 on t3.[type]=t4.[type]) on t2.errno=t3.errno) on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)inner join ecpsl010 as t5 on t1.emno=t5.t_empl inner join rp3440.baan.baan.tecsfc915230 as tec on t1.cprj=tec.t_cprj and t1.line=tec.t_line where t1.cprj like '%" + Global.gcprj + "%' and t1.line like '%" + Global.gline + "%' and t1.nloc like '%" + Global.gnopr + "%' and uclose like '%" + Global.gcstatus + "%' " + Global.d1 + "");
GridView1.DataBind();
}
break;
case "N131":
case "P722":
case "P711":
case "P723":
case "P712":
if (Global.query_cprj != null)
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.position,t1.nloc,emno,t2.usdtim,t4.typedesc,t3.errdsca,case convert(nchar(1),t2.[workstop]) when '1' then '是' else '否' end as workstop,t2.uclose,t2.errno,t2.puid,t2.pdsca,t5.t_name from (ecsfc929_memb as t1 inner join (ecsfc930_pud as t2 inner join (ecsfc932_ud as t3 inner join ecsfc931_ud as t4 on t3.[type]=t4.[type]) on t2.errno=t3.errno) on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)inner join ecpsl010 as t5 on t1.emno=t5.t_empl where t1.cprj='" + Global.query_cprj + "' and t1.line='" + Global.query_line + "' and t1.nopr='" + Global.query_nopr + "'");
GridView1.DataBind();
}
else
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.position,t1.nloc,emno,t2.usdtim,t4.typedesc,t3.errdsca,case convert(nchar(1),t2.[workstop]) when '1' then '是' else '否' end as workstop,t2.uclose,t2.errno,t2.puid,t2.pdsca,t5.t_name from (ecsfc929_memb as t1 inner join (ecsfc930_pud as t2 inner join (ecsfc932_ud as t3 inner join ecsfc931_ud as t4 on t3.[type]=t4.[type]) on t2.errno=t3.errno) on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)inner join ecpsl010 as t5 on t1.emno=t5.t_empl where t1.cprj like '%" + Global.gcprj + "%' and t1.line like '%" + Global.gline + "%' and t1.nloc like '%" + Global.gnopr + "%' and uclose like '%" + Global.gcstatus + "%' " + Global.d1 + "");
GridView1.DataBind();
}
break;
}
}
// GET api/<controller>/5
public DataTable Get(string id)
{
string line = cc1.connectionofc008_select("select line from ecsfc000_emplrole where empl='" + id + "'", "line");
if (line.Trim() != "N112")
{
return(cc1.QueryDataTable(string.Format(@"SELECT distinct
[cprj], [line], [nopr],
[nloc], convert(char(10),[apdt],20) as apdt,
[rutm], convert(nvarchar(11),edate)+' '+stuff(right('0000'+cast(edtim as nvarchar),4),3,0,':') as tb2,
convert(char(10),sdate,20)+' '+stuff(right('0000'+cast(sdtim as nvarchar),4),3,0,':') as tb,
[autm], [code], [mkind], [msfid], [pfc], t1.position ,statuscode,'' as cooperation
FROM [ecsfc929_memb] as t1
WHERE ([emno] = '{0}' and [pfc] !='是')
union all
select
t933.[cprj], t933.[line], t933.[nopr],
[nloc], convert(char(10),[apdt],20) as apdt, [rutm],
convert(nvarchar(11),t929.edate)+' '+stuff(right('0000'+cast(t929.edtim as nvarchar),4),3,0,':') as tb2,
convert(char(10),t929.sdate,20)+' '+stuff(right('0000'+cast(t929.sdtim as nvarchar),4),3,0,':') as tb,
[autm], [code], [mkind], t933.[msfid], [pfc],
t929.position ,statuscode,cooperation
from ecsfc933_mdt as t933 left join ecsfc929_memb as t929 on t933.cprj=t929.cprj and t933.line=t929.line and t933.nopr=t929.nopr
where cooperation='1' and t933.emno='{0}'
and t933.msfid in(select ftb.mid from(select t1.cprj,t1.line,t1.nopr,(select top 1 t2.msfid from ecsfc933_mdt as t2 where t2.cprj=t1.cprj and t2.line=t1.line and t2.nopr=t1.nopr group by t2.msfid order by t2.msfid desc) as mid from ecsfc933_mdt as t1 where t1.emno='{0}' and cooperation='1' group by t1.cprj,t1.line,t1.nopr )as ftb)", id)));
}
else
{
return(cc1.QueryDataTable(string.Format(@"SELECT distinct
[cprj], [line], [nopr],
[nloc], convert(char(10),[apdt],20) as apdt, [rutm],
convert(nvarchar(11),edate)+' '+stuff(right('0000'+cast(edtim as nvarchar),4),3,0,':') as tb2,
convert(char(10),sdate,20)+' '+stuff(right('0000'+cast(sdtim as nvarchar),4),3,0,':') as tb,
[autm], [code], [mkind], [msfid], [pfc],
RTRIM(t915.t_dsca) as position ,statuscode,''as cooperation
FROM [ecsfc929_memb] as t1 inner join rp3440.baan.baan.tecsfc915230 as t915
on t1.cprj=t915.t_cprj
WHERE ([emno] = '{0}' and [pfc] !='是')
union all
select
t933.[cprj], t933.[line], t933.[nopr],
[nloc], convert(char(10),[apdt],20) as apdt, [rutm],
convert(nvarchar(11),t929.edate)+' '+stuff(right('0000'+cast(t929.edtim as nvarchar),4),3,0,':') as tb2,
convert(char(10),t929.sdate,20)+' '+stuff(right('0000'+cast(t929.sdtim as nvarchar),4),3,0,':') as tb,
[autm], [code], [mkind], t933.[msfid], [pfc],
RTRIM(t915.t_dsca) as position ,statuscode,cooperation
from ecsfc933_mdt as t933 left join ecsfc929_memb as t929 on t933.cprj=t929.cprj and t933.line=t929.line and t933.nopr=t929.nopr inner join rp3440.baan.baan.tecsfc915230 as t915
on t933.cprj=t915.t_cprj and t933.line=t915.t_line
where cooperation='1' and t933.emno='{0}'
and t933.msfid in
(select ftb.mid from(select t1.cprj,t1.line,t1.nopr,(select top 1 t2.msfid from ecsfc933_mdt as t2 where t2.cprj=t1.cprj and t2.line=t1.line and t2.nopr=t1.nopr group by t2.msfid order by t2.msfid desc) as mid from ecsfc933_mdt as t1 where t1.emno='{0}' and cooperation='1' group by t1.cprj,t1.line,t1.nopr )as ftb)", id)));
}
}
private void GVBind()
{
switch (Session["gline"].ToString().Trim())
{
case "N112":
if (Convert.ToInt32(Session["change_mode"]) == 1)//change_mode 補完工
{
if (Session["SF_SPTrigger"].ToString().Trim() == "0")
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,tec.t_dsca as position,t1.mkind,t1.emno,t2.t_name,convert(char(10),sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(edtim as nvarchar),4),3,0,':'),108) as tb2 from ecsfc929_memb as t1 inner join ecpsl010 as t2 on t1.emno=t2.t_empl inner join rp3440.baan.baan.tecsfc915230 as tec on t1.cprj=tec.t_cprj and t1.line=tec.t_line where t1.code='*' and t1.pfc='否' and t1.cprj like '%" + Global.gcprj + "%' and t1.line like '%" + Session["gline2"] + "%' and t1.nloc like '%" + Global.gnopr + "%' " + Global.d1 + "");
}
else
{
GridView1.DataSource = cc1.QueryDataTable("select distinct t1.cprj,t1.line,t1.nopr,t1.nloc,tec.t_dsca as position,t1.mkind,t1.emno,t4.t_name,convert(char(10),t1.sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),t1.edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.edtim as nvarchar),4),3,0,':'),108) as tb2 from ((ecsfc929_memb as t1 inner join ecsfc933_mdt as t2 on t1.msfid=t2.msfid) left join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr) inner join ecpsl010 as t4 on t1.emno=t4.t_empl inner join rp3440.baan.baan.tecsfc915230 as tec on t1.cprj=tec.t_cprj and t1.line=tec.t_line where pfc='否' and code='*' and ((t3.WorkStop ='1' and t3.uclose='是')or(t3.WorkStop ='0')or(t3.WorkStop is null)) and t2.sdate is not null and t1.line='" + Session["gline"] + "'");
}
}
else
{
if (Session["SF_SPTrigger"].ToString().Trim() == "0")
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,tec.t_dsca as position,t1.mkind,t1.emno,t2.t_name,convert(char(10),sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(edtim as nvarchar),4),3,0,':'),108) as tb2 from ecsfc929_memb as t1 inner join ecpsl010 as t2 on t1.emno=t2.t_empl inner join rp3440.baan.baan.tecsfc915230 as tec on t1.cprj=tec.t_cprj and t1.line=tec.t_line where t1.code is null and t1.pfc='否' and t1.cprj like '%" + Global.gcprj + "%' and t1.line like '%" + Session["gline2"] + "%' and t1.nloc like '%" + Global.gnopr + "%'");
}
else
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,tec.t_dsca as position,t1.mkind,t1.emno,t4.t_name,convert(char(10),t1.sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),t1.edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.edtim as nvarchar),4),3,0,':'),108) as tb2 from ((ecsfc929_memb as t1 inner join ecsfc933_mdt as t2 on t1.msfid=t2.msfid) left join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr) inner join ecpsl010 as t4 on t1.emno=t4.t_empl inner join rp3440.baan.baan.tecsfc915230 as tec on t1.cprj=tec.t_cprj and t1.line=tec.t_line where pfc='否' and code='*' and ((t3.WorkStop ='1' and t3.uclose='是')or(t3.WorkStop ='0')or(t3.WorkStop is null)) and t2.sdate is null and t1.line='" + Session["gline"] + "'");
}
}
break;
case "N131":
case "P722":
if (Convert.ToInt32(Session["change_mode"]) == 1)//change_mode 補完工
{
if (Session["SF_SPTrigger"].ToString().Trim() == "0")
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,t1.position,t1.mkind,t1.emno,t2.t_name,convert(char(10),sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(edtim as nvarchar),4),3,0,':'),108) as tb2 from ecsfc929_memb as t1 inner join ecpsl010 as t2 on t1.emno=t2.t_empl where t1.code='*' and t1.pfc='否' and t1.cprj like '%" + Global.gcprj + "%' and t1.line like '%" + Session["gline2"] + "%' and t1.nloc like '%" + Global.gnopr + "%' " + Global.d1 + "");
}
else
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,t1.position,t1.mkind,t1.emno,t4.t_name,convert(char(10),t1.sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),t1.edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.edtim as nvarchar),4),3,0,':'),108) as tb2 from ((ecsfc929_memb as t1 inner join ecsfc933_mdt as t2 on t1.msfid=t2.msfid) left join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr) inner join ecpsl010 as t4 on t1.emno=t4.t_empl where pfc='否' and code='*' and ((t3.WorkStop ='1' and t3.uclose='是')or(t3.WorkStop ='0')or(t3.WorkStop is null)) and t2.sdate is not null and t1.line='" + Session["gline"] + "'");
}
}
else
{
if (Session["SF_SPTrigger"].ToString().Trim() == "0")
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,t1.position,t1.mkind,t1.emno,t2.t_name,convert(char(10),sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(edtim as nvarchar),4),3,0,':'),108) as tb2 from ecsfc929_memb as t1 inner join ecpsl010 as t2 on t1.emno=t2.t_empl where t1.code is null and t1.pfc='否' and t1.cprj like '%" + Global.gcprj + "%' and t1.line like '%" + Session["gline2"] + "%' and t1.nloc like '%" + Global.gnopr + "%'");
}
else
{
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,t1.position,t1.mkind,t1.emno,t4.t_name,convert(char(10),t1.sdate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.sdtim as nvarchar),4),3,0,':'),108) as tb,convert(char(10),t1.edate,20)+' '+convert(varchar(5),stuff(right('0000'+cast(t1.edtim as nvarchar),4),3,0,':'),108) as tb2 from ((ecsfc929_memb as t1 inner join ecsfc933_mdt as t2 on t1.msfid=t2.msfid) left join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr) inner join ecpsl010 as t4 on t1.emno=t4.t_empl where pfc='否' and code='*' and ((t3.WorkStop ='1' and t3.uclose='是')or(t3.WorkStop ='0')or(t3.WorkStop is null)) and t2.sdate is null and t1.line='" + Session["gline"] + "'");
}
}
break;
}
GridView1.DataBind();
}
private void GVBind()
{
comment_class1 cc1 = new comment_class1();
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,t1.nopr,t1.nloc,t1.emno,t2.t_name,t1.apdt from (ecsfc929_memb as t1 left join ecpsl010 as t2 on t1.emno=t2.t_empl) left join ecsfc000_emplrole as t3 on t1.emno=t3.empl where t1.code is" + Session["mod"] + " null and pfc!='是' and t1.cprj like '%" + Global.gcprj + "%' and t1.line like '%" + Global.gline + "%' and t1.nloc like '%" + Global.gnopr + "%' " + Global.d1 + " order by t1.cprj,t1.nopr asc");
GridView1.DataBind();
}
// GET api/<controller>/5
public DataTable Get(string id)
{
string[] key = id.Split(',');
if (key[1] == "1")
{
return(cc1.QueryDataTable(@"select t931.typedesc,t931.[type],t931.groupID "
+ "from ecsfc931_ud as t931 left join ecsfc003_errorGrp as t003 on t931.groupID=t003.groupID" +
" where t003.line='" + key[0] + "'"));
}
else
{
return(cc1.QueryDataTable(@"select t932.errno,t932.errdsca,t932.[type]
from (ecsfc932_ud as t932 inner join ecsfc931_ud as t931 on t932.[type]=t931.[type] )inner join ecsfc003_errorGrp as t003 on t931.groupID=t003.groupID
where t003.line='" + key[0] + "'"));
}
}
public void GV1Bind()
{
int tt = Convert.ToInt32(cc1.connectionofc008_select("select count(*) as ct from [ecsfc929_memb] WHERE ([emno] = '" + Session["emplnet"] + "' and [pfc] !='是')", "ct"));
GridView1.DataSource = cc1.QueryDataTable("select t1.cprj,t1.line,(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是') from (ecsfc929_memb as t1 full join ecsfc933_mdt as t2 on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr) full join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr where t1.line='" + Session["line"] + "' and (select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line)!=(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是') group by t1.cprj,t1.line order by max(t2.nopr) desc ");
GridView1.DataBind();
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable(@"select distinct rtrim(SUBSTRING(nloc,3,1)) as nloc
,SUBSTRING(convert(nvarchar(10),apdt),6,2) as am
,SUBSTRING(convert(nvarchar(10),apdt),9,2) as ad
,case when SUBSTRING(convert(nvarchar(10),t2.sdate),6,2) is null
then SUBSTRING(convert(nvarchar(10),t1.sdate),6,2)
else SUBSTRING(convert(nvarchar(10),t2.sdate),6,2) end as sm
,case when SUBSTRING(convert(nvarchar(10),t2.sdate),9,2) is null
then SUBSTRING(convert(nvarchar(10),t1.sdate),9,2)
else SUBSTRING(convert(nvarchar(10),t2.sdate),9,2) end as sd
from ecsfc929_memb as t1 left join ecsfc933_mdt as t2 on t1.msfid=t2.msfid where t1.cprj='" + id + "'"));
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable(string.Format(@"select distinct t_cprj,line,case when t004.t_dsca is not null then t004.t_dsca else t915.t_dsca end as t_dsca
from rp3440.baan.baan.tecsfc915230 as t915 full join ecsfc004_sinData as t004 on t915.t_cprj=t004.cprj and t915.t_line=t004.line
where (select count(*) from ecsfc929_memb where cprj=t915.t_cprj and pfc='否')!='0' and t_line='N112' order by t_dsca asc")));
}
// GET api/<controller>/5
public DataTable Get([FromBody] object value)
{
//return cc1.QueryDataTable("select cprj,line,nopr,emno,sdate from ecsfc933_mdt where emno='" + id + "' and cooperation='1'");
return(cc1.QueryDataTable("select cprj,line,nopr,emno,sdate from ecsfc933_mdt where emno='' and cooperation='1'"));
}
// GET api/<controller>/5
public DataTable Get(string id)
{
// if (id != "N112")
// {
// return cc1.QueryDataTable(@"select RTRIM(t1.cprj)as cprj,RTRIM(t1.line)as line,
//(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是'),
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='1'))as w1,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='2'))as w2,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='3'))as w3,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='4'))as w4,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='5'))as w5,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='6'))as w6,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='7'))as w7,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='8'))as w8
// from (ecsfc929_memb as t1 full join ecsfc933_mdt as t2 on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)
// full join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr
// where (select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line)!=(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是') and t1.line ='" + id
// + "' group by t1.cprj,t1.line order by max(t2.nopr) desc");
// }
// else
// {
// return cc1.QueryDataTable(@"select RTRIM(t1.cprj)as cprj,RTRIM(t1.line)as line,
//(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是'),
//(select top 1 case when t004.t_dsca is not null then t004.t_dsca else t915.t_dsca end as t_dsca from rp3440.baan.baan.tecsfc915230 as t915 full join ecsfc004_sinData as t004 on t915.t_cprj=t004.cprj and t915.t_line=t004.line where t_cprj=t1.cprj and t_line=t1.line order by t_mm desc) as t_dsca,(select distinct t929.mkind from ecsfc929_memb as t929 where t929.cprj=t1.cprj and t929.line=t1.line) as mkind,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='1'))as w1,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='2'))as w2,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='3'))as w3,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='4'))as w4,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='5'))as w5,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='6'))as w6,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='7'))as w7,
//RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='8'))as w8
// from (ecsfc929_memb as t1 full join ecsfc933_mdt as t2 on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)
// full join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr
// where (select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line)!=(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是') and t1.line ='" + id
// + "' group by t1.cprj,t1.line order by max(t2.nopr) desc");
// }
switch (id)
{
case "N112":
return(cc1.QueryDataTable(string.Format(@"select RTRIM(t1.cprj)as cprj,RTRIM(t1.line)as line,
(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是'),
(select top 1 case when t004.t_dsca is not null then t004.t_dsca else t915.t_dsca end as t_dsca from rp3440.baan.baan.tecsfc915230 as t915 full join ecsfc004_sinData as t004 on t915.t_cprj=t004.cprj and t915.t_line=t004.line where t_cprj=t1.cprj and t_line=t1.line order by t_mm desc) as t_dsca,(select distinct t929.mkind from ecsfc929_memb as t929 where t929.cprj=t1.cprj and t929.line=t1.line) as mkind,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='1'))as w1,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='2'))as w2,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='3'))as w3,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='4'))as w4,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='5'))as w5,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='6'))as w6,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='7'))as w7,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='8'))as w8
from (ecsfc929_memb as t1 full join ecsfc933_mdt as t2 on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)
full join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr
where (select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line)!=(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是') and t1.line ='{0}'
group by t1.cprj,t1.line order by max(t2.nopr) desc", id)));
case "N131":
return(cc1.QueryDataTable(string.Format(@"select RTRIM(t1.cprj)as cprj,RTRIM(t1.line)as line,
(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是'),
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='1'))as w1,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='2'))as w2,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='3'))as w3,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='4'))as w4,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='5'))as w5,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='6'))as w6,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='7'))as w7,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='8'))as w8
from (ecsfc929_memb as t1 full join ecsfc933_mdt as t2 on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)
full join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr
where (select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line)!=(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是') and t1.line ='{0}'
group by t1.cprj,t1.line order by max(t2.nopr) desc", id)));
default:
return(cc1.QueryDataTable(string.Format(@"select RTRIM(t1.cprj)as cprj,RTRIM(t1.line)as line,
(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是'),
t914.t_cdat,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='1'))as w1,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='2'))as w2,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='3'))as w3,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='4'))as w4,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='5'))as w5,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='6'))as w6,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='7'))as w7,
RTRIM((select convert(varchar(1),statuscode)+'_'+t_empl+'_'+t_name as stc from ecsfc929_memb as et1 left join ecpsl010 as ep on et1.emno=ep.t_empl where et1.cprj=t1.cprj and et1.line=t1.line and et1.nopr='8'))as w8
from ((ecsfc929_memb as t1 full join ecsfc933_mdt as t2 on t1.cprj=t2.cprj and t1.line=t2.line and t1.nopr=t2.nopr)
full join ecsfc930_pud as t3 on t1.cprj=t3.cprj and t1.line=t3.line and t1.nopr=t3.nopr) left join rp3440.baan.baan.tecsfc914230 as t914 on t1.cprj=t914.t_cprj and t1.line=t914.t_line
where (select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line)!=(select count(cprj) from ecsfc929_memb where cprj=t1.cprj and line=t1.line and pfc='是') and t1.line ='{0}'
group by t1.cprj,t1.line,t_no,t_cdat order by t_no asc,t_cdat asc", id)));
}
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable(@"select t932.errno,t932.errdsca,t932.[type],t931.typedesc,t932.lostno "
+ "from ecsfc931_ud as t931 inner join ecsfc003_errorGrp as t003 on t931.groupID=t003.groupID inner join ecsfc932_ud as t932 on t931.[type]=t932.[type] "
+ "where t003.line='" + id + "' order by t932.[type]"));
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable("select t_empl,t_name from ecpsl010 as t010 inner join ecsfc000_emplrole as t000 on t010.t_empl=t000.empl where t000.line='" + id + "'"));
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable("select empl,t_name,[role] from ecsfc000_emplrole as t000 inner join ecpsl010 as t010 on t000.empl=t010.t_empl"));
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable("select * from ecsfc000_emplrole where line='" + id + "'"));
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable(string.Format("select distinct sno from ChkItem where nextchk_date='{0}'", id)));
}
// GET api/<controller>/5
public DataTable Get(string id)
{
return(cc1.QueryDataTable("select cprj,line,nopr,emno,sdate from ecsfc933_mdt where emno='" + id + "' and cooperation='1'"));
}
