凯云水利水电工程造价系统 (二) 材料单价 (2)
接着上一篇的材料单价 (1)
3.4-3.2.2.2修改
(1)当点击材料单价的修改按钮时,就会弹出修改材料单价的窗体:
(图 1)
(2)修改之前先绑定你所要修改的数据,在打开窗体的同时,就在进行绑定数据操作,代码如下:
//打开修改窗体
function OpenUpdate(){
//自定义方法
var MaterialCostPriceID=$('#w材料单价').datagrid('getSelected');
//先选中所要修改材料单价的那一行
if(MaterialCostPriceID){
//判断是否存在要修改的那一行
$('#wUpdateMaterialPrice').window('open');
//修改材料单窗体打开
UpdateBinding();
//调用绑定修改数据的方法
}
else{
alert("请选中需要修改的行");
}
}
//修改材料单价-绑定
function UpdateBinding(){
var MaterialCostPriceID=$('#w材料单价').datagrid('getSelected');
先选中所要修改材料单价的那一行
$('#txtCode2').val(MaterialCostPriceID.Code);
//获取选中那一行的数据赋值给修改材料窗体里相应的文本框
$('#txtName2').val(MaterialCostPriceID.Name);
$('#txtUnit2').val(MaterialCostPriceID.Unit);
$('#txtMaterialCostPrice2').val(MaterialCostPriceID.MaterialCostPrice);
$ ('#txtPackagingPrice2').val(MaterialCostPriceID.PackagingPrice);
$('#txtFare2').val(MaterialCostPriceID.Fare);
$('#txtBuyingAndHoldPrice2').val(MaterialCostPriceID.BuyingAndHoldPrice);
$('#txtTransferSafeCost2').val(MaterialCostPriceID.TransferSafeCost);
$('#txtIfMainMaterial2').attr('checked',MaterialCostPriceID.IfMainMaterial);
$('#txtOwnerSupplyMaterial2').attr('checked',MaterialCostPriceID.OwnerSupplyMaterial);
$('#txtBudgetPrice2').val(MaterialCostPriceID.BudgetPrice);
$('#TreeBox').combotree('setValue',MaterialCostPriceID.MaterialPriceClassesID);
}
(3)运杂费的修改,不能手写,只能点击计算按钮,出现修改运杂费的计算窗体,会自动绑定相应的数据,然后进行修改,首先,先点击计算按钮:
(3.1)出现运杂费的计算窗体之后,可以单击运输项目的数据,右边文本框会出现之前保存好的数据:
(3.2)可以对之前保存好的数据进行修改,修改完数据之后,点击右上角的修改按钮,进行修改:
(图 4)
(3.3) 点击修改按钮,所触发的界面方法代码:
//修改运杂费
function UpdateYunZaFei(){
var XiangMu= $('#InsertYunShuXiangMu').datagrid('getSelected');
var CaiLiaoID=$('#w材料单价').datagrid('getSelected');
$.getJSON("/CaiLiaoDanJia/SelectedYunZaFei?YunShuXiangMuID="+ XiangMu.YunShuXiangMuID,
function(data){
$.getJSON("/CaiLiaoDanJia/UpdareunZaFei?YunShuXiangMuID="+XiangMu.YunShuXiangMuID+"&"
+"运输起点="+$('#YunShuQiDianWeiZhi1').val().trim()+"&"
+"装车费="+aa+"&"
+"每公里运费="+bb+"&"
+"运距="+cc+"&"
+"系数="+ee+"&"
+"装载系数="+ff+"&"
+"卸车费="+dd+"&"
+"其它="+gg+"&"
+"计算公式="+$('#YunShuGongShi1').val().trim()+"&"
+"每吨运杂费="+$('#yunzafei1').val().trim()+"&"
+"交货地点="+$('#JiaoHuoDiDiao1').val().trim()+"&"
+"交货条件="+$('#JiaoHuoTiaoJian1').val().trim()+"&"
+"交货等级="+$('#HuoWuDengJi1').val().trim()+"&"
+"YunZaFeiID="+data[0].YunZaFeiID,
//对修改的数据进行保存
function(data){
});
$.getJSON("/CaiLiaoDanJia/UpdateYunFeiXiangMu?MaterialPriceID="+CaiLiaoID.MaterialPriceID+"&"
//刷新运输项目操作
+"FeiYong="+$('#yunzafei1').val().trim()+"&"
+"YunShuXiangMuID="+XiangMu.YunShuXiangMuID,
function(data){
$.getJSON("/CaiLiaoDanJia/BangDingXiangMu?MaterialPriceID="+CaiLiaoID.MaterialPriceID,
//刷新材料单价datagrid操作
function(data){
$('#InsertYunShuXiangMu').datagrid('loadData',data);
CountYun();
// 调用计算运杂费合价的方法
});
});
});
}
//计算运杂费合价
function CountYun(rowIndex, rowData, changes){
//对运杂费的合计进行计算
var D=0;
var rengongMingXiID = $('#InsertYunShuXiangMu').datagrid('getData');
//获取运输项目的运输价格
for (var i = 0; i < rengongMingXiID.rows.length; i++)
//循环遍历
{
D += rengongMingXiID.rows[i].FeiYong*1;
//把运杂相加
}
$('#YingFuJingE1').val(D);
//把计算出的合价赋值给文本框
}
(3.4):修改运杂费的控制器代码:
#region 修改运杂费
public ContentResult UpdareunZaFei(string YunShuXiangMuID, string 运输起点, string 装车费, string 每公里运费, string 运距, string 系数, string 装载系数, string 卸车费, string 其它, string 计算公式, string 每吨运杂 费,string 交货地点, string 交货条件, string 交货等级, string YunZaFeiID)
{
int dt = myCaiLiaoDanJia.UpdareunZaFei(Convert.ToInt32(YunShuXiangMuID),运输起点, 装车费, 每公里运费, 运距, 系数, 装载系数, 卸车费, 其它, 计算公式, 每吨运杂费, 交货地点, 交货条件, 交货等级, Convert.ToInt32(YunZaFeiID));
return Content(dt.ToString());
}
#endregion
(3.5)修改运杂费的逻辑层的代码:
#region 修改
public int UpdareunZaFei(int YunShuXiangMuID, string 运输起点, string 装车费, string 每公里运费, string 运距,string 系数, string 装载系数, string 卸车费, string 其它, string 计算公式, string 每吨运杂费,
string 交货地点, string 交货条件, string 交货等级, int YunZaFeiID)
{
SqlParameter[] mySqlParameter = {
new SqlParameter("@Type",SqlDbType.Char),
new SqlParameter ("@YunShuXiangMuID",SqlDbType.Int),
new SqlParameter ("@运输起点",SqlDbType.Char),
new SqlParameter ("@装车费",SqlDbType.Char),
new SqlParameter ("@每公里运费",SqlDbType.Char),
new SqlParameter ("@运距",SqlDbType.Char),
new SqlParameter ("@系数",SqlDbType .Char),
new SqlParameter ("@装载系数",SqlDbType .Char),
new SqlParameter ("@卸车费",SqlDbType .Char ),
new SqlParameter ("@其它",SqlDbType.Char ),
new SqlParameter ("@计算公式",SqlDbType.Char),
new SqlParameter ("@每吨运杂费",SqlDbType.Char),
new SqlParameter ("@交货地点",SqlDbType.Char ),
new SqlParameter ("@交货条件",SqlDbType.Char),
new SqlParameter ("@交货等级",SqlDbType.Char),
new SqlParameter ("@YunZaFeiID",SqlDbType.Int),
};
mySqlParameter[0].Value = "UpdareunZaFei";
mySqlParameter[1].Value = YunShuXiangMuID;
mySqlParameter[2].Value = 运输起点;
mySqlParameter[3].Value = 装车费;
mySqlParameter[4].Value = 每公里运费;
mySqlParameter[5].Value = 运距;
mySqlParameter[6].Value = 系数;
mySqlParameter[7].Value = 装载系数;
mySqlParameter[8].Value = 卸车费;
mySqlParameter[9].Value = 其它;
mySqlParameter[10].Value = 计算公式;
mySqlParameter[11].Value = 每吨运杂费;
mySqlParameter[12].Value = 交货地点;
mySqlParameter[13].Value = 交货条件;
mySqlParameter[14].Value = 交货等级;
mySqlParameter[15].Value = YunZaFeiID;
int i = myDALMethod.DAL_OPTableDB_Par("CaiLiaoDanJia", mySqlParameter);
return i;
}
#endregion
(3.5)修改运杂费的数据层的SQL语句:
--修改
if @Type='UpdareunZaFei'
begin
update [Sys-YunZaFeiBiao]
set YunShuXiangMuID=@YunShuXiangMuID, 运输起点=@运输起点,装车费=@装车费, 每公里运费=@每公里运费, 运距=@运距, 系数=@系数, 装载系数=@装载系数, 卸车费=@卸车费,
其它=@其它, 计算公式=@计算公式, 每吨运杂费=@每吨运杂费, 交货地点=@交货地点, 交货条件=@交货条件, 交货等级=@交货等级
where YunZaFeiID=@YunZaFeiID
end
修改成功后的截图:
(4)运杂费修改成功后,就填写其它要修改的数据,然后点击保存按钮:
(图 6)
(5)点击修改材料单价里的保存按钮,所触发的界面的方法的代码:
function Update(){
var MaterialCostPriceID=$('#w材料单价').datagrid('getSelected');//获取材料单价的一行
var txtIfMainMaterial2=false;
if( document.getElementById('txtIfMainMaterial2').checked==true){//判断checkBox是否被选中
txtIfMainMaterial2=true;
//如果被选中,就赋值为true
}
var txtOwnerSupplyMaterial2=false;
if(document.getElementById('txtOwnerSupplyMaterial2').checked==true){
txtOwnerSupplyMaterial2=true;
}
if (confirm('是否需要修改?')){
$.getJSON("/CaiLiaoDanJia/MaterialPrice_Update?Code="+$('#txtCode2').val()+"&"
+"Name="+$('#txtName2').val()+"&"
+"Unit="+$('#txtUnit2').val()+"&"
+"MaterialCostPrice="+$('#txtMaterialCostPrice2').val()+"&"
+"PackagingPrice="+$('#txtPackagingPrice2').val()+"&"
+"Fare="+$('#txtFare2').val()+"&"
+"BuyingAndHoldPrice="+$('#txtBuyingAndHoldPrice2').val()+"&"
+"TransferSafeCost="+$('#txtTransferSafeCost2').val()+"&"
+"BudgetPrice="+$('#txtBudgetPrice2').val()+"&"
+"IfMainMaterial="+txtIfMainMaterial2+"&"
+"OwnerSupplyMaterial="+txtOwnerSupplyMaterial2+"&"
+"MaterialPriceClassesID="+$('#TreeBox').combobox('getValue')+"&"
+"MaterialPriceID="+MaterialCostPriceID.MaterialPriceID+"&"
+"BuildProjectID="+@Session["项目ID"],
function(data){
if(data>0){
//修改成功后的重新查询 $.getJSON("/CaiLiaoDanJia/SelectAllCaiLiao?BuildProjectID="+ @Session["项目ID"],
function(data){
$('#w材料单价').datagrid('loadData',data);
alert("修改成功!");
$('#wUpdateMaterialPrice').window('close');
} );
}
else{
alert("修改失败!");
}
});
}
else{
alert("修改失败!");
}
}
(6)控制器的代码:
#region 修改材料分类
public ContentResult MaterialPrice_Update(string Code, string Name, string Unit, string MaterialCostPrice, string PackagingPrice, string Fare,string BuyingAndHoldPrice, string TransferSafeCost, string BudgetPrice, bool IfMainMaterial, bool OwnerSupplyMaterial, int MaterialPriceClassesID, int BuildProjectID, int MaterialPriceID)
{
int i = myCaiLiaoDanJia.MaterialPrice_Update(Code, Name, Unit, Convert.ToDecimal(MaterialCostPrice), Convert.ToDecimal(PackagingPrice), Convert.ToDecimal(Fare), Convert.ToDecimal(BuyingAndHoldPrice), Convert.ToDecimal(TransferSafeCost), Convert.ToDecimal(BudgetPrice), IfMainMaterial, OwnerSupplyMaterial, MaterialPriceClassesID, BuildProjectID, MaterialPriceID);
return Content(i.ToString());
}
(7)逻辑层的数据返回给控制器的代码:
#region 修改材料单价
public int MaterialPrice_Update(string Code, string Name, string Unit, decimal MaterialCostPrice, decimal PackagingPrice, decimal Fare, decimal BuyingAndHoldPrice, decimal TransferSafeCost, decimal BudgetPrice, bool IfMainMaterial, bool OwnerSupplyMaterial, int MaterialPriceClassesID, int BuildProjectID, int MaterialPriceID)
{
SqlParameter[] mySqlParameter = {
new SqlParameter("@Type",SqlDbType.Char),
new SqlParameter ("@Code",SqlDbType.Char),
new SqlParameter ("@Name",SqlDbType.Char),
new SqlParameter ("@Unit",SqlDbType.Char),
new SqlParameter ("@MaterialCostPrice",SqlDbType.Decimal),
new SqlParameter ("@PackagingPrice",SqlDbType.Decimal),
new SqlParameter ("@Fare",SqlDbType .Decimal),
new SqlParameter ("@BuyingAndHoldPrice",SqlDbType .Decimal),
new SqlParameter ("@TransferSafeCost",SqlDbType .Decimal ),
new SqlParameter ("@BudgetPrice",SqlDbType.Decimal ),
new SqlParameter ("@IfMainMaterial",SqlDbType.Bit),
new SqlParameter ("@OwnerSupplyMaterial",SqlDbType.Bit),
new SqlParameter ("@MaterialPriceClassesID",SqlDbType.Int),
new SqlParameter ("@BuildProjectID",SqlDbType.Int),
new SqlParameter ("@MaterialPriceID",SqlDbType.Int),
};
mySqlParameter[0].Value = "MaterialPrice_Update";
mySqlParameter[1].Value = Code;
mySqlParameter[2].Value = Name;
mySqlParameter[3].Value = Unit;
mySqlParameter[4].Value = MaterialCostPrice;
mySqlParameter[5].Value = PackagingPrice;
mySqlParameter[6].Value = Fare;
mySqlParameter[7].Value = BuyingAndHoldPrice;
mySqlParameter[8].Value = TransferSafeCost;
mySqlParameter[9].Value = BudgetPrice;
mySqlParameter[10].Value = IfMainMaterial;
mySqlParameter[11].Value = OwnerSupplyMaterial;
mySqlParameter[12].Value = MaterialPriceClassesID;
mySqlParameter[13].Value = BuildProjectID;
mySqlParameter[14].Value = MaterialPriceID;
int i = myDALMethod.DAL_OPTableDB_Par("CaiLiaoDanJia", mySqlParameter);
return i;
}
#endregion
(8)数据层的SQL语句:
--材料单价的修改
if @Type='MaterialPrice_Update'
begin
update SYS_MaterialPriceList
set Code=@Code, Name=@Name, Unit=@Unit, MaterialCostPrice=@MaterialCostPrice, PackagingPrice=@PackagingPrice, Fare=@Fare, BuyingAndHoldPrice=@BuyingAndHoldPrice, TransferSafeCost=@TransferSafeCost, BudgetPrice=@BudgetPrice, IfMainMaterial=@IfMainMaterial,
OwnerSupplyMaterial=@OwnerSupplyMaterial,MaterialPriceClassesID=@MaterialPriceClassesID,BuildProjectID=@BuildProjectID
where SYS_MaterialPriceList.MaterialPriceID=@MaterialPriceID
end
(9)修改完材料单价的截图:
3.4-3.2.2.3 删除
(1)对于多余的材料可以进行删除操作:
(2)当点击删除按钮,传到界面层的代码:
//删除材料单价
function DeleteMaterialCostPrice(){
var MaterialPriceID=$('#w材料单价').datagrid('getSelected');
//选中要删除的一行
if(MaterialPriceID!=null){
//要先选中才能执行下面的代码
if(confirm('是否需要删除此项目?')){
//先确定是否删除此数据
$.getJSON("/CaiLiaoDanJia/DeleteMaterialPrice?MaterialPriceID="+MaterialPriceID.MaterialPriceID,
function(data){
if(data>0){
$.getJSON("/CaiLiaoDanJia/SelectAllCaiLiao?BuildProjectID="+ @Session["项目ID"],
function(data){
$('#w材料单价').datagrid('loadData',data);
alert("删除成功!");
} );
}
else{
alert("删除失败!");
}
});
}
}
else{
//如果没选中,就会有一个提醒
alert("请选中要删除的行!");
}
}
(3)界面的数据传给控制器的代码:
#region 删除材料分类
public ContentResult DeleteMaterialPrice(int MaterialPriceID)
{
int i = myCaiLiaoDanJia.DeleteMaterialPrice(MaterialPriceID);
return Content(i.ToString());
}
#endregion
(4)控制器的数据传给逻辑层的代码:
#region 删除材料单价
public int DeleteMaterialPrice(int MaterialPriceID)
{
SqlParameter[] mySqlParameter = {
new SqlParameter("@Type",SqlDbType.Char),
new SqlParameter ("@MaterialPriceID",SqlDbType.Int),
};
mySqlParameter[0].Value = "MaterialPrice_Delete";
mySqlParameter[1].Value = MaterialPriceID;
int i = myDALMethod.DAL_OPTableDB_Par("CaiLiaoDanJia", mySqlParameter);
return i;
}
#endregion
(5)数据层的SQL语句:
--材料单价的删除
if @Type='MaterialPrice_Delete'
begin
delete SYS_MaterialPriceList
where MaterialPriceID=@MaterialPriceID
end
(6)删除那一条数据后的截图:
3.4-3.2.2.4 复制
(1)可以对现有的材料单价进行复制操作,点击复制按钮之前,要先选中你要复制的数据:
(2)复制其实就是先选中一行数据,获取那一行的数据,点击复制执行的界面的方法代码:
//复制
var CopyCode="";
var CopyName="";
var CopyUnit="";
var CopyMaterialCostPrice="";
var CopyPackagingPrice="";
var CopyFare="";
var CopyBuyingAndHoldPrice="";
var CopyTransferSafeCost="";
var CopyBudgetPrice="";
var MaterialPriceClassesID="";
//设置全局变量,后面的粘贴功能可以用到
var copy=0;
function Copy(){
var MaterialPriceID=$('#w材料单价').datagrid('getSelected'); //选中要复制的那一行
if(MaterialPriceID){
//如果执行了Copy这个方法,就把1赋值给copy
copy=1;
if(confirm('是否需要复制此条数据?')){
CopyCode=MaterialPriceID.Code.trim();//把要复制那一行的数据赋值给定义的全局变量
CopyName=MaterialPriceID.Name.trim();
CopyUnit=MaterialPriceID.Unit.trim();
CopyMaterialCostPrice=MaterialPriceID.MaterialCostPrice;
CopyPackagingPrice=MaterialPriceID.PackagingPrice;
CopyFare=MaterialPriceID.Fare;
CopyBuyingAndHoldPrice=MaterialPriceID.BuyingAndHoldPrice;
CopyTransferSafeCost=MaterialPriceID.TransferSafeCost;
CopyBudgetPrice=MaterialPriceID.BudgetPrice;
CopyMaterialPriceClassesID=MaterialPriceID.MaterialPriceClassesID;
CopyIfMainMaterial=MaterialPriceID.IfMainMaterial;
CopyOwnerSupplyMaterial=MaterialPriceID.OwnerSupplyMaterial;
alert("复制成功!");
}
else{
alert("已放弃复制此数据!");
}
}
else{
alert("亲,请选中要复制的那一行数据!");
}
}
3.4-3.2.2.5 粘贴
(1)对上一操作复制完数据之后,就要进行粘贴功能,当提示复制成功之后,就点击粘贴按钮,就会把钢材复制的数据粘贴到最后一行,
点击粘贴所触发的界面方法的代码:
function Paste(){ //粘贴
if(copy==1){
//要先复制才能进行粘贴功能
var MaterialPriceID=$('#w材料单价').datagrid('getSelected');
if(MaterialPriceID){
if(confirm('是否需要粘贴此项目?')){
var y=CopyCode;
//获取复制的代号
var t=y.length;
//获取代号的长度
var e=y.substring(t-2,t);
//获取代号的最后两个字符串
var tr=CopyCode+"_复制";
//让代号最后末尾加上"_复制";,以便于区分
var i=tr.length;
var d=tr.substring(t-3,t);
if (e=="_复制"){
//如果复制的数据中代号末尾有"_复制",那就在末尾重复添加"_复制"
var Code=tr+"-_复制"
$.getJSON("/CaiLiaoDanJia/MaterialPrice_Insert?Code="+CopyCode+"&"
+"Name="+CopyName+"&"
+"Unit="+CopyUnit+"&"
+"MaterialCostPrice="+CopyMaterialCostPrice+"&"
+"PackagingPrice="+CopyPackagingPrice+"&"
+"Fare="+CopyFare+"&"
+"BuyingAndHoldPrice="+CopyBuyingAndHoldPrice+"&"
+"TransferSafeCost="+CopyTransferSafeCost+"&"
+"BudgetPrice="+CopyBudgetPrice+"&"
+"MaterialPriceClassesID="+CopyMaterialPriceClassesID+"&"
+"IfMainMaterial="+CopyIfMainMaterial+"&"
+"OwnerSupplyMaterial="+CopyOwnerSupplyMaterial+"&"
+"BuildProjectID="+@Session["项目ID"],
function data(){
BangDingSuoYouCaiLiao();
//如果粘贴成功,就调用绑定所有材料的方法
alert("粘贴成功,数据已经在最后一行");
});
}
else{
$.getJSON("/CaiLiaoDanJia/MaterialPrice_Insert?Code="+tr+"&"
+"Name="+CopyName+"&"
+"Unit="+CopyUnit+"&"
+"MaterialCostPrice="+CopyMaterialCostPrice+"&"
+"PackagingPrice="+CopyPackagingPrice+"&"
+"Fare="+CopyFare+"&"
+"BuyingAndHoldPrice="+CopyBuyingAndHoldPrice+"&"
+"TransferSafeCost="+CopyTransferSafeCost+"&"
+"BudgetPrice="+CopyBudgetPrice+"&"
+"MaterialPriceClassesID="+CopyMaterialPriceClassesID+"&"
+"IfMainMaterial="+CopyIfMainMaterial+"&"
+"OwnerSupplyMaterial="+CopyOwnerSupplyMaterial+"&"
+"BuildProjectID="+@Session["项目ID"],
function(data){
BangDingSuoYouCaiLiao();
alert("粘贴成功,数据已经在最后一行");
});
}
}
}
else{
alert("先复制数据!");
}
}else{
alert("请先复制");
}
}
(2)粘贴功能其实就是新增功能,所有其控制器、逻辑层、数据层的代码和添加材料单价的是一样的,如果想查看,请查看上一篇中的添加材料单价功能。
(3)粘贴成功之后的截图:
3.4-3.2.2.6 导出
(1)可以对全部材料单价进行导出功能,导出到一张Excel表里,方便相关人员随时查看:
(2)点击导出,界面的方法代码:
//导出
function daoru() {
window.location.href = "/CaiLiaoDanJia/DownloadFile?BuildProjectID="+@Session["项目ID"];
}
(3)控制器所涉及到的代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;//先引用
#region 导出材料单价
public FileResult DownloadFile(int BuildProjectID)
{
DataSet ds = new DataSet();
DataTable dt = myCaiLiaoDanJia.SelectAllCaiLiao(BuildProjectID);
string schoolname = "401";
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
}
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "导出.xls");
}
#endregion
(4)逻辑层其实就是一个查询功能,查出所有是材料单价:
#region 查询全部材料组成
public DataTable SelectAllCaiLiao(int BuildProjectID)
{
SqlParameter[] mySqlParameter ={
new SqlParameter("@Type",SqlDbType.Char),
new SqlParameter("@BuildProjectID",SqlDbType.Int),
};
mySqlParameter[0].Value = "SelectAllCaiLiao";
mySqlParameter[1].Value = BuildProjectID;
DataTable dt = myDALMethod.DAL_SelectDB_Par("CaiLiaoDanJia", mySqlParameter);
return dt;
}
(5)导出在Excel表的截图:
3.4-3.2.2.7 隐藏分类列表
(1)当点击隐藏分类列表按钮时,左边的材料单价分类窗体就会隐藏起来:
界面代码:
//隐藏
function Hidden(){
$('#p').panel('close');
}
3.4-3.2.2.8 显示分类列表
(1) 当点击显示分类列表按钮时,之前显示所谓材料单价分类窗体就会显示出来:
界面代码:
function Open(){
$('#p').panel('open');
}
3.4-3.2.2.9 导入
(1)我们可以在Excel写好要导入的材料单价,点击导入按钮,直接把数据添加到材料单价datagrid中:
(图 17)
(2)选中好要导入的文件后,就点击导入按钮,界面层所涉及到的方法的代码:
<form action="/CaiLiaoDanJia/GetTableFromExcel" method="post" enctype="multipart/form-data">
//是一个from表单提交
<text>选择上传文件</text>
<input name="file1" type="file" id="file" />
<input type="submit" name="Upload" value="导入" />
</form>
(3)传入到控制器的代码:
#region 导入
public ActionResult GetTableFromExcel()
{
HttpPostedFileBase fostField = Request.Files["file1"];
Stream streamfile = fostField.InputStream;
HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);
using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0))
{
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
table.Rows.Add(dataRow);
}
for (int i = 0; i < table.Rows.Count; i++)
{
myCaiLiaoDanJia.MaterialPrice_Insert(table.Rows[i][0].ToString(), table.Rows[i][1].ToString(), table.Rows[i][2].ToString(), Convert.ToDecimal(table.Rows[i][3]), Convert.ToDecimal(table.Rows[i][4]),
Convert.ToDecimal(table.Rows[i][5]), Convert.ToDecimal(table.Rows[i][6]), Convert.ToDecimal(table.Rows[i][7]), Convert.ToDecimal(table.Rows[i][8]), Convert.ToBoolean(table.Rows[i][9]), Convert.ToBoolean(table.Rows[i][10]), Convert.ToInt32(table.Rows[i][11]), Convert.ToInt32(table.Rows[i][12]));
//把值传给逻辑层
}
}
return Content("");
}
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK:
return string.Empty;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.ERROR:
return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
return cell.ToString();
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateFormulaCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
#endregion
(4)逻辑层所涉及到的代码:
#region 新增材料单价
public int MaterialPrice_Insert(string Code, string Name, string Unit, decimal MaterialCostPrice, decimal PackagingPrice, decimal Fare,
decimal BuyingAndHoldPrice, decimal TransferSafeCost, decimal BudgetPrice, bool IfMainMaterial, bool OwnerSupplyMaterial, int MaterialPriceClassesID, int BuildProjectID)
{
SqlParameter[] mySqlParameter = {
new SqlParameter("@Type",SqlDbType.Char),
new SqlParameter ("@Code",SqlDbType.Char),
new SqlParameter ("@Name",SqlDbType.Char),
new SqlParameter ("@Unit",SqlDbType.Char),
new SqlParameter ("@MaterialCostPrice",SqlDbType.Decimal),
new SqlParameter ("@PackagingPrice",SqlDbType.Decimal),
new SqlParameter ("@Fare",SqlDbType .Decimal),
new SqlParameter ("@BuyingAndHoldPrice",SqlDbType .Decimal),
new SqlParameter ("@TransferSafeCost",SqlDbType .Decimal ),
new SqlParameter ("@BudgetPrice",SqlDbType.Decimal ),
new SqlParameter ("@IfMainMaterial",SqlDbType.Bit),
new SqlParameter ("@OwnerSupplyMaterial",SqlDbType.Bit),
new SqlParameter ("@MaterialPriceClassesID",SqlDbType.Int),
new SqlParameter ("@BuildProjectID",SqlDbType.Int),
};
mySqlParameter[0].Value = "MaterialPrice_Insert";
mySqlParameter[1].Value = Code;
mySqlParameter[2].Value = Name;
mySqlParameter[3].Value = Unit;
mySqlParameter[4].Value = MaterialCostPrice;
mySqlParameter[5].Value = PackagingPrice;
mySqlParameter[6].Value = Fare;
mySqlParameter[7].Value = BuyingAndHoldPrice;
mySqlParameter[8].Value = TransferSafeCost;
mySqlParameter[9].Value = BudgetPrice;
mySqlParameter[10].Value = IfMainMaterial;
mySqlParameter[11].Value = OwnerSupplyMaterial;
mySqlParameter[12].Value = MaterialPriceClassesID;
mySqlParameter[13].Value = BuildProjectID;
int i = myDALMethod.DAL_OPTableDB_Par("CaiLiaoDanJia", mySqlParameter);
return i;
}
#endregion
(5)数据层所涉及的SQL 语句:
--材料单价的新增
if @Type='MaterialPrice_Insert'
begin
insert SYS_MaterialPriceList(Code, Name, Unit, MaterialCostPrice, PackagingPrice, Fare, BuyingAndHoldPrice, TransferSafeCost, BudgetPrice, IfMainMaterial, OwnerSupplyMaterial,MaterialPriceClassesID,BuildProjectID)
values (@Code,@Name, @Unit, @MaterialCostPrice, @PackagingPrice, @Fare, @BuyingAndHoldPrice, @TransferSafeCost, @BudgetPrice, @IfMainMaterial, @OwnerSupplyMaterial,@MaterialPriceClassesID,@BuildProjectID)
end
(6)导入成功之后的截图:
仅供学习参考,禁止用于商业用途,否则后果自负!