/// <summary> /// 获取未使用的烟道列表 /// </summary> public static SortingLineBoxList GetEmptyLineBoxList() { SortingLineBoxList sortingLineBoxList = new SortingLineBoxList(); using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString)) { cn.Open(); using (var cm = cn.CreateCommand()) { StringBuilder SQL = new StringBuilder(); SQL.Append("SELECT tl.boxCode,tl.boxName,td.CIGCODE,td.CIGNAME,tl.addressCode,tl.putNum FROM t_linebox tl LEFT JOIN t_sorting_line_detail_task td ON td.LINEBOXCODE = tl.boxCode where tl.boxType = 1 and td.CIGCODE is NULL ORDER by tl.addressCode"); cm.CommandText = SQL.ToString(); using (var dr = new SafeDataReader(cm.ExecuteReader())) { while (dr.Read()) { SortingLineBox sortingLineBox = new SortingLineBox(); sortingLineBox.Cigcode = dr.GetString("cigcode"); sortingLineBox.CigName = dr.GetString("cigname"); sortingLineBox.LineBoxCode = dr.GetString("boxcode"); sortingLineBox.LineBoxName = dr.GetString("boxname"); sortingLineBox.PlcAddress = dr.GetString("addresscode"); sortingLineBox.PutNum = dr.GetInt32("PutNum"); sortingLineBoxList.Add(sortingLineBox); } } } } return(sortingLineBoxList); }
/// <summary> /// 获取已使用全部烟道列表 /// </summary> public static SortingLineBoxList GetLineBoxList() { SortingLineBoxList sortingLineBoxList = new SortingLineBoxList(); using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString)) { cn.Open(); using (var cm = cn.CreateCommand()) { StringBuilder SQL = new StringBuilder(); SQL.Append("SELECT * "); SQL.Append("FROM (SELECT * "); SQL.Append(" FROM t_linebox tl "); SQL.Append(" WHERE tl.ISDYNAMICBOX = 0) tl "); SQL.Append("LEFT JOIN t_sortingsubline sb ON tl.SUBLINEID = sb.ID "); SQL.Append("LEFT JOIN "); SQL.Append(" ( SELECT LINEBOXCODE, CIGNAME, cigcode, SUM(qty) qty "); SQL.Append(" FROM t_sorting_line_detail_task "); SQL.Append(" GROUP BY LINEBOXCODE ) sd ON tl.boxcode = sd.LINEBOXCODE "); SQL.Append("UNION "); SQL.Append("SELECT * "); SQL.Append("FROM (SELECT * "); SQL.Append(" FROM t_linebox tl "); SQL.Append(" WHERE tl.ISDYNAMICBOX = 1) tl "); SQL.Append("LEFT JOIN t_sortingsubline sb ON tl.SUBLINEID = sb.ID "); SQL.Append("LEFT JOIN "); SQL.Append(" ( SELECT LINEBOXCODE, '混合仓', '', SUM(qty) qty "); SQL.Append(" FROM t_sorting_line_detail_task "); SQL.Append(" GROUP BY LINEBOXCODE ) sd ON tl.boxcode = sd.LINEBOXCODE "); cm.CommandText = SQL.ToString(); using (var dr = new SafeDataReader(cm.ExecuteReader())) { while (dr.Read()) { bool isexist = false; SortingLineBox sortingLineBox = new SortingLineBox(); sortingLineBox.Cigcode = dr.GetString("cigcode"); sortingLineBox.CigName = dr.GetString("cigname"); sortingLineBox.LineBoxCode = dr.GetString("boxcode"); sortingLineBox.PutNum = dr.GetInt32("putnum"); sortingLineBox.PARENTLINEBOX = dr.GetString("PARENTLINEBOX"); sortingLineBox.ABANDONPARENT = dr.GetString("ABANDONPARENT"); sortingLineBox.SUBLINEID = dr.GetString("SUBLINEID"); sortingLineBox.TOTQTY = dr.GetInt32("qty"); sortingLineBox.SublineSeq = dr.GetInt32("sequence"); sortingLineBox.IsDynamicbox = dr.GetInt32("ISDYNAMICBOX"); sortingLineBoxList.Add(sortingLineBox); } } } } return(sortingLineBoxList); }
/// <summary> /// 获取已使用烟道列表 /// </summary> public static SortingLineBoxList GetBindLineBoxList() { SortingLineBoxList sortingLineBoxList = new SortingLineBoxList(); using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString)) { cn.Open(); using (var cm = cn.CreateCommand()) { StringBuilder SQL = new StringBuilder(); SQL.Append("SELECT DISTINCT(td.LINEBOXCODE),td.LINEBOXNAME,td.CIGCODE,td.CIGNAME,tl.addressCode,tl.putNum,s.SORTINGTASKNO FROM t_sorting_line_task s join t_sorting_line_detail_task td ON s.ID = td.TASKID JOIN t_linebox tl ON td.LINEBOXCODE = tl.boxCode ORDER by td.LINEBOXNAME"); cm.CommandText = SQL.ToString(); using (var dr = new SafeDataReader(cm.ExecuteReader())) { while (dr.Read()) { bool isexist = false; SortingLineBox sortingLineBox = new SortingLineBox(); sortingLineBox.Cigcode = dr.GetString("cigcode"); sortingLineBox.CigName = dr.GetString("cigname"); sortingLineBox.LineBoxCode = dr.GetString("lineboxcode"); sortingLineBox.LineBoxName = dr.GetString("lineboxname"); sortingLineBox.PlcAddress = dr.GetString("addresscode"); sortingLineBox.PutNum = dr.GetInt32("PutNum"); sortingLineBox.SortingTaskNo = dr.GetString("SortingTaskNo"); for (int i = 0; i < sortingLineBoxList.Count; i++) { if (sortingLineBoxList[i].LineBoxCode == sortingLineBox.LineBoxCode) { isexist = true; sortingLineBoxList[i].CigName += "," + sortingLineBox.CigName; break; } } if (!isexist) { sortingLineBoxList.Add(sortingLineBox); } } } } } return(sortingLineBoxList); }
public static SortingLineBoxList GetOverLineBoxQty() { SortingLineBoxList sortingLineBoxList = new SortingLineBoxList(); using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString)) { cn.Open(); using (var cm = cn.CreateCommand()) { StringBuilder SQL = new StringBuilder(); SQL.Append("SELECT l.boxcode LINEBOXCODE, l.boxname LINEBOXname, l.putnum, l.sublineId, NULL AS bindCig, l.addressCode, tt.CIGCODE, tt.CIGNAME, tt.TOTQTY, ifnull(tw.NONQTY, 0) NONQTY, IFNULL(ty.FINQTY, 0) FINQTY, IFNULL(tp.PUTQTY,0) PUTQTY "); SQL.Append("FROM ( SELECT * "); SQL.Append(" FROM t_linebox "); SQL.Append(" WHERE isDynamicBox = 0 ) l "); SQL.Append("LEFT JOIN "); SQL.Append(" ( SELECT sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME, SUM(sd.QTY) TOTQTY "); SQL.Append(" FROM t_sorting_line_detail_task sd "); SQL.Append(" GROUP BY sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME "); SQL.Append(" ORDER BY lineboxname ) tt ON tt.LINEBOXCODE = l.boxCode "); SQL.Append("LEFT JOIN "); SQL.Append(" ( SELECT sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME, SUM(sd.QTY) NONQTY "); SQL.Append(" FROM t_sorting_line_task s "); SQL.Append(" JOIN t_sorting_line_detail_task sd ON s.ID = sd.TASKID "); SQL.Append(" WHERE s. STATUS = 0 OR s. STATUS = 1 "); SQL.Append(" GROUP BY sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME ) tw ON tt.LINEBOXCODE = tw.LINEBOXCODE AND tt.cigcode = tw.cigcode "); SQL.Append("LEFT JOIN "); SQL.Append(" ( SELECT sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME, SUM(sd.QTY) FINQTY "); SQL.Append(" FROM t_sorting_line_task s "); SQL.Append(" JOIN t_sorting_line_detail_task sd ON s.ID = sd.TASKID "); SQL.Append(" WHERE s. STATUS = 2 "); SQL.Append(" GROUP BY sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME ) ty ON tt.LINEBOXCODE = ty.LINEBOXCODE AND tt.cigcode = ty.cigcode "); SQL.Append("LEFT JOIN "); SQL.Append(" ( SELECT sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME, SUM(sd.QTY) PUTQTY "); SQL.Append(" FROM t_sorting_line_task s "); SQL.Append(" JOIN t_sorting_line_detail_task sd ON s.ID = sd.TASKID "); SQL.Append(" WHERE s. STATUS = 1 AND sd.`status` = 2 "); SQL.Append(" GROUP BY sd.LINEBOXCODE, sd.LINEBOXNAME, sd.CIGCODE, sd.CIGNAME ) tp ON tt.LINEBOXCODE = tp.LINEBOXCODE AND tt.cigcode = tp.cigcode "); SQL.Append(" "); SQL.Append(" "); SQL.Append(" "); SQL.Append("ORDER BY (l.boxcode * 1)"); cm.CommandText = SQL.ToString(); using (var dr = new SafeDataReader(cm.ExecuteReader())) { while (dr.Read()) { SortingLineBox sortingLineBox = new SortingLineBox(); sortingLineBox.bindCig = dr.GetString("bindCig"); sortingLineBox.shelfNum = dr.GetInt32("putnum"); //sortingLineBox.sortinglinecode = dr.GetString("sortinglinecode"); sortingLineBox.PlcAddress = dr.GetString("addressCode"); sortingLineBox.sublineId = dr.GetString("sublineId"); sortingLineBox.LineBoxCode = dr.GetString("LINEBOXCODE"); sortingLineBox.LineBoxName = dr.GetString("LINEBOXNAME"); sortingLineBox.Cigcode = dr.GetString("CigCode"); sortingLineBox.CigName = dr.GetString("CigName"); sortingLineBox.TOTQTY = dr.GetInt32("TOTQTY"); sortingLineBox.NONQTY = dr.GetInt32("NONQTY"); sortingLineBox.FINQTY = dr.GetInt32("FINQTY"); sortingLineBox.PutNum = dr.GetInt32("PUTQTY"); sortingLineBoxList.Add(sortingLineBox); } } } } return(sortingLineBoxList); }