//查格口状态 public bool SelectState(string plcid, string code) { try { DataTable tb = new DataTable(); string sqlStr = "SELECT * FROM t_prot where prot=(select prot from t_djmx WHERE ExpressNo='" + code + "'and plcid='" + plcid + "') "; tb = SqliteDbHelp.Query(sqlStr, null); if (tb != null) { if (tb.Rows.Count > 0) { if (tb.Rows[0]["sts"].ToString() == "0") { return(false); } else { return(true); } } } return(false); } catch (Exception ex) { return(false); } }
//添加扫描到的条码记录 public int InsertTask(string code, string time) { string sqlStr = "insert into t_djmx (ExpressNo,djsj)values('" + code + "','" + time + "') "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } }
public DataTable SelectOldData() { string selectStr = "select ExpressNo,plcid from t_djmx order by djsj desc limit 0,5 "; try { //DataSet data = SqliteDbHelp.ExecuteDataSet(sqlStr); DataTable currentDt = SqliteDbHelp.Query(selectStr, null); return(currentDt); } catch (Exception ex) { return(null); } }
// 更新端口的状态 public int UpdateErrProtSts(string prot, string text, string sts) { string sqlStr = "update t_prot set sts='" + sts + "', text='" + text + "' where prot='" + prot + "' "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } }
//查询格口号状态为 1或2的格口 public DataTable GetProtSend() { //1代表还没传送给WMS 2代表已经传送给WMS string sqlStr = "select * from t_prot where datasts='1' "; try { DataTable currentDt = SqliteDbHelp.Query(sqlStr, null); return(currentDt); } catch (Exception ex) { return(null); } }
//格口锁定清空数量 public int clearCount(string prot) { string sqlStr = "update t_prot set count=0 where prot='" + prot + "' "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } }
//查询前200条条码数据 public DataTable TwohundredData() { string selectStr = "select * from t_djmx order by djsj desc limit 0,100 "; //string sqlStr = "select top 200 * from t_djmx order by djsj desc ;select prot,(case sts when '0' then'解锁' when '1' then '锁定' end) sts,count from t_prot "; try { DataTable currentDt = SqliteDbHelp.Query(selectStr, null); return(currentDt); } catch (Exception ex) { return(null); } }
//更改格口数量 public int UpdateCount(int prot) { // string sqlStr = "update t_prot set count=(select ifnull(count,'0') from t_prot where prot='" + prot + "')+1 where prot='" + prot + "' "; string sqlStr = "update t_prot set count=count+1 where prot=" + prot; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } }
public int UpDataPlcData(string plcid, string expressNo, string endprot) { String sqlStr = " update t_djmx set endprot=" + endprot + ",endsts=1,endate =getdate() where" + " plcid=" + plcid + " and ExpressNo ='" + expressNo + "' "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } }
//更改实际格口表对应的条码状态 public int UpdateMatching(string plcid) { string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); string sqlStr = "update t_protstate set state='2', overdate ='" + time + "' " + "where id = (select id from t_protstate where plcid = '" + plcid + "' and state='1' order by id desc limit 0,1) "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { return(0); } }
//查询格口状态、数量 public DataTable GetTaskData() { string selectStr = "select prot,(case sts when '0' then'解锁' when '1' then '锁定' end) sts,count from t_prot "; //string sqlStr = "select top 200 * from t_djmx order by djsj desc ;select prot,(case sts when '0' then'解锁' when '1' then '锁定' end) sts,count from t_prot "; try { //DataSet data = SqliteDbHelp.ExecuteDataSet(sqlStr); DataTable currentDt = SqliteDbHelp.Query(selectStr, null); return(currentDt); } catch (Exception ex) { return(null); } }
//更改实际落袋口 public int UpdateEndprot(int endprot, string plcid) { string sqlStr = "update t_djmx set endprot=" + endprot + " where id=(select id " + "from t_djmx where plcid = '" + plcid + "' order by id desc limit 0,1) "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } }
//创建表单 public static void createDataBase() { //条码临时表 try { //条码记录表 string str = "select count(1) as c from sqlite_master where type='table' and name='t_djmx'"; string result = SqliteDbHelp.QueryReString(str, null).ToString(); if (result.Equals("0")) { SqliteDbHelp.ExecuteNonQuery(@"CREATE TABLE t_djmx(id integer primary key autoincrement, ExpressNo varchar(255),routeId varchar(255), prot varchar(255),sts varchar(255),djsj datetime,plcid varchar(255), endprot varchar(255),endsts varchar(255),wgh varchar(255), prtData varchar(255),result varchar(255),endate datetime,wmsseq varchar(255))", null); } //格口状态及数量表 str = "select count(1) as c from sqlite_master where type='table' and name='t_prot'"; result = SqliteDbHelp.QueryReString(str, null).ToString(); if (result.Equals("0")) { SqliteDbHelp.ExecuteNonQuery(@"CREATE TABLE t_prot(prot integer primary key autoincrement, sts varchar(255),datasts varchar(255),text varchar(255),count int)", null); List <string> stringsql = new List <string>(); for (int i = 1; i < 20; i++) { string sql = @"insert into t_prot (prot,sts,count,datasts )values('" + i + "','0','0','1') "; stringsql.Add(sql); } int a = SqliteDbHelp.ExecuteSqlTran(stringsql); } //实际反馈格口状态 str = "select count(1) as c from sqlite_master where type='table' and name='t_protstate'"; result = SqliteDbHelp.QueryReString(str, null).ToString(); if (result.Equals("0")) { SqliteDbHelp.ExecuteNonQuery(@"CREATE TABLE t_protstate(id integer primary key autoincrement, ExpressNo varchar(255),plcid varchar(255),begindate datetime,overdate datetime,state int)", null); } } catch (Exception e1) { return; } }
//查询实际格口是否写入 public DataTable MatchingData() { lock (this) { //1代表还没匹配到DB块的值 2代表已经匹配到值 string sqlStr = "Select * from t_protstate where state='1' "; try { DataTable currentDt = SqliteDbHelp.Query(sqlStr, null); return(currentDt); } catch (Exception ex) { return(null); } } }
public int UpDataWmsseqIdData(string seqId, string plcid) { lock (this) { string sqlStr = "update t_djmx set wmsseq='" + seqId + "' where plcid='" + plcid + "'"; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } } }
public int UpDataWcsWms(string prot) { lock (this) { string sqlStr = "update t_prot set datasts='2' where prot='" + prot + "' "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(0); } } }
// 根据 快递单号模糊查询 public DataTable queryByExpressNo(string expressNo, string start, string end) { DataTable tb = new DataTable(); try { string sqlStr = "select * from t_djmx where ExpressNo like '%" + expressNo + "' " + " and (djsj between '" + start + "' and '" + end + "') order by djsj desc"; tb = SqliteDbHelp.Query(sqlStr, null); return(tb); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(null); } }
public int UpdateDjmx(string ExpressNo, string plcid, string prot) { string time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); //string sqlStr = " insert into t_djmx (ExpressNo,plcid,prot )values('" + ExpressNo + "','" + plcid + "','" + prot + "') " ; //string sqlStr = "update t_djmx set plcid='" + plcid + "', prot='" + prot + "' where ExpressNo='" + ExpressNo + "' "; string sqlStr = " update t_djmx set plcid = '" + plcid + "',endsts='1', prot ='" + prot + "' where id = (select id " + "from t_djmx where ExpressNo = '" + ExpressNo + "' order by id desc limit 0,1); insert into t_protstate (ExpressNo,begindate,state,plcid )values('" + ExpressNo + "','" + time + "','1', '" + plcid + "') "; try { int i = SqliteDbHelp.ExecuteNonQuery(sqlStr, null); return(i); } catch (Exception ex) { return(0); } }
public DataTable GetPlcSednCode() { lock (this) { //1代表还没传送给WMS 2代表已经传送给WMS string sqlStr = "Select * from t_djmx where endsts='1' "; try { DataTable currentDt = SqliteDbHelp.Query(sqlStr, null); return(currentDt); } catch (Exception ex) { //SystemCommon.WriteLog("异常:" + ex.Message.ToString()); return(null); } } }
//删除超过7天数据 public void deleteOldData() { //删除t_code表中 大于当前时间7天的所有数据 //String sqlStr1 = "DELETE FROM t_code WHERE DATEDIFF(DAY,sj,getdate()) > 7"; String sqlStr2 = "DELETE FROM t_djmx WHERE DATEDIFF(DAY,djsj,getdate()) > 7"; // String sqlStr2 = "DELETE FROM t_djmx WHERE count(*)>10"; try { //SqliteDbHelp.ExecuteNonQuery(sqlStr1,null); SqliteDbHelp.ExecuteNonQuery(sqlStr2, null); } catch (Exception e) { return; } }