using NG_Tools;
using NGTools.Tools;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Services;
using System.Web.SessionState;

namespace NGTools.ASHX
{

    /// <summary>
    /// getTreeCombox 的摘要说明
    /// </summary>
    public class getTreeCombox : IHttpHandler, System.Web.SessionState.IRequiresSessionState
    {


        StringBuilder result = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        DataTable dtMenu = new DataTable();

        string strUserID = "";

        public DataTable dtFieldName = MySQLHelper.ExecuteDataTable("select * from field_name order by ID");
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";

            strUserID = context.Session["userAccount"].ToString();

 

            if (context.Request["action"] == "getJldSelect")   //手机端select使用
            {
                string strORG_Parent_ID = context.Session["userOrgID"].ToString();

                DataTable dtTree = new DataTable(); ;
                if (context.Session["userName"].ToString() == "廖德云")
                {

                    dtTree = _GetTable();
                }
                else
                {
                    dtTree = _GetTable(strORG_Parent_ID);
                }
                DataView dv = dtTree.DefaultView;
                dv.RowFilter = "ORG_PARENT_ID <> 'MS00'";
                DataTable dtTemp = dv.ToTable(false, "ORG_ID", "ORG_NAME");


                string jsonDWZC = JSONConvert.ToJson(dtTemp, "DWZC");

                DataTable dtTreeTemp = dtTemp.Copy();

                string JSONJLd = "\"JLD\":[{";

                DataTable dtJld = MySQLHelper.ExecuteDataTable("select * from jld");

                for (int i = 0; i < dtTreeTemp.Rows.Count; i++)
                {
                    dv = dtJld.DefaultView;
                    dv.RowFilter = "ORG_ID='" + dtTreeTemp.Rows[i]["ORG_ID"] + "'";
                    dtTemp = dv.ToTable(false, "ID", "JLD_Name");
                    if (dtTemp.Rows.Count == 0)
                    {
                        JSONJLd += "{},";
                        continue;
                    }
                    else
                    {
                        JSONJLd += JSONConvert.ToJson(dtTemp, dtTreeTemp.Rows[i]["ORG_ID"].ToString());
                    }
                    JSONJLd = JSONJLd.TrimEnd(',') + ",";

                }

                JSONJLd = JSONJLd.TrimEnd(',')+"}]"  ;

                string json = "[{"+jsonDWZC+"},{"+ JSONJLd +"}]" ;
                context.Response.Write(json);
                context.Response.End();
            }

            if (context.Request["action"] == "getJld")
            {
                string strORG_Parent_ID = context.Session["userOrgID"].ToString();
                DataTable dtTree = new DataTable(); ;
                if (context.Session["userName"].ToString() == "廖德云")
                {

                    dtTree = _GetTable();
                }
                else
                {
                    dtTree = _GetTable(strORG_Parent_ID);
                }
                DataTable dtJld = MySQLHelper.ExecuteDataTable("select * from jld");
                for (int i = 0; i < dtTree.Rows.Count; i++)
                {
                    DataView dv = dtJld.DefaultView;
                    dv.RowFilter = "ORG_ID='" + dtTree.Rows[i]["ORG_ID"] + "'";
                    DataTable dtTemp = dv.ToTable();
                    if (dtTemp.Rows.Count == 0)
                    {
                        continue;
                    }
                    for (int j = 0; j < dtTemp.Rows.Count; j++)
                    {
                        DataRow dr = dtTree.NewRow();
                        dr["ORG_ID"] = dtTemp.Rows[j]["ID"];
                        dr["ORG_NAME"] = dtTemp.Rows[j]["JLD_NAME"];
                        dr["ORG_PARENT_ID"] = dtTree.Rows[i]["ORG_ID"];
                        dtTree.Rows.Add(dr);
                    }

                }
                string json = _GetTree(dtTree, "ORG_ID", "ORG_NAME", "ORG_Parent_ID", "MS00");
                context.Response.Write(json);
                context.Response.End();
            }

            if (context.Request["action"] == "getGSC")
            {
                string strORG_Parent_ID = context.Session["userOrgID"].ToString();
                DataTable dtTree = new DataTable(); ;
                if (context.Session["userName"].ToString() == "廖德云")
                {
                    dtTree = _GetTable();
                }
                else
                {
                    dtTree = _GetTable(strORG_Parent_ID);
                }
                DataTable dtJld = MySQLHelper.ExecuteDataTable("select * from gsc");
                for (int i = 0; i < dtTree.Rows.Count; i++)
                {
                    DataView dv = dtJld.DefaultView;
                    dv.RowFilter = "ORG_ID='" + dtTree.Rows[i]["ORG_ID"] + "'";
                    DataTable dtTemp = dv.ToTable();
                    if (dtTemp.Rows.Count == 0)
                    {
                        continue;
                    }
                    for (int j = 0; j < dtTemp.Rows.Count; j++)
                    {
                        DataRow dr = dtTree.NewRow();
                        dr["ORG_ID"] = dtTemp.Rows[j]["SRJ"];
                        dr["ORG_NAME"] = dtTemp.Rows[j]["CPH"];
                        dr["ORG_PARENT_ID"] = dtTree.Rows[i]["ORG_ID"];
                        dtTree.Rows.Add(dr);
                    }

                }
                string json = _GetTree(dtTree, "ORG_ID", "ORG_NAME", "ORG_Parent_ID", "MS00");
                context.Response.Write(json);
                context.Response.End();
            }

