public DataTable GetLogDaTable(string userid, string date, string enddate)
{
DataTable table = new DataTable();
string sqlStr = " select * from [dbo].[t_mes] t"
+ " left join [dbo].[t_goods] d on t.goodsid =d.goodid "
+ " left join [dbo].[t_user] u on t.userid = t.userid where convert(varchar(100),begdate,23) >='" + date + "' and convert(varchar(100),begdate,23)<='" + enddate + "'";
if (userid.Trim().Length > 0)
{
sqlStr = sqlStr + " and username ='******'";
}
try
{
table = SqlServerHelper.ExecuteDataTable(sqlStr);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
return(table);
}
public int UpDataPlcData(string plcid, string expressNo, string endprot)
{
//string sqlStr = "update t_djmx set endprot='" + endprot + "',endsts='1',endate =getdate() where "
// + " plcid=(selec top 1 plcid from t_djmx plcid='" + plcid + "' and endsts='0' order by djsj desc) ";
// string sqlStr = "update t_djmx set endprot='" + endprot + "',endsts='1',endate =getdate() " +
// "where plcid='" +plcid +"' and endsts='0' and ExpressNo = (select top 1 ExpressNo from t_djmx where plcid='" +
// plcid + "' and endsts='0' order by djsj desc)";
//select top 1 ExpressNo from t_djmx where plcid=2 and endsts=1 order by djsj desc
// update t_djmx set endprot=9,endsts=1,endate =getdate() where plcid=4 and ExpressNo = 049158651324
String sqlStr = " update t_djmx set endprot=" + endprot + ",endsts=1,endate =getdate() where plcid=" + plcid + " and ExpressNo = " + expressNo;
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public string GetWmsSeq()
{
DataTable tb = new DataTable();
try
{
string sqlStr = "select (NEXT VALUE FOR seqID )as id ";
tb = SqlServerHelper.ExecuteDataTable(sqlStr);
if (tb != null)
{
if (tb.Rows.Count > 0)
{
return(tb.Rows[0]["id"].ToString());
}
}
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
// return "0;
}
Random ran = new Random();
return((ran.Next(1, 100)).ToString());
}
// 执行dml
private static int execDml(string sqlStr, SqlParameter[] commandParameters = null)
{
try
{
int i = SqlServerHelper.ExecuteNonQuery(CommandType.Text, sqlStr, commandParameters);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message);
return(0);
}
}
public int UpdateProtStsSum(string prot, int sum)
{
string sqlStr = "update t_prot set count='" + sum + " where prot='" + prot + "'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public int InsertTask(string code)
{
string sqlStr = "insert into t_code (ExpressNo)values('" + code + "')";// set endprot='" + endprot + "',endsts='1',endate =getdate() where plcid='" + plcid + "' and endsts='0'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
/// <summary>
/// 更新端口的状态
/// </summary>
/// <param name="prot"></param>
/// <param name="sts"></param>
/// <returns></returns>
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 = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public int UpdateProtSendSts(string prot)
{
string sqlStr = "update t_prot set datasts='0' where prot='" + prot + "'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public DataTable GetPlcSednCode()
{
lock (this)
{
string sqlStr = "Select * from t_djmx where endsts='1' ";///and ExpressNo!='noread'
try
{
return(SqlServerHelper.ExecuteDataTable(sqlStr));
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
}
}
public DataTable GetwmsSeqData(string wmsseq)
{
lock (this)
{
string sqlStr = "Select plcid from t_djmx where wmsseq='" + wmsseq + "' ";
try
{
return(SqlServerHelper.ExecuteDataTable(sqlStr));
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
}
}
public int UpdateCount(int prot)
{
//update t_prot set count=(select ifnull(count,'0') from t_prot where prot=111)+1 where prot=111
string sqlStr = "update t_prot set count=count+1 where prot=" + prot;
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public int UpdateEndprot(int endprot, string plcid)
{
//update t_djmx set endprot=4 where plcid=10417
string sqlStr = "update t_djmx set endprot=" + endprot + " where plcid=" + plcid;
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
public DataTable SelectSum(string prot)
{
DataTable tb = new DataTable();
try
{
string sqlStr = "select * from t_prot where prot='" + prot + "'";
tb = SqlServerHelper.ExecuteDataTable(sqlStr);
return(tb);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(null);
}
}
public int UpDataWmsPlcData(string plcid)
{
lock (this)
{
string sqlStr = "update t_djmx set endsts='2' where plcid='" + plcid + "'";
try
{
int i = SqlServerHelper.ExecuteNonQuery(sqlStr);
return(i);
}
catch (Exception ex)
{
SystemCommon.WriteLog("异常:" + ex.Message.ToString());
return(0);
}
}
}
