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)); }