            if (context.Request["action"] == "getOrder")
            {
                string userID = context.Session["userAccount"].ToString();
                string strType = context.Request.QueryString["type"];
                DataTable dtTree = new DataTable();
                if (context.Session["userName"].ToString() == "廖德云")
                {
                    if (strType == "yjh")
                    {
                        dtTree = MySQLHelper.ExecuteDataTable("select * from buy_detail where   payFlag='1'");

                    }
                    else
                    {
                        dtTree = MySQLHelper.ExecuteDataTable("select * from buy_detail where   payFlag='0'");

                    }
                }
                else
                {
                    if (strType == "yjh")
                    {
                        dtTree = MySQLHelper.ExecuteDataTable("select * from buy_detail where USER_ID='" + userID + "' and payFlag='1'");

                    }
                    else
                    {
                        dtTree = MySQLHelper.ExecuteDataTable("select * from buy_detail where USER_ID='" + userID + "' and payFlag='0'");

                    }
                }
                DataView dv = dtTree.DefaultView;
                DataTable dataTableDistinct = dv.ToTable(true, "USER_ID", "ORDER_ID");

                string json = _GetTree(dataTableDistinct, "ORDER_ID", "ORDER_ID", "USER_ID", userID);
                context.Response.Write(json);
                context.Response.End();
            }

            if (context.Request["action"] == "getORG")
            {
                string strORG_Parent_ID = context.Session["userOrgID"].ToString();
                DataTable dtTree = new DataTable(); ;
                if (context.Session["userName"].ToString() == "廖德云")
                {
                    dtTree = _GetTable();
                }
                else
                {
                    dtTree = _GetTable(strORG_Parent_ID);
                }
                string json = _GetTree(dtTree, "ORG_ID", "ORG_NAME", "ORG_Parent_ID", "MS00");
                context.Response.Write(json);
                context.Response.End();
            }

            if (context.Request["action"] == "getJldPar")
            {
                string strORG_Parent_ID = context.Session["userOrgID"].ToString();
                DataTable dtTree = new DataTable(); ;
                if (context.Session["userName"].ToString() == "廖德云")
                {
                    dtTree = _GetTable();
                }
                else
                {
                    dtTree = _GetTable(strORG_Parent_ID);
                }
                string jldID = context.Request.Form["jldid"];
                string strGN = context.Request.Form["gnmk"];
                DataTable dtJldPar = MySQLHelper.ExecuteDataTable("select * from jldpar where JLD_ID='" + jldID + "' and gnmk='" + strGN + "'");


                string json = JSONConvert.DataTableToJsonList(dtJldPar);

                context.Response.Write(json);
                context.Response.End();
            }

            if (context.Request["action"] == "getTree")
            {
                DataTable dtTree = _GetMenuTable();
                string json = _GetChildTree(dtTree, "Menu_ID", "Menu_Name", "Menu_URL", "Parent_ID", "M00");
                context.Response.Write(json);
                context.Response.End();
            }

            ///购买次数减一
            if (context.Request["action"] == "setRemainTimes")
            {
                if (context.Session["userName"].ToString() == "廖德云")
                {
                    context.Response.Write("1");
                    context.Response.End();
                }
                else
                {
                    if (context.Session["regDateTime"].ToString() != "")  //新注册用户 10天试用期
                    {
                        DateTime dtReg = DateTime.Parse(context.Session["regDateTime"].ToString());

                        if (dtReg.AddDays(10) >= DateTime.Now)
                        {
                            context.Response.Write("1");
                            context.Response.End();
                        }
                    }
                    else
                    {
                        string userID = context.Request.Form["userID"];
                        string menuID = context.Request.Form["menuID"];
                        context.Response.Write(setRemainTimes(userID, menuID).ToString());
                        context.Response.End();
                    }
                }
            }

    

            //查询得dataGrid的JSON数据
            //传入的参数格式是："tableName,条件字段~条件字段值"
            //返回JSON对象
            if (context.Request["action"] == "getDataGrid")
            {
                context.Response.Write(getData(context.Request.Form["par"]));
                context.Response.End();
            }

            //删除数据库数据
            //传入的参数格式是："tableName,条件字段~条件字段值"
            //返回JSON对象
            if (context.Request["action"] == "delData")
            {
                context.Response.Write(delData(context.Request.Form["par"]));
                context.Response.End();
            }
            //保存数据方法 
            // 传入的参数格式是："tableName,条件字段~条件字段值,~字段名~字段值"
            if (context.Request["action"] == "updateData")
            {
                context.Response.Write(updateData(context.Request.Form["par"]));
                context.Response.End();
            }
            // 传入的参数格式是："tableName,条件字段~条件字段值,~字段名~字段值"

            if (context.Request["action"] == "insertData")
            {
                context.Response.Write(insertData(context.Request.Form["par"]));
                context.Response.End();
            }

