<%@ WebHandler Language = "C#" Class="MESCommonBase" %>


using System;
using System.Web;
using Newtonsoft.Json;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using System.IO;
using System.Web.Script.Serialization;
using System.Data;
//using BizDataAccess;
using System.Data.SqlClient;//<a href="uploadInvoiceScan.ashx">uploadInvoiceScan.ashx</a>
using DataLinkMesWork;

using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
//using DbCallData;
using BasicData;
//using LitJson;
using DataLinkMesWork;

using System.Text.RegularExpressions;


public class MESCommonBase : IHttpHandler
{

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "application/json";
        var data = context.Request;
        var stream = new StreamReader(data.InputStream).ReadToEnd();
        string InvoiceNum;
        //JsonData jsonData;
        string responseText = "NULL";
        byte[] bytes = null;
        string fileName = "test.xsl";

        try
        {
            JsonData jsonData=null;
            try
            {
                jsonData = JsonMapper.ToObject(stream);

            }
            catch(Exception err)
            { }
            int type=0;
            try
            {
                if(jsonData!=null)
                {
                    if(jsonData.ContainsKey("Type"))
                    {
                        type =  Int32.Parse( jsonData["Type"].ToString());
                    }else if(jsonData.ContainsKey("type"))
                    {
                        type =  Int32.Parse( jsonData["type"].ToString());
                    }
                }
            }
            catch
            {

            }
            jsonobj dataobj=null;
            try
            {
                dataobj = new JavaScriptSerializer().Deserialize<jsonobj>(stream);
                if(dataobj!=null)
                {
                    type =  Int32.Parse(dataobj.Type);
                }

            }
            catch(Exception err)
            {
                dataobj = null;
            }

            if(dataobj==null)
            {
                try
                {
                    InvoiceNum = HttpContext.Current.Request["param"];
                    if(InvoiceNum!=null)
                    {
                        jsonData = JsonMapper.ToObject(InvoiceNum);
                        if(jsonData.ContainsKey("Type"))
                        {
                            type =  Int32.Parse( jsonData["Type"].ToString());
                        }else if(jsonData.ContainsKey("type"))
                        {
                            type =  Int32.Parse( jsonData["type"].ToString());
                        }
                    }
                }
                catch
                {
                    jsonData = null;
                }

            }
            string fileExtension="";
            HttpFileCollection files;
            string suffix;
            string name;
            switch(type)
            {
                case 3001:
                    try
                    {
                        //responseText = DataLinkMesWork.DbCallType1003_SqlCmd.SqlExec(dataobj.Param, jsonData);
                    }
                    catch (Exception err){}
                    context.Response.Write(responseText);
                    break;
                case 2001:

                    //DataLink.LogJsonData(jsonData);

                    MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension);
                    fileName = fileName + "."+fileExtension;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                    break;
                case 2201:

                    string Sn = jsonData["name"].ToString();
                    if (Sn.Length < 1) return;
                    //DataLink.LogJsonData(jsonData);
                    fileName = Sn + ".xls";
                    Excel_SY_OutPut2201(Sn, out  bytes);

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                    break;
                case 2002:
                    //DataLink.LogJsonData(jsonData);

                    MESDownloadExcel.ExcelWebCall.ExcelFilePdf(jsonData,out bytes,out fileName,out fileExtension);
                    fileName = fileName + "."+fileExtension;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();

                    break;

                case 2003:// 合成excel图片，下载
                    string[] dataimg;
                    if(context.Request.Form.Keys.Count>0)
                    {
                        dataimg = data.Form.GetValues(0);
                        MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref dataimg[0]);
                        if (bytes == null) return;
                        fileName = fileName + "."+dataimg[0];

                        HttpContext.Current.Response.ContentType = "application/octet-stream";
                        //HttpContext.Current.Response.ContentType = getContentType(extension);  
                        //通知浏览器下载文件而不是打开  
                        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                        HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                        HttpContext.Current.Response.BinaryWrite(bytes);
                        HttpContext.Current.Response.Flush();
                        HttpContext.Current.Response.Close();
                        //HttpContext.Current.Response.End();
                        //if(DataLinkMesWork.DataLink.isBakup=="1")
                        //{
                        //    DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix);
                        //}
                    }
                    break;
                case 2004:

                    //DataLink.LogJsonData(jsonData);
                    DataLinkMesWork.DataLink.ExePROCEDURE_Type2004(jsonData, out bytes, out fileName, out fileExtension);
                    //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension);
                    fileName = fileName + "."+fileExtension;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                    break;

