unity中数据库查询分页加搜索

数据库sql表

————————————————————-

————————————————————-

——————————————-

drop table if exists user_account;

/*==============================================================*/
/* Table: user_account                                          */
/*==============================================================*/
create table user_account
(
id                   bigint not null auto_increment,
user_id              bigint not null,
rmb_balance          decimal(10,2) not null,
avail                decimal(10,2) not null,
change_time          datetime not null,
primary key (id)
);

—————————————————-

drop table if exists user_account_log;

/*==============================================================*/
/* Table: user_account_log                                      */
/*==============================================================*/
create table user_account_log
(
id                   bigint not null auto_increment,
user_id              bigint not null,
type                 tinyint(1) not null comment ‘1 充值
2 消费
3 提现
4 退款’,
change_amount        decimal(10,2) not null,
rmb_balance          decimal(10,2) not null,
change_time          datetime not null,
source_id            varchar(100) comment ‘源单号，和该条记录相关的单号，如订单号，也可以为其他类型的单号。’,
primary key (id)
);
————————————————————-

————————————————————-

unity 账户日志

//账户日志
//
using UnityEngine;
using System.Collections;
using System.Collections.Generic;
using Newtonsoft.Json.Linq;
using System.Text;

public class bills_inquiry : MonoBehaviour {

    public GUISkin guiskin;
    //~ public GUIStyle window546;
    public GUIStyle closedButton;
    public GUIStyle Labeltext;

    Rect windowRect_bills_inquiry;

    public bool Isbills_inquiry_Open=false;
    /*****需要从用户登录得到的信息中获取*****/
    int StrMyId;       //user的id
    /****************************************/
    //~ string Strtype = ""; //类型
    //~ string Strchange_amount = "";    //变更金额
    //~ string Strrmb_balance = "";      //余额
    //~ string Strchange_time=";      //变更时间
    //~// string Strsource_id=";      //源单号，和该条记录相关的单号，如订单号，也可以为其他类型的单号。

    int SumPage;   //共多少页
    //~ int totalPage
    int PageSize = 10;
    int Page = 1;

    string EndDate = System.DateTime.Now.ToString("yyyy-MM-dd");
    string StartDate = System.DateTime.Now.AddDays(-365).ToString("yyyy-MM-dd");
    string StrStartDate = "";
    string StrEndDate = "";

    string[] Strtype = new string[]{}; //类型
    string[] Strchange_amount = new string[]{};    //变更金额
    string[] Strrmb_balance = new string[]{};      //余额
    string[] Strchange_time= new string[]{};      //变更时间
    //~ //string[] Strsource_id=new string[]{};      //源单号，和该条记录相关的单号，如订单号，也可以为其他类型的单号。

    string StrBalance="0.00″;        //当前余额
    // Use this for initialization
    bool loadonce=false;
    void Start()
    {
        StartCoroutine(MySelectData(1));
        windowRect_bills_inquiry = new Rect(30, 40, 546, 410);
    }

    // Update is called once per frame
    void Update () {

    }
    void OnGUI ()
    {
        GUI.skin=guiskin;
        GUI.contentColor=Color.black;
        if(Isbills_inquiry_Open)
        {
            if(loadonce==false)
            {
                StartCoroutine(MySelectData(1));
                loadonce=true;
                }else
                {
                    windowRect_bills_inquiry= GUI.Window(23, windowRect_bills_inquiry,DoMyWindow_bills_inquiry, "账户日志");
                }
            }
        }