            if (context.Request["action"] == "SaveDataGrid")
            {

                context.Response.Write(SaveDataGrid(context.Request.Form["par"]));
                context.Response.End();
            }
            if (context.Request["action"] == "SaveOneDataGrid")
            {
                context.Response.Write(SaveOneDataGrid(context.Request.Form["par"]));
                context.Response.End();
            }

            if (context.Request["action"] == "saveJldPar")
            {

                string strJLDID = context.Request.Form["jldid"];
                string strGNMK = context.Request.Form["gnmk"];
                string strPar = "[" + context.Request.Form["Par"] + "]";

                DataTable dtPar = JSONConvert.JsonToDatatable(strPar);
                string Result = "";
                DataTable dtJldPar = MySQLHelper.ExecuteDataTable("select * from jldpar where jld_id='" + strJLDID + "' and gnmk='" + strGNMK + "'");
                if (dtJldPar.Rows.Count > 0)
                {
                    string strSql = "update jldpar set ";
                    strSql += getUpdateSQLOne(dtPar, strJLDID, strGNMK);
                    int num = MySQLHelper.ExecuteNonQuery(strSql);
                    if (num > 0)
                    {
                        Result = ("成功修改ID为" + strJLDID + "的计量参数！");

                    }
                    else
                    {
                        Result = ("保存失败！");
                    }

                }
                else
                {
                    string strIFn = getInsertFieldName(dtPar);
                    string strIVn = getInsertFieldValue(dtPar);

                    string strSql = "insert into jldpar(";
                    strSql += strIFn.Insert(strIFn.Length, ",JLD_ID,GNMK") + ") values ";
                    strSql += strIVn.Insert(strIVn.Length - 1, ",'" + strJLDID + "','" + strGNMK + "'");
                    int num = MySQLHelper.ExecuteNonQuery(strSql);
                    if (num > 0)
                    {
                        Result = ("成功添加ID为" + strJLDID + "的计量参数！");

                    }
                    else
                    {
                        Result = ("保存失败！");
                    }


                }

                context.Response.Write(Result);
                context.Response.End();
            }
            if (context.Request["action"] == "saveMessage")
            {

                string []strDate = context.Request.Form["ly_date"].ToString ().Split (' ');
                string strPar = "[" + context.Request.Form["Par"] + "]";
                DataTable dtPar = JSONConvert.JsonToDatatable(strPar);

                string result="" ;
             

                string strIFn = getInsertFieldName(dtPar);
                string strIVn = getInsertFieldValue(dtPar);

                DateTime dately = DateTime.Parse(strDate[0] + " " + strDate[1]);

                string strSql = "insert into message(";
                strSql += strIFn.Insert(strIFn.Length, ",ly_date") + ") values ";
                strSql += strIVn.Insert(strIVn.Length - 1, ",'" + dately + "'");
                int num = MySQLHelper.ExecuteNonQuery(strSql);

                DataTable dtMessage = MySQLHelper.ExecuteDataTable("select * from message where user_Name='" + context.Session["userName"]+"'");

                result ="["+ JSONConvert.DataTableToJsonList(dtMessage).ToString ()+"]";


                context.Response.Write(result);
                context.Response.End();
            }

            if (context.Request["action"] == "getMessage")
            {
                string result ="";

                DataTable dtMessage = new DataTable();
                if (context.Session["userName"].ToString () == "廖德云")
                {
                    dtMessage = MySQLHelper.ExecuteDataTable("select * from message");
                }
                else
                { dtMessage = MySQLHelper.ExecuteDataTable("select * from message where user_Name='" + context.Session["userName"] + "'"); }
                result="["+JSONConvert.DataTableToJsonList(dtMessage)+"]";
                context.Response.Write(result);
                context.Response.End();
            }

                    ///购买次数减一
            if (context.Request["action"] == "getEnable")
            {

                int result = 0;

                if (context.Session["userName"].ToString() == "廖德云")
                {
                    result = 1;
                }
                else
                {    
                    if (context.Session["regDateTime"].ToString() != "")  //新注册用户 10天试用期
                    {
                        DateTime dtReg = DateTime.Parse(context.Session["regDateTime"].ToString());

                        if (dtReg.AddDays(10) >= DateTime.Now)
                        {
                            result = 1;
                        }
                    }

                    string userID = context.Request.Form["userID"];
                    string menuID = context.Request.Form["menuID"];


                    DataTable dtBuyMenu_ID = MySQLHelper.ExecuteDataTable("select * from buy_total where user_id='" + context.Session["userAccount"].ToString() + "'");
                    DataRow[] dr = dtBuyMenu_ID.Select("Menu_ID='" + menuID + "'");
                    if (dr.Length > 0)
                    {
                        if (dr[0]["Buy_Type"].ToString() == "无限制")
                        {
                            result = 1;
                        }
                        else
                        {

                            if (dr[0]["endDate"].ToString() == "")
                            {
                                if (dr[0]["remaintimes"].ToString() == "" || dr[0]["remaintimes"].ToString() == "0")
                                {
                                    result = 0;
                                }
                                else
                                {
                                    result = 1;
                                }
                            }
                            else
                            {
                                DateTime dtEndDate = DateTime.Parse(dr[0]["endDate"].ToString());
                                if (dtEndDate > DateTime.Now)
                                {
                                    result = 1;
                                }
                                else
                                {
                                    result = 0;
                                }
                            }

                        }

                    }
                    else
                    {
                        result = 0;
                    }
                }
                context.Response.Write(result);
                context.Response.End();
            }


        }


