private void createSPProductionOrder() { DateTime dt = Convert.ToDateTime(dtHead.GetValue("DocDate", 0)); for (int i = 0; i < Animals.Rows.Count; i++) { double AddQty = Math.Round(Convert.ToDouble(Animals.GetValue("Forecast", i)) - Convert.ToDouble(Animals.GetValue("QtySche", i)), 3); if (AddQty > 0) { SAPbobsCOM.ProductionOrders prodOrder = (SAPbobsCOM.ProductionOrders)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); prodOrder.ItemNo = Animals.GetValue("ItemCode", i).ToString(); double Yield = Convert.ToDouble(Program.objHrmsUI.getScallerValue("Select isnull(U_Yield,0) from oitt where code = '" + prodOrder.ItemNo + "'")); if (Yield == 0) { Yield = 100; } string strSqlDfltPL = "Select isnull(U_B1_dfltPL,'') PL from OITT where Code='" + prodOrder.ItemNo + "'"; string strDfltPL = Convert.ToString(Program.objHrmsUI.getScallerValue(strSqlDfltPL)); prodOrder.ProductionOrderType = SAPbobsCOM.BoProductionOrderTypeEnum.bopotDisassembly; prodOrder.PlannedQuantity = AddQty; prodOrder.DueDate = dt.Date; prodOrder.PostingDate = dt.Date; prodOrder.UserFields.Fields.Item("U_B1_Seq").Value = "1"; if (strDfltPL != "") { prodOrder.UserFields.Fields.Item("U_PMX_PLCD").Value = strDfltPL; } int result = prodOrder.Add(); if (result != 0) { int errorCode = 0; string errmsg = ""; oCompany.GetLastError(out errorCode, out errmsg); oApplication.MessageBox(errmsg); } else { int newWorEntry = Convert.ToInt32(oCompany.GetNewObjectKey()); prodOrder.GetByKey(newWorEntry); for (int p = 0; p < prodOrder.Lines.Count; p++) { prodOrder.Lines.SetCurrentLine(p); prodOrder.Lines.PlannedQuantity = prodOrder.Lines.PlannedQuantity * Yield / 100; } // prodOrder.ProductionOrderStatus = SAPbobsCOM.BoProductionOrderStatusEnum.boposReleased; prodOrder.Update(); } } } }
private void changeStatus() { string selButton = btAct.Selected.Value; string confirmMessage = "Are you sure you want to release selected production orders? "; if (selButton == "02") { confirmMessage = "Are you sure you want to cancel selected production orders? "; } int confirmresult = oApplication.MessageBox(confirmMessage, 1, "Yes", "No"); if (confirmresult == 1) { SAPbobsCOM.ProductionOrders prodOrder = (SAPbobsCOM.ProductionOrders)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); int selRowInd = 0; for (int i = 1; i <= mtReport.RowCount; i++) { if (mtReport.IsRowSelected(i)) { selRowInd = i - 1; string DocEntry = Convert.ToString(dtRpt.GetValue("DocEntry", selRowInd)); prodOrder.GetByKey(Convert.ToInt32(DocEntry)); if (selButton == "01") { prodOrder.ProductionOrderStatus = SAPbobsCOM.BoProductionOrderStatusEnum.boposReleased; } else { prodOrder.ProductionOrderStatus = SAPbobsCOM.BoProductionOrderStatusEnum.boposCancelled; } int result = prodOrder.Update(); if (result != 0) { int errorCode = 0; string errmsg = ""; oCompany.GetLastError(out errorCode, out errmsg); oApplication.MessageBox(errmsg); } else { } } } } }
public override void etAfterCmbSelect(ref SAPbouiCOM.ItemEvent pVal, ref bool BubbleEvent) { base.etAfterCmbSelect(ref pVal, ref BubbleEvent); if (pVal.ItemUID == cbPL.Item.UniqueID && !initiallizing) { fillReport(); } if (pVal.ItemUID == btAct.Item.UniqueID) { changeStatus(); fillReport(); btAct.Caption = "Change To"; } if (pVal.ItemUID == btPL.Item.UniqueID) { assignPL(); fillReport(); btPL.Caption = "Assign to Production Line"; } if (pVal.ItemUID == mtReport.Item.UniqueID) { if (pVal.ColUID == "cProdLine" || pVal.ColUID == "cLabel" || pVal.ColUID == "cFT") { mtReport.FlushToDataSource(); int selRowIndex = pVal.Row - 1; if (selRowIndex >= 0) { string DocEntry = Convert.ToString(dtRpt.GetValue("DocEntry", selRowIndex)); string ProductionLine = Convert.ToString(dtRpt.GetValue("ProdLine", selRowIndex)); string Label = Convert.ToString(dtRpt.GetValue("Label", selRowIndex)); SAPbobsCOM.ProductionOrders prodOrder = (SAPbobsCOM.ProductionOrders)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); prodOrder.GetByKey(Convert.ToInt32(DocEntry)); prodOrder.UserFields.Fields.Item("U_PMX_PLCD").Value = ProductionLine; prodOrder.UserFields.Fields.Item("U_B1_Label").Value = Label; int result = prodOrder.Update(); if (result != 0) { int errorCode = 0; string errmsg = ""; oCompany.GetLastError(out errorCode, out errmsg); oApplication.MessageBox(errmsg); } } } } }
public override void etAfterValidate(ref SAPbouiCOM.ItemEvent pVal, ref bool BubbleEvent) { base.etAfterValidate(ref pVal, ref BubbleEvent); if (pVal.ItemUID == mtReport.Item.UniqueID) { if (pVal.ColUID == "cFT") { mtReport.FlushToDataSource(); int selRowIndex = pVal.Row - 1; if (selRowIndex >= 0) { string DocEntry = Convert.ToString(dtRpt.GetValue("DocEntry", selRowIndex)); string Seq = Convert.ToString(dtRpt.GetValue("Seq", selRowIndex)); string FreeText = Convert.ToString(dtRpt.GetValue("FreeText", selRowIndex)); string prodLine = Convert.ToString(dtRpt.GetValue("ProdLine", selRowIndex)); if (pVal.ColUID == "cFT") { SAPbobsCOM.ProductionOrders prodOrder = (SAPbobsCOM.ProductionOrders)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); prodOrder.GetByKey(Convert.ToInt32(DocEntry)); prodOrder.UserFields.Fields.Item("U_B1_FrTxt").Value = FreeText; int result = prodOrder.Update(); if (result != 0) { int errorCode = 0; string errmsg = ""; oCompany.GetLastError(out errorCode, out errmsg); oApplication.MessageBox(errmsg); } else { } } else { // UpdateSeq(Convert.ToInt32(DocEntry), Convert.ToInt32(Seq), prodLine); } } } } }
private void createTypeProductionOrder() { oApplication.SetStatusBarMessage("Creating Main Type Production order of qty ", SAPbouiCOM.BoMessageTime.bmt_Short, false); DateTime dt = Convert.ToDateTime(dtHead.GetValue("DocDate", 0)); double AddQty = 0.00; AddQty = Math.Round(Convert.ToDouble(dtHead.GetValue("TodayQty", 0))); oApplication.SetStatusBarMessage("Creating Main Item Production order of qty " + AddQty.ToString(), SAPbouiCOM.BoMessageTime.bmt_Short, false); if (AddQty > 0) { SAPbobsCOM.ProductionOrders prodOrder = (SAPbobsCOM.ProductionOrders)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); prodOrder.ItemNo = cbAT.Selected.Value.ToString().Trim(); double Yield = Convert.ToDouble(Program.objHrmsUI.getScallerValue("Select isnull(U_Yield,0) from oitt where code = '" + prodOrder.ItemNo + "'")); if (Yield == 0) { Yield = 100; } string strSqlDfltPL = "Select isnull(U_B1_dfltPL,'') PL from OITT where Code='" + prodOrder.ItemNo + "'"; string strDfltPL = Convert.ToString(Program.objHrmsUI.getScallerValue(strSqlDfltPL)); prodOrder.ProductionOrderType = SAPbobsCOM.BoProductionOrderTypeEnum.bopotDisassembly; prodOrder.PlannedQuantity = AddQty; prodOrder.DueDate = dt.Date; prodOrder.PostingDate = dt.Date; prodOrder.UserFields.Fields.Item("U_B1_Seq").Value = "1"; prodOrder.UserFields.Fields.Item("U_PONum").Value = newPOEntry.ToString(); if (strDfltPL != "") { prodOrder.UserFields.Fields.Item("U_PMX_PLCD").Value = strDfltPL; string nextSeq = " Select max( isnull(convert(int,U_B1_SEQ),0)) + 1 from owor where Status<>'L' and ISNUMERIC (U_B1_SEQ) =1 and U_PMX_PLCD = '" + strDfltPL + "' "; string seq = Convert.ToString(Program.objHrmsUI.getScallerValue(nextSeq)); prodOrder.UserFields.Fields.Item("U_B1_Seq").Value = seq; } int result = prodOrder.Add(); if (result != 0) { int errorCode = 0; string errmsg = ""; oCompany.GetLastError(out errorCode, out errmsg); oApplication.MessageBox(errmsg); } else { int newWorEntry = Convert.ToInt32(oCompany.GetNewObjectKey()); //prodOrder.GetByKey(newWorEntry); //for (int p = 0; p < prodOrder.Lines.Count; p++) //{ // prodOrder.Lines.SetCurrentLine(p); // prodOrder.Lines.PlannedQuantity = prodOrder.Lines.PlannedQuantity * Yield / 100; //} //// prodOrder.ProductionOrderStatus = SAPbobsCOM.BoProductionOrderStatusEnum.boposReleased; //prodOrder.Update(); } } }
private void assignPL() { string selPL = btPL.Selected.Value; string selPLName = btPL.Selected.Description; string confirmMessage = "Are you sure you want selected production orders assign to " + selPLName; int confirmresult = oApplication.MessageBox(confirmMessage, 1, "Yes", "No"); if (confirmresult == 1) { SAPbobsCOM.ProductionOrders prodOrder = (SAPbobsCOM.ProductionOrders)oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); int selRowInd = 0; string posLast = "Y"; if (opFirst.Selected) { posLast = "N"; } string maxSeqNum = Program.objHrmsUI.getScallerValue("Select isnull( max( convert(int, U_B1_Seq)),0) from owor where U_PMX_PLCD='" + selPL + "' and isnull(owor.Status,'')<>'C' "); if (maxSeqNum == "") { maxSeqNum = "0"; } int maxSeqNumVal = Convert.ToInt32(maxSeqNum); for (int i = 1; i <= mtReport.RowCount; i++) { if (mtReport.IsRowSelected(i)) { maxSeqNumVal++; selRowInd = i - 1; string DocEntry = Convert.ToString(dtRpt.GetValue("DocEntry", selRowInd)); prodOrder.GetByKey(Convert.ToInt32(DocEntry)); prodOrder.UserFields.Fields.Item("U_PMX_PLCD").Value = selPL; if (posLast == "Y") { prodOrder.UserFields.Fields.Item("U_B1_Seq").Value = maxSeqNumVal.ToString(); } else { prodOrder.UserFields.Fields.Item("U_B1_Seq").Value = "1"; } int result = prodOrder.Update(); if (result != 0) { maxSeqNumVal--; int errorCode = 0; string errmsg = ""; oCompany.GetLastError(out errorCode, out errmsg); oApplication.MessageBox(errmsg); } else { if (posLast == "N") { string strUpdate = "Update owor set U_B1_SEQ = isnull(U_B1_SEQ,999) +1 where U_PMX_PLCD = '" + selPL + "' and docentry <> '" + DocEntry.ToString() + "' ; "; Program.objHrmsUI.ExecQuery(strUpdate, "Increment old Seq"); } } } } } }
public Backup() { logs.Clear(); WriteLog("[Log]", "--------------------------------------------------------------------------------"); WriteLog("[Log]", "Integration Begin:[" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt") + "]"); #region Connect to SAP SAPbobsCOM.Company oCom = new SAPbobsCOM.Company(); string dbServerType = ConfigurationManager.AppSettings.Get("dbServerType"); if (dbServerType == "MSSQL2005") { oCom.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2005; } else if (dbServerType == "MSSQL2008") { oCom.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008; } else if (dbServerType == "MSSQL2012") { oCom.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012; } else if (dbServerType == "MSSQL2014") { oCom.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2014; } else if (dbServerType == "HANADB") { oCom.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_HANADB; } else if (dbServerType == "DB_2") { oCom.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_DB_2; } oCom.Server = ConfigurationManager.AppSettings.Get("Server"); oCom.DbUserName = ConfigurationManager.AppSettings.Get("dbuser"); oCom.DbPassword = ConfigurationManager.AppSettings.Get("dbpass"); oCom.LicenseServer = ConfigurationManager.AppSettings.Get("LicenseServer"); oCom.CompanyDB = ConfigurationManager.AppSettings.Get("CompanyDB"); oCom.UserName = ConfigurationManager.AppSettings.Get("UserName"); oCom.Password = ConfigurationManager.AppSettings.Get("Password"); oCom.language = SAPbobsCOM.BoSuppLangs.ln_English; if (oCom.Connect() != 0) { WriteLog("[Error]", "Connection:[" + oCom.CompanyDB + "] Message:[" + oCom.GetLastErrorDescription() + "] Time:[" + DateTime.Now.ToString("hh: mm:ss tt") + "]"); goto EndApplication; } WriteLog("[Log]", "Connected to:[" + oCom.CompanyName + "] Time:[" + DateTime.Now.ToString("hh:mm:ss tt") + "]"); #endregion #region Connect to Data Source SqlConnection conn = new SqlConnection(conString); SqlCommand cmd = new SqlCommand("", conn); conn.Close(); try { conn.Open(); } catch (Exception ex) { WriteLog("[Error]", "Data Source Connection : " + DateTime.Now.ToString() + " : " + ex.Message); goto EndApplication; } SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(conString); string server = builder.DataSource; string database = builder.InitialCatalog; WriteLog("[Log]", "Connected to Data Source:[" + database + "] Time:[" + DateTime.Now.ToString("hh:mm:ss tt") + "]"); cmd.CommandText = "SELECT ROW_NUMBER() over (Partition by BaseEntry Order by BaseEntry )-1 as [Line], * " + "FROM [dbo].[ProductionReceipt] T0 Where IsNull(T0.Posted,'N') <> 'Y'"; SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); int nGR = dt.Rows.Count; if (nGR == 0) { WriteLog("[Log]", "No production receipt to import!"); conn.Close(); da.Dispose(); goto EndApplication; } List <int> idList = new List <int>(); Dictionary <int, DataTable> doc = new Dictionary <int, DataTable>(); //Parsing data into header and details foreach (DataRow row in dt.Rows) { int baseEntry = int.Parse(row["BaseEntry"].ToString()); if (doc.ContainsKey(baseEntry)) { DataTable dtDoc = doc[baseEntry]; dtDoc.ImportRow(row); doc[baseEntry] = dtDoc; } else { DataTable dtDoc = dt.Clone(); dtDoc.ImportRow(row); doc.Add(baseEntry, dtDoc); } } WriteLog("[Log]", "Record found:[" + doc.Keys.Count + "] Total Rows:[" + dt.Rows.Count + "]"); oCom.StartTransaction(); int n = 0; foreach (var item in doc) { try { SAPbobsCOM.Documents oDocReceipt = (SAPbobsCOM.Documents)oCom.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryGenEntry); SAPbobsCOM.ProductionOrders oDocProductionOrders = (SAPbobsCOM.ProductionOrders)oCom.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oProductionOrders); SAPbobsCOM.Items oItem = oCom.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oItems); DataTable dtDoc = doc[item.Key]; DataRow hRow = dtDoc.Rows[0]; int retcode = 0; idList = new List <int>(); string itemCode = hRow["ItemCode"].ToString(); oItem.GetByKey(itemCode); SAPbobsCOM.BoYesNoEnum isManageBatchNumbers = oItem.ManageBatchNumbers; WriteLog("[Log]", "No:[" + (++n) + "] H:Production Order[" + hRow["DocNum"].ToString() + "] Base:[" + item.Key + "] TotalRows:[" + dtDoc.Rows.Count + "]" + " PlannedQty:" + hRow["Quantity"].ToString()); // Add Header ----------------------------------- string proNo = hRow["DocNum"].ToString(); int baseEntry = int.Parse(hRow["BaseEntry"].ToString()); oDocReceipt.Series = 53; oDocReceipt.DocDate = DateTime.Parse(hRow["DocDate"].ToString()); oDocReceipt.DocDueDate = DateTime.Parse(hRow["DocDueDate"].ToString()); oDocReceipt.Comments = hRow["Comments"].ToString(); oDocReceipt.JournalMemo = hRow["JournalMemo"].ToString(); // Add Details start ---------------------------- for (int i = 0; i < dtDoc.Rows.Count; i++) { DataRow row = dtDoc.Rows[i]; int id = int.Parse(row["Id"].ToString()); WriteLog("[Log]", "Id:[" + row["Id"].ToString() + "] R:[" + i + "] ItemCode:" + itemCode + "] BatchNo:" + row["DistNumber"].ToString() + " " + row["status"].ToString() + " Qty:" + row["BatchQuantity"].ToString()); idList.Add(id); oDocReceipt.Lines.Add(); oDocReceipt.Lines.SetCurrentLine(i); oDocReceipt.Lines.BaseType = (int)SAPbobsCOM.BoObjectTypes.oProductionOrders; oDocReceipt.Lines.BaseEntry = int.Parse(row["BaseEntry"].ToString()); //oDocReceipt.Lines.BaseLine = 0; oDocReceipt.Lines.Quantity = Double.Parse(row["BatchQuantity"].ToString()); //Batch Quantity = Receipt Quantity oDocReceipt.Lines.WarehouseCode = "KH010"; oDocReceipt.Lines.FreeText = row["Notes"].ToString(); //oDocReceipt.Lines.WarehouseCode = row["WarehouseCode"].ToString(); string status = row["Status"].ToString().Trim(); oDocReceipt.Lines.TransactionType = (status == "AC") ? SAPbobsCOM.BoTransactionTypeEnum.botrntComplete : SAPbobsCOM.BoTransactionTypeEnum.botrntReject; if (isManageBatchNumbers.Equals("tYES")) { oDocReceipt.Lines.BatchNumbers.BatchNumber = row["DistNumber"].ToString(); oDocReceipt.Lines.BatchNumbers.Quantity = Double.Parse(row["BatchQuantity"].ToString()); if (!String.IsNullOrEmpty(row["ExpiryDate"].ToString())) { oDocReceipt.Lines.BatchNumbers.ExpiryDate = DateTime.Parse(row["ExpiryDate"].ToString()); } if (!String.IsNullOrEmpty(row["MnfDate"].ToString())) { oDocReceipt.Lines.BatchNumbers.ManufacturingDate = DateTime.Parse(row["MnfDate"].ToString()); } if (!String.IsNullOrEmpty(row["InDate"].ToString())) { oDocReceipt.Lines.BatchNumbers.AddmisionDate = DateTime.Parse(row["InDate"].ToString()); } oDocReceipt.Lines.BatchNumbers.Notes = row["Notes"].ToString(); oDocReceipt.Lines.BatchNumbers.Add(); } // add line end -------------------- } retcode = oDocReceipt.Add(); if (retcode == 0) { // Success string docEntry = ""; string docNum = ""; oCom.GetNewObjectCode(out docEntry); if (docEntry == "") { errMsg = "Unknown Error! Please try again!"; if (oCom.InTransaction) { oCom.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack); } WriteLog("[Error]", "Production Receipt:" + errMsg); continue; } SAPbobsCOM.Documents sapDoc = (SAPbobsCOM.Documents)oCom.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInventoryGenEntry); if (sapDoc.GetByKey(int.Parse(docEntry))) { docNum = sapDoc.DocNum.ToString(); } foreach (int id in idList) { string str = "exec sp_FT_UpdateProductionOrder '" + id + "','Y','" + "" + "','" + docNum + "'"; SqlCommand cmdExec = new SqlCommand(str, conn); cmdExec.ExecuteNonQuery(); WriteLog("[Success]", "Production Receipt:[" + docNum + "] has been added!"); } if (oCom.InTransaction) { oCom.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_Commit); } cmd.CommandText = "SELECT CASE WHEN Sum(isNull(BatchQuantity,0.00)) >= MAX(T0.Quantity) Then 1 else 0 END " + "FROM[dbo].[ProductionReceipt] T0 " + "Where ISNULL(T0.Posted,'N') = 'Y' AND T0.BaseEntry=" + baseEntry + " GROUP BY T0.BaseEntry"; Int32 totalReceived = (Int32)cmd.ExecuteScalar(); // Closed Production if (oDocProductionOrders.GetByKey(baseEntry) && totalReceived == 1) { string msg = "Production Order:[" + oDocProductionOrders.DocumentNumber.ToString() + "] has been closed!"; string rmk = oDocProductionOrders.Remarks; oDocProductionOrders.ProductionOrderStatus = SAPbobsCOM.BoProductionOrderStatusEnum.boposClosed; oDocProductionOrders.Remarks = rmk + " :closed by integration"; //oDocProductionOrders.ProductionOrderStatus = SAPbobsCOM.BoProductionOrderStatusEnum.boposCancelled; oDocProductionOrders.Update(); WriteLog("[Success]", msg); foreach (int id in idList) { string str = "exec sp_FT_UpdateProductionOrder '" + id + "','Y','" + msg + "','" + docNum + "'"; SqlCommand cmdExec = new SqlCommand(str, conn); cmdExec.ExecuteNonQuery(); } } } else { // Error oCom.GetLastError(out errCode, out errMsg); foreach (int id in idList) { string str = "exec sp_FT_UpdateProductionOrder '" + id + "','N','" + errMsg.Replace("'", "") + "',''"; SqlCommand cmdExec = new SqlCommand(str, conn); cmdExec.ExecuteNonQuery(); } WriteLog("[Error]", "Production Order:[" + proNo + "] Error Msg:" + oCom.GetLastErrorDescription()); if (oCom.InTransaction) { oCom.EndTransaction(SAPbobsCOM.BoWfTransOpt.wf_RollBack); } } } catch (Exception ex) { oCom.GetLastError(out errCode, out errMsg); string msg = (ex.Message + " SAP msg:" + errMsg).Replace("'", ""); foreach (int id in idList) { string str = "exec sp_FT_UpdateProductionOrder '" + id + "','N','" + msg + "','" + "" + "'"; SqlCommand cmdExec = new SqlCommand(str, conn); cmdExec.ExecuteNonQuery(); } WriteLog("[Error]", "Production Receipt:" + msg); } } #endregion EndApplication: bool sendEmail = false; foreach (KeyValuePair <string, List <string> > kvp in logs) { string status = kvp.Key; List <string> msgs = kvp.Value; if (status == "[Error]") { sendEmail = true; } } if (sendEmail) { WriteLog("[Log]", " ➜ Send E-Mail"); SendEmail(); } ; WriteLog("", "Integration End"); //Thread.Sleep(3000); }