// 更新端口的状态 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); } }
//格口锁定清空数量 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); } }
//添加扫描到的条码记录 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 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 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 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 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 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 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 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 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); } }
//删除超过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; } }