        /// <summary>
        /// 设置购买次数  计算一次减一
        /// </summary>
        /// <returns></returns>
        public int setRemainTimes(string userID, string Menu_ID)
        { 
            int intTimes = 0;
           
          
            DataTable dtMenu_ID = MySQLHelper.ExecuteDataTable("select * from buy_total where user_id='" + userID + "' and Menu_ID='" + Menu_ID + "'");
            if (dtMenu_ID.Rows.Count > 0)
            {
                if (dtMenu_ID.Rows[0]["remaintimes"].ToString() == "无限制")
                {
                    intTimes = 1;
                }
                DateTime endDate;
                if (dtMenu_ID.Rows[0]["endDate"].ToString() != "")
                {
                    endDate = DateTime.Parse(dtMenu_ID.Rows[0]["endDate"].ToString());
                    if (endDate >= DateTime.Now)
                    {
                        intTimes = 1;
                    }
                    else
                    {
                        if (dtMenu_ID.Rows[0]["remaintimes"].ToString() != "" & dtMenu_ID.Rows[0]["remaintimes"].ToString() != "0")
                        {
                            int cTimes = int.Parse(dtMenu_ID.Rows[0]["remaintimes"].ToString());
                            cTimes = cTimes - 1;
                            string strSQL = "update  buy_total set remaintimes='" + cTimes + "' where user_id='" + userID + "' and menu_id='" + Menu_ID + "'";
                            MySQLHelper.ExecuteNonQuery(strSQL);
                            if (cTimes >= 0)
                            {
                                intTimes = 1;
                            }
                            else
                            {
                                intTimes = 0;
                            }

                        }
                        else
                        {
                            intTimes = 0;
                        }

                    }
                }
                else
                {
                    
                    if (dtMenu_ID.Rows[0]["remaintimes"].ToString() != "" & dtMenu_ID.Rows[0]["remaintimes"].ToString() != "0")
                    {
                        int cTimes = int.Parse(dtMenu_ID.Rows[0]["remaintimes"].ToString());
                        cTimes = cTimes - 1;
                        string strSQL = "update  buy_total set remaintimes='" + cTimes + "' where user_id='" + userID + "' and menu_id='" + Menu_ID + "'";
                        MySQLHelper.ExecuteNonQuery(strSQL);
                        if (cTimes >= 0)
                        {
                            intTimes = 1;
                        }
                        else
                        {
                            intTimes = 0;
                        }
                    }

                }

            }

            return intTimes;
        }


        /// <summary>
        /// 查询组织结构数据库表
        /// </summary>
        /// <returns></returns>
        public int _SaveJldPar(string strPar)
        {
            DataTable dtPar = JSONConvert.JsonToDatatable("[" + strPar + "]");

            int i = 0;
            return i;
        }

        #region 数据库操作
        /// <summary>
        /// 批量更新数据库表格 修改，删除和插入 
        /// 从前端传来 表名@insetRows JSON@updateRows JSON @ deleteRows JSON
        /// </summary>
        /// <param name="strPar"></param>
        /// /// by liaodeyun
        /// <returns></returns>
        private string SaveOneDataGrid(string strPar)
        {
            string[] parm = strPar.Split('@');

            string strTableName = parm[0]; //数据库表名
            string strInsetRows = parm[1].Replace("},{", ",").Replace("\"name\":", "").Replace(",\"value\"", ""); //插入行
            string strUpdateRows = parm[2].Replace("},{", ",").Replace("\"name\":", "").Replace(",\"value\"", "");//修改行
            string strDeleteRows = parm[3].Replace("},{", ",").Replace("\"name\":", "").Replace(",\"value\"", "");//删除行

            DataTable dtInsert = JSONConvert.JsonToDatatable(strInsetRows);
            DataTable dtUpdate = JSONConvert.JsonToDatatable(strUpdateRows);
            DataTable dtDelete = JSONConvert.JsonToDatatable(strDeleteRows);

            string Result = "";
            //插入行
            if (dtInsert.Rows.Count > 0)
            {
                DataTable dtTemp = dtInsert.Copy(); ;
                dtTemp.Columns.Remove("ID"); ;
                string strSQL = "INSERT INTO " + strTableName + "(";
                strSQL += getInsertFieldName(dtTemp) + ") Values";
                strSQL += getInsertFieldValue(dtTemp);
                int num = MySQLHelper.ExecuteNonQuery(strSQL);

                Result = DataTableToJson(dtInsert, false); ;

            }

            //更新行
            if (dtUpdate.Rows.Count > 0)
            {
                string strSQL = "update " + strTableName + " set ";

                strSQL += getUpdateSQL(dtUpdate);

                int num = MySQLHelper.ExecuteNonQuery(strSQL);
                Result = DataTableToJson(dtUpdate, false); ;

            }

            //删除行
            if (dtDelete.Rows.Count > 0)
            {
                string strSQL = "delete from " + strTableName + " where ID in (";
                string strID = "";

                for (int j = 0; j < dtDelete.Rows.Count; j++)
                {
                    strID += "'" + dtDelete.Rows[j]["ID"] + "',";
                }


                strSQL += strID.TrimEnd(',') + ")";

                int num = MySQLHelper.ExecuteNonQuery(strSQL);
                Result = ("成功删除" + num + "条记录！");

            }


            return Result;
        }

