/// <summary>
/// A method for adding new field to B1 table
/// </summary>
/// <param name="name">Field Name</param>
/// <param name="description">Field description</param>
/// <param name="tableName">Table the field will be added to</param>
/// <param name="fieldType">Field Type</param>
/// <param name="size">Field size in the database</param>
/// <param name="mandatory">bool: if the value is mandatory to be filled</param>
public static void AddField(string name, string description, string tableName, SAPbobsCOM.BoFieldTypes fieldType, SAPbobsCOM.BoYesNoEnum mandatory, bool addedToUDT)
{
AddField(name, description, tableName, fieldType, null, mandatory, 0, addedToUDT);
}
/// <summary>
/// A method for adding new field to B1 table
/// </summary>
/// <param name="name">Field Name</param>
/// <param name="description">Field description</param>
/// <param name="tableName">Table the field will be added to</param>
/// <param name="fieldType">Field Type</param>
/// <param name="size">Field size in the database</param>
/// <param name="subType"></param>
/// <param name="mandatory"></param>
/// <param name="addedToUDT">If this field will be added to system table or User defined table</param>
/// <param name="valiedValue">The default selected value</param>
/// <param name="validValues">Add the values seperated by comma "," for value and description ex:(Value,Description)</param>
public static void AddField(string name, string description, string tableName, SAPbobsCOM.BoFieldTypes fieldType, Nullable <int> size, SAPbobsCOM.BoYesNoEnum mandatory, SAPbobsCOM.BoFldSubTypes subType, bool addedToUDT, string validValue, params string[] validValues)
{
var objUserFieldMD = B1Helper.diCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields) as SAPbobsCOM.UserFieldsMD;
try
{
if (addedToUDT)
{
tableName = string.Format("@{0}", tableName);
}
if (!IsFieldExists(name, tableName))
{
objUserFieldMD.TableName = tableName;
objUserFieldMD.Name = name;
objUserFieldMD.Description = description;
objUserFieldMD.Type = fieldType;
objUserFieldMD.Mandatory = mandatory;
if (size == null || size <= 0)
{
size = 10;
}
if (fieldType != SAPbobsCOM.BoFieldTypes.db_Numeric)
{
objUserFieldMD.Size = (int)size;
}
else
{
objUserFieldMD.EditSize = (int)size;
}
objUserFieldMD.SubType = subType;
if (validValue != null)
{
objUserFieldMD.DefaultValue = validValue;
}
if (validValues != null)
{
foreach (string s in validValues)
{
var valuesAttributes = s.Split(',');
if (valuesAttributes.Length == 2)
{
objUserFieldMD.ValidValues.Description = valuesAttributes[1];
}
objUserFieldMD.ValidValues.Value = valuesAttributes[0];
objUserFieldMD.ValidValues.Add();
}
}
if (objUserFieldMD.Add() != 0)
{
var error = Utilities.GetErrorMessage();
Utilities.LogException(error);
}
}
}
catch (Exception ex)
{
Utilities.LogException(ex);
}
finally
{
objUserFieldMD.ReleaseObject();
}
}
/// <summary>
/// A method for adding new field to B1 table
/// </summary>
/// <param name="name">Field Name</param>
/// <param name="description">Field description</param>
/// <param name="tableName">Table the field will be added to</param>
/// <param name="fieldType">Field Type</param>
/// <param name="size">Field size in the database</param>
/// <param name="mandatory">bool: if the value is mandatory to be filled</param>
/// <param name="subType"></param>
/// <param name="addedToUDT">If this field will be added to system table or User defined table</param>
public static void AddField(string name, string description, string tableName, SAPbobsCOM.BoFieldTypes fieldType, Nullable <int> size, SAPbobsCOM.BoYesNoEnum mandatory, SAPbobsCOM.BoFldSubTypes subType, bool addedToUDT)
{
AddField(name, description, tableName, fieldType, size, mandatory, subType, addedToUDT, null);
}
public static void CreateUDF(String udt, //can put business object as well
String udfName, String udfDesc, SAPbobsCOM.BoFieldTypes udfType, SAPbobsCOM.BoFldSubTypes udfSubType, int udfEditSize, Dictionary <string, string> udfValidValues = null, SAPbobsCOM.BoYesNoEnum udfMandatory = SAPbobsCOM.BoYesNoEnum.tNO)
{
if (!CheckFieldExists(udt, udfName))
{
SAPbobsCOM.UserFieldsMD oUDFMD = null;
try
{
oUDFMD = AddOnUtilities.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields);
oUDFMD.TableName = udt;
oUDFMD.Name = udfName;
oUDFMD.Description = udfDesc;
oUDFMD.Type = udfType;
oUDFMD.SubType = udfSubType;
oUDFMD.EditSize = udfEditSize;
oUDFMD.Mandatory = udfMandatory;
//foreach(var udfValidValue in udfValidValues)
//{
// oUDFMD.ValidValues.Value = udfValidValue.Key;
// oUDFMD.ValidValues.Value = udfValidValue.Key;
// oUDFMD.ValidValues.Add();
//}
AddOnUtilities.IRetCode = oUDFMD.Add();
AddOnUtilities.DIErrorHandler(String.Format("UDF {0} Created.", udfName));
}
catch (Exception ex)
{
AddOnUtilities.MsgBoxWrapper(ex.Message + " " + ex.StackTrace);
}
finally
{
//Important - release COM Object
System.Runtime.InteropServices.Marshal.ReleaseComObject(oUDFMD);
GC.Collect();
oUDFMD = null;
}
}
}
private bool CreaUDOMD(string sCode, string sName, string sTableName, string[] sFindColumns,
string[] sChildTables, SAPbobsCOM.BoYesNoEnum eCanCancel, SAPbobsCOM.BoYesNoEnum eCanClose,
SAPbobsCOM.BoYesNoEnum eCanDelete, SAPbobsCOM.BoYesNoEnum eCanCreateDefaultForm, string[] sFormColumns,
SAPbobsCOM.BoYesNoEnum eCanFind, SAPbobsCOM.BoYesNoEnum eCanLog, SAPbobsCOM.BoUDOObjType eObjectType,
SAPbobsCOM.BoYesNoEnum eManageSeries, SAPbobsCOM.BoYesNoEnum eEnableEnhancedForm,
SAPbobsCOM.BoYesNoEnum eRebuildEnhancedForm, string[] sChildFormColumns)
{
SAPbobsCOM.UserObjectsMD oUserObjectMD = null;
int i_Result = 0;
string s_Result = "";
try
{
oUserObjectMD = (SAPbobsCOM.UserObjectsMD)Conexion.company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserObjectsMD);
if (!oUserObjectMD.GetByKey(sCode))
{
oUserObjectMD.Code = sCode;
oUserObjectMD.Name = sName;
oUserObjectMD.ObjectType = eObjectType;
oUserObjectMD.TableName = sTableName;
oUserObjectMD.CanCancel = eCanCancel;
oUserObjectMD.CanClose = eCanClose;
oUserObjectMD.CanDelete = eCanDelete;
oUserObjectMD.CanCreateDefaultForm = eCanCreateDefaultForm;
oUserObjectMD.EnableEnhancedForm = eEnableEnhancedForm;
oUserObjectMD.RebuildEnhancedForm = eRebuildEnhancedForm;
oUserObjectMD.CanFind = eCanFind;
oUserObjectMD.CanLog = eCanLog;
oUserObjectMD.ManageSeries = eManageSeries;
if (sFindColumns != null)
{
for (int i = 0; i < sFindColumns.Length; i++)
{
oUserObjectMD.FindColumns.ColumnAlias = sFindColumns[i];
oUserObjectMD.FindColumns.Add();
}
}
if (sChildTables != null)
{
for (int i = 0; i < sChildTables.Length; i++)
{
oUserObjectMD.ChildTables.TableName = sChildTables[i];
oUserObjectMD.ChildTables.Add();
}
}
if (sFormColumns != null)
{
oUserObjectMD.UseUniqueFormType = SAPbobsCOM.BoYesNoEnum.tYES;
for (int i = 0; i < sFormColumns.Length; i++)
{
oUserObjectMD.FormColumns.FormColumnAlias = sFormColumns[i];
oUserObjectMD.FormColumns.Add();
}
}
if (sChildFormColumns != null)
{
if (sChildTables != null)
{
for (int i = 0; i < sChildFormColumns.Length; i++)
{
oUserObjectMD.FormColumns.SonNumber = 1;
oUserObjectMD.FormColumns.FormColumnAlias = sChildFormColumns[i];
oUserObjectMD.FormColumns.Add();
}
}
}
i_Result = oUserObjectMD.Add();
if (i_Result != 0)
{
Conexion.company.GetLastError(out i_Result, out s_Result);
StatusMessageError("Error: EstructuraDatos.cs > CreaUDOMD(): " + s_Result + ", creando el UDO " + sCode + ".");
return(false);
}
}
return(true);
}
catch (Exception ex)
{
StatusMessageError("Error: EstructuraDatos.cs > CreaUDOMD():" + ex.Message);
return(false);
}
finally
{
LiberarObjetoGenerico(oUserObjectMD);
}
}
private void CreaCampoMD(string NombreTabla, string NombreCampo, string DescCampo, SAPbobsCOM.BoFieldTypes TipoCampo, SAPbobsCOM.BoFldSubTypes SubTipo, int Tamano, SAPbobsCOM.BoYesNoEnum Obligatorio, string[] validValues, string[] validDescription, string valorPorDef, string tablaVinculada)
{
SAPbobsCOM.UserFieldsMD oUserFieldsMD = null;
try
{
if (NombreTabla == null)
{
NombreTabla = "";
}
if (NombreCampo == null)
{
NombreCampo = "";
}
if (Tamano == 0)
{
Tamano = 10;
}
if (validValues == null)
{
validValues = new string[0];
}
if (validDescription == null)
{
validDescription = new string[0];
}
if (valorPorDef == null)
{
valorPorDef = "";
}
if (tablaVinculada == null)
{
tablaVinculada = "";
}
oUserFieldsMD = (SAPbobsCOM.UserFieldsMD)Conexion.company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserFields);
oUserFieldsMD.TableName = NombreTabla;
oUserFieldsMD.Name = NombreCampo;
oUserFieldsMD.Description = DescCampo;
oUserFieldsMD.Type = TipoCampo;
if (TipoCampo != SAPbobsCOM.BoFieldTypes.db_Date)
{
oUserFieldsMD.EditSize = Tamano;
}
oUserFieldsMD.SubType = SubTipo;
if (tablaVinculada != "")
{
oUserFieldsMD.LinkedTable = tablaVinculada;
}
else
{
if (validValues.Length > 0)
{
for (int i = 0; i <= (validValues.Length - 1); i++)
{
oUserFieldsMD.ValidValues.Value = validValues[i];
if (validDescription.Length > 0)
{
oUserFieldsMD.ValidValues.Description = validDescription[i];
}
else
{
oUserFieldsMD.ValidValues.Description = validValues[i];
}
oUserFieldsMD.ValidValues.Add();
}
}
oUserFieldsMD.Mandatory = Obligatorio;
if (valorPorDef != "")
{
oUserFieldsMD.DefaultValue = valorPorDef;
}
}
m_iErrCode = oUserFieldsMD.Add();
if (m_iErrCode != 0)
{
Conexion.company.GetLastError(out m_iErrCode, out m_sErrMsg);
if ((m_iErrCode != -5002) && (m_iErrCode != -2035))
{
StatusMessageError("Error al crear campo de usuario: " + NombreCampo
+ "en la tabla: " + NombreTabla + " Error: " + m_sErrMsg);
}
}
else
{
StatusMessageSuccess("Se ha creado el campo de usuario: " + NombreCampo
+ " en la tabla: " + NombreTabla);
}
}
catch (Exception ex)
{
StatusMessageError("Error: EstructuraDatos.cs > CreaCampoMD():" + ex.Message);
}
finally
{
LiberarObjetoGenerico(oUserFieldsMD);
oUserFieldsMD = null;
}
}
/// <summary>
/// Method to create UDO
/// </summary>
/// <param name="udoCode"></param>
/// <param name="udoName"></param>
/// <param name="udoType"></param>
/// <param name="mainTable"></param>
/// <param name="oCanFind"></param>
/// <param name="oCanDelete"></param>
/// <param name="oCanCancel"></param>
/// <param name="oCanClose"></param>
/// <param name="oCanLog"></param>
/// <param name="oManageSeries"></param>
/// <param name="oYearTransfer"></param>
/// <param name="oCanArchive"></param>
/// <param name="oCanApprove"></param>
/// <param name="oCreateDeafaultForm"></param>
/// <param name="isEnhancedForm"></param>
/// <param name="isMenuItem"></param>
/// <param name="menuCaption"></param>
/// <param name="FathermenuID"></param>
/// <param name="position"></param>
/// <param name="MenuUId"></param>
/// <param name="formColumnList"></param>
/// <param name="findColumnList"></param>
/// <param name="childTableList"></param>
/// <param name="enchancedFormColumnList"></param>
/// <param name="isUniqueForm"></param>
public void Create(/*Basic Setting*/ string udoCode, string udoName, SAPbobsCOM.BoUDOObjType udoType, string mainTable /*Setting Services*/, SAPbobsCOM.BoYesNoEnum oCanFind, SAPbobsCOM.BoYesNoEnum oCanDelete, SAPbobsCOM.BoYesNoEnum oCanCancel, SAPbobsCOM.BoYesNoEnum oCanClose, SAPbobsCOM.BoYesNoEnum oCanLog, SAPbobsCOM.BoYesNoEnum oManageSeries, SAPbobsCOM.BoYesNoEnum oYearTransfer, SAPbobsCOM.BoYesNoEnum oCanArchive, SAPbobsCOM.BoYesNoEnum oCanApprove /*UI Setting*/, SAPbobsCOM.BoYesNoEnum oCreateDeafaultForm, SAPbobsCOM.BoYesNoEnum isEnhancedForm, SAPbobsCOM.BoYesNoEnum isMenuItem, string menuCaption, int FathermenuID, int position, string MenuUId /*Set Form Columns*/, IList <Models.FormColumnValues> formColumnList /*Setting the Fields for the find columns*/, IList <Models.FindColumnValues> findColumnList /*Linking child user tables*/, IList <Models.ChildTableValues> childTableList /*Setting the fields for Enhance Column*/, IList <Models.EnhancedColumnValues> enchancedFormColumnList /*Set enhanced Column service value*/, SAPbobsCOM.BoYesNoEnum isUniqueForm)
{
SAPbobsCOM.IUserObjectsMD oUDOMD = null;
SAPbobsCOM.IUserObjectMD_FindColumns oUDOFind = null;
SAPbobsCOM.IUserObjectMD_FormColumns oUDOForm = null;
SAPbobsCOM.IUserObjectMD_EnhancedFormColumns oEnhancedForm = null;
SAPbobsCOM.IUserObjectMD_ChildTables oChild = null;
PublicVariable.lRetCode = 0;
try
{
oUDOMD = (SAPbobsCOM.IUserObjectsMD)PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oUserObjectsMD);
oUDOFind = oUDOMD.FindColumns;
oUDOForm = oUDOMD.FormColumns;
oEnhancedForm = oUDOMD.EnhancedFormColumns;
oChild = oUDOMD.ChildTables;
//Basic Settings
oUDOMD.Code = udoCode;
oUDOMD.Name = udoName;
oUDOMD.ObjectType = udoType;
oUDOMD.TableName = mainTable;
//Setting Services
oUDOMD.CanFind = oCanFind;
oUDOMD.CanDelete = oCanDelete;
oUDOMD.CanCancel = oCanCancel;
oUDOMD.CanClose = oCanClose;
oUDOMD.CanLog = oCanLog;
oUDOMD.ManageSeries = oManageSeries;
oUDOMD.CanYearTransfer = oYearTransfer;
oUDOMD.CanArchive = oCanArchive;
oUDOMD.CanApprove = oCanApprove;
//UI Settings
oUDOMD.CanCreateDefaultForm = oCreateDeafaultForm;
oUDOMD.EnableEnhancedForm = isEnhancedForm;
if (isMenuItem == SAPbobsCOM.BoYesNoEnum.tYES)
{
oUDOMD.MenuItem = isMenuItem;
oUDOMD.FatherMenuID = FathermenuID;
oUDOMD.MenuCaption = menuCaption;
oUDOMD.MenuUID = MenuUId;
}
else
{
oUDOMD.MenuItem = isMenuItem;
}
//Setting fields for default form
if (formColumnList.Count != 0)
{
SAPbobsCOM.IUserObjectMD_FormColumns formColumns = oUDOMD.FormColumns;
foreach (Models.FormColumnValues fColumn in formColumnList)
{
formColumns.FormColumnAlias = fColumn.ColumnAlias;
formColumns.FormColumnDescription = fColumn.ColumnDescription;
formColumns.Add();
}
}
//Find Columns
if (oCanFind == SAPbobsCOM.BoYesNoEnum.tYES && findColumnList.Count != 0)
{
SAPbobsCOM.IUserObjectMD_FindColumns findColumns = oUDOMD.FindColumns;
foreach (Models.FindColumnValues fColumn in findColumnList)
{
findColumns.ColumnAlias = fColumn.ColumnAlias;
findColumns.ColumnDescription = fColumn.ColumnDescription;
findColumns.Add();
}
}
//Child Table property
if (childTableList.Count != 0)
{
foreach (Models.ChildTableValues fChildTable in childTableList)
{
oUDOMD.ChildTables.ObjectName = fChildTable.ObjectName;
oUDOMD.ChildTables.TableName = fChildTable.TableName;
oUDOMD.ChildTables.Add();
}
}
//Enhanced Column
if (enchancedFormColumnList.Count != 0)
{
SAPbobsCOM.IUserObjectMD_EnhancedFormColumns enhanceFormColumns = oUDOMD.EnhancedFormColumns;
foreach (Models.EnhancedColumnValues eColumn in enchancedFormColumnList)
{
enhanceFormColumns.ColumnAlias = eColumn.ColumnAlias;
enhanceFormColumns.ColumnDescription = eColumn.ColumnDescription;
enhanceFormColumns.ColumnIsUsed = eColumn.ColumnIsUsed;
enhanceFormColumns.Editable = eColumn.Editable;
enhanceFormColumns.ChildNumber = eColumn.ChildNumber;
enhanceFormColumns.Add();
}
oUDOMD.UseUniqueFormType = isUniqueForm;
}
//Add UDO
PublicVariable.lRetCode = oUDOMD.Add();
if (PublicVariable.lRetCode == 0)
{
//Program.oApplication.StatusBar.SetText(String.Format("UDO {0}, {1} Registration", udoCode, udoName), SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
}
else if (PublicVariable.lRetCode == -2035)
{
//Do Nothing
string error = PublicVariable.oCompany.GetLastErrorDescription();
}
else if (PublicVariable.lRetCode == -5002)
{
//Do Nothing
string error = PublicVariable.oCompany.GetLastErrorDescription();
}
else
{
string error = PublicVariable.oCompany.GetLastErrorDescription();
PublicVariable.oApplication.StatusBar.SetText(error, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
}
}
catch (Exception ex)
{
PublicVariable.oApplication.StatusBar.SetText(ex.Message, SAPbouiCOM.BoMessageTime.bmt_Short, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
}
finally
{
if (oUDOMD != null)
{
Marshal.FinalReleaseComObject(oUDOMD);
oUDOMD = null;
GC.Collect();
}
}
}
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);
}