public static void initMemoryData() { String sql = "SELECT a.*,b.regioncode,b.taskquantity FROM t_produce_poke a,t_produce_task b WHERE a.tasknum=b.tasknum AND a.tasknum IN( SELECT tasknum FROM (select DISTINCT tasknum from t_produce_poke t WHERE T.status='15' order by tasknum) WHERE ROWNUM <10 ) and status='15' ORDER BY b.regioncode,a.tasknum"; OracleDataReader myread = DataPublic.ReadDb(sql); try { String taskNum = "", taskQty = "", troughNum = "", pokeNum = "", regioncode = ""; while (myread.Read()) { taskNum = myread["tasknum"].ToString().Trim(); taskQty = myread["taskquantity"].ToString().Trim(); regioncode = myread["regioncode"].ToString().Trim(); if (!ht_taskinfo.ContainsKey(taskNum)) { // ht.Add(SortingPub.formatData(taskNum, 8), "0");//hashtable 存放任务号和0,表示未完成分拣 ht_taskinfo.Add(taskNum, regioncode + "-" + taskQty); } //存放 任务号对应的车组和任务数量 用 } } catch (Exception e) { } finally { myread.Close(); myread.Dispose(); } }
public static Hashtable GetHashTable() { //1. Hashtable ht = new Hashtable(); //init hashtable 取数据库车组在分拣中的 OracleDataReader myread1 = DataPublic.ReadDb("select regioncode,mainbelt from t_produce_task where state=20 group by regioncode,mainbelt "); if (myread1 != null) { while (myread1.Read()) { ht.Add(myread1["mainbelt"].ToString(), myread1["regioncode"].ToString()); } } String sql = "select * from ( select min(tasknum),regioncode,mainbelt from t_produce_task where state=10 group by regioncode,mainbelt order by min(tasknum)) where rownum<5"; OracleDataReader myread2 = DataPublic.ReadDb(sql); if (myread2 != null) { while (myread2.Read()) { if (ht.ContainsKey(myread1["mainbelt"].ToString())) { } else { ht.Add(myread2["mainbelt"].ToString(), myread2["regioncode"].ToString()); } } } return(ht); }
/* * 检查hash表中分拣数据状态,如果只有num个任务号未分拣,则开始新取拨烟数据。 * */ public int getTaskFinishNum() { String sql = "select count(1) num from (select distinct tasknum from t_produce_poke where status=15)"; int num = int.Parse(DataPublic.ExecuteScalar(sql).ToString()); return(num); //int retVal = 0; //retVal = this.htSize - hasFinish; //return retVal; }
public static int getTaskNum(int sortmachine) { string sql = "select distinct tasknum from t_produce_poke where status=15 and sortmachine=" + sortmachine; OracleDataReader myread = DataPublic.ReadDb(sql); if (myread != null && myread.Read()) { int tasknum = int.Parse(myread["tasknum"].ToString()); return(tasknum); } return(0); }
public static List <KeyValuePair <int, int> > initTask() { List <KeyValuePair <int, int> > list = new List <KeyValuePair <int, int> >(); string sql = "select distinct tasknum,sortmachine from t_produce_poke where status=15 order by tasknum"; OracleDataReader myread = DataPublic.ReadDb(sql); while (myread.Read()) { list.Add(new KeyValuePair <int, int>(int.Parse(myread["sortmachine"].ToString()), int.Parse(myread["tasknum"].ToString()))); } return(list); }
public static void updateCompleteStatus(int tasknum) { string sql = "update t_produce_poke set status=20 where tasknum=" + tasknum; DataPublic.ExeDb(sql); // sql = "select count(1) from t_produce_poke where status<>20 and tasknum= " + tasknum;// sortnum.ToString().Substring(1); // int exist= (int)DataPublic.ExecuteScalar(sql); //if (exist == 0) //{ sql = "update t_produce_task set state=30 ,finishtime=sysdate where tasknum=" + tasknum;// sortnum.ToString().Substring(1); DataPublic.ExeDb(sql); //} }
public void finishOrder(String taskno) { String sql = "update t_produce_task set state=30 where tasknum=" + taskno; DataPublic.ExecuteScalar(sql); //更新移库命令 //计算品牌库存 确定是否发送开箱 以及出库命令 //取订单里面的所有品牌 String sql2 = "select itemno from t_produce_taskline where tasknum=" + taskno; //取库存 String sql3 = "select sum(itemqty) from t_wms_storagearea_inoutline where cigarettecode=''"; //取烟仓阀值 String sql4 = "select THRESHOLD from t_procuce_sorttrough where CIGARETTECODE='' and troughtype=10"; //取重力式货架阀值 String sql5 = "select THRESHOLD from t_procuce_sorttrough where CIGARETTECODE='' and troughtype=20"; //对比库存与阀值 确定是否发送开箱以及立库出库 }
/// <summary> /// 执行一组 SQL 语句,并作为事务处理。 /// </summary> /// <param name="sqlList"></param> /// <returns></returns> public bool ExecuteSqlTrans(ArrayList sqlList) { //标志位,表示执行是否成功 bool blResult = false; try { innerTransaction = innerConnection.BeginTransaction(); innerCommand.Transaction = innerTransaction; for (int i = 0; i < sqlList.Count; i++) { if (!DataPublic.ISSQL(sqlList[i].ToString())) { //回滚 innerTransaction.Rollback(); return(false); } innerCommand.CommandText = sqlList[i].ToString(); innerCommand.ExecuteNonQuery(); } //提交数据库事务 innerTransaction.Commit(); blResult = true; } catch (Exception err) { blResult = false; //回滚 innerTransaction.Rollback(); //throw err; SaveErrLog(" [" + sqlList[0].ToString() + "] " + err.ToString());//保存错误日志 } return(blResult); }
public static void ResetAllTask() { ArrayList arrList = new ArrayList(); //String upOrderTable = "update t_produce_order set state='完成' where billcode in (select billcode from t_produce_task where tasknum='" + taskNum + "')"; String upTaskTable = "update t_produce_task set state='20'"; log.Write(upTaskTable); String upPokeTable = "update t_produce_poke set status='10' "; //arrList.Add(upOrderTable); arrList.Add(upTaskTable); arrList.Add(upPokeTable); try { DataPublic.ExecuteSqlTrans(arrList); } catch (Exception e) { Console.WriteLine("拨烟事物提交信息报错:" + e.StackTrace); //e.Message(); } }
public static bool updateTaskState(String taskNumfrom, String taskTo, String pokeStatus, String taskStatus) { bool retVal = false; ArrayList arrList = new ArrayList(); //String upOrderTable = "update t_produce_order set state='完成' where billcode in (select billcode from t_produce_task where tasknum='" + taskNum + "')"; String upTaskTable = "update t_produce_task set state='" + taskStatus + "' where tasknum>=" + taskNumfrom + " and taskNum<=" + taskTo; String upPokeTable = "update t_produce_poke set status='" + pokeStatus + "' where tasknum>=" + taskNumfrom + " and taskNum<=" + taskTo; //Console.WriteLine("拨烟事物提交更新报告:" + taskNum); //arrList.Add(upOrderTable); arrList.Add(upTaskTable); arrList.Add(upPokeTable); try { DataPublic.ExecuteSqlTrans(arrList); } catch (Exception e) { Console.WriteLine("拨烟事物提交信息报错:" + e.StackTrace); //e.Message(); } return(retVal); }
public static void WriteErrorLog(String errorCode) { String sql = "insert into t_produce_errorlog(Errorcode,createtime) values('" + errorCode + "',sysdate)"; DataPublic.ExeDbLog(sql); }
/* * 从数据库取拨烟数据,任务号(八位)订单总数量(四位)烟仓号A(两位)打几下(三位)烟仓号B(两位)打几下(三位);下一个任务号。。。 * */ public string getAllocateCigarDataFromDB(out String tasknums) { string retStr = ""; tasknums = ""; ht.Clear(); //String sql = "SELECT * FROM ( SELECT rownum seq, a.* FROM (select * from t_produce_poke t WHERE T.status='10' order by to_number(tasknum)) a )WHERE seq >=1 AND seq <200"; String sql = "SELECT a.*,b.regioncode,b.taskquantity FROM t_produce_poke a,t_produce_task b WHERE a.tasknum=b.tasknum AND a.tasknum IN( SELECT tasknum FROM (select DISTINCT tasknum from t_produce_poke t WHERE T.status='10' order by tasknum) WHERE ROWNUM <4 ) and status='10' ORDER BY b.regioncode,a.tasknum"; OracleDataReader myread = DataPublic.ReadDb(sql); try { String taskNum = "", taskQty = "", troughNum = "", pokeNum = "", regioncode = ""; String taskNumTemp = ""; String export = ""; //ht = new Hashtable(); while (myread.Read()) { taskNum = myread["tasknum"].ToString().Trim(); taskQty = myread["taskquantity"].ToString().Trim(); regioncode = myread["regioncode"].ToString().Trim(); export = myread["export"].ToString().Trim(); if (export.Equals("L")) { export = "1"; } else { export = "2"; } if (!taskNumTemp.Equals(taskNum)) { tasknums += "," + taskNum; //增加一个新任务号前,判断组装的字符串长度,所有拼接后的长度不能超过4000字节。 if (retStr.Length > 3500) { break; } if (!ht_taskinfo.ContainsKey(taskNum)) { ht.Add(SortingPub.formatData(taskNum, 8), "0");//hashtable 存放任务号和0,表示未完成分拣 ht_taskinfo.Add(taskNum, regioncode + "-" + taskQty); }//存放 任务号对应的车组和任务数量 用于更新分拣数据 retStr += ";" + SortingPub.formatData(taskNum, 8) + export; retStr += SortingPub.formatData(taskQty, 4); //已经取过的更新status为15 /*临时 * troughNum = myread["troughNum"].ToString().Trim(); * pokeNum = myread["pokeNum"].ToString().Trim(); * * retStr += SortingPub.formatData(troughNum, 2); * retStr += SortingPub.formatData(pokeNum, 3); * taskNumTemp = taskNum;*/ } troughNum = myread["machineseq"].ToString().Trim(); pokeNum = myread["pokeNum"].ToString().Trim(); retStr += SortingPub.formatData(troughNum, 2); retStr += SortingPub.formatData(pokeNum, 3); taskNumTemp = taskNum; } } finally { myread.Close(); myread.Dispose(); } if (ht != null) { htSize += ht.Count; } if (retStr.Length > 0) { retStr = retStr.Substring(1) + "."; } return(retStr); }
public void updateStatus(String taskNum, string status) { string upSql = "update t_produce_poke set status='" + status + "' where tasknum='" + taskNum + "' "; DataPublic.ExeDb(upSql); }
public static void updateStatus(int tasknum) { string sql = "update t_produce_poke set status=15 where tasknum=" + tasknum; DataPublic.ExeDb(sql); }
public static object[] GetTask() { object[] values = new object[73]; for (int i = 0; i < values.Length; i++) { values[i] = 0; } //按排序先取4个车组 排除主皮带号相同的车组 // // 通过 hashtable 过滤重复值 //Hashtable ht = new Hashtable(); //for (int i = 0; i < result.length; i++) //{ // if (ht.ContainsKey("")) // { } // else // { ht.Add("mainbelt", "regioncode"); // 更新对应车组状态为分拣中 // } //} //根据车组取订单发送 同时更新订单状态 string sql = "select tasknum from(select distinct tasknum from t_produce_poke where status=10 order by tasknum) where rownum=1"; //writelog.Write(sql); int tasknum = int.Parse(DataPublic.ExecuteScalar(sql).ToString()); //OracleDataReader reader = DataPublic.ReadDb(sql); //writelog.Write("search sql" + sql); //if (reader != null) //{ // if (reader.Read()) // { // tasknum = int.Parse(reader[tasknum].ToString()); // } // reader.Close(); // reader.Dispose(); //} if (tasknum != 0) { //sql = "select decode(min(sortnum),'',0,min(sortnum)) from t_produce_poke where status=10 and tasknum=" + tasknum; //int sortnum = int.Parse(DataPublic.ExecuteScalar(sql).ToString()); //if (sortnum != 0) //{ sql = "select sortnum,sortmachine, sum(pokenum) pokenum,machineseq,sortmachine,tasknum from t_produce_poke where tasknum=" + tasknum + " group by tasknum, sortnum,sortmachine,machineseq"; // writelog.Write(sql);878777 OracleDataReader myread = DataPublic.ReadDb(sql); try { int i = 0; int totalcount = 0; while (myread.Read()) { if (i == 0) { values[0] = int.Parse(myread["tasknum"].ToString()); values[1] = int.Parse(myread["sortmachine"].ToString()); values[3] = 0; values[72] = 1; } totalcount += int.Parse(myread["pokenum"].ToString()); int troughnum = int.Parse(myread["machineseq"].ToString()); values[3 + troughnum] = int.Parse(myread["pokenum"].ToString()); //if ((int)values[1] == 1) //{ // values[4] = 0; // values[5] = 0; // values[6] = 0; // values[7] = 0; // values[8] = 0; // values[9] = 0; // values[10] = 0; // values[11] = 0; // values[12] = 0; // values[13] = 0; // values[14] = 0; // values[15] = 0; // values[16] = 0; // values[17] = 0; // values[18] = 0; // values[19] = 0;//4-19 分拣机一 //} //else if ((int)values[1] == 2) //{ // values[20] = 0;//20-49分拣机二 // values[21] = 0; // values[22] = 0; // values[23] = 0; // values[24] = 0; // values[25] = 0; // values[26] = 0; // values[27] = 0; // values[28] = 0; // values[29] = 0; // values[30] = 0; // values[31] = 0; // values[32] = 0; // values[33] = 0; // values[34] = 0; // values[35] = 0; // values[36] = 0; // values[37] = 0; // values[38] = 0; // values[39] = 0; // values[40] = 0; // values[41] = 0; // values[42] = 0; // values[43] = 0; // values[44] = 0; // values[45] = 0; // values[46] = 0; // values[47] = 0; // values[48] = 0; // values[49] = 0; //} //else //{ // values[50] = 0;//分拣三 // values[51] = 0; // values[52] = 0; // values[53] = 0; // values[54] = 0; // values[55] = 0; // values[56] = 0; // values[57] = 0; // values[58] = 0; // values[59] = 0; // values[60] = 0; // values[61] = 0; // values[62] = 0; // values[63] = 0; // values[64] = 0; // values[65] = 0; // values[66] = 0; // values[67] = 0; // values[68] = 0; // values[69] = 0; // values[70] = 0; // values[71] = 0; //} } values[2] = totalcount; } catch (Exception e) { writelog.Write(e.Message); if (e.InnerException != null) { writelog.Write(e.InnerException.Message); } } finally { myread.Close(); myread.Dispose(); } // } } return(values); }