Skip to main content

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