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();
        }
    }
}