Esempio n. 1
0
        private static bool LoadTable(string ATableName, TSimpleYmlParser AYmlParser, TDBTransaction ATransaction)
        {
            if (!AYmlParser.StartParseList(StringHelper.UpperCamelCase(ATableName, false, false) + "Table"))
            {
                // TLogging.Log("empty table " + ATableName + " " + StringHelper.UpperCamelCase(ATableName, false, false) + "Table");
                return(false);
            }

            DataTable            table      = DBAccess.GDBAccessObj.SelectDT("Select * from " + ATableName, ATableName, ATransaction);
            List <OdbcParameter> Parameters = new List <OdbcParameter>();

            string        OrigInsertStatement = "INSERT INTO pub_" + ATableName + "() VALUES ";
            StringBuilder InsertStatement     = new StringBuilder(OrigInsertStatement);

            ConvertColumnNames(table.Columns);

            SortedList <string, string> RowDetails;
            int count = 0;

            while ((RowDetails = AYmlParser.GetNextLineAttributes()) != null)
            {
                count++;

                if (count != 1)
                {
                    if ((CommonTypes.ParseDBType(DBAccess.GDBAccessObj.DBType) == TDBType.SQLite) || ((count % 500) == 0))
                    {
                        // SQLite does not support INSERT of several rows at the same time
                        try
                        {
                            DBAccess.GDBAccessObj.ExecuteNonQuery(InsertStatement.ToString(), ATransaction, Parameters.ToArray());
                        }
                        catch (Exception e)
                        {
                            TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
                            throw;
                        }

                        InsertStatement = new StringBuilder(OrigInsertStatement);
                        Parameters      = new List <OdbcParameter>();
                    }
                    else
                    {
                        InsertStatement.Append(",");
                    }
                }

                InsertStatement.Append("(");

                // needed for workaround for version 0.2.24, AAPDocument.BaseCurrency and AAPPayment.BaseCurrency;
                if (ATableName == "a_ledger")
                {
                    FCurrencyPerLedger.Add(Convert.ToInt32(RowDetails["LedgerNumber"]), RowDetails["BaseCurrency"]);
                }

                bool firstColumn = true;

                foreach (DataColumn col in table.Columns)
                {
                    if (!firstColumn)
                    {
                        InsertStatement.Append(",");
                    }

                    firstColumn = false;

                    if (RowDetails.ContainsKey(col.ColumnName))
                    {
                        string strValue = RowDetails[col.ColumnName];

                        if (col.DataType == typeof(DateTime))
                        {
                            OdbcParameter p;

                            if (strValue.Length == "yyyy-MM-dd".Length)
                            {
                                p       = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Date);
                                p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd", CultureInfo.InvariantCulture);
                            }
                            else
                            {
                                p       = new OdbcParameter(Parameters.Count.ToString(), OdbcType.DateTime);
                                p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
                            }

                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(String))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
                            p.Value = strValue.Replace("&quot;", "\"");
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Int32))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Int);
                            p.Value = Convert.ToInt32(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Int64))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToInt64(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(double))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToDouble(strValue, CultureInfo.InvariantCulture);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(bool))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Bit);
                            p.Value = Convert.ToBoolean(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Decimal))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToDecimal(strValue, CultureInfo.InvariantCulture);
                            Parameters.Add(p);
                        }
                        else
                        {
                            // should not get here?
                            throw new Exception("error in ResetDatabase, LoadTable: " + col.DataType.ToString() + " has not yet been implemented");
                        }

                        InsertStatement.Append("?");
                    }
                    else
                    {
                        // the following statements are for the demo databases generated before version 0.2.24.
                        // CurrencyCode was added to a_ap_document and a_ap_payment.
                        // it is impossible during the load, to get the correct currencycode, via the supplier, because a_ap_supplier is loaded after a_ap_document.
                        // as a temporary workaround, and because we are still in Alpha, we are using the Base currency of the ledger
                        if ((ATableName == "a_ap_document") && (col.ColumnName == "CurrencyCode"))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
                            p.Value = FCurrencyPerLedger[Convert.ToInt32(RowDetails["LedgerNumber"])];
                            Parameters.Add(p);
                            InsertStatement.Append("?");
                        }
                        else if ((ATableName == "a_ap_payment") && (col.ColumnName == "CurrencyCode"))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
                            p.Value = FCurrencyPerLedger[Convert.ToInt32(RowDetails["LedgerNumber"])];
                            Parameters.Add(p);
                            InsertStatement.Append("?");
                        }
                        else
                        {
                            InsertStatement.Append("NULL"); // DEFAULT
                        }
                    }
                }

                InsertStatement.Append(")");
            }

            try
            {
                DBAccess.GDBAccessObj.ExecuteNonQuery(InsertStatement.ToString(), ATransaction, Parameters.ToArray());
            }
            catch (Exception e)
            {
                TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
                throw;
            }
            return(true);
        }