        /// <summary>
        /// 批量更新数据库表格 修改，删除和插入 
        /// 从前端传来 表名@insetRows JSON@updateRows JSON @ deleteRows JSON
        /// </summary>
        /// <param name="strPar"></param>
        /// /// by liaodeyun
        /// <returns></returns>
        private string SaveDataGrid(string strPar)
        {
            string[] parm = strPar.Split('@');

            string strTableName = parm[0]; //数据库表名
            string strInsetRows = parm[1];//.Replace("},{", ",").Replace("\"name\":", "").Replace(",\"value\"", ""); //插入行
            string strUpdateRows = parm[2];//.Replace("},{", ",").Replace("\"name\":", "").Replace(",\"value\"", "");//修改行
            string strDeleteRows = parm[3];//.Replace("},{", ",").Replace("\"name\":", "").Replace(",\"value\"", "");//删除行


            DataTable dtInsert = new DataTable();
            DataTable dtUpdate = new DataTable();
            DataTable dtDelete = new DataTable();
            string Result = "";

            if (strTableName == "buy_detail")
            {
                dtInsert = JSONConvert.JsonToDatatable(strInsetRows);



                if (dtInsert.Rows.Count > 0)
                {
                    dtInsert.Rows.RemoveAt(dtInsert.Rows.Count - 1);

                    string strTempValue = "";
                    for (int i = 0; i < dtInsert.Rows.Count; i++)
                    {
                        strTempValue += "(";
                        for (int j = 0; j < dtInsert.Columns.Count; j++)
                        {
                            strTempValue += "'" + dtInsert.Rows[i][j].ToString() + "',";

                        }
                        strTempValue = strTempValue.TrimEnd(',') + ",'" + strUpdateRows.Split('_')[0] + "','" + strUpdateRows.Split('_')[1] + "','" + strUpdateRows.Split('_')[2] + "','0'),";
                    }
                    strTempValue = strTempValue.TrimEnd(',');


                    string strTempField = "";
                    for (int i = 0; i < dtInsert.Columns.Count; i++)
                    {
                        strTempField += dtInsert.Columns[i].ColumnName + ",";
                    }
                    strTempField = strTempField.TrimEnd(',');


                    string strSQL = "INSERT INTO " + strTableName + "(";
                    strSQL += strTempField + ",USER_ID,ORG_ID,ORDER_ID,payFlag" + ") Values";
                    strSQL += strTempValue;
                    int num = MySQLHelper.ExecuteNonQuery(strSQL);

                    Result += ("成功购买" + num + "模块！");

                    return Result;

                }

            }
            else
            {
                dtInsert = JSONConvert.JsonToDatatable(strInsetRows);
                dtUpdate = JSONConvert.JsonToDatatable(strUpdateRows);
                dtDelete = JSONConvert.JsonToDatatable(strDeleteRows);
            }

            //批量插入行
            if (dtInsert.Rows.Count > 0)
            {
                string strSQL = "INSERT INTO " + strTableName + "(";
                strSQL += getInsertFieldName(dtInsert) + ") Values";
                strSQL += getInsertFieldValue(dtInsert);
                int num = MySQLHelper.ExecuteNonQuery(strSQL);
                Result += ("成功添加" + num + "条记录！");



            }

            //批量更新行
            if (dtUpdate.Rows.Count > 0)
            {
                string strSQL = "update " + strTableName + " set ";

                strSQL += getUpdateSQL(dtUpdate);

                int num = MySQLHelper.ExecuteNonQuery(strSQL);
                Result += ("成功修改" + num + "条记录！");

            }

            //批量删除行
            if (dtDelete.Rows.Count > 0)
            {
                string strSQL = "delete from " + strTableName + " where ID in (";
                string strID = "";

                for (int j = 0; j < dtDelete.Rows.Count; j++)
                {
                    strID += "'" + dtDelete.Rows[j]["ID"] + "',";
                }


                strSQL += strID.TrimEnd(',') + ")";

                int num = MySQLHelper.ExecuteNonQuery(strSQL);
                Result += ("成功删除" + num + "条记录！");

            }


            return Result;
        }


