private string InsertData(Net.SourceForge.Koogra.Excel.Row row)
{
string strResult = "";
using (MMSProDBDataContext db = new MMSProDBDataContext(ConfigurationManager.ConnectionStrings["mmsConString"].ConnectionString))
{
//检查往来单位类别
if (row.Cells[0] == null || row.Cells[1] == null)
{
return("往来单位类别信息不完整");
}
//写入往来单位类别信息
BusinessUnitType but;
//检查往来单位类别是否存在
if (!db.BusinessUnitType.Any(a => a.BusinessUnitTypeName == row.Cells[1].Value.ToString()))
{
but = new BusinessUnitType();
//MT.MaterialMainTypeCode =
but.BusinessUnitTypeCode = row.Cells[0].Value.ToString();
but.BusinessUnitTypeName = row.Cells[1].Value.ToString();
but.Remark = row.Cells[2] == null ? "" : row.Cells[2].Value.ToString();
db.BusinessUnitType.InsertOnSubmit(but);
db.SubmitChanges();
}
else
{
but = db.BusinessUnitType.SingleOrDefault(a => a.BusinessUnitTypeName == row.Cells[1].Value.ToString());
but.BusinessUnitTypeCode = row.Cells[0].Value.ToString();
but.BusinessUnitTypeName = row.Cells[1].Value.ToString();
but.Remark = row.Cells[2] == null ? "" : row.Cells[2].Value.ToString();
db.SubmitChanges();
}
//***************************************************************************
//写入单位信息
if (row.Cells[3] == null || row.Cells[4] == null)
{
return("单位信息不完整");
}
//写入单位信息
BusinessUnitInfo bui;
//检查单位是否存在
if (!db.BusinessUnitInfo.Any(a => a.BusinessUnitCode == row.Cells[3].Value.ToString()))
{
bui = new BusinessUnitInfo();
//MT.MaterialMainTypeCode =
bui.BusinessUnitCode = row.Cells[3].Value.ToString();
bui.BusinessUnitName = row.Cells[4].Value.ToString();
bui.Remark = row.Cells[5] == null ? "" : row.Cells[5].Value.ToString();
bui.BusinessUnitTypeID = but.BusinessUnitTypeID;
db.BusinessUnitInfo.InsertOnSubmit(bui);
db.SubmitChanges();
}
else
{
bui = db.BusinessUnitInfo.SingleOrDefault(a => a.BusinessUnitCode == row.Cells[3].Value.ToString());
bui.BusinessUnitCode = row.Cells[3].Value.ToString();
bui.BusinessUnitName = row.Cells[4].Value.ToString();
bui.BusinessUnitTypeID = but.BusinessUnitTypeID;
bui.Remark = row.Cells[5] == null ? "" : row.Cells[5].Value.ToString();
db.SubmitChanges();
}
//***************************************************************************
//写项目信息
if (row.Cells[6] == null || row.Cells[7] == null)
{
return("项目信息不完整");
}
ProjectInfo pi;
//检查生产厂商类别是否存在
if (!db.ProjectInfo.Any(a => a.ProjectCode == row.Cells[6].Value.ToString()))
{
pi = new ProjectInfo();
//MT.MaterialMainTypeCode =
pi.ProjectCode = row.Cells[6].Value.ToString();
pi.ProjectName = row.Cells[7].Value.ToString();
pi.Remark = row.Cells[8] == null ? "" : row.Cells[8].Value.ToString();
pi.Owner = bui.BusinessUnitID;
db.ProjectInfo.InsertOnSubmit(pi);
db.SubmitChanges();
}
else
{
pi = db.ProjectInfo.SingleOrDefault(a => a.ProjectCode == row.Cells[6].Value.ToString());
pi.ProjectCode = row.Cells[6].Value.ToString();
pi.ProjectName = row.Cells[7].Value.ToString();
pi.Remark = row.Cells[8] == null ? "" : row.Cells[8].Value.ToString();
pi.Owner = bui.BusinessUnitID;
db.SubmitChanges();
}
}
return(strResult);
}
//数据导入
private void InsertData(Net.SourceForge.Koogra.Excel.Row row)
{
//loading图标
//this.img.Visible = true;
//Response.AddHeader("Refresh", "0");
using (MMSProDBDataContext db = new MMSProDBDataContext(ConfigurationManager.ConnectionStrings["mmsConString"].ConnectionString))
{
//判断必填字段是否有空数据
for (int i = 0; i < 8; i++)
{
if (row.Cells[(uint)i] == null)
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('数据中有必填字段为空')</script>");
return;
}
}
//判断是否有此入库单
StorageInMain si = db.StorageInMain.SingleOrDefault(u => u.StorageInCode == row.Cells[0].Value.ToString().Trim());
if (si == null)
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('待导入的入库单号[ " + row.Cells[0].Value.ToString().Trim() + " ] 不存在')</script>");
return;
}
//判断是否有物料编码
MaterialInfo mi = db.MaterialInfo.SingleOrDefault(u => u.FinanceCode == row.Cells[1].Value.ToString().Trim());
if (mi == null)
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('待导入的物料编码[ " + row.Cells[1].Value.ToString().Trim() + " ] 不存在,请先建立物料后再尝试导入')</script>");
return;
}
//判断数据格式
//根/套/件
if (!Utility.Security.ValidString(row.Cells[2].Value.ToString().Trim(), Utility.CodeValideType.零或者非零开头的整数))
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('根/套/件数量只能是整数')</script>");
return;
}
//米
if (!Utility.Security.ValidString(row.Cells[3].Value.ToString().Trim(), Utility.CodeValideType.带两位小数的正实数))
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('米的数量只能是整数或者带两位小数的正实数')</script>");
return;
}
//吨
if (!Utility.Security.ValidString(row.Cells[4].Value.ToString().Trim(), Utility.CodeValideType.带两位小数的正实数))
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('吨的数量只能是整数或者带两位小数的正实数')</script>");
return;
}
//判断有无项目信息
ProjectInfo pi = db.ProjectInfo.SingleOrDefault(u => u.ProjectCode == row.Cells[5].Value.ToString().Trim());
if (pi == null)
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('待导入的项目编码[ " + row.Cells[5].Value.ToString().Trim() + " ] 不存在,请先建立项目信息后再尝试导入')</script>");
return;
}
//预计到库时间
if (ConvData(row.Cells[6].Value.ToString().Trim()).ToString() != "1900-1-1 0:00:00")
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('入库时间不和规范,请按年-月-日格式，如：2010-10-10')</script>");
return;
}
//判断批次信息
if (!ValideBatch(row.Cells[7].Value.ToString()))
{
ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('" + row.Cells[7].Value.ToString() + "处 格式错误！请按此格式输入，如：第一批,第二批,最多十批')</script>");
return;
}
//ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('"+row.Cells[7].Value.ToString()+" 检查完毕一切正常')</script>");
//开始数据导入
StorageProduce SID = new StorageProduce();
SID.StorageInID = Convert.ToInt32(Request.QueryString["storageInID"]);
//ClientScript.RegisterClientScriptBlock(typeof(string), "ShowMessage", "<script>alert('" + SID.StorageInID + "')</script>");
SID.MaterialID = (db.MaterialInfo.SingleOrDefault(u => u.FinanceCode == row.Cells[1].Value.ToString().Trim())).MaterialID;
SID.QuantityGentaojian = Convert.ToDecimal(row.Cells[2].Value.ToString().Trim());
SID.QuantityMetre = Convert.ToDecimal(row.Cells[3].Value.ToString().Trim());
SID.QuantityTon = Convert.ToDecimal(row.Cells[4].Value.ToString().Trim());
SID.ExpectedProject = (db.ProjectInfo.SingleOrDefault(u => u.ProjectCode == row.Cells[5].Value.ToString().Trim())).ProjectID;
SID.ExpectedTime = ConvData(row.Cells[6].Value.ToString().Trim());
SID.BatchIndex = row.Cells[7].Value.ToString();
var SevTime = db.ExecuteQuery <DateTime>("select getdate()", new object[] { });
SID.CreateTime = SevTime.First();
SID.Creator = reEmpId(SPContext.Current.Web.CurrentUser.LoginName);
if (row.Cells.MaxCol > 7)
{
SID.Remark = row.Cells[8].Value.ToString();
}
else
{
SID.Remark = string.Empty;
}
db.StorageProduce.InsertOnSubmit(SID);
db.SubmitChanges();
}
}
private string InsertData(Net.SourceForge.Koogra.Excel.Row row)
{
string strResult = "";
using (MMSProDBDataContext db = new MMSProDBDataContext(ConfigurationManager.ConnectionStrings["mmsConString"].ConnectionString))
{
//检查生产厂商类别
if (row.Cells[0] == null || row.Cells[1] == null)
{
return("生产厂商类别信息不完整");
}
//写入生产厂商类别信息
ManufacturerType mft;
//检查生产厂商类别是否存在
if (!db.ManufacturerType.Any(a => a.ManufacturerTypeCode == row.Cells[0].Value.ToString()))
{
mft = new ManufacturerType();
//MT.MaterialMainTypeCode =
mft.ManufacturerTypeCode = row.Cells[0].Value.ToString();
mft.ManufacturerTypeName = row.Cells[1].Value.ToString();
mft.Remark = row.Cells[2] == null ? "" : row.Cells[2].Value.ToString();
db.ManufacturerType.InsertOnSubmit(mft);
db.SubmitChanges();
}
else
{
mft = db.ManufacturerType.SingleOrDefault(a => a.ManufacturerTypeCode == row.Cells[0].Value.ToString());
mft.ManufacturerTypeCode = row.Cells[0].Value.ToString();
mft.ManufacturerTypeName = row.Cells[1].Value.ToString();
mft.Remark = row.Cells[2] == null ? "" : row.Cells[2].Value.ToString();
db.SubmitChanges();
}
//***************************************************************************
//写入生产厂商信息
if (row.Cells[3] == null || row.Cells[4] == null)
{
return("生产厂商信息不完整");
}
//写入生产厂商类别信息
Manufacturer mf;
//检查生产厂商类别是否存在
if (!db.Manufacturer.Any(a => a.ManufacturerCode == row.Cells[3].Value.ToString()))
{
mf = new Manufacturer();
//MT.MaterialMainTypeCode =
mf.ManufacturerCode = row.Cells[3].Value.ToString();
mf.ManufacturerName = row.Cells[4].Value.ToString();
mf.Remark = row.Cells[5] == null ? "" : row.Cells[5].Value.ToString();
mf.ManufacturerTypeID = mft.ManufacturerTypeID;
db.Manufacturer.InsertOnSubmit(mf);
db.SubmitChanges();
}
else
{
mf = db.Manufacturer.SingleOrDefault(a => a.ManufacturerCode == row.Cells[3].Value.ToString());
mf.ManufacturerCode = row.Cells[3].Value.ToString();
mf.ManufacturerName = row.Cells[4].Value.ToString();
mf.ManufacturerTypeID = mft.ManufacturerTypeID;
mf.Remark = row.Cells[5] == null ? "" : row.Cells[5].Value.ToString();
db.SubmitChanges();
}
}
return(strResult);
}
private string InsertData(Net.SourceForge.Koogra.Excel.Row row)
{
string strResult = "";
using (MMSProDBDataContext db = new MMSProDBDataContext(ConfigurationManager.ConnectionStrings["mmsConString"].ConnectionString))
{
//检查供货商类别
if (row.Cells[0] == null || row.Cells[1] == null)
{
return("供货商类别信息不完整");
}
//写入供货商类别信息
SupplierType spt;
//检查供货商类别是否存在
if (!db.SupplierType.Any(a => a.SupplierTypeCode == row.Cells[0].Value.ToString()))
{
spt = new SupplierType();
//MT.MaterialMainTypeCode =
spt.SupplierTypeCode = row.Cells[0].Value.ToString();
spt.SupplierTypeName = row.Cells[1].Value.ToString();
spt.Remark = row.Cells[2] == null ? "" : row.Cells[2].Value.ToString();
db.SupplierType.InsertOnSubmit(spt);
db.SubmitChanges();
}
else
{
spt = db.SupplierType.SingleOrDefault(a => a.SupplierTypeCode == row.Cells[0].Value.ToString());
spt.SupplierTypeCode = row.Cells[0].Value.ToString();
spt.SupplierTypeName = row.Cells[1].Value.ToString();
spt.Remark = row.Cells[2] == null ? "" : row.Cells[2].Value.ToString();
db.SubmitChanges();
}
//***************************************************************************
//写入供货商信息
if (row.Cells[3] == null || row.Cells[4] == null)
{
return("供货商信息不完整");
}
//写入供货商类别信息
SupplierInfo si;
//检查供货商类别是否存在
if (!db.SupplierInfo.Any(a => a.SupplierCode == row.Cells[3].Value.ToString()))
{
si = new SupplierInfo();
//MT.MaterialMainTypeCode =
si.SupplierCode = row.Cells[3].Value.ToString();
si.SupplierName = row.Cells[4].Value.ToString();
si.Remark = row.Cells[5] == null ? "" : row.Cells[5].Value.ToString();
si.SupplierTypeID = spt.SupplierTypeID;
db.SupplierInfo.InsertOnSubmit(si);
db.SubmitChanges();
}
else
{
si = db.SupplierInfo.SingleOrDefault(a => a.SupplierCode == row.Cells[3].Value.ToString());
si.SupplierCode = row.Cells[3].Value.ToString();
si.SupplierName = row.Cells[4].Value.ToString();
si.Remark = row.Cells[5] == null ? "" : row.Cells[5].Value.ToString();
si.SupplierTypeID = spt.SupplierTypeID;
db.SubmitChanges();
}
}
return(strResult);
}
private string InsertData(Net.SourceForge.Koogra.Excel.Row row)
{
string strResult = "";
using (MMSProDBDataContext db = new MMSProDBDataContext(ConfigurationManager.ConnectionStrings["mmsConString"].ConnectionString))
{
//分段获取4种值,父类无值直接跳出
//获取主类
if (row.Cells[0] == null || row.Cells[1] == null)
{
return("主类信息不完整");
}
MaterialType Temp;
//检查大类是否存在
if (!db.MaterialType.Any(a => a.MaterialTypeCode == row.Cells[0].Value.ToString()))
{
Temp = new MaterialType();
//MT.MaterialMainTypeCode =
Temp.MaterialTypeCode = row.Cells[0].Value.ToString();
Temp.MaterialTypeName = row.Cells[1].Value.ToString();
db.MaterialType.InsertOnSubmit(Temp);
db.SubmitChanges();
}
else
{
Temp = db.MaterialType.SingleOrDefault(a => a.MaterialTypeCode == row.Cells[0].Value.ToString());
Temp.MaterialTypeCode = row.Cells[0].Value.ToString();
Temp.MaterialTypeName = row.Cells[1].Value.ToString();
db.SubmitChanges();
}
//获取大类
if (row.Cells[2] == null || row.Cells[3] == null)
{
return("大类信息不完整");
}
MaterialMainType MT;
//检查大类是否存在
if (!db.MaterialMainType.Any(a => a.MaterialMainTypeCode == row.Cells[2].Value.ToString()))
{
MT = new MaterialMainType();
//MT.MaterialMainTypeCode =
MT.MaterialMainTypeCode = row.Cells[2].Value.ToString();
MT.MaterialMainTypeName = row.Cells[3].Value.ToString();
MT.MaterialTypeID = Temp.MaterialTypeID;
db.MaterialMainType.InsertOnSubmit(MT);
db.SubmitChanges();
}
else
{
MT = db.MaterialMainType.SingleOrDefault(a => a.MaterialMainTypeCode == row.Cells[2].Value.ToString());
MT.MaterialMainTypeCode = row.Cells[2].Value.ToString();
MT.MaterialMainTypeName = row.Cells[3].Value.ToString();
MT.MaterialTypeID = Temp.MaterialTypeID;
db.SubmitChanges();
}
//获取中类
if (row.Cells[4] == null || row.Cells[5] == null)
{
return("");
}
MaterialChildType mct;
if (!db.MaterialChildType.Any(a => a.MaterialChildTypeCode == row.Cells[4].Value.ToString()))
{
mct = new MaterialChildType();
mct.MaterialChildTypeCode = row.Cells[4].Value.ToString();
mct.MaterialChildTypeName = row.Cells[5].Value.ToString();
mct.MaterialMainTypeID = MT.MaterialMainTypeID;
db.MaterialChildType.InsertOnSubmit(mct);
db.SubmitChanges();
}
else
{
mct = db.MaterialChildType.SingleOrDefault(a => a.MaterialChildTypeCode == row.Cells[4].Value.ToString());
mct.MaterialChildTypeCode = row.Cells[4].Value.ToString();
mct.MaterialChildTypeName = row.Cells[5].Value.ToString();
mct.MaterialMainTypeID = MT.MaterialMainTypeID;
db.SubmitChanges();
}
//获取小类
if (row.Cells[6] == null || row.Cells[7] == null)
{
return("");
}
MaterialInfo mi;
if (!db.MaterialInfo.Any(a => a.FinanceCode == row.Cells[6].Value.ToString()))
{
mi = new MaterialInfo();
mi.MaterialchildTypeID = mct.MaterialChildTypeID;
mi.FinanceCode = row.Cells[6].Value.ToString();
mi.MaterialName = row.Cells[7].Value.ToString();
mi.SpecificationModel = row.Cells[8].Value.ToString() ?? "";
mi.Remark = row.Cells[9].Value.ToString() ?? "";
db.MaterialInfo.InsertOnSubmit(mi);
db.SubmitChanges();
}
else
{
mi = db.MaterialInfo.SingleOrDefault(a => a.FinanceCode == row.Cells[6].Value.ToString());
mi.MaterialchildTypeID = mct.MaterialChildTypeID;
mi.FinanceCode = row.Cells[6].Value.ToString();
mi.MaterialName = row.Cells[7].Value.ToString();
mi.SpecificationModel = row.Cells[8].Value.ToString() ?? "";
mi.Remark = row.Cells[9].Value.ToString() ?? "";
db.SubmitChanges();
}
}
return(strResult);
}
private string InsertData(Net.SourceForge.Koogra.Excel.Row row)
{
string strResult = "";
using (MMSProDBDataContext db = new MMSProDBDataContext(ConfigurationManager.ConnectionStrings["mmsConString"].ConnectionString))
{
//检查仓库
if (row.Cells[0] == null || row.Cells[1] == null || row.Cells[2] == null)
{
return("仓库信息不完整");
}
//写入仓库信息
StorageInfo si;
int empid = 0;
//检查仓库是否存在
if (!db.StorageInfo.Any(a => a.StorageCode == row.Cells[0].Value.ToString()))
{
si = new StorageInfo();
//MT.MaterialMainTypeCode =
si.StorageCode = row.Cells[0].Value.ToString();
si.StorageName = row.Cells[1].Value.ToString();
si.Remark = row.Cells[3] == null ? "" : row.Cells[3].Value.ToString();
empid = funGetUserIDbyAcc(row.Cells[2].Value.ToString());
if (empid == 0)
{
throw new Exception();
}
si.EmpID = empid;
db.StorageInfo.InsertOnSubmit(si);
db.SubmitChanges();
}
else
{
si = db.StorageInfo.SingleOrDefault(a => a.StorageCode == row.Cells[0].Value.ToString());
si.StorageCode = row.Cells[0].Value.ToString();
si.StorageName = row.Cells[1].Value.ToString();
si.Remark = row.Cells[3] == null ? "" : row.Cells[3].Value.ToString();
empid = funGetUserIDbyAcc(row.Cells[2].Value.ToString());
if (empid == 0)
{
throw new Exception();
}
si.EmpID = empid;
db.SubmitChanges();
}
//***************************************************************************
//写入垛位信息
if (row.Cells[4] == null || row.Cells[5] == null)
{
return("垛位信息不完整");
}
//写入垛位信息
PileInfo pi;
//检查垛位是否存在
if (!db.PileInfo.Any(a => a.PileCode == row.Cells[4].Value.ToString()))
{
pi = new PileInfo();
//MT.MaterialMainTypeCode =
pi.PileCode = row.Cells[4].Value.ToString();
pi.PileName = row.Cells[5].Value.ToString();
pi.PileSize = row.Cells[6] == null ? "" : row.Cells[6].Value.ToString();
pi.Remark = row.Cells[7] == null ? "" : row.Cells[7].Value.ToString();
pi.StorageID = si.StorageID;
db.PileInfo.InsertOnSubmit(pi);
db.SubmitChanges();
}
else
{
pi = db.PileInfo.SingleOrDefault(a => a.PileCode == row.Cells[4].Value.ToString());
pi.PileCode = row.Cells[4].Value.ToString();
pi.PileName = row.Cells[5].Value.ToString();
pi.PileSize = row.Cells[6] == null ? "" : row.Cells[6].Value.ToString();
pi.Remark = row.Cells[7] == null ? "" : row.Cells[7].Value.ToString();
pi.StorageID = si.StorageID;
db.SubmitChanges();
}
}
return(strResult);
}