        void DoMyWindow_bills_inquiry(int windowID)
        {
            GUILayout.BeginArea(new Rect (35, 70,546,410));

            GUILayout.Space (5);
            GUILayout.BeginHorizontal();
            GUILayout.Space (5);
            GUILayout.Label ("起止日期:",GUILayout.Width(60));
            StartDate=GUILayout.TextField (StartDate,10,GUILayout.Width(80));
            EndDate=GUILayout.TextField (EndDate,10,GUILayout.Width(80));
            if(GUILayout.Button("搜索",GUILayout.Width(40)))
            {
                StrStartDate = StartDate;
                StrEndDate = EndDate;
                StartCoroutine(MySelectData(1));
            }
            GUILayout.Space (80);
            GUILayout.Label ("当前余额:",GUILayout.Width(60));
            GUILayout.Label (StrBalance+"元",GUILayout.MinWidth(120),GUILayout.ExpandWidth(false));

            GUILayout.EndHorizontal();

            GUILayout.Space (1);

            GUILayout.BeginHorizontal();
            GUILayout.Label ("变更时间:",Labeltext,GUILayout.Width(120));
            GUILayout.Space (20);
            GUILayout.Label ("变更金额:",Labeltext,GUILayout.Width(130));
            GUILayout.Space (10);
            GUILayout.Label ("类型:",Labeltext,GUILayout.Width(60));
            GUILayout.Space (10);
            GUILayout.Label ("账户余额:",Labeltext,GUILayout.Width(130));
            GUILayout.EndHorizontal();

            GUILayout.Space (0);
            GUILayout.BeginArea(new Rect(4,60,476,211),","box");
            GUILayout.BeginVertical();
            for(int i=0;i<Strtype.Length;i++)
            {
                GUILayout.BeginHorizontal();
    GUILayout.Label (Strchange_time[i].Substring(0,10)+" "+Strchange_time[i].Substring(11),Labeltext,GUILayout.Width(120));    //"变更时间:"
    GUILayout.Space (20);
    GUILayout.Label (Strchange_amount[i],Labeltext,GUILayout.Width(130));    //"变更金额:"
    GUILayout.Space (10);
    string type="未知";
    switch(Strtype[i])
    {
        case "1":
        type="充值";
        break;
        case "2":
        type="消费";
        break;
        case "3":
        type="提现";
        break;
        case "4":
        type="退款";
        break;
    }
    GUILayout.Label (type,Labeltext,GUILayout.Width(60));     //"类型:"
    GUILayout.Space (10);
    GUILayout.Label(Strrmb_balance[i], Labeltext, GUILayout.Width(130));    //"账户余额:"
    GUILayout.EndHorizontal();
    }
    GUILayout.EndVertical();
    GUILayout.EndArea();
    GUILayout.BeginHorizontal();
    //~ GUILayout.Button("末页",GUILayout.Width(40));
    GUILayout.EndHorizontal();

    GUILayout.EndArea();
    //***************分页*******************
    //~ int page=10;
    //~ GUILayout.BeginArea(new Rect (0,windowRect_bills_inquiry.height-60,546,25));
    //~ GUILayout.BeginHorizontal();
    //~ print("//"+(windowRect_bills_inquiry.width/2-(40+17*page)));
    //~ GUILayout.Space(windowRect_bills_inquiry.width/2-(40+17*page));
    //~ GUILayout.Button("首页",GUILayout.Width(40));
    //~ for(int i=1;i<=page;i++)
    //~ {
    // GUILayout.Button(""+i,GUILayout.Width(30),GUILayout.Height(20));
    //~ GUILayout.Button(""+i,GUILayout.Width(30));
    //~ }
    //~ GUILayout.Button("末页",GUILayout.Width(40));
    //~ GUILayout.EndHorizontal();
    //~ GUILayout.EndArea();
    //———————————-//
    GUILayout.BeginArea(new Rect(0, windowRect_bills_inquiry.height – 60, 546, 25));
    GUILayout.BeginHorizontal();
    GUILayout.Space(windowRect_bills_inquiry.width / 2 – (160));

    if (GUILayout.Button("首页", GUILayout.Width(40)))
    {
        Page = 1;
        StartCoroutine(MySelectData(1));
    }

    if (GUILayout.Button("上一页", GUILayout.Width(50)))
    {
        if (Page <= 1)
        {
            Page = 1;
        }
        else
        {
            Page -= 1;
            StartCoroutine(MySelectData(Page));
        }
    }

    GUILayout.Space(20);
    GUILayout.Label("第" + Page + "页", GUILayout.MaxWidth(50));

    GUILayout.Label("共" + SumPage + "页", GUILayout.Width(50));
    if (GUILayout.Button("下一页", GUILayout.Width(50)))
    {
        if (Page >= SumPage)
        {
            Page = SumPage;
        }
        else
        {
            Page += 1;
            StartCoroutine(MySelectData(Page));
        }
    }
    if (GUILayout.Button("末页", GUILayout.Width(40)))
    {
        Page = SumPage;
        StartCoroutine(MySelectData(Page));
    }

    GUILayout.EndHorizontal();
    GUILayout.EndArea();

    //****************************************
    //~ GUI.Button(new Rect(windowRect_bills_inquiry.width/2-60,windowRect_bills_inquiry.height-60,40,20),"首页");
    //~ GUI.Button(new Rect(windowRect_bills_inquiry.width/2+20,windowRect_bills_inquiry.height-60,40,20),"末页");
    /*************窗口关闭按钮*************/
    if(GUI.Button(new Rect(503,21,21,10),",closedButton))
    {
        Isbills_inquiry_Open=false;
        loadonce=false;
    }
    GUI.DragWindow (new Rect (0,0, 10000, 410));
    }

    private string jsonURLBillsInquiry = SystemParamInit.getPhpServerURL() + "finance/user_account_log_select.php"; //获取用户表的point信息
    private string jsonURLUser_Account = SystemParamInit.getPhpServerURL() + "finance/user_account_select.php?user_id="; //获取用户余额
    //查询user_account_log表
    public IEnumerator MySelectData(int Page)
    {
        StrMyId=ObjectValue.userid;
        //*******************/获取账号余额/***********************//
        WWW getwwwUser_Account = new WWW(jsonURLUser_Account + '"' + StrMyId + '"');
        yield return getwwwUser_Account;
        string myHashMyUser_Account = getwwwUser_Account.data;
        //~ print(getwwwMyuser.data);
        JObject JMyUser_Account = JObject.Parse(myHashMyUser_Account);
        StrBalance = (string)JMyUser_Account["rmb_balance"];

        //____获取账户日志_____//
        Hashtable mydata = new Hashtable();
        mydata.Add("user_id", StrMyId);
        mydata.Add("page", Page);
        mydata.Add("startdate", StrStartDate);
        mydata.Add("enddate", StrEndDate);

        //将数据转换为json字符串
        string jsonstring = HashTable2Json(mydata);
        WWWForm form = new WWWForm();
        form.AddField("jsonstring", jsonstring);
        WWW getwwwMyuser = new WWW(jsonURLBillsInquiry, form);
        yield return getwwwMyuser;

        List<string> types = new List<string>();
        List<string> change_amounts = new List<string>();
        List<string> rmb_balances = new List<string>();
        List<string> change_times = new List<string>();
        //~ List<string> source_ids= new List<string>();

        string myHashMyuser = getwwwMyuser.data;
        print(getwwwMyuser.data);
        JObject o = JObject.Parse(myHashMyuser);
        JArray sizes = (JArray)o["jsondata"];
        JArray pages = (JArray)o["page"];
        print(pages);

        for (int i = 0; i < pages.Count; i++)
        {
            SumPage = int.Parse((string)pages[i]["total"]);
            if (SumPage % PageSize == 0)
            {
                SumPage = SumPage / PageSize;
            }
            else
            {
                SumPage = (SumPage / PageSize) + 1;
            }
            if(SumPage==0)
            {
                SumPage=1;
            }
        }

        for (int i = 0; i < sizes.Count; i++)
        {
            string type = (string)sizes[i]["type"];
            string change_amount = (string)sizes[i]["change_amount"];
            string rmb_balance = (string)sizes[i]["rmb_balance"];
            string change_time = (string)sizes[i]["change_time"];
            //~ string source_id= (string)sizes[i]["source_id"];

            types.Add((string)type);
            change_amounts.Add((string)change_amount);
            rmb_balances.Add((string)rmb_balance);
            change_times.Add((string)change_time);
            //~ source_ids.Add((string)source_id);
        }

        Strtype = types.ToArray();
        Strchange_amount = change_amounts.ToArray();   //变更金额
        Strrmb_balance = rmb_balances.ToArray();     //余额
        Strchange_time = change_times.ToArray();     //变更时间
        //~ Strsource_id=source_ids.ToArray();
    }

    /**
    * 单条记录转换JSON数据格式
    */
    public static string HashTable2Json(Hashtable hashtable){
        if (hashtable.Count < 1 ){
            return "{}";
        }
        //初始化
        StringBuilder sb = new StringBuilder();
        sb.Append('{');
        foreach (System.Collections.DictionaryEntry objDE in hashtable)
        {
            if (sb.Length > 1 )
            sb.Append(",");
            sb.Append("\" + objDE.Key.ToString() + "\":" + "\"+objDE.Value.ToString()+ "\");
        }

        sb.Append('}');
        //~ print("!!!!!!!!!!!!!!!!!"+ sb.ToString());
        return sb.ToString();
    }
}