aardio – MsSql数据库+虚表示例

给同事写了个数据录入的小程序，算是一个简单的【mssql数据库+虚表】示例。

主窗口

主窗口设计界面：

主窗口运行界面：

主窗口代码：

import fonts.fontAwesome; import win.ui; import win.ui.ctrl.vlistEx; /*DSG{ 
  {*/ mainForm = win.form(text="张某的程序";right=959;bottom=591;image=$"\res\_315b100ca64e65d61fb7Wh5pSZjX9laE.jpg";bkBottom=0;bkLeft=0;bkRight=0;bkTop=0;border="none";exmode="none";maximize=1;mode="popup";sysmenu=false;title=false) mainForm.add( datetimepick={cls="datetimepick";left=55;top=549;right=167;bottom=572;db=1;dl=1;edge=1;font=LOGFONT(h=-16);z=8}; datetimepick2={cls="datetimepick";left=198;top=549;right=314;bottom=572;db=1;dl=1;edge=1;font=LOGFONT(h=-16);z=12}; edit={cls="edit";left=380;top=549;right=511;bottom=572;db=1;dl=1;edge=1;font=LOGFONT(h=-16);tabstop=1;z=9}; plus={cls="plus";text='\uF044 添加';left=744;top=543;right=838;bottom=578;bgcolor=-;db=1;dl=1;font=LOGFONT(h=-16;name='FontAwesome');notify=1;z=4}; plus2={cls="plus";text='\uF002 查询';left=526;top=543;right=620;bottom=578;bgcolor=-;db=1;dl=1;font=LOGFONT(h=-16;name='FontAwesome');notify=1;z=3}; plus3={cls="plus";text='\uF122 退出';left=849;top=543;right=943;bottom=578;bgcolor=-;db=1;dr=1;font=LOGFONT(h=-16;name='FontAwesome');notify=1;z=5}; plus4={cls="plus";left=7;top=4;right=28;bottom=27;dl=1;dt=1;foreRepeat="stretch";foreground="\res\png-0056.png";z=6}; plus5={cls="plus";text='\uF045 导出';left=635;top=543;right=729;bottom=578;bgcolor=-;db=1;dl=1;font=LOGFONT(h=-16;name='FontAwesome');notify=1;z=7}; static={cls="static";text="日期";left=15;top=549;right=55;bottom=572;color=;db=1;dl=1;font=LOGFONT(h=-16);transparent=1;z=10}; static2={cls="static";text="至";left=176;top=549;right=196;bottom=572;color=;db=1;dl=1;font=LOGFONT(h=-16);transparent=1;z=11}; static3={cls="static";text="客户";left=339;top=549;right=379;bottom=572;color=;db=1;dl=1;font=LOGFONT(h=-16);transparent=1;z=13}; vlist={cls="vlistEx";text="自定义控件";left=3;top=30;right=958;bottom=527;border=1;db=1;dl=1;dr=1;dt=1;frame=1;z=1}; 张某的程序={cls="static";text="张某的程序";left=36;top=4;right=343;bottom=31;color=;dl=1;dt=1;font=LOGFONT(h=-16);transparent=1;z=2} ) /*}}*/ //定义按钮样式 var skin = { background={ active=0xFFD3E0BC; default=0xFF8FB2B0; hover=0xFF928BB3 } } mainForm.plus.skin(skin) mainForm.plus2.skin(skin) mainForm.plus3.skin(skin) mainForm.plus5.skin(skin) //连接mssql数据库 import sqlServerEx var db=sqlServerEx("221.1.2.3",11433,"zhang","zhang",""); if !db { win.msgboxErr("连接数据库失败！程序自动退出") //mainForm.close() //win.quitMessage() return ; } //查询mssql数据库，并显示到虚表中 mainForm.plus2.oncommand = function(id,event){ var kehu = (#mainForm.edit.text>0)?"and kehu='"++mainForm.edit.text++"'":"" var tb = db.getTable(`select id, riqi as 日期, kehu as 客户, pinming as 品名, jianshu as 件数, guige as 规格, kouqianzhongliang as 扣前重量, kouhouzhongliang as 扣后重量, kouqianzhongliang-kouhouzhongliang as 扣除重量, danjia as 单价, jine as 金额, baozhuangdanjia as 包装单价, baozhuangjine as 包装金额, rengongdanjia as 人工单价, rengongjine as 人工金额, zhuangchedanjia as 装车单价, zhuangchejine as 装车金额, kouqianjine as 扣前金额, kouhoujine as 扣后金额, kouchujine as 扣除金额, shifujine as 实付金额, shijian as 操作时间 from jilu where riqi>='`++mainForm.datetimepick.text++`' and riqi<='`++mainForm.datetimepick2.text++`'` ++ kehu ++ `order by id`) // 添加合计行 var sum={} for (i=1;#tb){ tb[i]["id"]=i //将id改为序号 for (c=1;#tb.fields){ sum[tb.fields[c]]:=0 if math.isFinite(tb[i][tb.fields[c]]) sum[tb.fields[c]]+=tonumber(tb[i][tb.fields[c]]) } } sum["id"]="" sum["日期"]="合计" table.push(tb,sum) mainForm.vlist.setTable(tb) } import win.ui.simpleWindow win.ui.simpleWindow(mainForm) mainForm.show(); //退出程序 mainForm.plus3.oncommand = function(id,event){ //mainForm.close() win.quitMessage() } //载入“添加”窗口 mainForm.plus.oncommand = function(id,event){ var frmChild = mainForm.loadForm("\dlg\addjilu.aardio",db); frmChild.show(); } //导出csv格式文件 mainForm.plus5.oncommand = function(id,event){ if mainForm.vlist.count=0 { mainForm.msgboxErr("没有查询内容！") return ; } import fsys.dlg var f=fsys.dlg.save("*.csv|*.csv","请选择要导出的文件",,mainForm) if !f return ; var csv="" var items=mainForm.vlist.getItems() for (i=1;#items){ var it=string.join(items[i],",") csv+=it+'\r\n' } import fsys.file var f=fsys.file(f,"w") if f.write(string.fromto(csv)){ mainForm.msgbox("文件导出完毕！") }else{ mainForm.msgboxErr("文件导出失败！") } f.close() } return win.loopMessage();

