private void ImportData(Worksheet Sht)
{
UnitOfWork uow = new UnitOfWork();
Item item;
ItemCategory itemCategory;
PriceCategory priceCategory;
String itemNo;
bool isAdmin = SecurityHelper.IsAdministrator();
int row = 1;
uow.BeginTransaction();
while (Sht.Cells[row, COL_ITEMNO].Value.ToString() != "")
{
StringBuilder sbError = new StringBuilder();
itemNo = Sht.Cells[row, COL_ITEMNO].Value.ToString();
item = Item.FindItem(uow, itemNo);
if (item != null)
{
bool UpdateTimeStamp;
if (isAdmin || item.ConfirmedBy != "")
UpdateTimeStamp = false;
else
UpdateTimeStamp = true;
if (Sht.Cells[row, COL_ITEMNAME].Value.ToString() != "")
item.ItemName = Sht.Cells[row, COL_ITEMNAME].Value.ToString();
if (Sht.Cells[row, COL_ITEMTYPE].Value.ToString() != "")
{
ItemType itemType = ItemType.FindItemType(uow, Sht.Cells[row, COL_ITEMTYPE].Value.ToString());
if (itemType != null)
item.ItemType = itemType;
else
sbError.AppendLine(string.Format("找不到此产品种类 {0}", Sht.Cells[row, COL_ITEMTYPE].Value.ToString()));
}
if (Sht.Cells[row, COL_MATERIAL].Value.ToString() != "")
{
Material material = Material.FindMaterial(uow, Sht.Cells[row, COL_MATERIAL].Value.ToString());
if (material != null)
item.Material = material;
else
sbError.AppendLine(string.Format("找不到此材料 {0}", Sht.Cells[row, COL_MATERIAL].Value.ToString()));
}
if (Sht.Cells[row, COL_ITEMCAT].Value.ToString() != "")
{
itemCategory = ItemCategory.FindItemCategory(uow, Sht.Cells[row, COL_ITEMCAT].Value.ToString());
if (itemCategory != null)
item.ItemCategory = itemCategory;
else
sbError.AppendLine(string.Format("找不到此产品类别 {0}", Sht.Cells[row, COL_ITEMCAT].Value.ToString()));
}
if (Sht.Cells[row, COL_PRICECAT].Value.ToString() != "")
{
priceCategory = PriceCategory.FindPriceCategory(uow, Sht.Cells[row, COL_PRICECAT].Value.ToString());
if (priceCategory != null)
item.PriceCategory = priceCategory;
else
{
sbError.AppendLine(string.Format("找不到此价钱类别 {0}", Sht.Cells[row, COL_PRICECAT].Value.ToString()));
}
}
if (Sht.Cells[row, COL_SALESPRICE].Value.ToString() != "")
item.SalesPrice = float.Parse(Sht.Cells[row, COL_SALESPRICE].Value.ToString());
if (Sht.Cells[row, COL_LEADTIME].Value.ToString() != "")
item.LeadTime = int.Parse(Sht.Cells[row, COL_LEADTIME].Value.ToString());
if (Sht.Cells[row, COL_SUPPLIER].Value.ToString() != "")
{
PriceDisc pDisc = PriceDisc.Find(uow, item.ItemNo, Sht.Cells[row, COL_SUPPLIER].Value.ToString(), PriceDisc.PriceType.Purch);
if (pDisc != null)
{
pDisc.Price = float.Parse(Sht.Cells[row, COL_PURCHPRICE].Value.ToString());
pDisc.updateTimeStamp = UpdateTimeStamp;
pDisc.Save();
}
else
{
pDisc = new PriceDisc(uow);
pDisc.Item = item;
pDisc.Price = float.Parse(Sht.Cells[row, COL_PURCHPRICE].Value.ToString());;
pDisc.PriceUnit = item.Unit;
pDisc.Type = PriceDisc.PriceType.Purch;
pDisc.ExternalContact = Vendor.FindVendor(uow, Sht.Cells[row, COL_SUPPLIER].Value.ToString());
pDisc.updateTimeStamp = UpdateTimeStamp;
pDisc.Save();
}
}
if (Sht.Cells[row, COL_MOQ].Value.ToString() != "")
item.MinOrderQty = float.Parse(Sht.Cells[row, COL_MOQ].Value.ToString());
if (Sht.Cells[row, COL_PPAP].Value.ToString() != "")
{
int PPAPStatus = int.Parse(Sht.Cells[row, COL_PPAP].Value.ToString());
switch (PPAPStatus)
{
case 1:
item.PPAPSampleStatus = Item.SampleStatus.Waiting;
break;
case 2:
item.PPAPSampleStatus = Item.SampleStatus.Submitted;
break;
case 3:
item.PPAPSampleStatus = Item.SampleStatus.Confirm;
break;
default:
break;
}
}
if (Sht.Cells[row, COL_NRI].Value.ToString() != "")
{
string QCStatus = Sht.Cells[row, COL_NRI].Value.ToString();
switch (QCStatus)
{
case "None":
item.QCStatus = Item.ItemQCStatus.None;
break;
case "PRRI":
item.QCStatus = Item.ItemQCStatus.PRRI;
break;
case "NRI":
item.QCStatus = Item.ItemQCStatus.NRI;
break;
}
}
if (Sht.Cells[row, COL_SHIPMETHOD].Value.ToString() != "")
{
item.ShipMethod = Sht.Cells[row, COL_SHIPMETHOD].Value.ToString();
}
item.IsConfirming = !UpdateTimeStamp;
item.updateTimeStamp = UpdateTimeStamp;
item.Save();
if (sbError.ToString() == "")
Sht.Cells[row, COL_REMARK].Value = "ok";
else
Sht.Cells[row, COL_REMARK].Value = sbError.ToString();
}
else
{
Sht.Cells[row, COL_REMARK].Value = "找不到此编码";
}
row++;
}
uow.CommitTransaction();
}
public Item CreateItemFromOutlookSystem(string itemNo, UnitOfWork uow, bool isOverride)
{
Item item;
item = Item.FindItem(uow, itemNo);
if (isOverride == false && item != null)
return item;
cnn = new ADODB.Connection();
rst = new ADODB.Record();
ConnectDB(cnn);
rst.Open(string.Format("{0}{1}.eml", partPath, itemNo), cnn, ADODB.ConnectModeEnum.adModeReadWrite, ADODB.RecordCreateOptionsEnum.adOpenIfExists, ADODB.RecordOpenOptionsEnum.adDelayFetchFields, "namwah", "ParaW0rld");
if (GetStringField(rst.Fields["nw:partno"]) == "")
{
if (isOverride == false)
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("在大货系统找不到此编码 : {0}", itemNo));
rst.Fields["urn:schemas:httpmail:subject"].Value = itemNo;
rst.Fields.Update();
rst.DeleteRecord(string.Format("{0}{1}.eml", partPath, itemNo), false);
return null;
}
try
{
if (item == null)
item = new Item(uow);
item.ItemNo = itemNo;
item.ItemName = GetStringField(rst.Fields["nw:partname"]);
item.ItemType = ItemType.FindItemType(uow, GetStringField(rst.Fields["nw:parttype"]));
item.CustomerItemNo = GetStringField(rst.Fields["nw:part:tmxrefno"]);
item.ItemDescription = GetStringField(rst.Fields["nw:description"]);
item.MinOrderQty = GetIntField(rst.Fields["nw:minorderqty"]);
item.DrawingNo = GetStringField(rst.Fields["nw:drawingno"]);
item.LeadTime = GetIntField(rst.Fields["nw:part:leadtime"]);
item.SalesPrice = GetFloatField(rst.Fields["nw:strprice"]);
item.Size = GetStringField(rst.Fields["nw:part:size"]);
if (item.ItemType.ItemTypeName == "Bar")
item.NetWeight = GetFloatField(rst.Fields["nw:part:chr1"]);
if (item.ItemType.ItemTypeName == "CaseBack")
{
item.OutsideMark = GetStringField(rst.Fields["nw:part:chr1"]);
item.QtyPerKg = GetFloatField(rst.Fields["nw:part:chr2"]);
item.InsideMark = GetStringField(rst.Fields["nw:part:chr6"]);
}
if (GetStringField(rst.Fields["nw:part:source"]) == "Buy")
item.Source = Item.ItemSource.Buy;
else
item.Source = Item.ItemSource.Make;
if (GetStringField(rst.Fields["nw:part:incharge"]) == "HK")
item.InCharge = Item.ItemInCharge.HK;
else
item.InCharge = Item.ItemInCharge.SZ;
item.PriceCategory = PriceCategory.FindPriceCategory(uow, GetStringField(rst.Fields["nw:part:pricecat"]));
item.ItemCategory = ItemCategory.FindItemCategory(uow, GetStringField(rst.Fields["nw:part:category"]));
item.Material = Material.FindMaterial(uow, GetStringField(rst.Fields["nw:material"]));
item.Warehouse = WareHouse.Find(uow, "SZ1良品仓");
item.Unit = Unit.FindUnit(uow, "PCS");
item.RemarkToSupplier = GetStringField(rst.Fields["nw:part:supplier:detail"]);
#region Save PriceDisc
if (item.PriceDiscs.Count > 0)
{
for (int i = 0; i < item.PriceDiscs.Count; i++)
{
if (item.PriceDiscs[i].Type == PriceDisc.PriceType.Purch)
{
item.PriceDiscs[i].Delete();
}
i--;
}
}
string[] supplierRefNo = GetStringField(rst.Fields["nw:part:supplier:refno"]).Split(',');
string[] supplierList = GetStringField(rst.Fields["nw:supplierlist"]).Split(',');
string[] supplierCost = GetStringField(rst.Fields["nw:part:cost"]).Split('-');
for (int i = 0; i < supplierList.Length; i++)
{
if (supplierList[i].Trim() != "")
{
PriceDisc pDisc = new PriceDisc(uow);
pDisc.Index = i;
pDisc.Item = item;
pDisc.Type = PriceDisc.PriceType.Purch;
pDisc.ExternalContact = Vendor.FindVendor(uow, supplierList[i]);
pDisc.ExternalName = "";
pDisc.ExternalNo = supplierRefNo[i];
if (supplierCost[i] == "")
pDisc.Price = 0;
else
pDisc.Price = float.Parse(supplierCost[i]);
pDisc.PriceQty = 1;
pDisc.PriceUnit = Unit.FindUnit(uow, "PCS");
pDisc.Save();
if (i == 0)
item.MainVendor = (Vendor)pDisc.ExternalContact;
}
}
#endregion
#region Clear BOM & Route
if (item.BomLines.Count > 0)
{
for (int i = 0; i < item.BomLines.Count; i++)
{
item.BomLines[i].Delete();
i--;
}
}
for (int i = 0; i < item.RouteLines.Count; i++)
{
item.RouteLines[i].Delete();
i--;
}
#endregion
#region Bom Line
if (GetStringField(rst.Fields["nw:bom:list:partno"]) != "")
{
string[] bPartNo = GetStringField(rst.Fields["nw:bom:list:partno"]).Split(',');
string[] bQty = GetStringField(rst.Fields["nw:bom:list:qty"]).Split(',');
for (int i = 0; i < bPartNo.Length; i++)
{
OutlookSystemItem osItem = new OutlookSystemItem();
item.Save();
uow.CommitChanges();
Item bomItem = osItem.CreateItemFromOutlookSystem(bPartNo[i], uow, isOverride);
if (isOverride == false && bomItem == null)
{
item.UnconfirmItem();
item.Save();
uow.CommitChanges();
return null;
}
CreateBomLine(item, bomItem, uow, int.Parse(bQty[i]));
}
CreateRouteLine(item, 10, 0, "A", "A", "", "", uow);
}
#endregion
#region Route
if (GetStringField(rst.Fields["nw:rout1:resourceno"]) != "bom" &&
GetStringField(rst.Fields["nw:rout1:resourceno"]) != "")
{
OutlookSystemItem osWipItem = new OutlookSystemItem();
item.Save();
uow.CommitChanges();
Item wipItem = osWipItem.CreateItemFromOutlookSystem(GetStringField(rst.Fields["nw:rout1:resourceno"]), uow, isOverride);
if (wipItem == null && isOverride == false)
{
item.UnconfirmItem();
item.Save();
uow.CommitChanges();
return null;
}
if (GetBoolField(rst.Fields["nw:rout1:resoption"]) == false)
{
// 只有一个流程 (非 电镀)
CreateRouteLine(item, 10, 0, GetStringField(rst.Fields["nw:rout1:type"]), GetStringField(rst.Fields["nw:rout1:type"]),
GetStringField(rst.Fields["nw:plating:plater"]), GetStringField(rst.Fields["nw:plating"]), uow);
int routeQty = 1;
if (GetStringField(rst.Fields["nw:rout1:type"]) == "M")
{
routeQty = GetIntField(rst.Fields["nw:part:chr1"]);
if (routeQty == 0)
routeQty = 1;
}
CreateBomLine(item, wipItem, uow, routeQty);
}
else
{
// 相同加工部门
// P Route
RouteLine rLine = CreateRouteLine(item, 20, 0, GetStringField(rst.Fields["nw:rout1:type"]), GetStringField(rst.Fields["nw:rout1:type"]),
GetStringField(rst.Fields["nw:plating:plater"]), GetStringField(rst.Fields["nw:plating"]), uow);
rLine.Save();
// M Route
RouteLine wipRouteLine = wipItem.GetFirstRouteLine();
if (wipRouteLine == null)
System.Diagnostics.Debug.Print("No WIP RouteLine");
else
{
RouteLine rLine2 = CreateRouteLine(item, 10, 20, wipRouteLine.WorkOper.WorkOperNo, wipRouteLine.WorkCenter.WorkCenterNo, "", "", uow);
rLine2.wipItem = wipItem;
rLine2.Save();
}
CreateBomLine(item, wipItem.BomLines[0].BomItem, uow, 1);
}
}
#endregion
if (item.Source == Item.ItemSource.Make && item.RouteLines.Count == 0)
{
if (item.PriceDiscs.Count > 0)
item.Source = Item.ItemSource.Buy;
else
item.Source = Item.ItemSource.None;
}
if (item.Source == Item.ItemSource.Buy && item.PriceDiscs.Count == 0)
{
if (item.RouteLines.Count > 0)
item.Source = Item.ItemSource.Make;
else
item.Source = Item.ItemSource.None;
}
item.ConfirmItem();
item.Save();
uow.CommitChanges();
}
catch (Exception ex)
{
DevExpress.XtraEditors.XtraMessageBox.Show(string.Format("产品 {0} 出现错误, 不能取出产品资料 \n {1}", itemNo, ex.Message));
throw new Exception("不能成功取出产品资料");
}
return item;
}