Json大字段
大字段就是数据库中一个字段保存由多条记录组成的一定格式的长字符串。
大字段好处：不用操作子表，速度快
坏处：不好查询，统计。
业务中很多主表和子表的关系，主表一条记录对应子表多条记录。
页面上采用Grid表格方式一次录入多条子表记录，
由于子表数据要进行多次修改，每次修改都操作子表的话效率不高，
因此某一环节前子表数据都保存在大字段中，某一环节时再把大字段中数据写到子表中去。
以前都是用ＸＭＬ大字段
最近用ＪＳＯＮ大字段，感觉比XML还方便些。
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DzdEdit.aspx.cs" Inherits="jsnh_dzd.DzdEdit" %> 2 3 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > 4 <html xmlns="http://www.w3.org/1999/xhtml"> 5 <head id="Head1" runat="server"> 6 <title>对帐单编辑</title> 7 <script type="text/javascript" src="js/jquery-1.8.2.js"></script> 8 <script type="text/javascript" src="js/jquery.ui.core.js"></script> 9 <script src="JS/jquery-ui.js" type="text/javascript"></script> 10 <script src="JS/jquery.validate.js" type="text/javascript"></script> 11 <script src="JS/messages_cn.js" type="text/javascript"></script> 12 <script src="JS/ui_formatmoney.source.js" type="text/javascript"></script> 13 <link href="Css/Validate/screen.css" rel="stylesheet" type="text/css" /> 14 <link href="Css/jquery.ui.all.css" rel="stylesheet" type="text/css" /> 15 <style type="text/css"> 16 .DataTable 17 { 18 table-layout: fixed; 19 border-collapse: collapse; 20 border-spacing: 0px; 21 width: 100%; 22 border: #000 solid 0px; 23 background-color: White; 24 } 25 .DataTable td 26 { 27 border: 0px solid #000; 28 height: 25px; 29 text-align: center; 30 background-color: White; 31 } 32 .DataTable th 33 { 34 background: #EFEFEF; 35 border: #000 solid 1px; 36 white-space: nowrap; 37 height: 21px; 38 border-top: 0px; 39 border-left: 0px; 40 background-color: White; 41 } 42 .DataTable0 43 { 44 table-layout: fixed; 45 border-collapse: collapse; 46 border-spacing: 0px; 47 width: 100%; 48 border: #000 solid 0px; 49 text-align: center; /*background-color:White;*/ 50 } 51 .DataTable0 > tr > td 52 { 53 border: 0px solid #000; 54 height: 25px; 55 text-align: center; 56 } 57 .DelHand 58 { 59 background-image:url('/jsnh_dzd/Css/images/索引.jpg'); 60 background-repeat:no-repeat; 61 62 background-position:-120px -13px; 63 width:30px; 64 border-style:none; 65 cursor:pointer; 66 67 } 68 </style> 69 <script type="text/javascript"> 70 var InFeilds = ["InDate", "InItem", "InMoney", "Remark","RecordID"]; 71 var OutFeilds = <%=OutFeilds%>;//["RecordID", "dDataDate","ProductID", "cProductName","cSpecification","cMeasureUnit", "nQuantity", "OutPrice","ReMark"]; 72 var AwardFeilds = ["ProductID", "AwardKey", "AwardMoney","cProductName"]; 73 //操作类型 74 var DoType; 75 //当前记录json 76 var CRJStr; 77 //枚举json 导出Excel 时json字符串中只有key 没有 Text; 78 var OptionJson = <%=OptionJson%>; 79 //商品OptionJson 80 var ProductOptionStr =""; 81 //var validator;// = $("#form1").validate({}); 82 function S4() { 83 return (((1 + Math.random()) * 0x10000) | 0).toString(16).substring(1); 84 } 85 function NewGuid() { 86 return (S4() + S4() + "-" + S4() + "-" + S4() + "-" + S4() + "-" + S4() + S4() + S4()); 87 } 88 function getUrlParam(name) { 89 var reg = new RegExp("(^|&)" + name + "=([^&]*)(&|$)"); //构造一个含有目标参数的正则表达式对象 90 var r = window.location.search.substr(1).match(reg); //匹配目标参数 91 if (r != null) return unescape(r[2]); return null; //返回参数值 92 } 93 $(function () { 94 $("#form1").validate({ 95 submitHandler:DoForm 96 }); 97 DoType = getUrlParam("DoType"); 98 //alert(DoType); 99 //$("#OutAddBtn").attr("disabled", "disabled"); 100 //$("#InAddBtn").attr("disabled", "disabled"); 101 $("#selectBtn").attr("disabled", "disabled"); 102 $("#exportBtn").attr("disabled","disabled").css("display","none"); 103 var lableDoType; 104 switch (DoType) { 105 case "add": 106 lableDoType = "新增"; 107 $("#selectBtn").removeAttr("disabled"); 108 break; 109 case "modi": 110 lableDoType = "修改"; 111 ShowData(); 112 break; 113 case "view": 114 default: 115 lableDoType = "查看"; 116 ShowData(); 117 $("input").attr("disabled","disabled"); 118 $("select").attr("disabled","disabled"); 119 $("button").attr("disabled","disabled").css("display","none"); 120 $("#exportBtn").removeAttr("disabled").css("display",""); 121 //$("img").attr("disabled","disabled"); 122 break; 123 } 124 $("#lableDoType").text(lableDoType); 125 }); 126 function ShowData(){ 127 CRJStr = <%=CRJStr%>; 128 $("#Id").val(CRJStr.Id); 129 $("#CustomId").val(CRJStr.CustomId); 130 $("#CustomName").val(CRJStr.CustomName); 131 //alert(CRJStr.OutJson); 132 ShowInData(eval("("+CRJStr.InJson+")")); 133 ShowOutData(eval("("+CRJStr.OutJson+")")); 134 ShowAwardData(eval("("+CRJStr.AwardJson+")")); 135 } 136 //添加收款项目 137 function AddInMoney() { 138 var InDateId = NewGuid(); 139 var strhtml = "<tr>"; 140 strhtml += "<td><input type='text' FeildName='" + InFeilds[0] + "' name='" + InDateId + "' id='" + InDateId + "' required style='width:90%;'/></td>"; 141 strhtml += "<td><input type='text' name='" + InDateId + "1' id='" + InDateId + "1' required FeildName='" + InFeilds[1] + "' style='width:90%;'/></td>"; 142 strhtml += "<td><input type='text' name='" + InDateId + "2' id='" + InDateId + "2' required type='number' FeildName='" + InFeilds[2] + "' style='width:90%;'/></td>"; 143 strhtml += "<td><input type='text' FeildName='" + InFeilds[3] + "' style='width:90%;'/></td>"; 144 strhtml += "<td><button onclick='DeleteRow(this)' class='DelHand' title='删除' /></button></td>"; 145 strhtml += "</tr>"; 146 //alert(strhtml); 147 $("#InMoneyTable").append(strhtml); 148 $("#" + InDateId).datepicker().datepicker("option", "dateFormat", "yy-mm-dd").datepicker("option", "appendText", "(yyyy-mm-dd)"); 149 } 150 //添加销货项目 151 function AddOutMoney() { 152 ShowOneOutData(false,""); 153 } 154 //添加奖励项目 155 function AddAwardMoney() { 156 ShowOneAwardData(false,""); 157 } 158 //删除行 IsOutDelete 是否销售记录删除 159 function DeleteRow(ele,IsOutDelete,CProductID) { 160 if(IsOutDelete){ 161 var Cval=$("#"+CProductID).val(); 162 if(Cval !="" && CheckHasAward(Cval)){ 163 alert("该记录商品已经有补贴"); 164 return; 165 } 166 } 167 if(confirm("确定要删除？")){ 168 $(ele).parent().parent().remove(); 169 UpdateProductOption(); 170 } 171 } 172 //保存 173 174 function DoForm(){ 175 //alert(0); return; 176 //收款项目 177 var InNum = $("#InMoneyTable tr").length; 178 var injstr = "["; 179 var inarr = new Array(); 180 for (var i = 1; i < InNum; i++) { 181 var ctr = $("#InMoneyTable tr:eq(" + i + ")"); 182 var cstr = "{"; 183 var carr = new Array(); 184 for (var j = 0; j < InFeilds.length; j++) { 185 carr.push("\"" + InFeilds[j] + "\":\"" + ctr.find("[FeildName='" + InFeilds[j] + "']").val() + "\""); 186 } 187 cstr += carr.join(',') + "}"; 188 inarr.push(cstr); 189 } 190 injstr += inarr.join(',') + "]"; 191 //销货项目 192 var OutNum = $("#OutMoneyTable tr").length; 193 //var InFeilds = ["InDate", "InItem", "InMoney", "Remark"]; 194 var outjstr = "["; 195 var outarr = new Array(); 196 for (var i = 1; i < OutNum; i++) { 197 var ctr = $("#OutMoneyTable tr:eq(" + i + ")"); 198 var cstr = "{"; 199 var carr = new Array(); 200 for (var j = 0; j < OutFeilds.length; j++) { 201 carr.push("\"" + OutFeilds[j] + "\":\"" + ctr.find("[FeildName='" + OutFeilds[j] + "']").val() + "\""); 202 } 203 cstr += carr.join(',') + "}"; 204 outarr.push(cstr); 205 } 206 outjstr += outarr.join(',') + "]"; 207 //奖励项目 208 var AwardNum = $("#AwardTable tr").length; 209 var awardjstr = "["; 210 var awardarr = new Array(); 211 for (var i = 1; i < AwardNum; i++) { 212 var ctr = $("#AwardTable tr:eq(" + i + ")"); 213 var cstr = "{"; 214 var carr = new Array(); 215 for (var j = 0; j < AwardFeilds.length-1; j++) { 216 carr.push("\"" + AwardFeilds[j] + "\":\"" + ctr.find("[FeildName='" + AwardFeilds[j] + "']").val() + "\""); 217 } 218 carr.push("\"" + AwardFeilds[AwardFeilds.length-1] + "\":\"" + ctr.find("[FeildName='" + AwardFeilds[0] + "'] option:selected").text() + "\""); 219 cstr += carr.join(',') + "}"; 220 awardarr.push(cstr); 221 } 222 awardjstr += awardarr.join(',') + "]"; 223 //alert(awardjstr); 224 //return; 225 var Posturl = "DzdEdit.aspx/"; 226 if (DoType == "add") { 227 Posturl += "DoAdd"; 228 } 229 if (DoType == "modi") { 230 Posturl += "DoModi"; 231 } 232 $.ajax({ 233 type: "POST", 234 url: Posturl, 235 data: "{ 'Id':'" + $("#Id").val() + "','InJson': '" + injstr + "', 'OutJson': '" + outjstr + "', 'AwardJson': '" + awardjstr 236 + "', 'CustomId': '" + $("#CustomId").val() + "', 'CustomName': '" + $("#CustomName").val() + "' }", 237 contentType: "application/json", 238 dataType: "json", 239 success: function (msg) { 240 if (msg.d == "T") { 241 alert("操作成功"); 242 window.close(); 243 window.opener.location.reload(); 244 } 245 else { 246 alert("操作失败,请联系管理员"); 247 } 248 }, 249 error: function (xhr, msg) { alert(msg); } 250 }); 251 } 252 function DoSelect() { 253 //window.open('pop/customsingle.aspx', '', 254 //'resizable:no;scroll:yes;status:no;dialogWidth=700px;dialogHeight=500px;center=yes;help=no'); 255 var result = window.showModalDialog('pop/customsingle.aspx', '', 256 'resizable:no;scroll:yes;status:no;dialogWidth=800px;dialogHeight=500px;center=yes;help=no'); 257 if (typeof (result) != "undefined") { 258 var resultJson = eval("(" + result + ")"); 259 //alert(resultJson.CustomId); 260 $("#CustomId").val(resultJson.CustomId); 261 $("#CustomName").val(resultJson.CustomName); 262 //禁用销货 收款 添加按钮 263 $("#OutAddBtn").attr("disabled", "disabled"); 264 $("#InAddBtn").attr("disabled", "disabled"); 265 //获取销货记录,收款记录 266 $.ajax({ 267 type: "POST", 268 url: "DzdEdit.aspx/GetInOutData", 269 data: "{ CustomId:" + resultJson.CustomId + " }", 270 contentType: "application/json; charset=utf-8", 271 dataType: "json", 272 success: function (msg) { 273 var result = eval("(" + msg.d + ")")//$.parseJSON("'"+msg.d+"'"); 274 //数据信息 275 if($("#InMoneyTable tr").length>1){ 276 if(confirm("已经有收款记录,确定再追加?")){ 277 ShowInData(result.QueryInData); 278 } 279 } 280 else{ 281 ShowInData(result.QueryInData); 282 } 283 if($("#OutMoneyTable tr").length>1){ 284 if(confirm("已经有销货记录,确定再追加?")){ 285 ShowOutData(result.QueryOutData); 286 } 287 } 288 else{ 289 ShowOutData(result.QueryOutData); 290 } 291 //ShowInData(result.QueryInData); 292 //ShowOutData(result.QueryOutData); 293 //启用销货 收款 添加按钮 294 $("#OutAddBtn").removeAttr("disabled"); 295 $("#InAddBtn").removeAttr("disabled"); 296 }, 297 error: function (xhr, msg) { alert(msg); } 298 }); 299 } 300 } 301 //显示销售数据 302 function ShowOutData(ShowData) { 303 for (var i = 0; i < ShowData.length; i++) { 304 ShowOneOutData(true,ShowData[i]); 305 } 306 UpdateProductOption(); 307 } 308 //显示一条销售数据 309 //HasValue 是否包含值，OneData 当前记录数据 310 function ShowOneOutData(HasValue,OneData){ 311 var OutDateId = NewGuid(); 312 //["RecordID", "dDataDate","ProductID", "cProductName","cSpecification","cMeasureUnit", "nQuantity", "OutPrice","ReMark"] 313 var DataStr = "<tr style='background-color:rgb(229, 242, 248);'>"; 314 DataStr += "<td><input type='hidden' FeildName='" + OutFeilds[0] + "' name='" + OutDateId + "0' id='" + OutDateId + 315 "0' "; 316 if(HasValue){ 317 DataStr += " value='" +OneData[OutFeilds[0]] + "'"; 318 } 319 DataStr += " /></td>"; 320 DataStr += "<td><input type='text' style='width:90%;' FeildName='" + OutFeilds[1] + "' required name='" + OutDateId + "1' id='" + OutDateId + 321 "1' "; 322 // if(HasValue){ 323 // DataStr += " value='" +OneData[OutFeilds[1]] + "'"; 324 // } 325 DataStr += " /></td>"; 326 DataStr += "<td><input type='hidden' FeildName='" + OutFeilds[2] + "' name='" + OutDateId + "2' id='" + OutDateId + 327 "2' "; 328 if(HasValue){ 329 DataStr += " value='" +OneData[OutFeilds[2]] + "'"; 330 } 331 DataStr += " /></td>"; 332 DataStr += "<td><input type='text' style='width:80%;' FeildName='" + OutFeilds[3] + "' readonly required name='" + OutDateId + "3' id='" + OutDateId + 333 "3' "; 334 if(HasValue){ 335 DataStr += " value='" +OneData[OutFeilds[3]] + "'"; 336 } 337 DataStr += " /><button onclick='DoSelectProduct(\""+OutDateId+"\")'>选择</button></td>"; 338 DataStr += "<td><input type='text' style='width:90%;' FeildName='" + OutFeilds[4] + "' readonly name='" + OutDateId + "4' id='" + OutDateId + 339 "4' "; 340 if(HasValue){ 341 DataStr += " value='" +OneData[OutFeilds[4]] + "'"; 342 } 343 DataStr += " /></td>"; 344 DataStr += "<td><input type='text' style='width:90%;' FeildName='" + OutFeilds[5] + "' readonly name='" + OutDateId + "5' id='" + OutDateId + 345 "5' "; 346 if(HasValue){ 347 DataStr += " value='" +OneData[OutFeilds[5]] + "'"; 348 } 349 DataStr += " /></td>"; 350 DataStr += "<td><input type='text' style='width:90%;' FeildName='" + OutFeilds[6] + "' required type='number' name='" + OutDateId + "6' id='" + OutDateId + 351 "6' "; 352 if(HasValue){ 353 DataStr += " value='" +OneData[OutFeilds[6]] + "'"; 354 } 355 DataStr += " /></td>"; 356 DataStr += "<td><input type='text' style='width:90%;' FeildName='" + OutFeilds[7] + "' required type='number' name='" + OutDateId + "7' id='" + OutDateId + 357 "7' "; 358 if(HasValue){ 359 DataStr += " value='" +OneData[OutFeilds[7]] + "'"; 360 } 361 DataStr += " /></td>"; 362 DataStr += "<td><input type='text' style='width:90%;' FeildName='" + OutFeilds[8] + "' name='" + OutDateId + "8' id='" + OutDateId + 363 "8' "; 364 if(HasValue){ 365 DataStr += " value='" +OneData[OutFeilds[8]] + "'"; 366 } 367 DataStr += " /></td>"; 368 DataStr += "<td><button onclick='DeleteRow(this,true,\""+OutDateId+"2\")' class='DelHand' title='删除' /></button></td>"; 369 DataStr += "</tr>"; 370 //alert(DataStr); 371 $("#OutMoneyTable").append(DataStr); 372 $("#" + OutDateId+"1").datepicker().datepicker("option", "dateFormat", "yy-mm-dd").datepicker("option", "appendText", "(yyyy-mm-dd)").datepicker("setDate", OneData[OutFeilds[1]]); 373 374 } 375 function DoSelectProduct(CGuid){ 376 //alert(CGuid); 377 //判断该商品是否已经添加有补贴，有的话不让选商品 378 var CProductID= $("#"+CGuid+"2").val(); 379 if(CProductID!="" && CheckHasAward($("#"+CGuid+"2").val())){ 380 alert("该商品已经有补贴"); 381 return; 382 } 383 var result = window.showModalDialog('pop/ProductSingle.aspx', '', 384 'resizable:no;scroll:yes;status:no;dialogWidth=800px;dialogHeight=500px;center=yes;help=no'); 385 if (typeof (result) != "undefined") { 386 var resultJson = eval("(" + result + ")"); 387 //alert(resultJson.ProductID); 388 $("#"+CGuid+"2").val(resultJson.ProductID); 389 $("#"+CGuid+"3").val(resultJson.cProductName); 390 $("#"+CGuid+"4").val(resultJson.cSpecification); 391 $("#"+CGuid+"5").val(resultJson.cMeasureUnit); 392 //更新补贴商品下拉框 393 UpdateProductOption(); 394 } 395 } 396 //检查该商品是否有补贴 有返回true 没有返回 false 397 function CheckHasAward(CProductID){ 398 var Awrads = $("#AwardTable [FeildName='"+AwardFeilds[0]+"']"); 399 for (var i = 0; i < Awrads.length; i++) { 400 if($(Awrads[i]).val()==CProductID){ 401 return true; 402 } 403 } 404 return false; 405 } 406 function ShowInData(ShowData) { 407 $.each(ShowData, function (i, item) { 408 var InDateId = NewGuid(); 409 var strhtml = "<tr>"; 410 strhtml += "<td><input type='text' FeildName='" + InFeilds[0] + "' id='" + InDateId + "' required style='width:90%;'/></td>"; 411 strhtml += "<td><input type='text' FeildName='" + InFeilds[1] + "' name='" + InDateId + "1' required value='" + ShowData[i][InFeilds[1]] + "' style='width:90%;'/></td>"; 412 strhtml += "<td><input type='text' FeildName='" + InFeilds[2] + "' name='" + InDateId + "2' required type='number' value='" + ShowData[i][InFeilds[2]] + "' style='width:90%;'/></td>"; 413 strhtml += "<td><input type='text' FeildName='" + InFeilds[3] + "' value='" + ShowData[i][InFeilds[3]] + "' style='width:90%;'/></td>"; 414 strhtml += "<td><button onclick='DeleteRow(this)' class='DelHand' title='删除' /></button></td>"; 415 strhtml += "</tr>"; 416 //alert(strhtml); 417 $("#InMoneyTable").append(strhtml); 418 $("#" + InDateId).datepicker().datepicker("option", "dateFormat", "yy-mm-dd").datepicker("option", "appendText", "(yyyy-mm-dd)").datepicker("setDate", ShowData[i][InFeilds[0]]); 419 420 }); 421 } 422 function ShowAwardData(ShowData) { 423 for (var i = 0; i < ShowData.length; i++) { 424 ShowOneAwardData(true,ShowData[i]); 425 } 426 } 427 function ShowOneAwardData(HasValue,OneData){ 428 var AwardId = NewGuid(); 429 var strhtml = "<tr>"; 430 strhtml += "<td><select id='" + AwardId + "0' FeildName='" + AwardFeilds[0] + "' name='" + AwardId + "0' required style='width:90%;'>"+ProductOptionStr+"</select></td>"; 431 strhtml += "<td><select id='" + AwardId + "1' FeildName='" + AwardFeilds[1] + "' name='" + AwardId + "1' required style='width:90%;'><%=OptionStr%></select></td>"; 432 strhtml += "<td><input type='text' FeildName='" + AwardFeilds[2] + "' name='" + AwardId + "2' required type='number' "; 433 if(HasValue){ 434 strhtml +=" value='" + OneData[AwardFeilds[2]] + "'"; 435 } 436 strhtml +=" style='width:90%;'/></td>"; 437 strhtml += "<td><button onclick='DeleteRow(this)' class='DelHand' title='删除' /></button></td>"; 438 strhtml += "</tr>"; 439 //alert(strhtml); 440 $("#AwardTable").append(strhtml); 441 if(HasValue){ 442 $("#" + AwardId+"0").val(OneData[AwardFeilds[0]]); 443 $("#" + AwardId+"1").val(OneData[AwardFeilds[1]]); 444 } 445 } 446 //导出Excel 447 function DoExcel(){ 448 var excel; 449 try 450 { 451 excel= new ActiveXObject("Excel.Application"); //实例化Excel.Application对象 452 } 453 catch(e) 454 { 455 alert( "您必须安装Excel电子表格软件，同时IE设置如下：打开IE浏览器的工具-〉Internet选项-〉安全-〉自定义级别中的“对没有标记为安全的ActiveX控件进行初始化和脚本运行”设置为“启用”后重启IE即可！"); 456 $("#ExportError").css("display",""); 457 return; 458 } 459 $("#exportBtn").attr("disabled", "disabled"); 460 var workB = excel.Workbooks.Add(); ////添加新的工作簿 461 var sheet = workB.ActiveSheet; 462 var crow = 1; 463 var MaxCol = 11; 464 //sheet.Cells(crow,1).Select();//选中Excel中的单元格 465 //sheet.Pictures.Insert("http://www.jsnh.com.cn/Public/images/logo.gif");//插入图片 466 crow++; 467 sheet.Cells(crow, 1).value="北京金色农华种业科技有限公司"; 468 sheet.Cells(crow,1).Font.Bold = true; 469 sheet.Cells(crow,1).Font.Size = 14; 470 sheet.Range(sheet.Cells(crow,1),sheet.Cells(crow,MaxCol)).mergecells=true; 471 sheet.Cells(crow,1).HorizontalAlignment =3; 472 crow++; 473 sheet.Cells(crow, 1).value="客户往来对帐单"; 474 sheet.Cells(crow,1).Font.Bold = true; 475 sheet.Cells(crow,1).Font.Size = 14; 476 sheet.Range(sheet.Cells(crow,1),sheet.Cells(crow,MaxCol)).mergecells=true; 477 crow++; 478 sheet.Cells(crow, 1).value="结算日期：<%=beginDate.Year %> 年 <%=beginDate.Month %> 月 <%=beginDate.Day %> 日- <%=endDate.Year %> 年 <%=endDate.Month %> 月 <%=endDate.Day %> 日"; 479 sheet.Cells(crow,1).Font.Bold = true; 480 sheet.Cells(crow,1).Font.Size = 12; 481 sheet.Range(sheet.Cells(crow,1),sheet.Cells(crow,MaxCol)).mergecells=true; 482 crow++; 483 sheet.Cells(crow, 1).value="客户名称:"+CRJStr.CustomName; 484 sheet.Range(sheet.Cells(crow,1),sheet.Cells(crow,MaxCol)).mergecells=true; 485 crow++; 486 sheet.Cells(crow, 1).value=" 截至 年 月 日，贵单位购我公司商品及交付款项详列本对帐单请核对，并依此办理年终结算。自我公司支付以下款项之日起双方不存在应付未付应收未收的款项。若无异议,烦请在页底签章确认,并将开户行、银行帐号及户名工整填写后一并传真至 (010-xxxxxx)并将原件寄回公司。（公司地址：xxx，邮编：xxx）如有异议，请及时与我公司联系。"; 487 sheet.Range(sheet.Cells(crow,1),sheet.Cells(crow,MaxCol)).mergecells=true; 488 sheet.Cells(crow,1).Font.Size = 10; 489 sheet.Cells(crow, 1).WrapText=true; 490 sheet.Rows(crow).RowHeight = 24;//设置行高 491 crow++; 492 var MaxInCol = 4; 493 sheet.Cells(crow, 1).value="收款项目(1)"; 494 sheet.Range(sheet.Cells(crow,1),sheet.Cells(crow,MaxInCol)).mergecells=true; 495 sheet.Cells(crow, MaxInCol+1).value="销货项目(2)"; 496 sheet.Range(sheet.Cells(crow,MaxInCol+1),sheet.Cells(crow,MaxCol)).mergecells=true; 497 //sheet.Cells(3,1).HorizontalAlignment = 3; 498 //第四行 499 crow++; 500 var InOutTitles = ["打款日期","<%=(DateTime.Now.Year-1).ToString()%>-<%=DateTime.Now.Year.ToString()%>年度货款","打款金额","备 注", 501 "提货日期","商品代号","规 格","本地计量","数 量","单 价","金 额"]; 502 for (var i = 0 ; i < InOutTitles.length; i++) { 503 sheet.Cells(crow, i+1).value=InOutTitles[i]; 504 } 505 crow++; 506 var InSum=0; 507 var OutSum =0; 508 var InJ = eval("("+CRJStr.InJson+")"); 509 for (var i = 0 ; i < InJ.length; i++) { 510 for (var j = 0; j < MaxInCol; j++) { 511 sheet.Cells(crow+i, j+1).value=InJ[i][InFeilds[j]]; 512 } 513 InSum+=parseFloat(InJ[i][InFeilds[2]]); 514 } 515 //alert(CRJStr.OutJson); 516 //["RecordID", "dDataDate","ProductID", "cProductName","cSpecification","cMeasureUnit", "nQuantity", "OutPrice","ReMark"]; 517 var OutJ = eval("("+CRJStr.OutJson+")"); 518 for (var i = 0 ; i < OutJ.length; i++) { 519 sheet.Cells(crow+i, MaxInCol+1).value=OutJ[i][OutFeilds[1]]; 520 for(var j=3;j<=7;j++){ 521 sheet.Cells(crow+i, MaxInCol+j-1).value=OutJ[i][OutFeilds[j]]; 522 } 523 var CMoney = parseFloat(OutJ[i][OutFeilds[6]])*parseFloat(OutJ[i][OutFeilds[7]]); 524 OutSum+=CMoney; 525 sheet.Cells(crow+i, MaxCol).value = CMoney; 526 } 527 //合计 528 //alert(InJ.length); 529 var InOutLength = InJ.length>OutJ.length?InJ.length:OutJ.length; 530 InOutLength += crow; 531 InOutLength = InOutLength>15?InOutLength:15; 532 InOutLength++; 533 //alert(InOutLength); 534 sheet.Cells(InOutLength, 1).value="本项合计"; 535 //sheet.Range(sheet.Cells(InOutLength,1),sheet.Cells(InOutLength,2)).mergecells=true; 536 sheet.Cells(InOutLength,3).value=InSum; 537 sheet.Cells(InOutLength, MaxInCol+1).value="本项合计"; 538 //sheet.Range(sheet.Cells(InOutLength,4),sheet.Cells(InOutLength,8)).mergecells=true; 539 sheet.Cells(InOutLength,MaxCol).value=OutSum; 540 InOutLength++; 541 sheet.Cells(InOutLength, 1).value="公司补贴(3)"; 542 sheet.Range(sheet.Cells(InOutLength,1),sheet.Cells(InOutLength,MaxInCol)).mergecells=true; 543 sheet.Range(sheet.Cells(InOutLength,MaxInCol+1),sheet.Cells(InOutLength,MaxCol)).mergecells=true; 544 //奖励 545 var AwardJ = eval("("+CRJStr.AwardJson+")"); 546 var AwardAllStr =""; 547 var AwardSum =0; 548 $.each(AwardJ,function(i,item){ 549 //alert(item[AwardFeilds[1]]); 550 //alert(OptionJson[item[AwardFeilds[1]]]); 551 AwardAllStr += OptionJson[item[AwardFeilds[1]]].toString() + " " + item[AwardFeilds[2]].toString() + "; " ; 552 AwardSum += parseFloat(item[AwardFeilds[2]]); 553 }); 554 //alert(AwardAllStr); 555 sheet.Cells(InOutLength, MaxInCol+1).value=AwardAllStr; 556 sheet.Cells(InOutLength, MaxInCol+1).WrapText=true; 557 InOutLength++; 558 sheet.Cells(InOutLength, 1).value="补贴合计"; 559 sheet.Range(sheet.Cells(InOutLength,1),sheet.Cells(InOutLength,MaxInCol)).mergecells=true; 560 sheet.Cells(InOutLength, MaxInCol+1).value=AwardSum; 561 sheet.Range(sheet.Cells(InOutLength,MaxInCol+1),sheet.Cells(InOutLength,MaxCol)).mergecells=true; 562 InOutLength++; 563 sheet.Cells(InOutLength, 1).value="往来余额合计 = (1) - (2) + (3)"; 564 sheet.Range(sheet.Cells(InOutLength,1),sheet.Cells(InOutLength,MaxInCol-1)).mergecells=true; 565 var TotalNum =InSum-OutSum+AwardSum; 566 sheet.Cells(InOutLength,MaxInCol).value= TotalNum; 567 sheet.Cells(InOutLength,MaxInCol).Interior.Color = 13408767;
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using System.Web.Services; 8 using System.Data; 9 using System.Text; 10 using System.Data.SqlClient; 11 using System.Reflection; 12 13 namespace jsnh_dzd 14 { 15 public partial class DzdEdit : System.Web.UI.Page 16 { 17 public string OptionStr = ""; 18 public string OptionJson = ""; 19 public string CRJStr = "\"\""; 20 public string OutFeilds = ""; 21 public static DateTime beginDate = Convert.ToDateTime("2012-8-1"); 22 public static DateTime endDate = Convert.ToDateTime("2013-7-31"); 23 protected void Page_Load(object sender, EventArgs e) 24 { 25 //生成销售字段数组 26 PropertyInfo[] pis = typeof(Sales).GetProperties(); 27 string[] strOutTemp = new string[pis.Length]; 28 int i=0; 29 foreach (PropertyInfo pi in pis) 30 { 31 strOutTemp[i] = pi.Name; 32 i++; 33 } 34 OutFeilds= string.Join("\",\"",strOutTemp); 35 OutFeilds = "[\"" + OutFeilds + "\"]"; 36 //补贴科目下拉框数据 37 CreateOption(); 38 //获取当前记录数据 39 string Id = Request["Id"]; 40 GetCurrentRecord(Id); 41 } 42 /// <summary> 43 /// //获取当前记录数据 44 /// </summary> 45 /// <param name="Id"></param> 46 private void GetCurrentRecord(string Id) 47 { 48 if (!string.IsNullOrEmpty(Id)) 49 { 50 //获取当前记录数据 51 string sql = "select * from CW_Statement where Id='" + Id + "'"; 52 DataTable dtCR = DbHelperSQL.Query(sql).Tables[0]; 53 Statement st = new Statement(); 54 if (dtCR.Rows.Count > 0) 55 { 56 DataRow dr = dtCR.Rows[0]; 57 st.AwardJson = dr["AwardJson"].ToString(); 58 st.CreatorDate = dr["CreatorDate"].ToString(); 59 st.CustomId = dr["CustomId"].ToString(); 60 st.CustomName = dr["CustomName"].ToString(); 61 st.Id = dr["Id"].ToString(); 62 st.InJson = dr["InJson"].ToString(); 63 st.OutJson = dr["OutJson"].ToString(); 64 } 65 CRJStr = JsonHelper.JsonSerializer<Statement>(st); 66 } 67 } 68 /// <summary> 69 /// 补贴科目下拉框数据 70 /// </summary> 71 private void CreateOption() 72 { 73 StringBuilder sb = new StringBuilder(); 74 sb.Append("<option value=''></option>"); 75 DataTable dtAwardEnum = DbHelperSQL.Query("select * from cw_awardenum").Tables[0]; 76 OptionJson += "{"; 77 foreach (DataRow dr in dtAwardEnum.Rows) 78 { 79 OptionJson += "\"" + dr["EnumKey"].ToString() + "\":\"" + dr["EnumName"].ToString() + "\","; 80 sb.Append("<option value='" + dr["EnumKey"].ToString() + "'>" + dr["EnumName"].ToString() + "</option>"); 81 } 82 if (OptionJson.Length > 1) 83 { 84 OptionJson = OptionJson.TrimEnd(','); 85 } 86 OptionJson += "}"; 87 OptionStr = sb.ToString(); 88 } 89 [WebMethod] 90 public static string DoAdd(string Id,string InJson, string OutJson, string AwardJson, string CustomId, string CustomName) 91 { 92 string sql = @"insert into CW_Statement (InJson,OutJson,AwardJson,CustomId,CustomName,CreatorId,CreatorName,CreatorDate,IsLock,Remark) 93 values (@InJson,@OutJson,@AwardJson,@CustomId,@CustomName,@CreatorId,@CreatorName,@CreatorDate,@IsLock,@Remark)"; 94 SqlParameter[] parameters = { 95 new SqlParameter("@InJson", SqlDbType.NVarChar,-1), 96 new SqlParameter("@OutJson", SqlDbType.NVarChar,-1), 97 new SqlParameter("@AwardJson", SqlDbType.NVarChar,-1), 98 new SqlParameter("@CustomId", SqlDbType.VarChar,50), 99 new SqlParameter("@CustomName", SqlDbType.NVarChar,50), 100 new SqlParameter("@CreatorId", SqlDbType.VarChar,50), 101 new SqlParameter("@CreatorName", SqlDbType.NVarChar,50), 102 new SqlParameter("@CreatorDate", SqlDbType.DateTime), 103 new SqlParameter("@IsLock", SqlDbType.NVarChar,50), 104 new SqlParameter("@Remark", SqlDbType.NVarChar,-1)}; 105 parameters[0].Value = InJson; 106 parameters[1].Value = OutJson; 107 parameters[2].Value = AwardJson; 108 parameters[3].Value = CustomId; 109 parameters[4].Value = CustomName; 110 parameters[5].Value = ""; 111 parameters[6].Value = ""; 112 parameters[7].Value = DateTime.Now; 113 parameters[8].Value = "未锁定"; 114 parameters[9].Value = ""; 115 int rows = DbHelperSQL.ExecuteSql(sql, parameters); 116 if (rows > 0) 117 { 118 return "T"; 119 } 120 else 121 { 122 return "F"; 123 } 124 } 125 [WebMethod] 126 public static string DoModi(string Id, string InJson, string OutJson, string AwardJson, string CustomId, string CustomName) 127 { 128 string sql = @"update CW_Statement set InJson=@InJson,OutJson=@OutJson,AwardJson=@AwardJson,UpdateId=@UpdateId, 129 UpdateName=@UpdateName,UpdateDate=@UpdateDate 130 where Id=@Id"; 131 SqlParameter[] parameters = { 132 new SqlParameter("@InJson", SqlDbType.NVarChar,-1), 133 new SqlParameter("@OutJson", SqlDbType.NVarChar,-1), 134 new SqlParameter("@AwardJson", SqlDbType.NVarChar,-1), 135 new SqlParameter("@UpdateId", SqlDbType.VarChar,50), 136 new SqlParameter("@UpdateName", SqlDbType.NVarChar,50), 137 new SqlParameter("@UpdateDate", SqlDbType.DateTime), 138 new SqlParameter("@Id", SqlDbType.Int)}; 139 parameters[0].Value = InJson; 140 parameters[1].Value = OutJson; 141 parameters[2].Value = AwardJson; 142 parameters[3].Value = ""; 143 parameters[4].Value = ""; 144 parameters[5].Value = DateTime.Now; 145 parameters[6].Value = Id; 146 int rows = DbHelperSQL.ExecuteSql(sql, parameters); 147 if (rows > 0) 148 { 149 return "T"; 150 } 151 else 152 { 153 return "F"; 154 } 155 } 156 157 /// <summary> 158 /// 获取销货记录,收款记录 159 /// </summary> 160 /// <param name="Page"></param> 161 /// <param name="PageSize"></param> 162 /// <param name="sqlwhere"></param> 163 /// <returns></returns> 164 [WebMethod] 165 public static string GetInOutData(int CustomId) 166 { 167 string bDate = beginDate.ToShortDateString(); //"2012-8-1"; 168 string eDate = endDate.ToShortDateString();//"2013-7-31"; 169 DataTable dtIn; 170 string sqlIn = @"select RecordID,dDataDate InDate,cContent InItem,mCredit InMoney from XS_FDSettleReceipt 171 where mCredit<>0 and cSettleSort<>'往来期初' and CustID=" + CustomId + 172 " and dDataDate>='"+bDate+"' and dDataDate<='"+eDate+"' order by InDate"; 173 dtIn = DbHelperSQL.Query(sqlIn).Tables[0]; 174 List<InData> listI = new List<InData>(); 175 foreach (DataRow dr in dtIn.Rows) 176 { 177 InData indata = new InData(); 178 indata.InDate = Convert.ToDateTime(dr["InDate"].ToString()).ToString("yyyy-MM-dd"); 179 indata.InItem = dr["InItem"].ToString(); 180 indata.InMoney = dr["InMoney"].ToString(); 181 indata.RecordID = dr["RecordID"].ToString(); 182 indata.Remark = ""; 183 listI.Add(indata); 184 } 185 string jsonInString = JsonHelper.JsonSerializer<List<InData>>(listI); 186 DataTable dtOut; 187 string sqlOut = @"select A.RecordID, A.dDataDate,B.cProductName,B.cSpecification, 188 A.nQuantity,B.cMeasureUnit,B.ProductID 189 from XS_SA_Sales A 190 left join BS_Product B on A.ProductID=B.ProductID and A.cEnteCode=B.cEnteCode 191 where A.CustID=" + CustomId + " and A.dDataDate>='"+bDate+"' and A.dDataDate<='"+eDate+"' order by A.dDataDate"; 192 dtOut = DbHelperSQL.Query(sqlOut).Tables[0]; 193 List<Sales> listC = new List<Sales>(); 194 foreach (DataRow dr in dtOut.Rows) 195 { 196 Sales c = new Sales(); 197 c.RecordID = dr["RecordID"].ToString(); 198 c.dDataDate = Convert.ToDateTime(dr["dDataDate"].ToString()).ToString("yyyy-MM-dd"); 199 c.cProductName = dr["cProductName"].ToString(); 200 c.cSpecification = dr["cSpecification"].ToString(); 201 c.cMeasureUnit = dr["cMeasureUnit"].ToString(); 202 c.nQuantity = dr["nQuantity"].ToString(); 203 c.ProductID = dr["ProductID"].ToString(); 204 c.OutPrice = ""; 205 c.ReMark = ""; 206 listC.Add(c); 207 } 208 string jsonString = JsonHelper.JsonSerializer<List<Sales>>(listC); 209 string result = "{\"QueryInData\":" + jsonInString + ",\"QueryOutData\":" + jsonString + "}"; 210 return result; 211 } 212 } 213 }
ＪＳＯＮ大字段写到子表时用到了JSON反序列化集合
参考http://www.cnblogs.com/zhaozhan/archive/2011/01/09/1931340.html
posted on 2013-05-15 15:00 thegavincheng 阅读(352) 评论(0) 编辑 收藏 举报