                case 15://上传文件
                    files = context.Request.Files;
                    if (files.Count > 0)
                    {

                        fileName = files[0].FileName;
                        bytes = new byte[files[0].InputStream.Length];
                        files[0].InputStream.Read(bytes, 0, bytes.Length);

                        suffix = fileName.Substring(fileName.LastIndexOf(".")+1);
                        name = fileName.Substring(0,fileName.LastIndexOf("."));

                        responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes);
                        context.Response.Write(responseText);
                        //if(DataLinkMesWork.DataLink.isBakup=="1")
                        //{
                        //    DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes);
                        //}
                    }
                    else
                    {
                        fileName = "";
                        bytes = new byte[1];
                        suffix = "";
                        name = "";
                        responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes);
                        context.Response.Write(responseText);
                        //if(DataLinkMesWork.DataLink.isBakup=="1")
                        //{
                        //    DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes);
                        //}
                    }

                    break;
                case 16://上传文件
                    DataLink.LogJsonData(jsonData);
                    DataLinkMesWork.DataLink.ExePROCEDURE_Type16(jsonData, out bytes, out fileName, out suffix);
                    if (bytes == null) return;
                    fileName = fileName + "."+suffix;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                    //if(DataLinkMesWork.DataLink.isBakup=="1")
                    //{
                    //    DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix);
                    //}
                    break;
                case 4000:
                    string userIP;
                    HttpRequest Request = HttpContext.Current.Request;
                    // 如果使用代理，获取真实IP
                    if (context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"] != "")
                        userIP = context.Request.ServerVariables["REMOTE_ADDR"];
                    else
                        userIP = context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
                    if (userIP == null || userIP == "")
                        userIP = context.Request.UserHostAddress;
                    context.Response.Write(userIP);
                    break;
                default:

                    DataLink.LogJsonData(jsonData);

                    responseText = DataLinkMesWork.DataLink.SqlWebCall(type,jsonData,dataobj);

                    context.Response.Write(responseText);

                    //if(DataLinkMesWork.DataLink.isBakup=="1")
                    //{
                    //    DataLinkMesWork.DataLink.SqlWebCall_Bakup(type,jsonData,dataobj);
                    //}
                    break;
            }
        }
        catch(Exception err)
        {
            context.Response.Write(responseText);
        }
    }

    static public void Excel_SY_OutPut2201(string Sn, out byte[] bytes)
    {

        DataTable dt1 = new DataTable();
        SqlParameter[] thisParms1 = new SqlParameter[1];
        thisParms1[0] = new SqlParameter("总成号", Sn);
        string proName1 = "质量数据报表_表头数据查询";
        DataLink.ExecuteStoredProcedure(proName1, ref thisParms1, out dt1);

        DataTable dt2 = new DataTable();
        SqlParameter[] thisParms2 = new SqlParameter[1];
        thisParms2[0] = new SqlParameter("总成号", Sn);
        string proName2 = "质量数据报表_质量数据";
        DataLink.ExecuteStoredProcedure(proName2, ref thisParms2, out dt2);


        DataTable dt3 = new DataTable();
        SqlParameter[] thisParms3 = new SqlParameter[1];
        thisParms3[0] = new SqlParameter("总成号", Sn);
        string proName3 = "质量数据报表_过站数据";
        DataLink.ExecuteStoredProcedure(proName3, ref thisParms3, out dt3);


        DataTable dt4 = new DataTable();

        var res = Func( dt1, dt2, dt3, dt4,out bytes);

        //WriteExcel1001(ref wk, ds, firstBodyRow,  ishasbottom);
        //OutExcelFileMemoryStream(wk, out bytesOut);
    }
    static bool Func( DataTable dt1, DataTable dt2, DataTable dt3, DataTable dt4,out byte[] bytes)
    {
        bytes = null;
        try
        {
            string filePath = AppDomain.CurrentDomain.BaseDirectory+"\\TemplateMES.xls";


            //Server.MapPath(“~”) \\ Server.MapPath("/WebSite1")

            // IWorkbook wk = null;// --GetHSSFWorkbook(bytes, fileExtension);
            //var filePath = "TemplateMES.xls";
            // File.Copy(filePath, outPath, true);

            using (FileStream fs = File.OpenRead(filePath))
            {
                IWorkbook workbook = null;
                // 2007+
                if (filePath.IndexOf(".xlsx") > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                // 2003-
                else if (filePath.IndexOf(".xls") > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }

                ISheet sheet = workbook.GetSheetAt(0);

                // 从后往前写，避免增加行的问题
                sheet.GetRow(34).GetCell(0).SetCellValue("制单人：" + dt1.Rows[0]["制单人"].ToString());
                sheet.GetRow(34).GetCell(3).SetCellValue("版本号：" + dt1.Rows[0]["版本号"].ToString());
                sheet.GetRow(34).GetCell(7).SetCellValue("保存期限：" + dt1.Rows[0]["保存期限"].ToString());
                sheet.GetRow(35).GetCell(0).SetCellValue("制单日期：" + dt1.Rows[0]["制单日期"].ToString());
                sheet.GetRow(35).GetCell(3).SetCellValue("表单编号：" + dt1.Rows[0]["表单编号"].ToString());



                for (int i = 0; i < dt3.Rows.Count; i++)
                {
                    sheet.CopyRow(28, 28 + i + 1);
                }
                try
                {
                    for (int i = 0; i < dt3.Rows.Count; i++)
                    {
                        sheet.GetRow(28 + i).GetCell(0).SetCellValue(i+1);
                        sheet.GetRow(28 + i).GetCell(1).SetCellValue(dt3.Rows[i]["工位号"].ToString());
                        sheet.GetRow(28 + i).GetCell(2).SetCellValue(dt3.Rows[i]["工位名称"].ToString());
                        sheet.GetRow(28 + i).GetCell(3).SetCellValue(dt3.Rows[i]["在线时间"].ToString());
                        sheet.GetRow(28 + i).GetCell(4).SetCellValue(dt3.Rows[i]["到达时间"].ToString());
                        sheet.GetRow(28 + i).GetCell(6).SetCellValue(dt3.Rows[i]["离开时间"].ToString());
                        sheet.GetRow(28 + i).GetCell(9).SetCellValue(dt3.Rows[i]["是否合格"].ToString());
                        sheet.GetRow(28 + i).GetCell(10).SetCellValue(dt3.Rows[i]["操作者工号"].ToString());
                        sheet.GetRow(28 + i).GetCell(11).SetCellValue(dt3.Rows[i]["说明"].ToString());
                    }
                }
                catch
                {

                }

                try
                {
                    sheet.GetRow(17).GetCell(0).SetCellValue("检测时间：" + dt1.Rows[0]["检测时间"].ToString());

                    sheet.GetRow(18).GetCell(0).SetCellValue("机型代码：" + dt1.Rows[0]["机型代码"].ToString());
                    sheet.GetRow(18).GetCell(3).SetCellValue("1挡速比：" + dt1.Rows[0]["1挡速比"].ToString());
                    sheet.GetRow(18).GetCell(5).SetCellValue("1挡自学习：" + dt1.Rows[0]["1挡自学习"].ToString());
                    sheet.GetRow(18).GetCell(7).SetCellValue("1挡NVH：" + dt1.Rows[0]["1挡NVH"].ToString());
                    sheet.GetRow(18).GetCell(9).SetCellValue("倒挡开关：" + dt1.Rows[0]["倒挡开关"].ToString());

                    sheet.GetRow(19).GetCell(0).SetCellValue("检测结果：" + dt1.Rows[0]["检测结果"].ToString());
                    sheet.GetRow(19).GetCell(3).SetCellValue("2挡速比：" + dt1.Rows[0]["2挡速比"].ToString());
                    sheet.GetRow(19).GetCell(5).SetCellValue("2挡自学习：" + dt1.Rows[0]["2挡自学习"].ToString());
                    sheet.GetRow(19).GetCell(7).SetCellValue("2挡NVH：" + dt1.Rows[0]["2挡NVH"].ToString());
                    sheet.GetRow(19).GetCell(9).SetCellValue("空挡开关：" + dt1.Rows[0]["空挡开关"].ToString());

                    sheet.GetRow(20).GetCell(3).SetCellValue("3挡速比：" + dt1.Rows[0]["3挡速比"].ToString());
                    sheet.GetRow(20).GetCell(5).SetCellValue("3挡自学习：" + dt1.Rows[0]["3挡自学习"].ToString());
                    sheet.GetRow(20).GetCell(7).SetCellValue("3挡NVH：" + dt1.Rows[0]["3挡NVH"].ToString());
                    sheet.GetRow(20).GetCell(9).SetCellValue("取力器PTO：" + dt1.Rows[0]["取力器PTO"].ToString());

                    sheet.GetRow(21).GetCell(3).SetCellValue("4挡速比：" + dt1.Rows[0]["4挡速比"].ToString());
                    sheet.GetRow(21).GetCell(5).SetCellValue("4挡自学习：" + dt1.Rows[0]["4挡自学习"].ToString());
                    sheet.GetRow(21).GetCell(7).SetCellValue("4挡NVH：" + dt1.Rows[0]["4挡NVH"].ToString());
                    sheet.GetRow(21).GetCell(9).SetCellValue("里程表：" + dt1.Rows[0]["里程表"].ToString());

                    sheet.GetRow(22).GetCell(3).SetCellValue("5挡速比：" + dt1.Rows[0]["5挡速比"].ToString());
                    sheet.GetRow(22).GetCell(5).SetCellValue("5挡自学习：" + dt1.Rows[0]["5挡自学习"].ToString());
                    sheet.GetRow(22).GetCell(7).SetCellValue("5挡NVH：" + dt1.Rows[0]["5挡NVH"].ToString());
                    sheet.GetRow(22).GetCell(9).SetCellValue("换挡平顺性：" + dt1.Rows[0]["换挡平顺性"].ToString());

                    sheet.GetRow(23).GetCell(3).SetCellValue("6挡速比：" + dt1.Rows[0]["6挡速比"].ToString());
                    sheet.GetRow(23).GetCell(5).SetCellValue("6挡自学习：" + dt1.Rows[0]["6挡自学习"].ToString());
                    sheet.GetRow(23).GetCell(7).SetCellValue("6挡NVH：" + dt1.Rows[0]["6挡NVH"].ToString());

                    sheet.GetRow(24).GetCell(3).SetCellValue("R挡速比：" + dt1.Rows[0]["R挡速比"].ToString());
                    sheet.GetRow(24).GetCell(5).SetCellValue("R挡自学习：" + dt1.Rows[0]["R挡自学习"].ToString());
                    sheet.GetRow(24).GetCell(7).SetCellValue("R挡NVH：" + dt1.Rows[0]["R挡NVH"].ToString());
                }
                catch
                {

                }


                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    sheet.CopyRow(10, 10 + i + 1);
                }
                try
                {
                    for (int i = 0; i < dt2.Rows.Count; i++)
                    {
                        sheet.GetRow(10 + i).GetCell(0).SetCellValue(i+1);
                        sheet.GetRow(10 + i).GetCell(1).SetCellValue(dt2.Rows[i]["测量工位号"].ToString());
                        sheet.GetRow(10 + i).GetCell(2).SetCellValue(dt2.Rows[i]["测量项目描述"].ToString());
                        sheet.GetRow(10 + i).GetCell(3).SetCellValue(dt2.Rows[i]["测量单位"].ToString());
                        sheet.GetRow(10 + i).GetCell(4).SetCellValue(dt2.Rows[i]["检验类型"].ToString());
                        sheet.GetRow(10 + i).GetCell(5).SetCellValue(dt2.Rows[i]["实测值"].ToString());
                        sheet.GetRow(10 + i).GetCell(6).SetCellValue(dt2.Rows[i]["标准值"].ToString());
                        sheet.GetRow(10 + i).GetCell(7).SetCellValue(dt2.Rows[i]["上限值"].ToString());
                        sheet.GetRow(10 + i).GetCell(8).SetCellValue(dt2.Rows[i]["下限值"].ToString());
                        sheet.GetRow(10 + i).GetCell(9).SetCellValue(dt2.Rows[i]["检验结果"].ToString());
                        sheet.GetRow(10 + i).GetCell(10).SetCellValue(dt2.Rows[i]["员工工号"].ToString());
                        sheet.GetRow(10 + i).GetCell(11).SetCellValue(dt2.Rows[i]["说明"].ToString());
                    }

                }
                catch
                {

                }
                try
                {
                    sheet.GetRow(4).GetCell(0).SetCellValue("订单号：" + dt1.Rows[0]["订单号"].ToString());
                    sheet.GetRow(4).GetCell(2).SetCellValue("SN号：" + dt1.Rows[0]["SN号"].ToString());

                    sheet.GetRow(5).GetCell(0).SetCellValue("零件号：" + dt1.Rows[0]["零件号"].ToString());
                    sheet.GetRow(5).GetCell(2).SetCellValue("福田零件号：" + dt1.Rows[0]["福田零件号"].ToString());
                    sheet.GetRow(5).GetCell(5).SetCellValue("产品型号：" + dt1.Rows[0]["产品型号"].ToString());

                    sheet.GetRow(6).GetCell(0).SetCellValue("序列号：" + dt1.Rows[0]["序列号"].ToString());
                    sheet.GetRow(6).GetCell(2).SetCellValue("生产日期：" + dt1.Rows[0]["生产日期"].ToString());

                    sheet.GetRow(7).GetCell(0).SetCellValue("检验日期：" + dt1.Rows[0]["检验日期"].ToString());
                }
                catch
                {

                }


                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                bytes = ms.ToArray();

                //using (FileStream localFs = File.OpenWrite(outPath))
                //{
                //    workbook.Write(localFs);
                //    localFs.Close();
                //}
            }
            return true;
        }
        catch (Exception err)
        {
            return false;
        }
    }

    public class SqlCmd
    {
        //public Insert Insert;
        //public Select Select;
        //public Select_check Select_check;
        //public Select_pages Select_pages;
        //public Select_pages_check Select_pages_check;
        //public Delete Delete;
        //public Update Update;

        static public string SqlExec(string sqlCmd)
        {
            var cmd  = JsonConvert.DeserializeObject<Cmd>(sqlCmd);
            string result = "{ }";
            switch (cmd.cmd)
            {
                case Cmd.Insert:
                    result = Insert(sqlCmd);
                    break;
                case Cmd.Select:
                    result = Select(sqlCmd);
                    break;
                case Cmd.Select_check:
                    result = Select_check(sqlCmd);
                    break;
                case Cmd.Select_pages:
                    result = Select_pages(sqlCmd);
                    break;
                case Cmd.Select_pages_check:
                    result = Select_pages_check(sqlCmd);
                    break;
                case Cmd.Delete:
                    result = Delete(sqlCmd);
                    break;
                case Cmd.Update:
                    result = Update(sqlCmd);
                    break;
            }
            return result;
        }

        static private string Select(string sqlCmd)
        {
            string result = "{ }";
            string sql = SelectSql(sqlCmd);
            DataTable dt;
            SqlCall.ExecuteDataTable(sql, out dt);
            result = JsonConvert.SerializeObject(dt);
            return result;
        }
        static private string Select_check(string sqlCmd)
        {
            string result = "{ }";
            string sql = SelectSql_check(sqlCmd);
            DataTable dt;
            SqlCall.ExecuteDataTable(sql, out dt);
            result = JsonConvert.SerializeObject(dt);
            return result;
        }
        static private string Select_pages(string sqlCmd)
        {
            string tbname;
            string fieldkey;
            string where;
            string fieldshow;
            string fieldorder;
            DataTable dt = new DataTable();
            string result = "{ }";
            var item = JsonConvert.DeserializeObject<Select_pages>(sqlCmd);
            int pagecurrent = item.PageCurrent;
            int pagesize = item.PageSize;
            int pagecount;
            int itemcount;
            where = GetWhereString(item.Where, item.WhereTime);
            where = where.Replace("WHERE", "");
            fieldorder = GetOrderPage(item.fieldOrder);
            fieldshow = Get_fieldshow(item.fieldshow);
            fieldkey = item.fieldkey;
            tbname = item.tbname;
            SqlCall.ExecPageQuery(tbname, fieldkey, where, fieldshow, fieldorder, pagecurrent, pagesize, out pagecount, out itemcount, out dt);
            result = JsonConvert.SerializeObject(dt);
            string resultOutput = "[{ \"ItemCount\":\"" + itemcount + "\",\"PageCount\":\"" + pagecount + "\"}]";
            string jsonStr = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}";
            result = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}";
            return result;
        }

        static private string Select_pages_check(string sqlCmd)
        {
            string tbname;
            string fieldkey;
            string where;
            string fieldshow;
            string fieldorder;
            int pagecurrent = 1;
            int pagesize = 15;
            int pagecount;
            int itemcount;
            DataTable dt = new DataTable();
            string result = "{ }";
            var item = JsonConvert.DeserializeObject<Select_pages_check>(sqlCmd);
            where = GetWhereString_check(item.Where, item.WhereTime);
            where = where.Replace("WHERE", "");
            fieldorder = GetOrderPage(item.fieldOrder);
            fieldshow = Get_fieldshow(item.fieldshow);
            fieldkey = "'" + item.fieldkey + "'";
            tbname = item.tbname;
            SqlCall.ExecPageQuery(tbname, fieldkey, where, fieldshow, fieldorder, pagecurrent, pagesize, out pagecount, out itemcount, out dt);
            result = JsonConvert.SerializeObject(dt);
            string resultOutput = "[{ \"ItemCount\":\"" + itemcount + "\",\"PageCount\":\"" + pagecount + "\"}]";
            string jsonStr = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}";
            result = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}";
            return result;
        }
        static private string Insert(string sqlCmd)
        {
            string result = "[{ \"result\":\"0\"}]";
            string sql;
            sql = InsertSql(sqlCmd);
            string errorMessage;
            SqlCall.ExecuteNonQuery(sql, out errorMessage);
            if (errorMessage == "")
            {
                result = "[{ \"result\":\"1\"}]";
            }
            return result;
        }

        static private string Delete(string sqlCmd)
        {
            string result = "[{ \"result\":\"0\"}]";
            string sql;
            sql = DeleteSql(sqlCmd);
            string errorMessage;
            SqlCall.ExecuteNonQuery(sql, out errorMessage);
            if (errorMessage == "")
            {
                result = "[{ \"result\":\"1\"}]";
            }
            return result;
        }
        static private string Update(string sqlCmd)
        {
            string result = "[{ \"result\":\"0\"}]";
            string sql;
            sql = UpdateSql(sqlCmd);
            if(sql.Length<1) return result;
            string errorMessage;
            SqlCall.ExecuteNonQuery(sql, out errorMessage);
            if (errorMessage == "")
            {
                result = "[{ \"result\":\"1\"}]";
            }
            return result;
        }
        static private string InsertSql(string sqlCmd)
        {
            var itemSql = JsonConvert.DeserializeObject<Insert>(sqlCmd);

            string sql = "";
            string field="";
            string value="";
            foreach(var item in itemSql.fieldshow)
            {
                field = field + item + ",";
            }
            field = field.Remove(field.LastIndexOf(","), 1);
            field = "(" + field + ")";

            foreach (var item in itemSql.values)
            {
                if(item is string)
                {
                    if (cheackValueSpecialFormat(item.ToString()))
                    {
                        value = value + " "+item + " , ";
                    }
                    else
                    {
                        value = value + " '"+item + "' , ";
                    }

                }
                else
                {
                    value = value + item + ",";
                }
            }
            value = value.Remove(value.LastIndexOf(","), 1);
            value = "(" + value + ")";

            sql = " INSERT INTO " + itemSql.tbname + field + " VALUES " + value;

            return sql;
        }
        static private string DeleteSql(string sqlCmd)
        {
            var item = JsonConvert.DeserializeObject<Delete>(sqlCmd);
            string where;
            where = GetWhereString(item.Where, item.WhereTime);
            if (where.Length < 1) return "";
            string sql;
            sql = "DELETE  FROM " + item.tbname;
            if (where.Length > 1)
            {
                sql = sql + " where " + where;
            }
            return sql;
        }
        static private string UpdateSql(string sqlCmd)
        {
            var itemSql = JsonConvert.DeserializeObject<Update>(sqlCmd);
            string field = "";
            if(itemSql.fieldshow.Count<1) return "";
            if(itemSql.fieldshow.Count!= itemSql.values.Count) return "";
            for (int i=0;i< itemSql.fieldshow.Count;i++)
            {
                if(itemSql.values[i] is string)
                {
                    if (cheackValueSpecialFormat(itemSql.values[i].ToString())) {
                        field = field + itemSql.fieldshow[i] + " = " + itemSql.values[i]+" , ";
                    }
                    else
                    {
                        field = field + itemSql.fieldshow[i] + " = '" + itemSql.values[i]+"' , ";
                    }
                }
                else
                {
                    field = field +  itemSql.fieldshow[i] + " = " + itemSql.values[i]+" , ";
                }
            }
            field = field.Remove(field.LastIndexOf(","), 1);
            string where;
            where = GetWhereString(itemSql.Where, itemSql.WhereTime);
            if (where.Length < 1) return "";
            string sql;
            sql = "UPDATE " + itemSql.tbname + " SET " + field;
            if (where.Length > 1)
            {
                sql = sql+ " where " + where;
            }
            return sql;
        }
        static private bool cheackValueSpecialFormat(string fieldValue) {
            bool isSpecialFormat = false;
            fieldValue = fieldValue.ToLower();
            if (fieldValue.Contains("getdate")) {
                isSpecialFormat = true;
            }
            if (fieldValue.Contains("convert(")) {
                isSpecialFormat = true;
            }
            if (fieldValue.Contains("left(")) {
                isSpecialFormat = true;
            }
            if (fieldValue.Contains("rank()")) {
                isSpecialFormat = true;
            }
            if (fieldValue.Contains("case ")) {
                isSpecialFormat = true;
            }
            if (fieldValue.Contains("row_number ")) {
                isSpecialFormat = true;
            }

            return isSpecialFormat;
        }
        static private string SelectSql(string sqlCmd)
        {
            var item = JsonConvert.DeserializeObject<Select>(sqlCmd);
            string where;
            string orderby;
            where = GetWhereString(item.where, item.WhereTime);
            string field="";
            field = Get_fieldshow( item.fieldshow);
            string sql;
            sql = "SELECT " + field + " FROM " + item.tbname;
            if(where.Length>1)
            {
                sql = sql + " WHERE " + where;
                if(item.fieldOrder.Count>0)
                {
                    orderby = GetOrder(item.fieldOrder);
                    sql = sql + orderby;
                }
            }
            return sql;
        }

        static  private string Get_fieldshow(List<string> fieldshow)
        {
            string field="";
            foreach(var item in fieldshow)
            {
                field = field + item + ", ";
            }
            field = field.Remove(field.LastIndexOf(","), 1);
            return field;
        }

        static private string SelectSql_check(string sqlCmd)
        {
            var item = JsonConvert.DeserializeObject<Select_check>(sqlCmd);
            string where;
            string orderby;
            string field="";
            field = Get_fieldshow( item.fieldshow);

            where = GetWhereString_check(item.where_check, item.WhereTime);
            string sql;
            sql = "SELECT " + field + " FROM " + item.tbname;
            if (where.Length > 1)
            {
                sql = sql + where;
                if (item.fieldOrder.Count > 0)
                {
                    orderby = GetOrder(item.fieldOrder);
                    sql = sql + orderby;
                }
            }
            return sql;
        }
        static string GetOrder(List<string> item)
        {
            string order = "";
            foreach(var s in item)
            {
                order = order + s+",";
            }
            order = order.Remove(order.LastIndexOf(","), 1);
            order = " ORDER BY " + order;
            return order;

        }
        static string GetOrderPage(List<string> item)
        {
            string order = "";
            foreach (var s in item)
            {
                order = order + s + ",";
            }
            order = order.Remove(order.LastIndexOf(","), 1);
            return order;

        }
        static string GetWhereString(List<Where> Where, List<WhereTime> WhereTime)
        {
            string where = " 1 = 1 ";
            string whereTime = "";
            if(Where.Count > 0)
            {
                where = where + GetWhere(Where);
                if(WhereTime.Count > 0)
                {
                    whereTime = GetWhereTime(WhereTime);
                    where = where + " AND " + whereTime;
                }
            }
            else
            {
                if (WhereTime.Count > 0)
                {
                    where = GetWhereTime(WhereTime);
                }
            }

            return where;
        }
        static string GetWhere(List<Where> Where)
        {
            string where="";
            for (int i=0; i<Where.Count;i++)
            {
                string item;
                item = GetWhereItem(Where[i]);
                where = where + " AND " + item;
            }

            return where;
        }
        static private string GetWhereTime(List<WhereTime>  WhereTime)
        {
            string where = "";
            for (int i = 0; i < WhereTime.Count; i++)
            {
                string item;
                item = GetWhereTimeItem(WhereTime[i]);
                if (i == 0)
                {
                    where = where + item;
                } else
                {
                    where = where + " AND " + item;
                }

            }
            return where;
        }
        static string GetOrder(List<Where> Where)
        {
            string where = "";
            for (int i = 0; i < Where.Count; i++)
            {
                string item;
                item = GetWhereItem(Where[i]);
                if (i == 0)
                {
                    where = " WHERE " + item;
                }
                else
                {
                    where = where + " AND " + item;
                }
            }

            return where;
        }
        static string GetWhereString_check(List<Where_check> Where, List<WhereTime> WhereTime)
        {
            string where = "";
            string whereTime = "";
            int itemCount=0;
            for (int i = 0; i < Where.Count; i++)
            {
                string item;

                if (Where[i].IsCheck)
                {
                    item = GetWhereItem(Where[i]);
                    if (itemCount == 0)
                    {
                        where = " WHERE " + item;
                    }
                    else
                    {
                        where = where + " AND " + item;
                    }
                    itemCount++;
                }
            }

            if(itemCount>0)
            {
                if(WhereTime.Count > 0)
                {
                    //whereTime = GetWhereTime(WhereTime);

                    where = where + " AND " + whereTime;
                }
            }
            else
            {
                if (WhereTime.Count > 0)
                {
                    //whereTime = GetWhereTime(WhereTime);

                    where = " WHERE " + whereTime;
                }
            }

            return where;
        }

        static private string GetWhereItem(Where_check item)
        {
            string where;
            if (item.value is string)
            {
                where = " (" + item.fieldname + "='" + item.value + "') ";

            }
            else
            {
                where = " (" + item.fieldname + "=" + item.value + ") ";
            }
            return where;
        }
        static private string GetWhereTimeItem(WhereTime item)
        {
            string where = " ("+item.fieldname + ">='" + item.start + "' AND " + item.fieldname + "<='" + item.end + "') ";
            return where;
        }
        static private string GetWhereItem(Where item)
        {
            string where;

            if (item.value is string)
            {
                if(item.logic==null)
                {
                    where = "(" + item.fieldname + "='" + item.value + "')";
                }
                else
                {
                    where = "(" + item.fieldname + item.logic + "'" +item.value + "')";
                }
            }
            else
            {
                if(item.logic==null)
                {
                    where = "(" + item.fieldname + "=" + item.value + ")";

                }
                else
                {
                    where = "(" + item.fieldname + item.logic + item.value + ")";

                }
            }
            return where;
        }
    }
    public class SqlCall
    {

        public static bool ExecuteDataTable(string sql, out DataTable dt)
        {
            dt = new DataTable();
            try
            {
                DataSet ds = new DataSet();
                bool r = DataLink.ExecuteDataset(sql, out  ds);
                dt = ds.Tables[0];
                return r;
            }
            catch
            {
                return false;
            }
        }

        public static bool ExecuteNonQuery(string sql, out string errorMessage)
        {
            return DataLink.ExecuteNonQuery(sql, out errorMessage);
        }
        public static bool ExecPageQuery(string tbname, string fieldkey, string where, string fieldshow, string fieldorder, int pagecurrent, int pagesize, out int pagecount, out int itemcount, out DataTable dt)
        {
            dt = new DataTable();
            SqlParameter[] thisParms = new SqlParameter[9];
            thisParms[0] = new SqlParameter("@tbname", tbname);
            thisParms[0].Direction = ParameterDirection.Input;
            thisParms[1] = new SqlParameter("@fieldkey", fieldkey);
            thisParms[1].Direction = ParameterDirection.Input;
            thisParms[2] = new SqlParameter("@where", where);
            thisParms[2].Direction = ParameterDirection.Input;
            thisParms[3] = new SqlParameter("@fieldshow", fieldshow);
            thisParms[3].Direction = ParameterDirection.Input;
            thisParms[4] = new SqlParameter("@fieldorder", fieldorder);
            thisParms[4].Direction = ParameterDirection.Input;
            thisParms[5] = new SqlParameter("@pagecurrent", pagecurrent);
            thisParms[5].Direction = ParameterDirection.Input;
            thisParms[6] = new SqlParameter("@pagesize", pagesize);
            thisParms[6].Direction = ParameterDirection.Input;
            thisParms[7] = new SqlParameter("@pagecount", SqlDbType.Int, 32);
            thisParms[7].Direction = ParameterDirection.Output;
            thisParms[8] = new SqlParameter("@itemcount", SqlDbType.Int, 32);
            thisParms[8].Direction = ParameterDirection.Output;

            pagecount = 0;
            itemcount = 0;
            string procedureName = "sp_xt_pagesplit1";
            if (DataLink.ExecuteStoredProcedure(procedureName, ref thisParms, out dt))
            {
                pagecount = Convert.ToInt32(thisParms[7].Value.ToString());
                itemcount = Convert.ToInt32(thisParms[8].Value.ToString());
                return true;
            }
            return false;
        }
    }
    public class InsertType
    {
        public string type;
        public Insert value;
    }
    public class SelectType
    {
        public string type;
        public Select value;
    }
    public class Select_checkType
    {
        public string type;
        public Select_check value;
    }
    public class Select_pagesType
    {
        public string type;
        public Select_pages value;
    }
    public class Select_pages_checkType
    {
        public string type;
        public Select_pages_check value;
    }
    public class DeleteType
    {
        public string type;
        public Delete value;
    }
    public class UpdateType
    {
        public string type;
        public Update value;
    }
    public class Cmd
    {
        public string cmd;
        public const  string Insert = "Insert";
        public const  string Select = "Select";
        public const  string Select_check = "Select_check";
        public const  string Select_pages = "Select_pages";
        public const  string Select_pages_check = "Select_pages_check";
        public const  string Delete = "Delete";
        public const  string Update = "Update";
    }
    public class Insert
    {
        public string cmd;
        public string tbname;
        public List<string> fieldshow = new List<string>();
        public List<object> values = new List<object>();
    }
    public class Select
    {
        public string cmd;
        public string tbname;
        public List<string> fieldshow = new List<string>();
        public List<Where> where = new List<Where>();
        public List<string> fieldOrder = new List<string>();
        public List<WhereTime> WhereTime = new List<WhereTime>();
    }
    public class Select_check
    {
        public string cmd;
        public string tbname;
        public List<string> fieldshow = new List<string>();
        public List<Where_check> where_check = new List<Where_check>();
        public List<string> fieldOrder = new List<string>();
        public List<WhereTime> WhereTime = new List<WhereTime>();
    }
    public class Select_pages
    {
        public string cmd;
        public string tbname;
        public string fieldkey;
        public List<string> fieldshow = new List<string>();
        public List<Where> Where = new List<Where>();
        public List<string> fieldOrder = new List<string>();
        public int PageCurrent;
        public int PageSize;
        public int PageCount;
        public int ItemCount;
        public List<WhereTime> WhereTime = new List<WhereTime>();
    }
    public class Select_pages_check
    {
        public string cmd;
        public string tbname;
        public string fieldkey;
        public List<string> fieldshow = new List<string>();
        public List<Where_check> Where = new List<Where_check>();
        public List<string> fieldOrder = new List<string>();
        public int PageCurrent;
        public int PageSize;
        public int PageCount;
        public int ItemCount;
        public List<WhereTime> WhereTime = new List<WhereTime>();
    }
    public class Delete
    {
        public string cmd;
        public string tbname;
        public List<Where> Where = new List<Where>();
        public List<WhereTime> WhereTime = new List<WhereTime>();
    }
    public class Update
    {
        public string cmd;
        public string tbname;
        public List<string> fieldshow = new List<string>();
        public List<object> values = new List<object>();
        public List<Where> Where = new List<Where>();
        public List<WhereTime> WhereTime = new List<WhereTime>();
    }
    public class Where
    {
        public string fieldname;
        public object value;
        public string logic;

    }
    public class Where_check
    {
        public bool IsCheck;
        public string fieldname;
        public object value;
    }
    public class WhereTime
    {
        public string fieldname;
        public string start;
        public string end;
    }
}