“添加” 窗口

1、窗口设计界面

2、窗口运行界面

3、代码

import win.ui; import fonts.fontAwesome import win.dlg.message /*DSG{ 
  {*/ var winform = win.form(text="aardio form";right=699;bottom=469;image=$"\res\_315b100ca64e65d61fb7Wh5pSZjX9laE.jpg";bkBottom=0;bkLeft=0;bkRight=0;bkTop=0;border="none";max=false;min=false) winform.add( datetimepick={cls="datetimepick";left=100;top=44;right=270;bottom=70;edge=1;font=LOGFONT(h=-16);z=3}; plus={cls="plus";text='\uF044 保存';left=579;top=336;right=673;bottom=371;bgcolor=-;db=1;dr=1;font=LOGFONT(h=-16;name='FontAwesome');notify=1;z=1}; plus2={cls="plus";text='\uF122 取消';left=579;top=392;right=673;bottom=427;bgcolor=-;db=1;dr=1;font=LOGFONT(h=-16;name='FontAwesome');notify=1;z=2}; plus3={cls="plus";left=3;top=2;right=24;bottom=25;dl=1;dt=1;foreRepeat="stretch";foreground="\res\png-0056.png";z=5}; static={cls="static";text="添加记录";left=32;top=2;right=147;bottom=29;color=;dl=1;dt=1;font=LOGFONT(h=-16);transparent=1;z=4}; static2={cls="static";text="日期";left=29;top=46;right=103;bottom=73;color=;dl=1;dt=1;font=LOGFONT(h=-16);transparent=1;z=6} ) /*}}*/ var args={...} if #args<2{ winform.msgboxErr("数据库无效") winform.close() win.quitMessage() return winform; } var db = args[2] win.dlg.message(winform) var fields1={"kehu","pinming","jianshu","guige","danjia","baozhuangdanjia","rengongdanjia","zhuangchedanjia","kouchubili"} var names1 ={"客户","品名" ,"件数" ,"规格" ,"单价" ,"包装单价" ,"人工单价" ,"装车单价" ,"扣除比例"} var fields2={"kouqianzhongliang","kouhouzhongliang","jine","baozhuangjine","rengongjine","zhuangchejine","kouqianjine","kouhoujine","kouchujine","shifujine"} var names2 ={"重量" ,"扣后重量" ,"金额","包装金额" ,"人工金额" ,"装车金额" ,"扣前金额" ,"扣后金额" ,"扣除金额" ,"实付金额"} var edits={} for(i=1;math.max(#fields1,#fields2) ;1){ var top=50+i*35 var height=30 if i<=#fields1{ winform.add({ {cls="static";text=names1[i];left=30;top=top;right=100;bottom=top+height;color=;dl=1;dt=1;font=LOGFONT(h=-16);transparent=1;z=7}; }) edits[fields1[i]]=winform.add({ {cls="edit";tabstop=i;left=100;top=top;right=270;bottom=top+height;edge=1;font=LOGFONT(h=-16);z=8}; })[1] } if i<=#fields2{ winform.add({ {cls="static";text=names2[i];left=300;top=top;right=370;bottom=top+height;color=;dl=1;dt=1;font=LOGFONT(h=-16);transparent=1;z=7}; }) edits[fields2[i]]=winform.add({ {cls="edit";left=370;top=top;right=540;bottom=top+height;edge=1;font=LOGFONT(h=-16);z=8}; })[1] } } winform.plus2.oncommand = function(id,event){ winform.close() } var skin = { background={ active=0xFFD3E0BC; default=0xFF8FB2B0; hover=0xFF928BB3 } } winform.plus.skin(skin) winform.plus2.skin(skin) edits_oncommand = function(id,event){ var jianshu=tonumber(edits["jianshu"].text):0 var guige= tonumber(edits["guige"].text):0 var danjia= tonumber(edits["danjia"].text):0 var baozhuangdanjia= tonumber(edits["baozhuangdanjia"].text):0 var rengongdanjia= tonumber(edits["rengongdanjia"].text):0 var zhuangchedanjia= tonumber(edits["zhuangchedanjia"].text):0 var kouchubili = tonumber(edits["kouchubili"].text):0 var kouqianzhongliang= math.round(jianshu*guige,2) var kouhouzhongliang = math.round(jianshu*guige*kouchubili,2) var jine = math.round(kouqianzhongliang*danjia,2) var baozhuangjine = math.round(baozhuangdanjia*jianshu,2) var rengongjine = math.round(rengongdanjia*kouqianzhongliang,2) var zhuangchejine = math.round(zhuangchedanjia*jianshu,2) var kouqianjine = jine + baozhuangjine + rengongjine + zhuangchejine var kouhoujine = math.round(kouhouzhongliang*danjia,2) + baozhuangjine + rengongjine + zhuangchejine var kouchujine = math.round( kouqianjine - kouhoujine,2) var shifujine = math.floor(kouhoujine/10,0)*10 edits["kouqianzhongliang"].text=tostring(kouqianzhongliang) edits["kouhouzhongliang"].text=tostring(kouhouzhongliang) edits["jine"].text=tostring(jine) edits["baozhuangjine"].text=tostring(baozhuangjine) edits["rengongjine"].text=tostring(rengongjine) edits["zhuangchejine"].text=tostring(zhuangchejine) edits["kouqianjine"].text=tostring(kouqianjine) edits["kouhoujine"].text=tostring(kouhoujine) edits["kouchujine"].text=tostring(kouchujine) edits["shifujine"].text=tostring(shifujine) } fields1={"jianshu","guige","danjia","baozhuangdanjia","rengongdanjia","zhuangchedanjia","kouchubili"} for(i=1;#fields1;1){ edits[fields1[i]].oncommand = edits_oncommand } winform.plus.oncommand = function(id,event){ import console var names="riqi," var values="'"+winform.datetimepick.text+"'," for k,v in edits{ if !#v.text{ winform.msgboxErr("信息没有填写完整！","错误") return ; } names+=k+"," if k="kehu" or k="pinming"{ values+="'"+v.text+"'," }else { values+=(#v.text?v.text:"0")+"," } } names=string.trimright(names,",") values=string.trimright(values,",") var sql="insert into jilu("+names+") values("+values+")" if db.exec(sql) { for(hwnd,ctrl in winform.eachControlEx("edit")){ ctrl.text="" } winform.msgOk("已添加",1500) }else { winform.msgErr("添加失败",1500) } } import win.ui.simpleWindow win.ui.simpleWindow(winform) winform.show(); edits["kehu"].setFocus() win.loopMessage(); return winform;