using System;
|
using System.Collections.Generic;
|
using System.Text;
|
|
namespace CommonUtil.Json
|
{
|
/// <summary>
|
/// json对象转sql
|
/// </summary>
|
public class JsonToSql
|
{
|
/// <summary>
|
/// json对象转sql
|
/// </summary>
|
/// <param name="request"></param>
|
/// <param name="errmessage"></param>
|
/// <returns></returns>
|
public static string GetSql(CustomerSearchRequest request, ref string errmessage)
|
{
|
List<CustomerConition> conitionlist = request.conition;
|
string tablename = request.group;
|
if (string.IsNullOrEmpty(tablename))
|
{
|
errmessage = "会员表名能为空";
|
return "";
|
}
|
List<string> swherelist = new List<string>();
|
List<string> updatelist = new List<string>();
|
foreach (CustomerConition conition in conitionlist)
|
{
|
if (conition.Fields.Count > 0)
|
{
|
string conitionstring = string.Empty;
|
string upstring = string.Empty;
|
string insertfiled = string.Empty;
|
string insertvalue = string.Empty;
|
string upfiledvalue = string.Empty;
|
foreach (Field filed in conition.Fields)
|
{
|
if (string.IsNullOrEmpty(filed.field))
|
{
|
return "查询条件传入出错";
|
}
|
if (string.IsNullOrEmpty(filed.type) && request.Operation != "add")
|
{
|
return "查询条件传入出错";
|
}
|
if (string.IsNullOrEmpty(filed.value))
|
{
|
return "查询条件传入出错";
|
}
|
if (request.Operation != "add")
|
{
|
if (conitionstring != "")
|
{
|
switch (filed.type)
|
{
|
case "term ":
|
conitionstring += " and " + filed.field + "='" + filed.value + "'";
|
break;
|
case "terms ":
|
conitionstring += " and " + filed.field + "in(" + filed.value + ")";
|
break;
|
case "gt":
|
conitionstring += " and " + filed.field + ">'" + filed.value + "'";
|
break;
|
case "gte":
|
conitionstring += " and " + filed.field + ">='" + filed.value + "'";
|
break;
|
case "lt":
|
conitionstring += " and " + filed.field + "<'" + filed.value + "'";
|
break;
|
case "lte":
|
conitionstring += " and " + filed.field + "<='" + filed.value + "'";
|
break;
|
case "like":
|
conitionstring += " and " + filed.field + "like'%" + filed.value + "%'";
|
break;
|
}
|
|
}
|
else
|
{
|
switch (filed.type)
|
{
|
case "term":
|
conitionstring += " " + filed.field + "='" + filed.value + "'";
|
break;
|
case "terms":
|
conitionstring += " " + filed.field + "in(" + filed.value + ")";
|
break;
|
case "gt":
|
conitionstring += " " + filed.field + ">'" + filed.value + "'";
|
break;
|
case "gte":
|
conitionstring += " " + filed.field + ">='" + filed.value + "'";
|
break;
|
case "lt":
|
conitionstring += " " + filed.field + "<'" + filed.value + "'";
|
break;
|
case "lte":
|
conitionstring += " " + filed.field + "<='" + filed.value + "'";
|
break;
|
case "like":
|
conitionstring += " " + filed.field + "like'%" + filed.value + "%'";
|
break;
|
}
|
}
|
}
|
else
|
{
|
if (insertfiled != "")
|
{
|
insertfiled += "," + filed.field;
|
}
|
else
|
{
|
insertfiled += filed.field;
|
}
|
if (insertvalue != "")
|
{
|
insertvalue += "," + filed.value;
|
}
|
else
|
{
|
insertvalue += filed.value;
|
}
|
}
|
}
|
if (conition.UpdateFields != null)
|
{
|
foreach (Field upfiled in conition.UpdateFields)
|
{
|
if (!string.IsNullOrEmpty(upfiled.field) && !string.IsNullOrEmpty(upfiled.value))
|
{
|
if (upfiledvalue != "")
|
{
|
|
upfiledvalue += "," + upfiled.field + "=" + upfiled.value;
|
}
|
else
|
{
|
upfiledvalue += upfiled.field + "=" + upfiled.value;
|
}
|
}
|
else
|
{
|
errmessage = "查询条件传入出错";
|
return "";
|
}
|
}
|
}
|
|
if (!string.IsNullOrEmpty(conitionstring))
|
{
|
swherelist.Add(conitionstring);
|
}
|
if (!string.IsNullOrEmpty(insertfiled) && !string.IsNullOrEmpty(insertvalue))
|
{
|
swherelist.Add("(" + insertfiled + ")values(" + insertvalue + ")");
|
|
}
|
if (!string.IsNullOrEmpty(upfiledvalue))
|
{
|
updatelist.Add(upfiledvalue);
|
}
|
|
}
|
}
|
StringBuilder selsql = new StringBuilder();
|
switch (request.Operation)
|
{
|
case "search":
|
foreach (string swhere in swherelist)
|
{
|
selsql.Append("select " + request.GetField + " from " + tablename + " where " + swhere + "");
|
}
|
break;
|
case "add":
|
foreach (string swhere in swherelist)
|
{
|
|
selsql.Append("insert into " + tablename + " " + swhere + ";");
|
}
|
break;
|
case "edit":
|
for (int i = 0; i < swherelist.Count - 1; i++)
|
{
|
selsql.Append("update " + tablename + " set " + updatelist[i] + " " + swherelist[i] + ";");
|
}
|
break;
|
}
|
return selsql.ToString();
|
}
|
}
|
}
|
