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