public static string sear(string bh, string companyID, string province, string city, string country)
{
string sql1 = "";
if (bh != "")
{
sql1 += " and bh='" + bh + "'";
}
if (companyID != "0")
{
sql1 += " and companyID=" + companyID;
}
if (province != "省份")
{
sql1 += " and province like '%" + province + "%'";
}
if (city != "地级市")
{
sql1 += " and city like '%" + city + "%'";
}
if (country != "市、县级市")
{
sql1 += " and country like '%" + country + "%'";
}
string sql = "select id,bh,mechineName,province,city,country,(select name from asm_company where asm_mechine.companyID=asm_company.id) companyName,case zt when '1' then '禁用' when '2' then '正常' when '3' then '过期' else '其他' end zt from asm_mechine where 1=1 " + sql1;
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dt));
}
return("1");
}
public static string getMechine(string mechineID)
{
string mechineInfo = null;
try
{
mechineInfo = RedisHelper.GetRedisModel <string>(mechineID + "_mechineInfoSet");
}
catch
{
}
if (string.IsNullOrEmpty(mechineInfo))
{
string sql = "select am.*,ac.p1,ac.p2,ac.p3,ac.p4,ac.p5,ac.p6,ac.p7,ac.p8,ac.p9,ac.p10,am.setTem,'' videoListNo,'' productTypeNo,'' androidProductNo,'' priceSwitch from asm_mechine am left join asm_company ac on am.companyID=ac.id where am.id='" + mechineID + "'";
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
RedisHelper.SetRedisModel <string>(mechineID + "_mechineInfoSet", OperUtil.DataTableToJsonWithJsonNet(dt));
mechineInfo = OperUtil.DataTableToJsonWithJsonNet(dt);
}
}
return(mechineInfo);
}
public static string sear(string name, string phone, string companyID, string operaID)
{
string sql1 = "";
if (name.Trim() != "")
{
sql1 += " and (name like '%" + name + "%' or nickName like '%" + name + "%')";
}
if (phone.Trim() != "")
{
sql1 += " and linkphone like '%" + phone + "%'";
}
//if (operaID!="0")
//{
// sql1 += " and agentID="+operaID;
//}
string sql = "select *,(select name from asm_role ar where ar.id=ao.qx) roleName from asm_opera ao where ao.companyid=" + companyID + " " + sql1;
DataTable dd = DbHelperSQL.Query(sql).Tables[0];
if (dd.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dd));
}
return("1");
}
public static string getMechineList(string companyID)
{
if (companyID == "0")
{
string sqlme = "select id sCode,bh sName from asm_mechine where 1=1";
DataTable dd = DbHelperSQL.Query(sqlme).Tables[0];
if (dd.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dd));
}
else
{
return("1");
}
}
else
{
string sqlme = "select id sCode,bh sName from asm_mechine where companyID=" + companyID;
DataTable dd = DbHelperSQL.Query(sqlme).Tables[0];
if (dd.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dd));
}
else
{
return("1");
}
}
}
public static string search(string mechineID, string opera, string companyID, string agentID)
{
string sql = " where 1=1 and A.companyID=" + companyID;
if (mechineID != "")
{
sql += " and A.id in(" + mechineID + ")";
}
if (opera != "")
{
sql += " and B.name='" + opera + "'";
}
//if (agentID != "0")
//{
// sql += " and dls='" + agentID + "'";
//}
string sql1 = "select A.*,B.name operaName,(select name from asm_opera where asm_opera.id=dls) dlsName from (select am.*,ac.name,amt.name mechineType, case am.statu when '0' then '正常' when '1' then '脱机' when '2' then '温度异常' else '其他' end sta,case am.zt when '1' then '禁用' when '2' then '正常' when '3' then '过期' else '其他' end t,(select des from asm_zfbhb where id=am.hbid) hbdes from asm_mechine am left join asm_company ac on am.companyID=ac.id left join asm_mechineType amt on am.version=amt.id) A left join asm_opera B on A.operaID=B.id" + sql;
DataTable dt = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
else
{
return("1");
}
}
public static string search(string startTime, string endTime, string bh, string type, string companyID)
{
string sql = " where companyID= " + companyID;
if (startTime != "")
{
sql += " and am.validateTime>'" + startTime + "'";
}
if (endTime != "")
{
sql += " and am.validateTime<'" + endTime + "'";
}
if (bh != "")
{
sql += " and am.bh='" + bh + "'";
}
if (type != "0")
{
sql += " and am.zt=" + type;
}
string sql1 = "select am.*,ac.name,amt.name mechineType, case am.statu when '0' then '正常' when '1' then '脱机' when '2' then '温度异常' else '其他' end sta,case am.zt when '1' then '禁用' when '2' then '正常' when '3' then '过期' else '其他' end t from asm_mechine am left join asm_company ac on am.companyID=ac.id left join asm_mechineType amt on am.version=amt.id " + sql;
DataTable dt = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
else
{
return("1");
}
}
public static string getProductList(string typeID, string companyID)
{
if (typeID != "0")
{
string sql = "select * from asm_product where companyID=" + companyID + " and protype=" + typeID;
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
else
{
return("1");
}
}
else
{
string sql = "select * from asm_product where companyID=" + companyID;
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
else
{
return("1");
}
}
}
public static string getOrderList(string bh, string mechineID, string zq, string companyID)
{
string sql = " 1=1 ";
if (!string.IsNullOrEmpty(bh))
{
sql += " and A.orderNO='" + bh + "'";
}
if (!string.IsNullOrEmpty(mechineID) && mechineID != "0")
{
sql += " and A.mechineID=" + mechineID;
}
if (!string.IsNullOrEmpty(zq) && zq != "0")
{
sql += " and zq=" + zq;
}
string sql1 = "select A.*,b.bh from (select ao.*,am.name,am.phone from asm_order ao left join asm_member am on ao.memberID=am.id where am.companyID=" + companyID + ") A left join asm_mechine B on A.mechineID=B.id where 1=1 and " + sql;
DataTable dt = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
else
{
return("1");
}
}
public static string getOperaList(string companyID)
{
if (companyID != "0")
{
string sql = "select * from asm_opera where companyID=" + companyID + " and id IN(select operaID from asm_mechine where companyID=" + companyID + ")";
DataTable dd = DbHelperSQL.Query(sql).Tables[0];
if (dd.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dd));
}
else
{
return("1");
}
}
else
{
string sql = "select * from asm_opera where companyID!=0";
DataTable dd = DbHelperSQL.Query(sql).Tables[0];
if (dd.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dd));
}
else
{
return("1");
}
}
}
public static string getData(string mechineID, string time)
{
string sql = "select distinct am.time,at.temperature from asm_Mtime am left join asm_temperature at on am.time=SUBSTRING(at.time,11,6) where mechineID=" + mechineID;
if (!string.IsNullOrEmpty(time))
{
sql += " and at.time like '" + time + "%'";
}
else
{
sql += " and at.time like '" + DateTime.Now.ToString("yyyy-MM-dd") + "%'";
}
sql += " order by am.time";
string timeStr = "", dataStr = "";
DataTable da = DbHelperSQL.Query(sql).Tables[0];
if (da.Rows.Count > 0)
{
for (int i = 0; i < da.Rows.Count; i++)
{
//timeStr += OperUtil.ConvertDateTimeToInt(DateTime.Parse(dt.Rows[i]["time"].ToString())) +",";
timeStr += "" + da.Rows[i]["time"].ToString().Trim() + ",";
dataStr += da.Rows[i]["temperature"].ToString() + ",";
}
timeStr = timeStr.Substring(0, timeStr.Length - 1);
dataStr = dataStr.Substring(0, dataStr.Length - 1);
string ss = OperUtil.DataTableToJsonWithJsonNet(da) + "@" + timeStr + "@" + dataStr;
return(ss);
}
else
{
return("1");
}
}
public static object sear(string mechineID, string start, string end, string pageCurrentCount)
{
string sql1 = "";
if (!string.IsNullOrEmpty(mechineID))
{
sql1 += " and r.mechineID in(" + mechineID + ")";
}
if (!string.IsNullOrEmpty(start))
{
sql1 += " and timeStr>'" + start + "'";
}
if (!string.IsNullOrEmpty(end))
{
sql1 += " and timeStr<'" + end + "'";
}
string sql = "select r.*,m.name,(select mechineName from asm_mechine where asm_mechine.id=r.mechineID)mechineName,(select bh from asm_mechine where asm_mechine.id=r.mechineID)bh from asm_mechineRecord r left join asm_member m on r.memberID=m.id where 1=1 " + sql1;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.timeStr desc", sql, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
return(new { code = 200, db = OperUtil.DataTableToJsonWithJsonNet(dt), count = Math.Ceiling(d) });
}
return(new { code = 500 });
}
public static object sear(string pcbh, string keyword, string statuType, string pageCurrentCount, string cardNO)
{
string sql1 = "";
if (!string.IsNullOrEmpty(pcbh))
{
sql1 += " and codeBH='" + pcbh + "'";
}
if (!string.IsNullOrEmpty(keyword))
{
sql1 += " and (m.name like '%" + keyword + "%' or m.phone like '%" + keyword + "%')";
}
if (!string.IsNullOrEmpty(cardNO))
{
sql1 += " and cardNO like '%" + cardNO + "%'";
}
if (statuType != "0")
{
sql1 += " and statu=" + statuType;
}
string sql = "select p.*,m.name,m.phone from asm_payCodeList p left join asm_member m on p.memberID=m.id where 1=1 " + sql1;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.id desc", sql, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
return(new { code = 200, db = OperUtil.DataTableToJsonWithJsonNet(dt), count = Math.Ceiling(d) });
}
return(new { code = 500, msg = "" });
}
public static string getOrderList(string mechineID, string companyID, string pageCurrentCount)
{
string sql = " 1=1 ";
if (!string.IsNullOrEmpty(mechineID) && mechineID != "0")
{
sql += " and E.mechineID=" + mechineID;
}
if (!string.IsNullOrEmpty(companyID) && companyID != "0")
{
sql += " and E.companyID=" + companyID;
}
string sql1 = "select E.*,F.proName,F.price2 from (select C.*,D.name nickName from (select A.*,B.name from (select ao.*, am.bh,am.companyID from asm_order ao left join asm_mechine am on ao.mechineID=am.id) A left join asm_company B on A.companyID=B.id) C left join asm_member D on C.memberID=D.id) E left join asm_product F on E.productID=F.productID where 1=1 and " + sql;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.id desc", sql1, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
string ss = Math.Ceiling(d) + "@" + OperUtil.DataTableToJsonWithJsonNet(dt);
//string ss =OperUtil.DataTableToJsonWithJsonNet(da);
return(ss);
}
else
{
return("1");
}
}
public static string getProductList(string keyword, string qy, string pageCurrentCount)
{
string sql1 = " and 1=1";
if (keyword.Trim() != "")
{
sql1 += " and C.proName like '%" + keyword + "%'";
}
if (qy != "0")
{
sql1 += " and C.companyID=" + qy;
}
string sql = "select C.*,D.typeName from (select A.*,B.name from(select ap.* from asm_product ap left join asm_mechine ac on ap.mechineID=ac.id) A left join asm_company B on A.companyID=B.id) C left join asm_protype D on C.protype=D.productTypeID where 1=1 " + sql1;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.productID ", sql, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
string ss = Math.Ceiling(d) + "@" + OperUtil.DataTableToJsonWithJsonNet(dt);
//string ss =OperUtil.DataTableToJsonWithJsonNet(da);
return(ss);
}
else
{
return("1");
}
}
public static string getOrderList(string zt, string orderNO, string pageCurrentCount)
{
string sql = " orderNO = '" + orderNO + "' ";
if (zt != "0")
{
sql += " and zt='" + zt + "'";
}
string sql1 = "select *,"
+ " (select name from asm_member where asm_member.id = asm_orderlistDetail.memberID) name ,"
+ " (select proName from asm_product where asm_product.productID = asm_orderlistDetail.productID) pname,"
+ " case zt when '1' then '已完成' when '2' then '已失效' when '3' then '已转售' when '4' then '待取货' when '5' then '待配送' when '6' then '已售出' when '7' then '兑换' else '' end ztName"
+ " from asm_orderlistDetail where " + sql;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.id ", sql1, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
string ss = Math.Ceiling(d) + "@" + OperUtil.DataTableToJsonWithJsonNet(dt);
//string ss =OperUtil.DataTableToJsonWithJsonNet(da);
return(ss);
}
else
{
return("1");
}
}
public static string search(string gsid, string bh)
{
string sql = " where 1=1";
if (bh != "")
{
sql += " and am.bh='" + bh + "'";
}
if (gsid != "0")
{
sql += " and am.companyID='" + gsid + "'";
}
string sql1 = "select am.*,ac.name,amt.name mechineType,(select count(*) from asm_videoAddMechine where mechineID=am.id) num,case am.statu when '0' then '正常' when '1' then '脱机' when '2' then '温度异常' else '其他' end sta from asm_mechine am left join asm_company ac on am.companyID=ac.id left join asm_mechineType amt on am.version=amt.id " + sql;
DataTable dt = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
else
{
return("1");
}
}
public static string getOrderList(string bh, string mechineID, string zq, string companyID, string pageCurrentCount, string zt, string keywords, string start, string end)
{
string sql = " 1=1 ";
if (!string.IsNullOrEmpty(bh))
{
sql += " and A.orderNO='" + bh + "'";
}
if (!string.IsNullOrEmpty(mechineID) && mechineID != "0")
{
sql += " and A.mechineID=" + mechineID;
}
if (!string.IsNullOrEmpty(zq) && zq != "0")
{
sql += " and zqNum=" + zq;
}
if (!string.IsNullOrEmpty(zt) && zt != "-1")
{
sql += " and A.orderZT=" + zt;
}
if (!string.IsNullOrEmpty(keywords))
{
sql += " and (A.phone like '%" + keywords + "%' or A.memberID='" + keywords + "')";
}
if (!string.IsNullOrEmpty(start))
{
sql += " and A.createTime>'" + start + "'";
}
if (!string.IsNullOrEmpty(end))
{
sql += " and A.createTime<'" + end + "'";
}
string where = string.Empty;
if (!companyID.Equals("0"))
{
where = " and am.companyID=" + companyID;
}
string sql1 = "select A.*,b.bh,(select payType from asm_pay_info api where api.trxid=A.trxID) payType1 ,(select proName from asm_product ap where ap.productID=A.productID) proName from (select ao.*,am.name,am.phone from asm_orderlist ao left join asm_member am on ao.memberID=am.id where fkzt=1 " + where + ") A left join asm_mechine B on A.mechineID=B.id where 1=1 and " + sql;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.id desc", sql1, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
string ss = Math.Ceiling(d) + "@@@" + OperUtil.DataTableToJsonWithJsonNet(dt);
//string ss =OperUtil.DataTableToJsonWithJsonNet(da);
return(ss);
}
else
{
return("1");
}
}
public static object getOrderList(string mechineIDList, string companyID, string start, string end, string pageCurrentCount, string selType, string fkzt, string keyword, string trxid)
{
string sql1 = "";
if (!string.IsNullOrEmpty(mechineIDList))
{
sql1 += " and mechineID in (" + mechineIDList + ")";
}
if (!string.IsNullOrEmpty(start))
{
sql1 += " and paytime>'" + start.Replace("-", "").Replace(":", "").Replace(" ", "") + "'";
}
if (!string.IsNullOrEmpty(end))
{
sql1 += " and paytime<'" + end.Replace("-", "").Replace(":", "").Replace(" ", "") + "'";
}
if (selType != "0")
{
sql1 += " and type=" + selType;
}
if (fkzt != "-1")
{
sql1 += " and statu=" + fkzt;
}
if (!string.IsNullOrEmpty(keyword))
{
string sqlM = "select * from asm_member where convert(varchar,id)='" + keyword + "' or phone='" + keyword + "'";
DataTable dm = DbHelperSQL.Query(sqlM).Tables[0];
if (dm.Rows.Count > 0)
{
sql1 += " and memberID='" + dm.Rows[0]["id"].ToString() + "'";
}
}
if (!string.IsNullOrEmpty(trxid))
{
sql1 += " and trxid='" + trxid + "'";
}
string sql = "select * from(select A.*,(select bh from asm_mechine where id=A.mechineID) bh,"
+ " (select top 1 name from asm_member where unionID = A.unionID) name,"
+ " (select top 1 id from asm_member where unionID = A.unionID) memberID,"
+ " (select proName from asm_product where productID=A.productID)proname,"
+ " (select top 1 phone from asm_member where unionID = A.unionID) phone"
+ " from"
+ " (select p.*, s.productname, CONVERT(decimal(18,2),trxamt/100.00)totalMoney, s.bz from asm_pay_info p left join asm_sellDetail s on p.trxid = s.billno) A) C where companyID=" + companyID + sql1;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.id desc", sql, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
return(new { code = 200, db = OperUtil.DataTableToJsonWithJsonNet(dt), count = Math.Ceiling(d) });
}
return(null);
}
public static string getMemberList(string keyword, string qy, string start, string end, string pageCurrentCount, string mechineID, string dj, string minMoney, string maxMoney)
{
string sql = "1=1";
if (keyword.Trim() != "")
{
sql += " and (A.memberBH='" + keyword + "' or A.name='" + keyword + "' or A.phone='" + keyword + "' or CONVERT(varchar,A.id)='" + keyword + "')";
}
if (qy != "0")
{
sql += " and A.companyID=" + qy;
}
if (mechineID != "0")
{
sql += " and A.mechineID=" + mechineID;
}
if (start.Trim() != "")
{
sql += " and A.createDate>'" + start + "'";
}
if (end.Trim() != "")
{
sql += " and A.createDate<'" + end + "'";
}
if (dj != "-1")
{
sql += " and A.dj=" + dj;
}
if (minMoney != "")
{
sql += " and A.AvailableMoney>'" + minMoney + "'";
}
if (maxMoney != "")
{
sql += " and A.AvailableMoney<'" + maxMoney + "'";
}
string sql1 = "select A.*,B.name companyName from (select am.*,ac.bh from asm_member am left join asm_mechine ac on am.mechineID=ac.id) A left join asm_company B on A.companyID=B.id where 1=1 and " + sql;
int startIndex = (int.Parse(pageCurrentCount) - 1) * Config.pageSize + 1;
int endIndex = int.Parse(pageCurrentCount) * Config.pageSize;
DataTable dt = Config.getPageDataTable("order by T.id desc", sql1, startIndex, endIndex);
DataTable da = DbHelperSQL.Query(sql1).Tables[0];
if (dt.Rows.Count > 0)
{
double d = double.Parse((da.Rows.Count / double.Parse(Config.pageSize.ToString())).ToString());
string ss = Math.Ceiling(d) + "@@@" + OperUtil.DataTableToJsonWithJsonNet(dt);
Util.Debuglog("ss=" + ss, "会员列表.txt");
//string ss =OperUtil.DataTableToJsonWithJsonNet(da);
return(ss);
}
else
{
return("1");
}
}
public static string getQX(string qxID)
{
string sql = "select * from asm_qx where roleID=" + qxID;
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dt));
}
return("1");
}
public static string getInfo(string operaID)
{
string sql = "select *,(select name from asm_role ar where ar.id=ao.qx) roleName from asm_opera ao where ao.companyid=0 and id=" + operaID;
DataTable dd = DbHelperSQL.Query(sql).Tables[0];
if (dd.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dd));
}
return("1");
}
public static string sear(string companyID)
{
string sql = "select * from asm_role where companyID=" + companyID;
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dt));
}
return("1");
}
public static string FullLookup(string id)
{
string sql1 = "select * from asm_activity_fulldiscount where id=" + id;
DataTable dd = DbHelperSQL.Query(sql1).Tables[0];
if (dd.Rows.Count > 0)
{
return(OperUtil.DataTableToJsonWithJsonNet(dd));
}
return("");
}
public static string sear(string mechineID)
{
string sql = "select avm.*, av.description,av.path,av.name from [dbo].[asm_videoAddMechine] avm left join asm_video av on avm.videoID=av.id where av.companyID!=0 and avm.mechineID=" + mechineID;
DataTable dd = DbHelperSQL.Query(sql).Tables[0];
if (dd.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dd);
return(ss);
}
return("1");
}
public static string getMechineList(string companyID)
{
string sql = "select * from asm_mechine where companyID=" + companyID;
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
return("1");
}
public static string sear(string memberID)
{
string sql = "select * from asm_moneyChange where memberID=" + memberID + " order by id desc";
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
return("1");
}
public static string getBrokenDownListNew()
{
string sql = "select top 1 bh,statu,case statu when '1' then '脱机' when '2' then '温度异常' else '' end brokenName,case statu when '1' then '停止运行' when '2' then '运行中' else '' end runStatu,brokenTime from asm_mechine where statu!=0 and brokenTime!='' and DATEDIFF(MI,CONVERT(datetime,brokentime),GETDATE())<5 order by brokenTime desc";
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
return("1");
}
public static string getBrokenDownList()
{
string sql = "select top 5 id,bh,statu,case statu when '1' then '脱机' when '2' then '温度异常' else '' end brokenName,case statu when '1' then '停止运行' when '2' then '运行中' else '' end runStatu,brokenTime from asm_mechine where statu!=0 order by brokenTime desc";
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
return("1");
}
public static string getHotProduct(string name)
{
string sql = "select *,(select typeName from asm_protype app where app.productTypeID=ap.protype) typeName,(select name from asm_company where id=ap.companyID) companyName from asm_product ap where ap.companyID in (select id from asm_company where name like '%" + name + "%')";
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
return("1");
}
public static string ssts(string companyID)
{
string sql = @"select acct,am.name,am.sumConsume,am.sumRecharge,am.AvailableMoney, SUBSTRING(paytime,0,5)+'-'+SUBSTRING(paytime,5,2)+'-'+SUBSTRING(paytime,7,2)+' ' +SUBSTRING(paytime,9,2)+':'+SUBSTRING(paytime,11,2) time from asm_pay_info,asm_member am where (asm_pay_info.acct = am.openID or asm_pay_info.acct = am.minOpenID) and 1 = 1 and type = 1 and paytime like '" + DateTime.Now.ToString("yyyyMMdd") + "%' and am.companyID=" + companyID + " order by paytime desc";
DataTable dt = DbHelperSQL.Query(sql).Tables[0];
if (dt.Rows.Count > 0)
{
string ss = OperUtil.DataTableToJsonWithJsonNet(dt);
return(ss);
}
return("1");
}
