public static object[] GetSendTasks(decimal isCompleted, out StringBuilder outStr)
{
using (DZEntities en = new DZEntities())
{
object[] data = new object[7];
StringBuilder sb = new StringBuilder();
for (int i = 0; i < data.Length; i++)
{
data[i] = 0;
}
UnnormalInfo info = new UnnormalInfo();
string sqlStr = "select sortnum,sum(pokenum) as num from t_un_poke where ctype=1 and status = 10 group by sortnum order by sortnum";
info = en.ExecuteStoreQuery <UnnormalInfo>(sqlStr).FirstOrDefault();
if (info != null)
{
data[0] = info.SortNum;
data[1] = info.SortNum;
data[2] = info.Num;
data[3] = info.Num;
data[4] = 0;
data[5] = 0;
data[6] = 1;
sb.AppendLine(data[0] + "，数量：" + data[2] + "，任务发送标志位：" + data[6]);
}
//int count = en.T_UN_POKE_HUNHE.Where(item => item.SORTNUM == info.SortNum).Count();
else
{
sb.AppendLine("特异型烟道暂无任务");
}
outStr = sb;
return(data);
}
}
public static object[] GetSendTask(decimal isCompleted, out StringBuilder outStr)
{
using (DZEntities en = new DZEntities())
{
object[] data = new object[6];
StringBuilder sb = new StringBuilder();
for (int i = 0; i < data.Length; i++)
{
data[i] = 0;
}
List <T_PRODUCE_REPLENISHPLAN> list = new List <T_PRODUCE_REPLENISHPLAN>();
string sqlStr = "select * from T_PRODUCE_REPLENISHPLAN where iscompleted = 10 and cigarettecode in(" +
"select cigarettecode from t_produce_sorttrough where groupno=3) and troughnum<=6 order by id";
list = en.ExecuteStoreQuery <T_PRODUCE_REPLENISHPLAN>(sqlStr).ToList();
data[0] = list[0].TASKNUM;
data[1] = Convert.ToDecimal(list[0].TROUGHNUM);
data[2] = list[0].JYCODE;
data[3] = 0;
data[4] = 0;
data[5] = 1;
sb.AppendLine("任务号：" + data[0] + "，" + data[1] + "号通道补烟，任务发送标志位：" + data[5]);
outStr = sb;
return(data);
}
}
public static List <Replenish> GetReplenishByCigName(int index, string condition)
{
using (DZEntities en = new DZEntities())
{
List <Replenish> list = new List <Replenish>();
string sqlStr = "";
if (condition == "")
{
sqlStr = "select tr.TASKNUM,tr.CIGARETTECODE,tr.CIGARETTENAME,tr.JYCODE,tr.REPLENISHQTY,tr.STATUS,tr.TROUGHNUM " +
"from T_PRODUCE_REPLENISHPLAN tr order by TASKNUM,TROUGHNUM";
}
else
{
if (index == 0)
{
sqlStr = "select tr.TASKNUM,tr.CIGARETTECODE,tr.CIGARETTENAME,tr.JYCODE,tr.REPLENISHQTY,tr.STATUS,tr.TROUGHNUM " +
"from T_PRODUCE_REPLENISHPLAN tr where tr.CIGARETTENAME like '" + condition + "%' order by TASKNUM,TROUGHNUM";
}
else
{
sqlStr = "select tr.TASKNUM,tr.CIGARETTECODE,tr.CIGARETTENAME,tr.JYCODE,tr.REPLENISHQTY,tr.STATUS,tr.TROUGHNUM " +
"from T_PRODUCE_REPLENISHPLAN tr where tr.JYCODE like '" + condition + "%' order by TASKNUM,TROUGHNUM";
}
}
list = en.ExecuteStoreQuery <Replenish>(sqlStr).ToList();
return(list);
}
}
/// <summary>
/// 获取新的ID
/// </summary>
/// <returns></returns>
private decimal GetTroughId()
{
using (DZEntities en = new DZEntities())
{
return(en.ExecuteStoreQuery <decimal>(Str_SortTId, null).FirstOrDefault());
}
}
public static List <OnCigOrderInfo> GetOneCigOrder()
{
using (DZEntities en = new DZEntities())
{
List <OnCigOrderInfo> list = new List <OnCigOrderInfo>();
//var query = en.T_UN_POKE.GroupBy(item => new { item.BILLCODE, item.SORTNUM }).Where(item => item.Sum(items => items.POKENUM) == 1).Select(item =>
// new { BillCode = item.Key.BILLCODE, Sortnum = item.Key.SORTNUM, Num = item.Sum(items => items.POKENUM) }).OrderBy(item => item.Sortnum).ToList();
//list = (from task in en.T_UN_TASK
// join poke in query on task.SORTNUM equals poke.Sortnum
// select new OnCigOrderInfo
// {
// RegionCode = task.REGIONCODE,
// RegionDesc = task.REGIONDESC,
// BillCode = task.BILLCODE,
// CustomerCode = task.CUSTOMERCODE,
// CustomerName = task.CUSTOMERNAME,
// Num = poke.Num ?? 0,
// SortNum = poke.Sortnum ?? 0
// }).OrderBy(item => item.SortNum).ToList();
string sqlStr = "select task.regioncode,task.regiondesc,task.billcode,task.customercode,task.customername,poke.num,POKE.SORTNUM from t_un_task task, " +
"(select billcode,sortnum,sum(pokenum) as num from t_un_poke group by billcode,sortnum having sum(pokenum)=1 order by sortnum) poke " +
"where poke.sortnum=task.sortnum order by task.sortnum";
list = en.ExecuteStoreQuery <OnCigOrderInfo>(sqlStr).ToList();
return(list);
}
}
public static decimal GetMinSortNum()
{
using (DZEntities en = new DZEntities())
{
string sqlStr = "select min(sortnum) as num from t_un_poke where ctype=1 and status = 15 group by sortnum order by sortnum";
return(en.ExecuteStoreQuery <decimal>(sqlStr).FirstOrDefault());
}
}
public static List <ReplenishInfo> GetReplenish(decimal condition)
{
using (DZEntities en = new DZEntities())
{
string sqlStr = "";
//Func<T_PRODUCE_REPLENISHPLAN, bool> fun;
if (condition == 0)
{
sqlStr = "select r.TROUGHNUM as ThroughNum,r.CIGARETTENAME,JYCODE,REPLENISHQTY,TASKNUM,r.CIGARETTECODE from t_produce_replenishplan r,t_produce_sorttrough s " +
" where s.machineseq=r.troughnum and s.cigarettecode=r.cigarettecode " +
" and s.groupno=3 order by r.id";
}
else
{
sqlStr = "select r.TROUGHNUM as ThroughNum,r.CIGARETTENAME,JYCODE,REPLENISHQTY,TASKNUM,r.CIGARETTECODE from t_produce_replenishplan r,t_produce_sorttrough s " +
" where s.machineseq=r.troughnum and s.cigarettecode=r.cigarettecode " +
" and s.groupno=3 and iscompleted=" + condition + " order by r.id";
}
List <ReplenishInfo> list = new List <ReplenishInfo>();
list = en.ExecuteStoreQuery <ReplenishInfo>(sqlStr).ToList();
//var query = en.T_PRODUCE_REPLENISHPLAN.Where(fun).Select(item => new
//{
// ThroughNum = item.TROUGHNUM,
// CigaretteName = item.CIGARETTENAME,
// JYCode = item.JYCODE,
// ReplenishQTY = item.REPLENISHQTY ?? 0,
// TaskNum = item.TASKNUM,
// cigarettecode = item.CIGARETTECODE
//}).OrderBy(item => Convert.ToDecimal(item.TaskNum)).ToList();
//list = query.Join(en.T_PRODUCE_SORTTROUGH, plan => new { plan.ThroughNum, plan.cigarettecode }, through => new { through.TROUGHNUM, through.CIGARETTECODE }, (plan, through) =>
// new
// {
// throughNum = plan.ThroughNum,
// CigaretteName = plan.CigaretteName,
// JYCode = plan.JYCode,
// ReplenishQTY = plan.ReplenishQTY,
// TaskNum = plan.TaskNum,
// groupNo = through.GROUPNO
// }).Where(item => item.groupNo == 3).Select(item => new ReplenishInfo
// {
// ThroughNum = item.throughNum,
// CigaretteName = item.CigaretteName,
// JYCode = item.JYCode,
// ReplenishQTY = item.ReplenishQTY,
// TaskNum = item.TaskNum
// }).OrderBy(item => Convert.ToDecimal(item.TaskNum)).ToList();
return(list);
}
}
public static List <T_PRODUCE_REPLENISHPLAN> GetReplenishplan()
{
using (DZEntities en = new DZEntities())
{
List <T_PRODUCE_REPLENISHPLAN> list = new List <T_PRODUCE_REPLENISHPLAN>();
string sqlStr = "select * from T_PRODUCE_REPLENISHPLAN where iscompleted != 20 and cigarettecode in(" +
"select cigarettecode from t_produce_sorttrough where groupno=3) and troughnum<=6 order by id";
list = en.ExecuteStoreQuery <T_PRODUCE_REPLENISHPLAN>(sqlStr).ToList();
return(list);
}
}
/// <summary>
/// 获取已发送未完成的件烟信息
/// </summary>
/// <param name="taskNum"></param>
/// <returns></returns>
public static List <T_PRODUCE_REPLENISHPLAN> GetFinishedReplenishplan()
{
using (DZEntities en = new DZEntities())
{
List <T_PRODUCE_REPLENISHPLAN> list = new List <T_PRODUCE_REPLENISHPLAN>();
string sqlStr = "select * from T_PRODUCE_REPLENISHPLAN where cigarettecode in (select distinct cigarettecode from T_PRODUCE_SORTTROUGH where groupno = 3 ) and troughnum<=6 and ISCOMPLETED = 20 order by id desc";
list = en.ExecuteStoreQuery <T_PRODUCE_REPLENISHPLAN>(sqlStr).Take(10).ToList();
//list = en.T_PRODUCE_REPLENISHPLAN.Where(item => (item.TROUGHNUM == "1" || item.TROUGHNUM == "2" || item.TROUGHNUM == "3" || item.TROUGHNUM == "4" || item.TROUGHNUM == "5" || item.TROUGHNUM == "6") && item.ISCOMPLETED == 15 && item.STATUS == 1).ToList();
return(list);
}
}
public static List <SortInfo> GetSortInfo()
{
using (DZEntities en = new DZEntities())
{
List <SortInfo> list = new List <SortInfo>();
string sqlStr = "select a.ctype,a.machineseq,a.cigarettecode,CigaretteName,decode(SortedNum,null,0,SortedNum) as SortedNum,decode(SortedNum,null,TotalNum,(TotalNum-SortedNum)) as UnSortNum,TotalNum from " +
" (select ctype,machineseq,cigarettecode,i.itemname as CigaretteName,sum(pokenum) as TotalNum from t_un_poke p,t_wms_item i where p.cigarettecode=i.itemno " +
" group by ctype,machineseq,cigarettecode,i.itemname order by ctype,machineseq) a left join " +
" (select ctype,machineseq,cigarettecode,sum(pokenum) as SortedNum from t_un_poke where status=20 group by ctype,machineseq,cigarettecode " +
" order by ctype,machineseq) b on a.ctype=b.ctype and a.machineseq=b.machineseq and a.cigarettecode=b.cigarettecode";
list = en.ExecuteStoreQuery <SortInfo>(sqlStr).ToList();
return(list);
}
}
public static List <HunheInfo> GetHunheData(decimal machinseq)
{
using (DZEntities en = new DZEntities())
{
List <decimal> t = new List <decimal>();
t = en.T_PRODUCE_SORTTROUGH.Where(item => /*item.GROUPNO == 2 &&*/ item.CIGARETTETYPE == 40).GroupBy(item => item.MACHINESEQ).OrderBy(item => item.Key).Select(item => item.Key ?? 0).ToList();
List <HunheInfo> list = new List <HunheInfo>();
string sqlStr = "";
if (machinseq == 0)
{
if (t.Count == 2)
{
//sqlStr = "SELECT * FROM T_PRODUCE_SORTTROUGH S,T_UN_POKE P WHERE S.TROUGHNUM=P.TROUGHNUM AND (S.MACHINESEQ=" + t[0] +
// " or S.MACHINESEQ=" + t[1] + ") AND CIGARETTETYPE = 40 "+
// " ORDER BY SORTNUM";
sqlStr = "SELECT P.MACHINESEQ,T.CUSTOMERNAME,S.CIGARETTENAME,P.POKENUM,P.SORTNUM,T.REGIONDESC FROM T_PRODUCE_SORTTROUGH S,T_UN_POKE P,T_UN_TASK T" +
" WHERE S.TROUGHNUM=P.TROUGHNUM AND T.SORTNUM=P.SORTNUM" +
" and (S.MACHINESEQ=" + t[0] + " OR S.MACHINESEQ= " + t[1] + ") AND CIGARETTETYPE = 40 ORDER BY P.SORTNUM,P.TROUGHNUM";
}
else if (t.Count == 1)
{
//sqlStr = "SELECT * FROM T_PRODUCE_SORTTROUGH S,T_UN_POKE P WHERE S.TROUGHNUM=P.TROUGHNUM AND S.MACHINESEQ=" + t[0] +
// " AND CIGARETTETYPE = 40 AND "+
// " ORDER BY SORTNUM";
sqlStr = "SELECT P.MACHINESEQ,T.CUSTOMERNAME,S.CIGARETTENAME,P.POKENUM,P.SORTNUM,T.REGIONDESC FROM T_PRODUCE_SORTTROUGH S,T_UN_POKE P,T_UN_TASK T" +
" WHERE S.TROUGHNUM=P.TROUGHNUM AND T.SORTNUM=P.SORTNUM" +
" and S.MACHINESEQ=" + t[0] + " AND CIGARETTETYPE = 40 ORDER BY P.SORTNUM,P.TROUGHNUM";
}
}
else
{
if (machinseq == 1)
{
sqlStr = "SELECT P.MACHINESEQ,T.CUSTOMERNAME,S.CIGARETTENAME,P.POKENUM,P.SORTNUM,T.REGIONDESC FROM T_PRODUCE_SORTTROUGH S,T_UN_POKE P,T_UN_TASK T" +
" WHERE S.TROUGHNUM=P.TROUGHNUM AND CTYPE=1 AND S.GROUPNO=1 AND T.SORTNUM=P.SORTNUM" +
" and S.MACHINESEQ=" + machinseq + " AND CIGARETTETYPE = 40 ORDER BY P.SORTNUM,P.TROUGHNUM";
}
else
{
sqlStr = "SELECT P.MACHINESEQ,T.CUSTOMERNAME,S.CIGARETTENAME,P.POKENUM,P.SORTNUM,T.REGIONDESC FROM T_PRODUCE_SORTTROUGH S,T_UN_POKE P,T_UN_TASK T" +
" WHERE S.TROUGHNUM=P.TROUGHNUM AND CTYPE=2 AND S.GROUPNO=2 AND T.SORTNUM=P.SORTNUM" +
" and S.MACHINESEQ=" + machinseq + " AND CIGARETTETYPE = 40 ORDER BY P.SORTNUM,P.TROUGHNUM";
}
}
list = en.ExecuteStoreQuery <HunheInfo>(sqlStr).ToList();
return(list);
}
}
public static List <MixInfos> GetMixCig4(decimal machineSeq, decimal groupNo, decimal pullState)
{
using (DZEntities en = new DZEntities())
{
//en.T_UN_POKE_HUNHE.Where(item=>item.PULLSTATUS==0&&item.MACHINESEQ==machineSeq).Select(item=>new CigarettInfo{ BigBoxCode="0", CigCode=item.CIGARETTECODE})
List <MixInfos> list = new List <MixInfos>();
//list = (from a in en.T_UN_POKE_HUNHE
// join b in en.T_PRODUCE_SORTTROUGH on a.CIGARETTECODE equals b.CIGARETTECODE
// where b.GROUPNO == groupNo && a.PULLSTATUS == pullState
// select new MixInfo { PokeID = a.POKEID ?? 0, SortNum = a.SORTNUM ?? 0, CigCode = b.CIGARETTECODE, CigName = b.CIGARETTENAME, PokeNum = 1 })
// .OrderByDescending(item=>item.PokeID).OrderByDescending(item =>item.SortNum )
// .Take(15).ToList();
string sqlStr = "select pokeid,sortnum,p.cigarettecode as cigcode,cigarettename as cigname,t.troughnum as throughnum,sortnum,1 as pokenum from t_un_poke_hunhe p, t_produce_sorttrough t where t.cigarettecode=p.cigarettecode " +
"and t.machineseq=1 and pullstatus=1 order by p.sortnum desc,t.troughnum desc";
list = en.ExecuteStoreQuery <MixInfos>(sqlStr).Take(500).ToList();
return(list);
}
}