Esempio n. 2
0
        private static bool LoadTable(string ATableName, TSimpleYmlParser AYmlParser, TDBTransaction AReadWriteTransaction,
                                      ref SortedList <Int32, string> ACurrencyPerLedger)
        {
            if (!AYmlParser.StartParseList(StringHelper.UpperCamelCase(ATableName, false, false) + "Table"))
            {
                // TLogging.Log("empty table " + ATableName + " " + StringHelper.UpperCamelCase(ATableName, false, false) + "Table");
                return(false);
            }

            DataTable            table      = AReadWriteTransaction.DataBaseObj.SelectDT("Select * from " + ATableName, ATableName, AReadWriteTransaction);
            List <OdbcParameter> Parameters = new List <OdbcParameter>();

            string        OrigInsertStatement = "INSERT INTO pub_" + ATableName + "() VALUES ";
            StringBuilder InsertStatement     = new StringBuilder(OrigInsertStatement);

            ConvertColumnNames(table.Columns);

            SortedList <string, string> RowDetails;
            int count = 0;

            while ((RowDetails = AYmlParser.GetNextLineAttributes()) != null)
            {
                count++;

                if (count != 1)
                {
                    if ((DBAccess.DBType == TDBType.MySQL) ||
                        ((count % 500) == 0))
                    {
                        // MySQL is very slow with INSERT of several rows at the same time
                        try
                        {
                            AReadWriteTransaction.DataBaseObj.ExecuteNonQuery(InsertStatement.ToString(), AReadWriteTransaction, Parameters.ToArray());
                        }
                        catch (Exception e)
                        {
                            TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
                            throw;
                        }

                        InsertStatement = new StringBuilder(OrigInsertStatement);
                        Parameters      = new List <OdbcParameter>();
                    }
                    else
                    {
                        InsertStatement.Append(",");
                    }
                }

                InsertStatement.Append("(");

                // needed for workaround for version 0.2.24, AAPDocument.BaseCurrency and AAPPayment.BaseCurrency;
                if (ATableName == "a_ledger")
                {
                    ACurrencyPerLedger.Add(Convert.ToInt32(RowDetails["LedgerNumber"]), RowDetails["BaseCurrency"]);
                }

                // workaround for 2015.09: a_account_hierarchy_detail.a_report_order_i must not be NULL
                if (ATableName == "a_account_hierarchy_detail")
                {
                    if (!RowDetails.ContainsKey("ReportOrder") || RowDetails["ReportOrder"] == string.Empty)
                    {
                        RowDetails["ReportOrder"] = "-1";
                    }
                }

                if (ATableName == "a_gift")
                {
                    if (!RowDetails.ContainsKey("LinkToPreviousGift") || RowDetails["LinkToPreviousGift"] == string.Empty)
                    {
                        RowDetails["LinkToPreviousGift"] = "false";
                    }
                    if (!RowDetails.ContainsKey("PrintReceipt") || RowDetails["PrintReceipt"] == string.Empty)
                    {
                        RowDetails["PrintReceipt"] = "true";
                    }
                }

                if (ATableName == "a_recurring_transaction")
                {
                    if (!RowDetails.ContainsKey("Reference") || RowDetails["Reference"] == string.Empty)
                    {
                        RowDetails["Reference"] = "EMPTY";
                    }
                }

                if (ATableName == "a_transaction")
                {
                    if (!RowDetails.ContainsKey("Reference") || RowDetails["Reference"] == string.Empty)
                    {
                        RowDetails["Reference"] = "EMPTY";
                    }
                }

                bool firstColumn = true;

                foreach (DataColumn col in table.Columns)
                {
                    if (!firstColumn)
                    {
                        InsertStatement.Append(",");
                    }

                    firstColumn = false;

                    if (RowDetails.ContainsKey(col.ColumnName))
                    {
                        string strValue = RowDetails[col.ColumnName];

                        if (col.DataType == typeof(DateTime))
                        {
                            OdbcParameter p;

                            if (strValue.Length == "yyyy-MM-dd".Length)
                            {
                                p       = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Date);
                                p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd", CultureInfo.InvariantCulture);
                            }
                            else
                            {
                                p       = new OdbcParameter(Parameters.Count.ToString(), OdbcType.DateTime);
                                p.Value = DateTime.ParseExact(strValue, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
                            }

                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(String))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.VarChar);
                            p.Value = strValue.Replace("&quot;", "\"");
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Int32))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Int);
                            p.Value = Convert.ToInt32(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Int64))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToInt64(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(double))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToDouble(strValue, CultureInfo.InvariantCulture);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(bool))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Bit);
                            p.Value = Convert.ToBoolean(strValue);
                            Parameters.Add(p);
                        }
                        else if (col.DataType == typeof(Decimal))
                        {
                            OdbcParameter p = new OdbcParameter(Parameters.Count.ToString(), OdbcType.Decimal);
                            p.Value = Convert.ToDecimal(strValue, CultureInfo.InvariantCulture);
                            Parameters.Add(p);
                        }
                        else
                        {
                            // should not get here?
                            throw new Exception("error in ResetDatabase, LoadTable: " + col.DataType.ToString() + " has not yet been implemented");
                        }

                        InsertStatement.Append("?");
                    }
                    else
                    {
                        if (col.ColumnName == "ModificationId")
                        {
                            InsertStatement.Append("CURRENT_TIMESTAMP");
                        }
                        else
                        {
                            InsertStatement.Append("NULL"); // DEFAULT
                        }
                    }
                }

                InsertStatement.Append(")");
            }

            try
            {
                AReadWriteTransaction.DataBaseObj.ExecuteNonQuery(InsertStatement.ToString(), AReadWriteTransaction, Parameters.ToArray());
            }
            catch (Exception e)
            {
                TLogging.Log("error in ResetDatabase, LoadTable " + ATableName + ":" + e.Message);
                throw;
            }
            return(true);
        }