        /// <summary>
        /// 从datatable获取批量更新的SQL字符串 格式如下
        /// UPDATE user SET 
        ///phoneno = CASE id 
        ///    WHEN 1 THEN 3 
        ///    WHEN 2 THEN 4 
        ///END, 
        ///authority = CASE id 
        ///    WHEN 1 THEN 'New Title 1'
        ///    WHEN 2 THEN 'New Title 2'
        ///END
        ///WHERE id IN (1,2);
        /// </summary>
        /// by liaodeyun
        /// <param name="dtTemp"></param>
        /// <returns></returns>
        private string getUpdateSQL(DataTable dtTemp)
        {
            string strTemp = "";
            string strID = "";
            string[] strFieldValue = new string[dtTemp.Columns.Count];
            for (int i = 0; i < dtTemp.Columns.Count; i++)
            {
                if (dtTemp.Columns[i].ColumnName != "ID")
                {
                    strFieldValue[i] = dtTemp.Columns[i].ColumnName + " = case ID ";
                    for (int j = 0; j < dtTemp.Rows.Count; j++)
                    {
                        strFieldValue[i] += "when '" + dtTemp.Rows[j]["ID"] + "' then '" + dtTemp.Rows[j][i] + "' ";
                    }
                    strFieldValue[i] += " end";
                }
            }
            strTemp = String.Join(",", strFieldValue);
            strTemp = strTemp.TrimEnd(',');

            string[] SID = new string[dtTemp.Rows.Count];

            for (int j = 0; j < dtTemp.Rows.Count; j++)
            {
                SID[j] = dtTemp.Rows[j]["ID"].ToString();
            }
            strID = String.Join("','", SID);
            strTemp = strTemp + " where ID in ('" + strID + "')";
            return strTemp.TrimStart(',');
        }

        private string getUpdateSQLOne(DataTable dtTemp, string strID, string strGN)
        {
            string strTemp = "";

           

            string[] strFieldValue = new string[dtTemp.Columns.Count];
            for (int i = 0; i < dtTemp.Columns.Count; i++)
            {

                strFieldValue[i] = dtTemp.Columns[i].ColumnName + " = case JLD_ID ";
                for (int j = 0; j < dtTemp.Rows.Count; j++)
                {
                    strFieldValue[i] += "when '" + strID + "' then '" + dtTemp.Rows[j][i] + "' ";
                }
                strFieldValue[i] += " end";

            }
            strTemp = String.Join(",", strFieldValue);
            strTemp = strTemp.TrimEnd(',');


            strTemp = strTemp + " where JLD_ID = '" + strID + "' and gnmk='" + strGN + "'";
            return strTemp.TrimStart(',');
        }

        /// <summary>
        /// 从datatable获取批量插入的字段字符串
        /// </summary>
        /// <param name="dtTemp"></param>
        /// by 廖德云
        /// <returns>逗号分隔的字段名字符串，，，，</returns>
        private string getInsertFieldName(DataTable dtTemp)
        {
            string strTemp = "";
            for (int i = 0; i < dtTemp.Columns.Count; i++)
            {
                strTemp += dtTemp.Columns[i].ColumnName + ",";
            }
            strTemp = strTemp.TrimEnd(',');
            return strTemp;
        }
        /// <summary>
        /// 从datatable获取批量插入的字段值字符串
        /// </summary>
        /// <param name="dtTemp"></param>
        /// by 廖德云
        /// <returns>逗号单引号分隔的字段名字符串‘’，‘’，‘’</returns>
        private string getInsertFieldValue(DataTable dtTemp)
        {
            string strTemp = "";
            for (int i = 0; i < dtTemp.Rows.Count; i++)
            {
                strTemp += "(";
                for (int j = 0; j < dtTemp.Columns.Count; j++)
                {
                    strTemp += "'" + dtTemp.Rows[i][j].ToString() + "',";

                }
                strTemp = strTemp.TrimEnd(',') + "),";
            }
            strTemp = strTemp.TrimEnd(',');

            return strTemp;
        }



        /// <summary>
        ///删除数据方法 
        ///传入的参数格式是："tableName,条件字段~条件字段值"
        /// </summary>
        /// <returns>操作记录数</returns>
        private string delData(string strPar)
        {
            string[] parm = strPar.Split(',');
            string strSQL = "delete from ";
            try
            {
                string strTableName = parm[0];
                string[] strConditionField = parm[1].Split('~');

                strSQL += strTableName + " where ";
                strSQL += strConditionField[0] + "='";
                strSQL += strConditionField[1] + "'";
            }
            catch (Exception)
            {
            }
            int intdel = MySQLHelper.ExecuteNonQuery(strSQL);
            return ("成功删除" + intdel + "条记录！");

        }
        /// <summary>
        /// 更新数据方法
        /// 传入的参数格式是："tableName,条件字段~条件字段值,~字段名~字段值"
        /// </summary>
        /// <returns>操作记录数</returns>
        private string updateData(string strPar)
        {
            string[] parm = strPar.Split(',');

            string strSQL = "update ";
            try
            {
                string strTableName = parm[0];
                string[] strConditionField = parm[1].Split('~');
                string[] strValueField = parm[2].Split('~');

                strSQL += strTableName + " set ";
                for (int i = 0; i < strValueField.Length; i = i + 2)
                {
                    strSQL += strValueField[i] + "='" + strValueField[i + 1] + "',";
                }
                strSQL = strSQL.TrimEnd(',');
                strSQL += " where ";
                strSQL += strConditionField[0] + "='";
                strSQL += strConditionField[1] + "'";
            }
            catch (Exception)
            {
            }
            int intdel = MySQLHelper.ExecuteNonQuery(strSQL);
            return ("成功保存" + intdel + "条记录！");
        }

