private DataTable 제품별list(string strStrat, String strEnd, String strLotno)
{
StringBuilder sb = new StringBuilder();
//sb.AppendLine("select cv.ITEM_NM as 제품명 ,cv.SPEC as 규격 ,isnull(z.F_SUB_AMT,0) 총수량 ,isnull(z.POOR_AMT,0) 불량수 ,CASE WHEN ISNULL(Z.F_SUB_AMT,'0') = 0 THEN 0 ELSE (ISNULL(Z.POOR_AMT,'0')/ ISNULL(Z.F_SUB_AMT,'0')) END 불량율,ISNULL(z.loss,0) as LOSS량,CASE WHEN ISNULL(Z.F_SUB_AMT,'0') = 0 THEN 0 ELSE (ISNULL(Z.loss,'0')/ ISNULL(Z.F_SUB_AMT,'0')) END LOSS율 from N_ITEM_CODE cv left join (");
//sb.AppendLine(" SELECT b.ITEM_NM,max(b.SPEC) as spec ,SUM(LOSS) as loss,sum(F_SUB_AMT) F_SUB_AMT ,SUM(POOR_AMT) as POOR_AMT FROM F_WORK_FLOW_DETAIL A ");
//sb.AppendLine(" left join N_ITEM_CODE b on b.ITEM_CD=A.ITEM_CD ");
//sb.AppendLine(" group by b.ITEM_NM");
//sb.AppendLine(" )z on z.ITEM_NM= cv.ITEM_NM ");
//sb.AppendLine(" where loss>0 or POOR_AMT>0 ");
sb.AppendLine(" select a.LOT_NO as LOT넘버 ,a.F_CHK_DATE as 일자 ,b.FLOW_NM as 검사명,C.ITEM_NM as 제품명,C.SPEC as 규격,a.MEASURE_CNT as 수량 ,c.CUST_CD,D.CUST_NM ,ISNULL(E.LINE_NM,'미지정') AS LINE_NM ,z.불량수 from F_FLOW_CHK as a");
sb.AppendLine(" inner join N_FLOW_CODE as B on B.FLOW_CD =a.FLOW_CD ");
sb.AppendLine(" inner join N_ITEM_CODE as C on C.ITEM_CD=a.ITEM_CD ");
sb.AppendLine(" lefT JOIN N_CUST_CODE AS d ON D.CUST_CD=C.CUST_CD");
sb.AppendLine(" left JOIN N_LINE_CODE AS E ON E.LINE_CD=C.LINE_CD ");
sb.AppendLine(" left join (select LOT_NO,SUM(convert(int,CHK_VALUE)) as 불량수 from F_FLOW_CHK_DETAIL group by LOT_NO) z on z.LOT_NO=a.LOT_NO ");
sb.AppendLine(" where a.F_CHK_DATE>='" + strStrat + "' and a.F_CHK_DATE<='" + strEnd + "' ");
if (strLotno != null)
{
sb.AppendLine("and a.LOT_NO='" + strLotno + "' ");
}
//sb.AppendLine(" select max (b.FLOW_DATE) as 일자 ,a.LOT_NO, sum(poor_amt) as 불량수 , SUM(Loss) as Loss량 , min(F_SUB_AMT) as 총수량 from F_WORK_FLOW_DETAIL as a");
//sb.AppendLine(" inner join F_WORK_FLOW B on B.LOT_NO=A.LOT_NO ");
//sb.AppendLine(" group by a.LOT_NO ");
//sb.AppendLine(" having SUM(Loss)>0 or sum(poor_amt) >0 ");
//if (strStrat != null)
//{
// sb.AppendLine(" and max (B.FLOW_DATE)>='" + strStrat + "' and max (B.FLOW_DATE)<='" + strEnd + "' ");
//}
//sb.AppendLine(" where loss>0 or POOR_AMT>0 ");
//sb.AppendLine(" where loss>0 or POOR_AMT>0 ");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_작업지시서현황_List(string sDayFrom, string sDayTo)
{
StringBuilder sb = new StringBuilder();
//=================== 작업지시서현황 ======================
sb.AppendLine("select '' AS no, A.W_INST_DATE");
sb.AppendLine(" ,A.W_INST_CD ");
sb.AppendLine(" ,A.LOT_NO ");
sb.AppendLine(" ,A.ITEM_CD ");
sb.AppendLine(" ,B.ITEM_NM ");
sb.AppendLine(" ,B.ITEM_GUBUN ");
sb.AppendLine(" ,B.SPEC ");
sb.AppendLine(" ,A.CUST_CD ");
sb.AppendLine(" ,D.CUST_NM ");
sb.AppendLine(" ,A.DELIVERY_DATE ");
sb.AppendLine(" ,A.INST_AMT");
sb.AppendLine(" ,A.CHARGE_AMT ");
sb.AppendLine(" ,A.PACK_AMT ");
sb.AppendLine(" ,A.PLAN_NUM");
sb.AppendLine(" ,A.PLAN_ITEM");
sb.AppendLine(" ,A.INSTAFF ");
sb.AppendLine(" ,A.INST_NOTICE ");
sb.AppendLine(" ,ISNULL(C.COMPLETE_YN,'N') AS COMPLETE_YN ");
sb.AppendLine(" ,ISNULL((SELECT MAX(F_SUB_DATE) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO), '') AS 공정일자 ");
sb.AppendLine(" ,ISNULL((SELECT MAX(F_SUB_AMT) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO), 0) AS 공정수량 ");
sb.AppendLine(" ,ISNULL((SELECT SUM(LOSS) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO), 0) AS 불량수량 ");
sb.AppendLine(" FROM F_WORK_INST A ");
sb.AppendLine(" LEFT OUTER JOIN N_ITEM_CODE B ON A.ITEM_CD = B.ITEM_CD ");
sb.AppendLine(" LEFT OUTER JOIN F_WORK_FLOW C ON A.LOT_NO = C.LOT_NO ");
sb.AppendLine(" LEFT OUTER JOIN N_CUST_CODE D ON A.CUST_CD = D.CUST_CD ");
sb.AppendLine(" WHERE A.W_INST_DATE BETWEEN @p_from AND @p_to ");
sb.AppendLine(" order by A.W_INST_DATE desc, A.W_INST_CD desc ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
sCommand.Parameters.AddWithValue("@p_from", sDayFrom);
sCommand.Parameters.AddWithValue("@p_to", sDayTo);
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable Today_Delivery()
{
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
//=================== 작업지시서현황 ======================
sb.AppendLine("SELECT A.DELIVERY_DATE,B.SPEC , B.ITEM_NM ");
sb.AppendLine("FROM F_WORK_INST A ");
sb.AppendLine("LEFT OUTER JOIN N_ITEM_CODE B ON A.ITEM_CD = B.ITEM_CD ");
sb.AppendLine("WHERE A.DELIVERY_DATE = '" + today + "' ");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
// private void 불량율bind(){
// DataTable dt불량율 = 불량율();
// // dgv불량율.DataSource = dt불량율;
// for (int i = 0; i < dt불량율.Rows.Count; i++)
// {
// dgv불량율.Rows.Add();
// dgv불량율.Rows[i].Cells["NOW제품명"].Value = dt불량율.Rows[i]["제품명"].ToString();
// dgv불량율.Rows[i].Cells["NOW규격"].Value = dt불량율.Rows[i]["규격"].ToString();
// dgv불량율.Rows[i].Cells["NOW수량"].Value = dt불량율.Rows[i]["수량"].ToString();
// }
// //panCenter.Dock = DockStyle.Fill;
// //w_home1.Dock = DockStyle.Fill;
//}
private DataTable 불량율()
{
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
//sb.AppendLine("SELECT X.POOR_NM as 불량 ");
//sb.AppendLine(",convert(int,ISNULL(Z.불량갯수,'0'))AS 불량갯수, ");
//sb.AppendLine("CASE WHEN ISNULL(CONVERT(DECIMAL(18,2),Z.전체수),'0') = 0 THEN 0 ");
//sb.AppendLine("ELSE CONVERT(DECIMAL(18,2),(ISNULL(Z.불량갯수,'0')/ ISNULL(Z.전체수,'0'))) ");
//sb.AppendLine("END 불량율 FROM N_POOR_CODE X ");
//sb.AppendLine(" LEFT JOIN( ");
//sb.AppendLine(" select SUM(A.POOR_AMT) AS 불량갯수 ,isnull(B.POOR_NM,'알수없음') as POOR_NM ,SUM(A.F_SUB_AMT) AS 전체수 from F_WORK_FLOW_DETAIL A ");
//sb.AppendLine(" FULL OUTER join N_POOR_CODE B on A.POOR_CD=B.POOR_CD");
//sb.AppendLine(" RIGHT join N_FLOW_CODE c on A.FLOW_CD=c.FLOW_CD ");
//sb.AppendLine(" where A.POOR_AMT>0 ");
//sb.AppendLine(" GROUP BY B.POOR_NM ");
//sb.AppendLine(" )Z ON Z.POOR_NM= X.POOR_NM ");
sb.AppendLine(" select ITEM_NM AS 제품명 ,MaX(item.SPEC) as 규격,MIN(CONVERT(INT,F_SUB_AMT)) as 수량 from F_WORK_FLOW_DETAIL as A ");
sb.AppendLine("inner join N_ITEM_CODE as item on item.ITEM_CD=A.ITEM_CD ");
sb.AppendLine(" where COMPLETE_YN='Y' and A.F_SUB_DATE=convert(varchar(10), getdate(), 120) ");
sb.AppendLine(" group by ITEM_NM ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
private DataTable 월별수량( )
{
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
//==================ㅈ납기지연율==
//임시테이블 셀프조인할려고 만듬
sb.AppendLine(" select ROW_NUMBER() OVER(ORDER BY substring(W_INST_DATE,0,8) ) AS ROWNUM ,substring(W_INST_DATE,0,8) as 월별 ,convert(int,SUM(INST_AMT)) as 총생산량 ");
sb.AppendLine(" into #월별생산량 ");
sb.AppendLine(" from F_WORK_INST as A ");
sb.AppendLine(" group by substring(W_INST_DATE,0,8) ");
//셀프조인하여 전로우값 을 가져옴
sb.AppendLine(" select A.월별,convert(int,isnull(a.총생산량,0)) as 올해총생산량,convert(int,isnull(prev.총생산량,0)) as 전년도총생산량 , case when isnull(prev.총생산량,0)=0 then 0 else ");
sb.AppendLine(" (convert(decimal,isnull(a.총생산량,0) - isnull(prev.총생산량,0))) / isnull(prev.총생산량,0)*100 end 율 ");
sb.AppendLine(" from #월별생산량 as A ");
sb.AppendLine(" LEFT JOIN #월별생산량 as prev ON prev.ROWNUM = A.ROWNUM - 1");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_작업지시서_모니터링_List(string sDayFrom, string sDayTo)
{
StringBuilder sb = new StringBuilder();
//=================== 작업지시서현황 ======================
sb.AppendLine("select '' AS no, A.W_INST_DATE");
sb.AppendLine(" ,A.W_INST_CD ");
sb.AppendLine(" ,A.LOT_NO ");
sb.AppendLine(" ,A.ITEM_CD ");
sb.AppendLine(" ,B.ITEM_NM ");
sb.AppendLine(" ,B.ITEM_GUBUN ");
sb.AppendLine(" ,B.SPEC ");
sb.AppendLine(" ,A.CUST_CD ");
sb.AppendLine(" ,D.CUST_NM ");
sb.AppendLine(" ,A.DELIVERY_DATE ");
sb.AppendLine(" ,A.INST_AMT");
sb.AppendLine(" ,A.CHARGE_AMT ");
sb.AppendLine(" ,A.PACK_AMT ");
sb.AppendLine(" ,A.PLAN_NUM");
sb.AppendLine(" ,A.PLAN_ITEM");
sb.AppendLine(" ,A.INSTAFF ");
sb.AppendLine(" ,A.INST_NOTICE ");
//2019-11-12 이재원 작업지시서_모니터링에서 생산일자와 생산수량이 출력되지 않는 것 수정
//------------------------------------------
sb.AppendLine(" ,ISNULL(E.INPUT_AMT, 0) AS INPUT_AMT ");
sb.AppendLine(" ,ISNULL(E.INPUT_DATE,'') AS INPUT_DATE ");
//------------------------------------------
sb.AppendLine(" ,ISNULL(C.COMPLETE_YN,'N') AS COMPLETE_YN ");
sb.AppendLine(" ,ISNULL((SELECT MAX(F_SUB_DATE) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO), '') AS 공정일자 ");
sb.AppendLine(" ,ISNULL((SELECT MAX(F_SUB_AMT) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO), 0) AS 공정수량 ");
//2019-11-12 이재원 불량수량을 프로그램상에서 0인지 구분해서 공백으로 넣지 않고 받아올때부터 0이면 공백 숫자가 있으면 숫자를 찍도록 쿼리문 수정
sb.AppendLine(" ,CASE WHEN (ISNULL((SELECT SUM(LOSS) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO), 0) = 0) THEN '' ELSE (SELECT CONVERT(nvarchar,SUM(LOSS)) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO) END AS 불량수량 ");
//2019-11-12 이재원 불량일자를 어떤 것으로 표시해야하는지 조건이 없음 LOT_SUB별로 뿌리는 테이블이 아니기 때문
//따라서 공정과정중 가장 최근에 불량이 발생한 일자를 뿌리도록 일단 구현하였음. 추후 수정해야할 듯
sb.AppendLine(" ,ISNULL((SELECT TOP 1 (FLOW_DATE) FROM F_WORK_FLOW_DETAIL AS K WHERE K.LOT_NO = A.LOT_NO AND LOSS != 0 order by FLOW_DATE desc), '') AS 불량일자 ");
sb.AppendLine(" FROM F_WORK_INST A ");
sb.AppendLine(" LEFT OUTER JOIN N_ITEM_CODE B ON A.ITEM_CD = B.ITEM_CD ");
sb.AppendLine(" LEFT OUTER JOIN F_WORK_FLOW C ON A.LOT_NO = C.LOT_NO ");
sb.AppendLine(" LEFT OUTER JOIN N_CUST_CODE D ON A.CUST_CD = D.CUST_CD ");
sb.AppendLine(" LEFT OUTER JOIN F_ITEM_INPUT E ON A.LOT_NO = E.LOT_NO ");
sb.AppendLine(" WHERE A.W_INST_DATE BETWEEN @p_from AND @p_to ");
sb.AppendLine(" order by A.W_INST_DATE desc, A.W_INST_CD desc ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
sCommand.Parameters.AddWithValue("@p_from", sDayFrom);
sCommand.Parameters.AddWithValue("@p_to", sDayTo);
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_구매요청현황_List(string sDayFrom, string sDayTo)
{
StringBuilder sb = new StringBuilder();
//=================== 구매요청현황 ======================
sb.AppendLine(" SELECT '' AS no, A.ORDER_DATE ");
sb.AppendLine(" ,A.ORDER_CD ");
sb.AppendLine(" ,A.ORDER_DATE + '-' + CONVERT(CHAR(4),A.ORDER_CD ) AS ORDER_NUM ");
sb.AppendLine(" ,A.CUST_CD ");
sb.AppendLine(" ,D.CUST_NM ");
sb.AppendLine(" ,A.INPUT_REQ_DATE ");
sb.AppendLine(" ,A.STAFF_CD ");
sb.AppendLine(" ,A.COMMENT ");
sb.AppendLine(" ,E.STAFF_NM ");
sb.AppendLine(" ,ISNULL(A.COMPLETE_YN,'N') AS COMPLETE_YN ");
sb.AppendLine(" ,ISNULL((SELECT COUNT(SEQ) FROM F_ORDER_DETAIL AS K WHERE A.ORDER_DATE = K.ORDER_DATE AND A.ORDER_CD = K.ORDER_CD), 0) AS 품목수량 ");
sb.AppendLine(" ,ISNULL((SELECT SUM(TOTAL_AMT) FROM F_ORDER_DETAIL AS K WHERE A.ORDER_DATE = K.ORDER_DATE AND A.ORDER_CD = K.ORDER_CD), 0) AS 전체수량 ");
sb.AppendLine(" FROM F_ORDER AS A ");
sb.AppendLine(" LEFT OUTER JOIN N_CUST_CODE AS D ON A.CUST_CD = D.CUST_CD ");
sb.AppendLine(" LEFT OUTER JOIN N_STAFF_CODE AS E ON A.STAFF_CD = E.STAFF_CD ");
sb.AppendLine(" WHERE A.ORDER_DATE BETWEEN @p_from AND @p_to ");
sb.AppendLine(" ORDER BY A.ORDER_DATE DESC, A.ORDER_CD ASC ");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
sCommand.Parameters.AddWithValue("@p_from", sDayFrom);
sCommand.Parameters.AddWithValue("@p_to", sDayTo);
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_납기지연_List(String condition)
{
StringBuilder sb = new StringBuilder();
//==================ㅈ납기지연율==
sb.AppendLine("Create Table #TEST1( Name Varchar(50))");
sb.AppendLine(" Insert Into #TEST1 (Name) values ('01') Insert Into #TEST1 (Name) values ('02')Insert Into #TEST1 (Name) values ('03') Insert Into #TEST1 (Name) values ('04')Insert Into #TEST1 (Name) values ('05') Insert Into #TEST1 (Name) values ('06') Insert Into #TEST1 (Name) values ('07') Insert Into #TEST1 (Name) values ('08') Insert Into #TEST1 (Name) values ('09') Insert Into #TEST1 (Name) values ('10') Insert Into #TEST1 (Name) values ('11') Insert Into #TEST1 (Name) values ('12')");
sb.AppendLine(" select substring(c.OUTPUT_DATE,6,2)as 월 ,sum(c.OUTPUT_AMT) as 율 into #Tmp총수량 from F_ITEM_OUT_DETAIL c ");
if (condition != "")
{
sb.AppendLine(" where c.OUTPUT_DATE like '" + condition + "%' ");
}
sb.AppendLine(" group by substring(c.OUTPUT_DATE,6,2) ");
sb.AppendLine(" select substring(a.w_inst_date,6,2) as 월, SUM(b.OUTPUT_AMT) as 율 into #Tmp납기량 from F_WORK_INST a ");
sb.AppendLine(" inner join F_ITEM_OUT_DETAIL b on A.LOT_NO =B.LOT_NO and a.DELIVERY_DATE<b.OUTPUT_DATE ");
if (condition != "")
{
sb.AppendLine(" where a.w_inst_date like '" + condition + "%' ");
}
sb.AppendLine(" group by substring(a.w_inst_date,6,2) ");
sb.AppendLine(" select test.Name as 월, isnull(z.납기준수율,0) as 납기준수율 from #Test1 as test ");
sb.AppendLine(" left join( ");
sb.AppendLine(" select a.월,isnull(100-(b.율/a.율)*100,100) as 납기준수율 from #Tmp총수량 a ");
sb.AppendLine(" left join #tmp납기량 b on a.월=b.월 ");
sb.AppendLine(" ) as z on z.월= test.Name ");
//select substring(a.w_inst_date,6,2) as 월, SUM(b.OUTPUT_AMT) as 율 from F_WORK_INST a
// inner join F_ITEM_OUT_DETAIL b on A.LOT_NO =B.LOT_NO and a.DELIVERY_DATE<b.OUTPUT_DATE
// where a.W_INST_DATe like '2020%'
// group by substring(a.w_inst_date,6,2)
Debug.WriteLine("납기준율");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
private DataTable allMenu()
{
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
sb.AppendLine(" select A.TopID, A.SubID,A.AsmName,A.SubName from T_SubMenu as A ");
sb.AppendLine(" where A.VIEW_YN = 'Y' ");
sb.AppendLine(" order by SortNo ");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_공정이동식별표_List()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT A.W_INST_DATE AS INPUT_DATE, '' AS no ");
sb.AppendLine(", '' AS 입고일자, '' AS 입고번호, '' AS 입고순번, A.ITEM_CD AS 제품코드, C.ITEM_NM AS 제품명, C.SPEC AS 규격 ");
sb.AppendLine(", '' AS HEAT_NO, '' AS HEAT_TIME, '' AS ORDER_DATE, '' AS ORDER_CD, '' AS ORDER_SEQ, '' AS RAW_MAT_GUBUN ");
sb.AppendLine(", '' AS S_CODE_NM, C.UNIT_CD AS 단위코드, U.UNIT_NM AS 단위명 ");
sb.AppendLine(", '' AS 수량, '' AS 단가, '' AS 금액, '' AS 제조번호, '' AS 바코드제조번호, Z.CUST_NM AS 업체명, '' AS 박스번호 ");
sb.AppendLine(" FROM F_WORK_INST A ");
sb.AppendLine(" LEFT OUTER JOIN N_ITEM_CODE C ON A.ITEM_CD = C.ITEM_CD ");
sb.AppendLine(" left outer join N_CUST_CODE Z on A.CUST_CD = Z.CUST_CD ");
sb.AppendLine(" left outer join N_UNIT_CODE U on C.UNIT_CD = U.UNIT_CD ");
sb.AppendLine(" WHERE 1=1 ");
sb.AppendLine(" AND A.LOT_NO = '" + txt_lot_no.Text.ToString() + "' ");
sb.AppendLine(" ORDER BY A.LOT_NO");
//sb.AppendLine(" SELECT A.FLOW_DATE AS INPUT_DATE, '' AS no ");
//sb.AppendLine(", '' AS 입고일자, '' AS 입고번호, '' AS 입고순번, A.ITEM_CD AS 제품코드, C.ITEM_NM AS 제품명, C.SPEC AS 규격 ");
//sb.AppendLine(", '' AS HEAT_NO, '' AS HEAT_TIME, '' AS ORDER_DATE, '' AS ORDER_CD, '' AS ORDER_SEQ, '' AS RAW_MAT_GUBUN ");
//sb.AppendLine(", '' AS S_CODE_NM, C.UNIT_CD AS 단위코드, U.UNIT_NM AS 단위명 ");
//sb.AppendLine(", '' AS 수량, '' AS 단가, '' AS 금액, '' AS 제조번호, '' AS 바코드제조번호, Z.CUST_NM AS 업체명, '' AS 박스번호 ");
//sb.AppendLine(" FROM F_WORK_FLOW A ");
//sb.AppendLine(" LEFT OUTER JOIN N_ITEM_CODE C ON A.ITEM_CD = C.ITEM_CD ");
//sb.AppendLine(" left outer join F_WORK_INST AS K on A.LOT_NO = K.LOT_NO ");
//sb.AppendLine(" left outer join N_CUST_CODE Z on K.CUST_CD = Z.CUST_CD ");
//sb.AppendLine(" left outer join N_UNIT_CODE U on C.UNIT_CD = U.UNIT_CD ");
//sb.AppendLine(" WHERE 1=1 ");
//sb.AppendLine(" AND A.LOT_NO = '" + txt_lot_no.Text.ToString() + "' ");
//sb.AppendLine(" ORDER BY A.LOT_NO");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
private DataTable bookmark()
{
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
sb.AppendLine(" select A.TopID,A.SubID,B.AsmName,B.SubName from N_AUTH_SUB as A ");
sb.AppendLine("inner join T_SubMenu as B on A.TopID=B.TopID and A.SubID=B.SubID ");
sb.AppendLine("where STAFF_CD='" + Common.p_strStaffNo + "'and A.bookmark='Y'");
sb.AppendLine(" order by SortNo");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable Today_Notice()
{
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT top 20 SEQ, TITLE, CONTENT, INSTAFF ,substring(intime,0,11) as intime2,intime ");
sb.AppendLine("FROM N_NOTICE ");
sb.AppendLine(" order by intime DESC ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_원자재입고식별표_List()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT ");
sb.AppendLine("METAL_CD,METAL_LOTNO,METAL_MODEL,METAL_SPEC,METAL_MAKECUST,METAL_ORDERCUST,METAL_INPUT_DATE,METAL_MAKE_DATE,COMMENT ");
sb.AppendLine("FROM N_METAL_CODE ");
sb.AppendLine("WHERE 1=1 ");
sb.AppendLine("ORDER BY METAL_CD ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable LotGrid()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" select A.LOT_NO from F_WORK_INST A inner join F_WORK_INST_DETAIL B ");
sb.AppendLine(" on A.W_INST_DATE = B.W_INST_DATE and A.W_INST_CD = B.W_INST_CD ");
sb.AppendLine(" inner join F_WORK_FLOW_DETAIL C on A.LOT_NO = C.LOT_NO ");
sb.AppendLine(" inner join N_RAW_CODE D on B.RAW_MAT_CD = D.RAW_MAT_CD ");
sb.AppendLine(" WHERE A.W_INST_DATE >= '" + dtp_date1.Text.ToString().Substring(0, 10) + "' AND A.W_INST_DATE <= '" + dtp_date2.Text.ToString().Substring(0, 10) + "' ");
sb.AppendLine(" GROUP BY A.LOT_NO ");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_제품입고식별표_List()
{
StringBuilder sb = new StringBuilder();
//sb.AppendLine("SELECT A.INPUT_DATE, '' AS no, '' AS 입고일자, '' AS 입고번호, '' AS 입고순번, '' AS 원부재료코드, '' 원부재료명, '' AS 규격 ");
//sb.AppendLine(", '' AS HEAT_NO, '' AS HEAT_TIME, '' AS ORDER_DATE, '' AS ORDER_CD, '' AS ORDER_SEQ, '' AS RAW_MAT_GUBUN ");
//sb.AppendLine(", '' AS S_CODE_NM, '' AS 단위코드, '' AS 단위명 ");
//sb.AppendLine(", '' AS 수량, '' AS 단가, '' AS 금액, '' AS 제조번호, '' AS 바코드제조번호 ");
//sb.AppendLine(" FROM F_RAW_DETAIL AS A ");
//sb.AppendLine(" WHERE A.INPUT_DATE >= '" + start_date.Text.ToString() + "' AND A.INPUT_DATE <= '" + end_date.Text.ToString() + "'");
sb.AppendLine(" SELECT '' AS INPUT_DATE, '' AS no ");
sb.AppendLine(", '' AS 입고일자, '' AS 입고번호, '' AS 입고순번, '' AS 제품코드, '' 제품명, '' AS 규격 ");
sb.AppendLine(", '' AS HEAT_NO, '' AS HEAT_TIME, '' AS ORDER_DATE, '' AS ORDER_CD, '' AS ORDER_SEQ, '' AS RAW_MAT_GUBUN ");
sb.AppendLine(", '' AS S_CODE_NM, '' AS 단위코드, '' AS 단위명 ");
sb.AppendLine(", '' AS 부위, '' AS 축종, '' AS 함량, '' AS 유통기한, '' AS 묶음번호, '' AS 냉동구분, '' AS 원산지, '' AS 유형 ");
sb.AppendLine(", '' AS 수량, '' AS 단가, '' AS 금액, '' AS 제조번호, '' AS 바코드제조번호, '' AS 업체명, '' AS 박스번호 ");
//sb.AppendLine(" FROM F_ITEM_INPUT A ");
//sb.AppendLine(" LEFT OUTER JOIN F_ITEM_INPUT_DETAIL B ON A.INPUT_DATE = B.INPUT_DATE AND A.INPUT_CD = B.INPUT_CD ");
//sb.AppendLine(" LEFT OUTER JOIN N_ITEM_CODE C ON A.ITEM_CD = C.ITEM_CD ");
////sb.AppendLine(" LEFT OUTER JOIN N_FLOW_CODE D ON B.FLOW_CD = D.FLOW_CD ");
////sb.AppendLine(" INNER JOIN F_ITEM_INPUT Z ON A.LOT_NO = Z.LOT_NO AND B.LOT_SUB = Z.LOT_SUB AND B.F_STEP = Z.F_STEP ");
////sb.AppendLine(" left outer join F_WORK_INST AS K on A.LOT_NO = K.LOT_NO ");
//sb.AppendLine(" WHERE 1=1 ");
////sb.AppendLine(" AND D.ITEM_IDEN_YN = 'Y' ");
////sb.AppendLine(" AND A.COMPLETE_YN = 'Y' ");
//sb.AppendLine(" ORDER BY A.INPUT_DATE, A.INPUT_CD");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
private DataTable 일정관리()
{
//select title,date from F_DATES
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
sb.AppendLine("select title,date,isnull(color,'#000000') as color from F_DATES ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_원자재입고식별표_List()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT A.INPUT_DATE, '' AS no, '' AS 입고일자, '' AS 입고번호, '' AS 입고순번, '' AS 원부재료코드, '' 원부재료명, '' 축종, '' 단위, '' 등급, '' 부위, '' 유형, '' 냉동구분, '' 도축장, '' 유통기한, '' AS 원산지 ");
sb.AppendLine(", '' AS HEAT_NO, '' AS HEAT_TIME, '' AS ORDER_DATE, '' AS ORDER_CD, '' AS ORDER_SEQ, '' AS RAW_MAT_GUBUN ");
sb.AppendLine(", '' AS S_CODE_NM, '' AS 단위코드, '' AS 단위명 ");
sb.AppendLine(", '' AS 수량, '' AS 단가, '' AS 금액, '' AS 제조번호, '' AS 바코드제조번호, '' AS 공급처, '' AS 라벨명 ");
sb.AppendLine(" FROM F_RAW_DETAIL AS A ");
sb.AppendLine(" WHERE A.INPUT_DATE >= '" + start_date.Text.ToString() + "' AND A.INPUT_DATE <= '" + end_date.Text.ToString() + "'");
sb.AppendLine(" AND (A.CHECK_YN = 'Y' OR A.CHECK_YN = 'O') ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable notice2(String condition)
{
wnAdo wAdo = new wnAdo();
StringBuilder sb = new StringBuilder();
sb.AppendLine("select A.TITLE,A.CONTENT,A.INTIME,B.STAFF_NM from n_notice as A ");
sb.AppendLine("inner join N_STAFF_CODE as B on A.INSTAFF=B.STAFF_CD");
sb.AppendLine("where INTIME like '%" + condition + "%' ");
Debug.WriteLine(sb);
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
private DataTable MakeFrmPrt()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" '' AS 매출일자 ");
sb.AppendLine(",'' AS 전표번호 ");
sb.AppendLine(",'' AS 항목번호 ");
sb.AppendLine(",'' AS 입력방법 ");
sb.AppendLine(",'' AS 매출구분명 ");
sb.AppendLine(",'' AS 매출시각 ");
sb.AppendLine(",'' AS 서명일시 ");
sb.AppendLine(",'' AS 거래처명 ");
sb.AppendLine(",'' AS 거래처사업자번호 ");
sb.AppendLine(",'' AS 거래처대표자명 ");
sb.AppendLine(",'' AS 계좌별칭 ");
sb.AppendLine(",'' AS 주소 ");
sb.AppendLine(",'' AS 전잔고 ");
sb.AppendLine(",'' AS 당일매출액 ");
sb.AppendLine(",'' AS 당일수금액 ");
sb.AppendLine(",'' AS 잔고 ");
sb.AppendLine(",'' AS 상품코드 ");
sb.AppendLine(",'' AS 상품명 ");
sb.AppendLine(",'' AS 규격 ");
sb.AppendLine(",'' AS 낱개수량계 ");
sb.AppendLine(",'' AS 입력금액계 ");
sb.AppendLine(",'' AS 부가세액계 ");
sb.AppendLine(",'' AS 총수량계 ");
sb.AppendLine(",'' AS 포함금액계 ");
sb.AppendLine(",'' AS 대체사업자번호 ");
sb.AppendLine(",'' AS 대체사업자번호2 ");
sb.AppendLine(",'' AS 대체대표자 ");
sb.AppendLine(",'' AS 대체상호명 ");
sb.AppendLine(",'' AS 대체연락처 ");
sb.AppendLine(",'' AS 대체이메일 ");
sb.AppendLine(",'' AS 대체업태 ");
sb.AppendLine(",'' AS 대체종목 ");
sb.AppendLine(",'' AS 대체우편번호 ");
sb.AppendLine(",'' AS 대체주소 ");
sb.AppendLine(",'' AS 대체직인파일 ");
sb.AppendLine(",'' AS 대체공지컴퓨터 ");
sb.AppendLine(",'' AS 상품폰트 ");
sb.AppendLine(",'' AS 규격폰트 ");
sb.AppendLine(",'' AS 상품상세명 ");
sb.AppendLine(",'' AS 박스수량 ");
sb.AppendLine(",'' AS 중간수량 ");
sb.AppendLine(",'' AS 낱개수량 ");
sb.AppendLine(",'' AS 박스표시 ");
sb.AppendLine(",'' AS 총수량 ");
sb.AppendLine(",'' AS 박스단가 ");
sb.AppendLine(",'' AS 낱개단가 ");
sb.AppendLine(",'' AS 금액 ");
sb.AppendLine(",'' AS 부가세액 ");
sb.AppendLine(",'' AS 포함금액 ");
sb.AppendLine(",'' AS 서비스박스 ");
sb.AppendLine(",'' AS 서비스낱개 ");
sb.AppendLine(",'' AS 배송사원명 ");
sb.AppendLine(",'' AS 비고 ");
sb.AppendLine(",'' AS 표시순번 ");
sb.AppendLine(",'' AS 페이지그룹 ");
sb.AppendLine(",'' AS 표시페이지 ");
sb.AppendLine(",'' AS 박스바코드 ");
sb.AppendLine(",'' AS 중간바코드 ");
sb.AppendLine(",'' AS 낱개바코드 ");
sb.AppendLine(",'' AS 상품비고 ");
sb.AppendLine(",'' AS 매출년월 ");
sb.AppendLine(",'' AS 박스수량계 ");
sb.AppendLine(",'' AS 중간수량계 ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
//탑 메뉴
public DataTable sTopMenuList()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("select ");
sb.AppendLine(" a.UtilCd ");
sb.AppendLine(" , a.TopID ");
sb.AppendLine(" , a.TopName ");
sb.AppendLine(" , a.TopPath ");
sb.AppendLine(" , b.UTIL_COLOR ");
sb.AppendLine(" from T_Sales_TopMenu a ");
sb.AppendLine(" inner join N_UTIL_CODE b ");
sb.AppendLine(" on a.UtilCd = b.UTIL_CD ");
sb.AppendLine(" order by a.SortNo asc ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
return(wAdo.SqlCommandSelect(sCommand));
}
public DataTable fn_제품현황_List(string sDayFrom, string sDayTo)
{
StringBuilder sb = new StringBuilder();
//=================== 제품별 집계 ======================
sb.AppendLine(" SELECT '' AS NO, A.ITEM_CD, MAX(B.ITEM_NM) AS ITEM_NM, MAX(B.SPEC) AS SPEC, MIN(DELIVER_REQ_DATE) AS 납기요청일 ");
sb.AppendLine(" , SUM(A.수주수량) AS 수주수량, SUM(A.수주생산계획) AS 수주생산계획, SUM(A.수주잔량) AS 수주잔량 ");
sb.AppendLine(" , SUM(A.생산계획) AS 생산계획, SUM(A.생산실적) AS 생산실적, SUM(A.생산잔량) AS 생산잔량 ");
sb.AppendLine(" , SUM(A.출하계획) AS 출하계획, SUM(A.출하실적) AS 출하실적, SUM(A.출하잔량) AS 출하잔량 ");
sb.AppendLine(" , MAX(A.재고) AS 재고 ");
sb.AppendLine(" FROM ( ");
sb.AppendLine(" SELECT O.ITEM_CD, O.PLAN_DATE, L.DELIVER_REQ_DATE, TOTAL_AMT AS 수주수량, 0 AS 수주생산계획,0 AS 수주잔량 ");
sb.AppendLine(" , 0 AS 생산계획,0 AS 생산실적,0 AS 생산잔량, 0 AS 출하계획,0 AS 출하실적,0 AS 출하잔량, 0 AS 재고 ");
sb.AppendLine(" FROM F_PLAN_DETAIL AS O LEFT OUTER JOIN F_PLAN AS L ON O.PLAN_DATE = L.PLAN_DATE AND O.PLAN_CD = L.PLAN_CD ");
sb.AppendLine(" WHERE O.PLAN_DATE BETWEEN @p_from AND @p_to ");
sb.AppendLine(" UNION ALL ");
sb.AppendLine(" SELECT ITEM_CD, W_INST_DATE, DELIVERY_DATE, 0,0,0, INST_AMT,0,0, 0,0,0, 0 ");
sb.AppendLine(" FROM F_WORK_INST WHERE W_INST_DATE BETWEEN @p_from AND @p_to ");
sb.AppendLine(" UNION ALL ");
sb.AppendLine(" SELECT ITEM_CD, INPUT_DATE, INPUT_DATE, 0,0,0, 0,INPUT_AMT,0, 0,0,0, 0 ");
sb.AppendLine(" FROM F_ITEM_INPUT WHERE INPUT_DATE BETWEEN @p_from AND @p_to ");
sb.AppendLine(" UNION ALL ");
sb.AppendLine(" SELECT ITEM_CD, OUTPUT_DATE, OUTPUT_DATE, 0,0,0, 0,0,0, OUTPUT_AMT,OUTPUT_AMT,0, 0 ");
sb.AppendLine(" FROM F_ITEM_OUT_DETAIL WHERE OUTPUT_DATE BETWEEN @p_from AND @p_to ");
sb.AppendLine(" ) AS A LEFT OUTER JOIN N_ITEM_CODE AS B ON A.ITEM_CD = B.ITEM_CD ");
sb.AppendLine(" GROUP BY A.ITEM_CD ");
sb.AppendLine(" ORDER BY ITEM_NM ASC ");
////=================== 제품별 건별 ===말이안됨(수주-생산-출하 링크가 없음)===================
//sb.AppendLine(" SELECT '' AS NO, A.ITEM_CD, B.ITEM_NM, B.SPEC, DELIVER_REQ_DATE AS 납기요청일 ");
//sb.AppendLine(" , 수주수량, 수주생산계획, 수주잔량 ");
//sb.AppendLine(" , 생산계획, 생산실적, 생산잔량 ");
//sb.AppendLine(" , 출하계획, 출하실적, 출하잔량 ");
//sb.AppendLine(" , 재고 ");
//sb.AppendLine(" FROM ( ");
//sb.AppendLine(" SELECT O.ITEM_CD, O.PLAN_DATE, L.DELIVER_REQ_DATE, TOTAL_AMT AS 수주수량, 0 AS 수주생산계획,0 AS 수주잔량 ");
//sb.AppendLine(" , 0 AS 생산계획,0 AS 생산실적,0 AS 생산잔량, 0 AS 출하계획,0 AS 출하실적,0 AS 출하잔량, 0 AS 재고 ");
//sb.AppendLine(" FROM F_PLAN_DETAIL AS O LEFT OUTER JOIN F_PLAN AS L ON O.PLAN_DATE = L.PLAN_DATE AND O.PLAN_CD = L.PLAN_CD ");
//sb.AppendLine(" WHERE O.PLAN_DATE BETWEEN @p_from AND @p_to ");
//sb.AppendLine(" UNION ALL ");
//sb.AppendLine(" SELECT ITEM_CD, W_INST_DATE, DELIVERY_DATE, 0,0,0, INST_AMT,0,0, 0,0,0, 0 ");
//sb.AppendLine(" FROM F_WORK_INST WHERE W_INST_DATE BETWEEN @p_from AND @p_to ");
//sb.AppendLine(" UNION ALL ");
//sb.AppendLine(" SELECT ITEM_CD, INPUT_DATE, INPUT_DATE, 0,0,0, 0,INPUT_AMT,0, 0,0,0, 0 ");
//sb.AppendLine(" FROM F_ITEM_INPUT WHERE INPUT_DATE BETWEEN @p_from AND @p_to ");
//sb.AppendLine(" UNION ALL ");
//sb.AppendLine(" SELECT ITEM_CD, OUTPUT_DATE, OUTPUT_DATE, 0,0,0, 0,0,0, OUTPUT_AMT,OUTPUT_AMT,0, 0 ");
//sb.AppendLine(" FROM F_ITEM_OUT_DETAIL WHERE OUTPUT_DATE BETWEEN @p_from AND @p_to ");
//sb.AppendLine(" ) AS A LEFT OUTER JOIN N_ITEM_CODE AS B ON A.ITEM_CD = B.ITEM_CD ");
////sb.AppendLine(" GROUP BY A.ITEM_CD ");
//sb.AppendLine(" ORDER BY ITEM_NM ASC ");
SqlCommand sCommand = new SqlCommand(sb.ToString());
if (sCommand.CommandText.Equals(null))
{
wnLog.writeLog(wnLog.LOG_ERROR, wnLog.LOGSTRING_NO_QUERY);
return(null);
}
sCommand.Parameters.AddWithValue("@p_from", sDayFrom);
sCommand.Parameters.AddWithValue("@p_to", sDayTo);
return(wAdo.SqlCommandSelect(sCommand));
}
