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