private void btnSO_Click(object sender, EventArgs e)
{
if (InitExcel("Sales Order") == false)
return;
ImportSalesOrder();
UnitOfWork uow = new UnitOfWork();
SalesOrderLine soLine;
int row = 3;
string woNo = "";
uow.BeginTransaction();
while (ExcelHelper.GetCellStringValue(xlSht, row, 1) != "")
{
soLine = new SalesOrderLine(uow);
soLine.SalesOrder = uow.FindObject<SalesOrder>(new BinaryOperator("OrderNo", ExcelHelper.GetCellStringValue(xlSht, row, 1)));
soLine.OrderType = uow.FindObject<OrderType>(new BinaryOperator("Name", ExcelHelper.GetCellStringValue(xlSht, row, 3)));
soLine.PoDate = ExcelHelper.GetCellDateTimeValue(xlSht, row, 4);
soLine.Item = Item.FindItem(uow, ExcelHelper.GetCellStringValue(xlSht, row, 5));
soLine.CustomerOrderNo = ExcelHelper.GetCellStringValue(xlSht, row, 6);
soLine.CustomerItemNo = ExcelHelper.GetCellStringValue(xlSht, row, 7);
soLine.CustomerItemName = ExcelHelper.GetCellStringValue(xlSht, row, 8);
soLine.NeedDate = ExcelHelper.GetCellDateTimeValue(xlSht, row, 9);
soLine.NeedQty = ExcelHelper.GetCellIntValue(xlSht, row, 10);
soLine.Price = ExcelHelper.GetCellFloatValue(xlSht, row, 11);
soLine.WareHouse = uow.FindObject<WareHouse>(new BinaryOperator("WarehouseName", ExcelHelper.GetCellStringValue(xlSht, row,12)));
soLine.ShipMethod = uow.FindObject<ShipMethod>( new BinaryOperator("ShipMethodName", ExcelHelper.GetCellStringValue(xlSht, row, 13)));
soLine.PriceUnit = uow.FindObject<Unit>(new BinaryOperator("UnitName", ExcelHelper.GetCellStringValue(xlSht, row, 14)));
woNo = ExcelHelper.GetCellStringValue(xlSht, row, 15);
if (woNo != "")
{
soLine.WorkOrder = WorkOrder.Find(uow, woNo);
}
soLine.Save();
soLine.ApprovedSalesOrderLine();
row++;
}
uow.CommitTransaction();
ReleaseExcel();
}
public void ImportSSTK()
{
string soPath = "http://nwszmail/public/namwah/Parts2/SafetyStock/";
string strSQL = "";
Dictionary<string, SalesOrder> dictSo = new Dictionary<string, SalesOrder>();
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rst = new ADODB.Recordset();
Item item;
string partNo;
string soLineIndex;
CacheItem();
ConnectDB(cnn, soPath);
strSQL = "SELECT \"nw:cpo:item:initneeddate\", \"nw:cpo:date\" ";
strSQL = strSQL + " , \"nw:cpo:item:needqty\", \"nw:cpo:item:needdate\", \"nw:cpo:item:priority\", \"nw:part:sstqty\" ";
strSQL = strSQL + " , \"nw:cpo:no\", \"nw:part:tmxrefno\" ";
strSQL = strSQL + " FROM \"" + soPath;
strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false )";
rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);
session.BeginTransaction();
XPCollection<SalesOrderLine> soLines = new XPCollection<SalesOrderLine>(session);
soLines.Criteria = CriteriaOperator.Parse("SalesOrder.OrderType == SalesOrder.SalesOrderType.SSTK");
session.BeginTransaction();
session.Delete(soLines);
session.CommitTransaction();
DateTime sTime = DateTime.Now;
string poNo;
while (!rst.EOF)
{
partNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
soLineIndex = rst.Fields["nw:cpo:no"].Value.ToString() + "-" + rst.Fields["nw:part:tmxrefno"].Value.ToString();
poNo = rst.Fields["nw:cpo:no"].Value.ToString();
if (dictItem.ContainsKey(partNo))
{
item = dictItem[partNo];
SalesOrder so;
if (dictSo.ContainsKey(rst.Fields["nw:cpo:no"].Value.ToString()))
{
so = dictSo[rst.Fields["nw:cpo:no"].Value.ToString()];
}
else
{
so = session.FindObject<SalesOrder>(new BinaryOperator("OrderNo", rst.Fields["nw:cpo:no"].Value.ToString()));
if (so == null)
{
so = new SalesOrder(session);
so.SetOrderNo(rst.Fields["nw:cpo:no"].Value.ToString());
so.OrderType = SalesOrder.SalesOrderType.SSTK;
so.Customer = GetCustomer("TIMEX");
so.Save();
}
dictSo.Add(rst.Fields["nw:cpo:no"].Value.ToString(), so);
}
SalesOrderLine soLine = new SalesOrderLine(session);
soLine.SalesOrder = so;
soLine.Item = item;
so.AddLineNo();
soLine.SetOrderLineNo(so.LastLineNo);
soLine.CustomerItemNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
soLine.CustomerOrderNo = rst.Fields["nw:cpo:no"].Value.ToString();
//soLine.InitNeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:initneeddate"].Value.ToString());
soLine.NeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:needdate"].Value.ToString());
soLine.NeedQty = float.Parse(rst.Fields["nw:part:sstqty"].Value.ToString());
soLine.PoDate = DateTime.Parse(rst.Fields["nw:cpo:date"].Value.ToString());
soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
soLine.ApprovedSalesOrderLine();
soLine.Save();
}
rst.MoveNext();
}
System.Diagnostics.Debug.WriteLine("Start Time : " + sTime.ToString() + ". End Time : " + DateTime.Now.ToString());
session.CommitTransaction();
System.Diagnostics.Debug.WriteLine("Commit Time : " + DateTime.Now.ToString());
}
public static SalesOrderLine Create(SalesOrder order, WorkOrder wo, string remark, Session session)
{
SalesOrderLine orderLine = new SalesOrderLine(session);
orderLine.SalesOrder = order;
orderLine.Item = wo.Item;
if (wo.Item.CustomerItemNo == "")
orderLine.CustomerItemNo = wo.Item.ItemNo;
else
orderLine.CustomerItemNo = wo.Item.CustomerItemNo;
orderLine.CustomerItemName = wo.Item.ItemName;
orderLine.CustomerPrice = wo.Item.SalesPrice;
orderLine.OrderType = wo.OrderType;
orderLine.PriceUnit = order.Customer.Currency;
orderLine.InitNeedDate = wo.NeedDate;
orderLine.NeedDate = wo.NeedDate;
orderLine.NeedQty = wo.NeedQty;
orderLine.WorkOrder = wo;
orderLine.Save();
wo.SalesOrderLine = orderLine;
wo.Save();
return orderLine;
}
public void ImportSalesOrder()
{
string soPath = "http://nwszmail/public/namwah/Sales/Timex/PurchaseOrders/";
string strSQL = "";
Dictionary<string, SalesOrder> dictSo = new Dictionary<string, SalesOrder>();
ADODB.Connection cnn = new ADODB.Connection();
ADODB.Recordset rst = new ADODB.Recordset();
Item item;
/*
XPCollection<SalesOrderLine> soLines = new XPCollection<SalesOrderLine>(session);
session.BeginTransaction();
session.Delete(soLines);
session.CommitTransaction();
return;
*/
string partNo;
string soLineIndex;
CacheItem();
CacheSalesOrderLine(Namwah.Module.BO.SalesOrder.SalesOrderType.Normal);
ConnectDB(cnn, soPath);
strSQL = "SELECT \"nw:partno\" , \"nw:cpo:item:ignore\", \"nw:cpo:item:index\", \"nw:cpo:item:initneeddate\", \"nw:cpo:date\" ";
strSQL = strSQL + " , \"nw:cpo:item:needqty\", \"nw:cpo:item:needdate\", \"nw:cpo:item:priority\", \"nw:cpo:item:shippedqty\" ";
strSQL = strSQL + " , \"nw:cpo:item:remark\", \"nw:cpo:no\", \"nw:customer\", \"nw:part:tmxrefno\" ";
strSQL = strSQL + " FROM \"" + soPath;
strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false AND \"nw:customer\" = 'Timex' )";
return ;
rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);
session.BeginTransaction();
DateTime sTime = DateTime.Now;
string poNo;
while (!rst.EOF)
{
partNo = rst.Fields["nw:partno"].Value.ToString();
soLineIndex = rst.Fields["nw:cpo:no"].Value.ToString() + "-" + rst.Fields["nw:cpo:item:index"].Value.ToString();
poNo = rst.Fields["nw:cpo:no"].Value.ToString();
if (poNo.StartsWith("PoHistory") == false)
{
if (dictItem.ContainsKey(partNo))
{
if (dictSalesOrderLine.ContainsKey(soLineIndex))
{
SalesOrderLine soLine = dictSalesOrderLine[soLineIndex];
soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
soLine.SetShipQty(float.Parse(rst.Fields["nw:cpo:item:shippedqty"].Value.ToString()));
soLine.Save();
}
else
{
item = dictItem[partNo];
SalesOrder so;
if (dictSo.ContainsKey(rst.Fields["nw:cpo:no"].Value.ToString()))
{
so = dictSo[rst.Fields["nw:cpo:no"].Value.ToString()];
}
else
{
so = session.FindObject<SalesOrder>(new BinaryOperator("OrderNo", rst.Fields["nw:cpo:no"].Value.ToString()));
if (so == null)
{
so = new SalesOrder(session);
so.SetOrderNo(rst.Fields["nw:cpo:no"].Value.ToString());
so.Customer = GetCustomer(rst.Fields["nw:customer"].Value.ToString());
so.Save();
}
dictSo.Add(rst.Fields["nw:cpo:no"].Value.ToString(), so);
}
SalesOrderLine soLine = new SalesOrderLine(session);
soLine.SalesOrder = so;
soLine.Item = item;
soLine.SetOrderLineNo(int.Parse(rst.Fields["nw:cpo:item:index"].Value.ToString()));
soLine.CustomerItemNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
soLine.CustomerOrderNo = rst.Fields["nw:cpo:no"].Value.ToString();
if (rst.Fields["nw:cpo:item:ignore"].Value.ToString() != "")
soLine.IgnorePlanningReport = bool.Parse(rst.Fields["nw:cpo:item:ignore"].Value.ToString());
if (rst.Fields["nw:cpo:item:initneeddate"].Value.ToString() != "")
soLine.InitNeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:initneeddate"].Value.ToString());
soLine.NeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:needdate"].Value.ToString());
soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
soLine.PoDate = DateTime.Parse(rst.Fields["nw:cpo:date"].Value.ToString());
soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
soLine.SetShipQty(float.Parse(rst.Fields["nw:cpo:item:shippedqty"].Value.ToString()));
soLine.ApprovedSalesOrderLine();
soLine.Save();
}
}
}
rst.MoveNext();
}
System.Diagnostics.Debug.WriteLine("Start Time : " + sTime.ToString() + ". End Time : " + DateTime.Now.ToString());
session.CommitTransaction();
System.Diagnostics.Debug.WriteLine("Commit Time : " + DateTime.Now.ToString());
}
public static SalesOrderLine Create(SalesOrder sOrder, string cItemNo, string cItemName,
float price, float needQty, DateTime needDate, string remark,
ref string errMsg, Session session)
{
Item item = session.FindObject<Item>(new BinaryOperator("CustomerItemNo", cItemNo));
errMsg = "";
if (item == null)
{
errMsg = string.Format("找不到这产品资料, 人客编码 {0}", cItemNo);
return null;
}
try
{
SalesOrderLine orderLine = new SalesOrderLine(session);
orderLine.SalesOrder = sOrder;
orderLine.Item = item;
orderLine.CustomerItemNo = cItemNo;
orderLine.CustomerItemName = cItemName;
orderLine.CustomerPrice = price;
orderLine.CustomerOrderNo = sOrder.OrderNo;
orderLine.InitNeedDate = needDate;
orderLine.NeedDate = needDate;
orderLine.NeedQty = needQty;
orderLine.Remark = remark;
orderLine.Save();
return orderLine;
}
catch (Exception ex)
{
errMsg = ex.Message;
return null;
}
}
