public Stream GetProduct(string querystr)
{
if (mysqlEngine.myCon == null)
{
mysqlEngine.OpenMysql();
}
TBODY response = new TBODY();
response.msgcode = 0;
response.errinfo = "";
response.status = -1;
QueryCmd query = Common.DeserializeJsonToObject <QueryCmd>(querystr);
string sql = "";
string sql2 = "";
sql = Common.CreateSqlStr(query, ref sql2);
QueryResult qr = new QueryResult();
qr.page = query.page;
if (mysqlEngine.getResultset(sql, sql2, ref qr))
{
response.status = 0;
}
else
{
response.status = 1;
}
response.data = qr;
return(new MemoryStream(Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(response))));
}
public Stream UserAuth(string tbody)
{
if (mysqlEngine.myCon == null)
{
mysqlEngine.OpenMysql();
}
UserEntity user = Common.DeserializeJsonToObject <UserEntity>(tbody);
TBODY response = new TBODY();
response.msgcode = 0;
response.errinfo = "";
response.status = -1;
mysqlEngine.Authed(user, ref response);
return(new MemoryStream(Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(response))));
}
public void Authed(UserEntity user, ref TBODY response)
{
string sqlstr = "select * from users where username = '******'";
MySqlDataReader reader = null;
MySqlCommand mySqlCommand = getSqlCommand(sqlstr, myCon);
try
{
reader = mySqlCommand.ExecuteReader();
while (reader.Read())
{
if (reader.HasRows)
{
UserEntity auth = new UserEntity();
auth.username = reader.GetString(1);
auth.password = reader.GetString(2);
auth.flag = reader.GetInt32(3);
if (auth.username == user.username && auth.password == user.password)
{
response.status = 0;
}
else
{
response.status = 1;
response.errinfo = "用户名或密码不正确！";
}
response.data = auth;
return;
}
response.errinfo = "无用户记录！";
response.status = 1;
}
}
catch (Exception)
{
response.errinfo = "未知错误，查询失败！";
response.status = 1;
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public bool getStatistic(string outdata, ref TBODY resp)
{
List <int> statstic = new List <int>();
using (MySqlConnection con = new MySqlConnection(M_str_sqlcon))
{
con.Open();
string[] resultString = outdata.Split(new string[] { "|" }, StringSplitOptions.None);
string sqlstr = "select count(*) from outbound where optdate >= '" + resultString[0] + "' and optdate <= '" + resultString[1] + "' and mac like '3071B2%'";
using (MySqlCommand cmd = new MySqlCommand(sqlstr, con))
{
//MySqlTransaction trans = con.BeginTransaction();
try
{
//cmd.Connection = trans.Connection;
//cmd.Transaction = trans;
cmd.CommandText = sqlstr;
statstic.Add(Convert.ToInt32(cmd.ExecuteScalar()));
sqlstr = "select count(*) from outbound where optdate >= '" + resultString[0] + "' and optdate <= '" + resultString[1] + "' and mac like '000A5A%'";
cmd.CommandText = sqlstr;
statstic.Add(Convert.ToInt32(cmd.ExecuteScalar()));
sqlstr = "select count(*) from outbound where optdate >= '" + resultString[0] + "' and optdate <= '" + resultString[1] + "'";
cmd.CommandText = sqlstr;
statstic.Add(Convert.ToInt32(cmd.ExecuteScalar()) - statstic[0] - statstic[1]);
}
catch
{
resp.errinfo = "统计出现未知错误.";
resp.data = statstic;
return(false);
}
finally
{
}
}
}
resp.data = statstic;
return(true);
}
public Stream GetStatistics(string data)
{
if (mysqlEngine.myCon == null)
{
mysqlEngine.OpenMysql();
}
TBODY querybody = Common.DeserializeJsonToObject <TBODY>(data);
TBODY response = new TBODY();
response.msgcode = 0;
response.errinfo = "";
if (mysqlEngine.getStatistic(querybody.data.ToString(), ref response))
{
response.status = 0;
}
else
{
response.status = -1;
}
return(new MemoryStream(Encoding.UTF8.GetBytes(JsonConvert.SerializeObject(response))));
}
public bool InsertOutRecord(string outdata, ref TBODY resp)
{
string sqlstr = "";
bool flag = false;
bool dup = false;
ComMessage_t data = new ComMessage_t();
string[] resultString = outdata.Split(new string[] { "|" }, StringSplitOptions.None);
switch (resultString[0])
{
case "sn":
sqlstr = "select id from outbound where sn = '" + resultString[1] + "'";
if (!getOutValidate(sqlstr))
{
resp.errinfo = "重复的设备，该设备已出库...";
dup = true;
//return false;
}
sqlstr = "select * from packing where sn = '" + resultString[1] + "'";
break;
case "mac":
sqlstr = "select id from outbound where mac = '" + resultString[1] + "'";
if (!getOutValidate(sqlstr))
{
resp.errinfo = "重复的设备，该设备已出库...";
dup = true;
//return false;
}
sqlstr = "select * from packing where mac = '" + resultString[1] + "'";
break;
}
//从包装部数据表查询出库数据
MySqlCommand mySqlCommand = null;
MySqlDataReader reader = null;
try
{
lock (lockobj)
{
mySqlCommand = getSqlCommand(sqlstr, myCon);
reader = mySqlCommand.ExecuteReader();
while (reader.Read())
{
if (reader.HasRows)
{
data.username = reader.GetString(1);
data.boxid = reader.GetString(2);
data.orderid = reader.GetString(3);
data.mac = reader.GetString(4);
data.wifimac = reader.GetString(5);
data.gpsn = reader.GetString(6);
data.sn = reader.GetString(7);
data.optdate = reader.GetString(8);
data.softversion = reader.GetString(9);
if (data.softversion != resultString[2])
{
flag = false;
resp.errinfo = "软件版本不一致，请核对软件版本[" + data.softversion + "]";
break;
}
flag = true;
}
}
}
}
catch (Exception)
{
flag = false;
resp.errinfo = "从包装数据表提取数据失败。";
}
finally
{
if (reader != null)
{
reader.Close();
}
}
if (data == null)
{
resp.errinfo = "从包装数据表无改设备。";
return(false);
}
if (!dup && flag)
{
//将数据插入到出库表单
sqlstr = string.Format("INSERT INTO outbound(username,boxid,orderid,mac,wifimac,gpsn,sn,optdate,destination,softversion) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", data.username, data.boxid.ToUpper(), data.orderid, data.mac.ToUpper(), data.wifimac == null ? "" : data.wifimac.ToUpper(), data.gpsn == null ? "" : data.gpsn, data.sn.ToUpper(), DateTime.Now.ToString("yyyy-MM-dd"), resultString[3], data.softversion);
if (!Execute(sqlstr))
{
resp.errinfo = "记录数据失败。";
return(false);
}
}
resp.data = data;
return(flag && !dup);
}