        /// <summary>
        /// 插入数据方法
        /// 传入的参数格式是："tableName,条件字段~条件字段值,~字段名~字段值"
        /// </summary>
        /// <returns>操作记录数</returns>
        private string insertData(string strPar)
        {
            string[] parm = strPar.Split(',');

            string strSQL = "insert into ";
            try
            {
                string strTableName = parm[0];
                string[] strConditionField = parm[1].Split('~');
                string[] strValueField = parm[2].Split('~');
                strSQL += strTableName + " (";
                for (int i = 0; i < strValueField.Length; i = i + 2)
                {
                    strSQL += strValueField[i] + ",";
                }
                strSQL = strSQL.TrimEnd(',');
                strSQL += ") values(";
                for (int i = 0; i < strValueField.Length; i = i + 2)
                {
                    strSQL += "'" + strValueField[i + 1] + "',";
                }
                strSQL = strSQL.TrimEnd(',') + ")";
            }
            catch (Exception)
            {
            }
            int intdel = MySQLHelper.ExecuteNonQuery(strSQL);
            return ("成功添加" + intdel + "条记录！");
        }


        /// <summary>
        /// 获取网格数据表格并转换成JSON
        /// 传入的参数格式是："tableName,条件字段~条件字段值
        /// </summary>
        /// <returns>JSON格式</returns>
        private string getData(string strPar)
        {
            string[] parm = strPar.Split(',');

            string strSQL = "";
            try
            {
                string strTableName = parm[0];
                if (strTableName == "user")
                {
                    strSQL = "select user_account,user_name,ORG_ID,phoneNo,authority from ";
                }
                else
                {
                    strSQL = "select * from ";
                }
                string[] strConditionField = parm[1].Split('~');
                if (strConditionField[0] == "" || strConditionField[1] == "")
                {
                    strSQL += strTableName;

                }
                else
                {
                    strSQL += strTableName + " where ";
                    strSQL += strConditionField[0] + "='";
                    strSQL += strConditionField[1] + "'";
                }
            }
            catch (Exception)
            {

            }

            DataTable dtTemp = MySQLHelper.ExecuteDataTable(strSQL);
            string json = DataTableToJson(dtTemp, true);
            return json;

        }
        #endregion
        #region 根据DataTable生成EasyUI Accord Tree Json树结构

