import win.ui;
/*DSG{{*/
var winform = win.form(text="aardio form";right=759;bottom=479)
winform.add(
buttonAdd={cls="button";text="复制";left=516;top=442;right=587;bottom=473;z=11};
buttonClose={cls="button";text="退出";left=679;top=442;right=750;bottom=473;z=10};
buttonDel={cls="button";text="删除";left=597;top=442;right=668;bottom=473;z=12};
buttonEnd={cls="button";text="末页";left=434;top=442;right=505;bottom=473;z=6};
buttonExcelWriteAll={cls="button";text="导出Excel";left=572;top=-1;right=653;bottom=33;z=8};
buttonFirst={cls="button";text="首页";left=189;top=442;right=260;bottom=473;z=5};
buttonNext={cls="button";text="下页";left=352;top=442;right=423;bottom=473;z=3};
buttonRead={cls="button";text="读取";left=453;top=-1;right=534;bottom=33;z=9};
buttonUp={cls="button";text="上页";left=271;top=442;right=342;bottom=473;z=2};
listview={cls="listview";left=5;top=33;right=747;bottom=438;ah=1;aw=1;dt=1;edge=1;fullRow=1;hscroll=1;vscroll=1;z=1};
staticPage={cls="static";text="第 1 页";left=40;top=448;right=160;bottom=465;transparent=1;z=4};
staticTitle={cls="static";text="CS架构数据表查询实例";left=40;top=1;right=384;bottom=33;color=32768;font=LOGFONT(h=-24;name='微软雅黑';weight=700);transparent=1;z=7}
)
/*}}*/

//CS架构mysql数据表查询实例

import access;
import mysql.client;
import godking.libxl;
import godking.message;
import process;
import console;

//import win.ui.grid;
//win.ui.grid(winform.listview);

winform.listview.setExtended(4/*_LVS_EX_CHECKBOXES*/); //set

import win.ui.listviewEx;
var listview = win.ui.listviewEx(winform.listview);
listview.setcolumnsEditbox(2);
listview.setcolumnsCombobox(3,{"男","女"}); //双击修改
listview.enableDoubleBuffering(); //双缓冲

//数据来源: mysql数据库
//数据表: 导出
var saveExcelFile = "D:\1_aardio\test2.xlsx";
//分页参数
var dataTable,dataOldTable = {},{};
var pageNo,pageLine,pageNum,pageLineNo = 0,20,0,0;
//分页数据处理
winform.page = function(){
	winform.staticPage.text = "共"++pageNum++"页,第"++pageNo++"页";
	winform.listview.clear();
	winform.listview.setColumns(dataTable.fields);
	for(i=1;pageLine;1){
		var pi = (pageNo-1)*pageLine+i;
		if dataTable[pi]{
			pageLineNo = pi;
			var tab = {};
			for k,v in dataTable.fields table.push(tab,dataTable[pi][v]);
			winform.listview.addItem(tab); //数组
		}
	}
}
winform.page(); //预显示
//缩放窗口
winform.adjust = function( cx,cy,wParam ) {	 
	winform.page(); //重新读取
};
//读取Excel
winform.ExcelRead = function(){
	var db,err = access(ExcelFile);
	var dbTable = db.eachTableObject()(); //迭代器,第1张表
	dataTable = db.getTable("select * from ["++dbTable++"]")
	db.close();	
}
//Mysql服务器配置
//server,port,uid,pwd = "192.168.2.136",3306,"root","123456";
server,port,uid,pwd = "127.0.0.1",3306,"root","123456";
dbName,dbTbl = "test","person";
//读取Mysql
winform.MysqlRead = function(){
	db,err = mysql.client(
    	server = server; //数据库服务器,可省略默认为localhost
    	port = 3306;
    	uid = uid;//用户名,可省略默认为root
    	pwd = pwd;
	);
	if !db { winform.msgErr("连接失败!"+err); return ; }
	db.selectDb(dbName);
	var result = db.query(`select * from `+dbTbl+`;`);
	dataTable = result.getTable();
	//console.dump(dataTable);
	dataOldTable = table.clone(dataTable);
}
//读取数据表
winform.buttonRead.oncommand = function(id,event){
	//winform.ExcelRead();
	winform.MysqlRead();
	pageNo=1;	
	pageNum = math.floor(#dataTable/pageLine)+(#dataTable%pageLine>0?1:0)
	winform.page(); //分页显示
}
winform.buttonRead.oncommand(); //预读取
//首页
winform.buttonFirst.oncommand = function(id,event){
	pageNo=1;
	winform.page(); //分页显示
}
//上页
winform.buttonUp.oncommand = function(id,event){
	if pageNo>1 pageNo--;
	winform.page(); //分页显示
}
//下一页
winform.buttonNext.oncommand = function(id,event){
	if pageNo<pageNum pageNo++;
	winform.page(); //分页显示
}
//末页
winform.buttonEnd.oncommand = function(id,event){
	pageNo=pageNum;
	winform.page(); //分页显示
}
//复制
winform.buttonAdd.oncommand = function(id,event){
	var tab = {};
	for k,v in dataTable.fields table.push(tab,dataTable[pageLineNo][v]);
	winform.listview.addItem(tab); //数组
	
	//mysql: insert
}
//删除
winform.buttonDel.oncommand = function(id,event){
	var checkTab = winform.listview.checked;
	for(i=#checkTab;1;-1){
		winform.listview.delItem(checkTab[i]);
	}
	
	//mysql: delete
}
//鼠标左键点击事件
/*
winform.listview.onClick = function(item,subItem,nmListView){
    var index = winform.listview.selIndex; //选择的行
    var v = winform.listview.getItemText(item,subItem); //取值
    winform.msgbox(string.join({item,subItem,v},",")); //显示行、列、值信息
}
*/
//鼠标双击修改事件
winform.listview.onEditChanged = function(text,iItem,iSubItem){
	var piItem = (pageNo-1)*pageLine+iItem;
    if dataTable[piItem] dataTable[piItem][dataTable.fields[iSubItem]] = text; //回写
    
    //mysql: update
    
}
//数据保存写入excel
winform.saveExcelFile = function(saveTable){
	var book = godking.libxl.new(saveExcelFile);
	var sheet = book.sheet();
	var format = book.addFormat(); //样式
	format.border = 1; /*_LIBXL_BORDERSTYLE_THIN*/
	sheet.setTable(1,1,saveTable,format,#saveTable.fields);
	book.save();
	book.release(); //释放内存
	process.execute(saveExcelFile); //打开
}
//数据保存准备
winform.buttonExcelWriteAll.oncommand = function(id,event){
	var saveTable = dataTable;
	var titleTab = {};
	for k,v in saveTable.fields {
		titleTab[v]=v;
	}
	table.insert(saveTable,titleTab);
	winform.saveExcelFile(saveTable);
}
//退出
winform.buttonClose.oncommand = function(id,event){
	win.quitMessage()
}

winform.show();
win.loopMessage();