        /// <summary>
        /// 查询组织结构数据库表
        /// </summary>
        /// <returns></returns>
        public DataTable _GetTable(string strORG_PARENT_ID = "MS00")
        {
            DataTable dtOrg = new DataTable();
            if (strORG_PARENT_ID == "MS00")
            {
                dtOrg = MySQLHelper.ExecuteDataTable("select * from organization order by ID");

            }
            else
            {
                dtOrg = MySQLHelper.ExecuteDataTable("select * from organization where ORG_ID='" + strORG_PARENT_ID + "' or ORG_PARENT_ID='" + strORG_PARENT_ID + "'");
            }
            return dtOrg;
        }
        /// <summary>
        /// 查询菜单数据库表
        /// </summary>
        /// <returns></returns>
        public DataTable _GetMenuTable()
        {
            DataTable dtTemp = MySQLHelper.ExecuteDataTable(stringSQL.strSQL_Menu);
            return dtTemp;
        }
        /// <summary>
        /// 根据DataTable生成EasyUI children Tree Json树结构
        /// </summary>
        /// <param name="tabel">数据源</param>
        /// <param name="idCol">ID列</param>
        /// <param name="txtCol">Text列</param>
        /// <param name="url">节点Url</param>
        /// <param name="rela">关系字段</param>
        /// <param name="pId">父ID</param>
        private string _GetTree(DataTable tabel, string idCol, string txtCol, string rela, object pId)
        {
            result.Append(sb.ToString());
            sb.Clear();

            //if (tabel.Rows.Count > 0)
            {
                sb.Append("[");
                string filer = string.Format("{0}='{1}'", rela, pId);
                DataRow[] rows = tabel.Select(filer);
                if (rows.Length > 0)
                {
                    foreach (DataRow row in rows)
                    {
                        DataView dv = tabel.DefaultView;
                        dv.RowFilter = rela + "='" + row[idCol].ToString() + "'";

                        if (dv.ToTable().Rows.Count > 0)
                        {
                            sb.Append("{\"id\":\"" + row[idCol] + "\",\"text\":\"" + row[txtCol] + "\",\"state\":\"closed\"");
                        }
                        else
                        {
                            sb.Append("{\"id\":\"" + row[idCol] + "\",\"text\":\"" + row[txtCol] + "\",\"state\":\"open\"");

                        }
                        if (tabel.Select(string.Format("{0}='{1}'", rela, row[idCol])).Length > 0)
                        {
                            sb.Append(",\"children\":");
                            _GetTree(tabel, idCol, txtCol, rela, row[idCol]);
                            result.Append(sb.ToString());
                            sb.Clear();
                        }
                        result.Append(sb.ToString());
                        sb.Clear();
                        sb.Append("},");
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                }
                sb.Append("]");
                result.Append(sb.ToString());
                sb.Clear();
            }
            return result.ToString();
        }




        /// <summary>
        /// 根据DataTable生成EasyUI children Tree Json树结构
        /// </summary>
        /// <param name="tabel">数据源</param>
        /// <param name="idCol">ID列</param>
        /// <param name="txtCol">Text列</param>
        /// <param name="url">节点Url</param>
        /// <param name="rela">关系字段</param>
        /// <param name="pId">父ID</param>
        private string _GetChildTree(DataTable tabel, string idCol, string txtCol, string url, string rela, object pId)
        {
            result.Append(sb.ToString());
            sb.Clear();

            if (tabel.Rows.Count > 0)
            {
                sb.Append("[");
                string filer = string.Format("{0}='{1}'", rela, pId);
                DataRow[] rows = tabel.Select(filer);
                if (rows.Length > 0)
                {
                    foreach (DataRow row in rows)
                    {
                        DataView dv = tabel.DefaultView;
                        dv.RowFilter = "parent_id='" + row["Menu_ID"].ToString() + "'";

                        if (dv.ToTable().Rows.Count > 0)
                        {
                            sb.Append("{\"id\":\"" + row[idCol] + "\",\"text\":\"" + row[txtCol] + "\",\"attributes\":\"" + row[url] + "\",\"state\":\"closed\"" + ",\"iconCls\":\"" + row["iconCls"] + "\"");
                        }
                        else
                        {
                            sb.Append("{\"id\":\"" + row[idCol] + "\",\"text\":\"" + row[txtCol] + "\",\"attributes\":\"" + row[url] + "\",\"state\":\"open\"" + ",\"iconCls\":\"" + row["iconCls"] + "\"");

                        }
                        if (tabel.Select(string.Format("{0}='{1}'", rela, row[idCol])).Length > 0)
                        {
                            sb.Append(",\"children\":");
                            _GetChildTree(tabel, idCol, txtCol, url, rela, row[idCol]);
                            result.Append(sb.ToString());
                            sb.Clear();
                        }
                        result.Append(sb.ToString());
                        sb.Clear();
                        sb.Append("},");
                    }
                    sb = sb.Remove(sb.Length - 1, 1);
                }
                sb.Append("]");
                result.Append(sb.ToString());
                sb.Clear();
            }
            return result.ToString();
        }






        #endregion
        #region "datatabale 转换成tree"
        /// <summary>
        /// 把DataTable数据转换为Json格式
        /// </summary>
        /// <param name="dt">传入DataTable数据</param>
        /// <returns></returns>
        public string DataTableToJson(DataTable dt, bool multRow)
        {
            StringBuilder jsonBuilder = new StringBuilder();
            jsonBuilder.Append("{\"total\"");
            jsonBuilder.Append(":");
            jsonBuilder.Append(dt.Rows.Count);
            jsonBuilder.Append(",\"rows\":");
            if (dt.Rows.Count != 1 || multRow) jsonBuilder.Append("[");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                jsonBuilder.Append("{");
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    jsonBuilder.Append("\"");
                    jsonBuilder.Append(dt.Columns[j].ColumnName);
                    jsonBuilder.Append("\":\"");
                    if (dt.Columns[j].ColumnName == "RQ")
                    {
                        jsonBuilder.Append(dt.Rows[i][j].ToString().Split(' ')[0]);
                    }
                    else
                    {
                        jsonBuilder.Append(dt.Rows[i][j].ToString());
                    }
                    jsonBuilder.Append("\",");
                }
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
                jsonBuilder.Append("},");
            }
            if (dt.Rows.Count > 0)
            {
                jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            }
            if (dt.Rows.Count != 1 || multRow)
            { jsonBuilder.Append("],"); }
            else { jsonBuilder.Append(","); };
            jsonBuilder.Append("\"title");
            //jsonBuilder.Append(dt.TableName);
            jsonBuilder.Append("\":[");
            //这是循环获取列名称
            jsonBuilder.Append("{\"title\": \"\", \"field\":\"ck\",\"checkbox\":\"true\",\"sortable\":\"false\"},");
            for (int n = 0; n < dt.Columns.Count; n++)
            {
                jsonBuilder.Append("{");
                jsonBuilder.Append("\"field");
                jsonBuilder.Append("\":\"");
                jsonBuilder.Append(dt.Columns[n].ColumnName);
                jsonBuilder.Append("\",");
                jsonBuilder.Append("\"title");
                jsonBuilder.Append("\":\"");
                jsonBuilder.Append(getStrColumnName(dt.Columns[n].ColumnName));
                jsonBuilder.Append("\",\"sortable\":\"true\"");
                jsonBuilder.Append("},");
            }
            jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
            jsonBuilder.Append("},");

            jsonBuilder.Remove(jsonBuilder.Length - 2, 2);
            jsonBuilder.Append("]");
            jsonBuilder.Append("}");
            return jsonBuilder.ToString();
        }
        private string getStrColumnName(string strName)
        {
            try
            {
                string temp = dtFieldName.Select("field_text='" + strName + "'")[0]["field_name"].ToString();
                return temp;
            }
            catch (Exception)
            {
                return "";

            }

        }
        #endregion

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}