2309 lines
148 KiB
C#
2309 lines
148 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Globalization;
|
|
using System.Reflection;
|
|
using DevExpress.Data.Filtering;
|
|
using DevExpress.ExpressApp;
|
|
using DevExpress.ExpressApp.Actions;
|
|
using DevExpress.ExpressApp.Xpo;
|
|
using DevExpress.Persistent.BaseImpl;
|
|
using DevExpress.Xpo;
|
|
using MySql.Data.MySqlClient;
|
|
using Nuvolar.Module.Win;
|
|
|
|
namespace Nuvolar.Module.BusinessObjects.Migration
|
|
{
|
|
// For more typical usage scenarios, be sure to check out https://documentation.devexpress.com/eXpressAppFramework/clsDevExpressExpressAppViewControllertopic.aspx.
|
|
public partial class Migration_VC : ViewController
|
|
{
|
|
public Migration_VC()
|
|
{
|
|
InitializeComponent();
|
|
// Target required Views (via the TargetXXX properties) and create their Actions.
|
|
}
|
|
protected override void OnActivated()
|
|
{
|
|
base.OnActivated();
|
|
// Perform various tasks depending on the target View.
|
|
}
|
|
protected override void OnViewControlsCreated()
|
|
{
|
|
base.OnViewControlsCreated();
|
|
// Access and customize the target View control.
|
|
}
|
|
protected override void OnDeactivated()
|
|
{
|
|
// Unsubscribe from previously subscribed events and release other references and resources.
|
|
base.OnDeactivated();
|
|
}
|
|
|
|
private void aMigrateBaseObjects_Execute(object sender, SimpleActionExecuteEventArgs e)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
XPObjectSpace _ocur = View.ObjectSpace as XPObjectSpace;
|
|
|
|
if (_MigrationParameters.SQLType == eSQLType.MSSQL)
|
|
{
|
|
Sync_XPObjectType_MSSQL_MySQL(_ocur);
|
|
Sync_Country_MSSQL_MySQL(_ocur);
|
|
//Sync_HCategory_ChartOfAccounts_MSSQL_MySQL(_ocur);
|
|
//Sync_ChartOfAccounts_MSSQL_MySQL(_ocur);
|
|
//Sync_ChartOfAccountsYear_MSSQL_MySQL(_ocur);
|
|
|
|
//Sync_HCategory_Controlling_MSSQL_MySQL(_ocur);
|
|
//Sync_Controlling_MSSQL_MySQL(_ocur);
|
|
//Sync_ControllingYear_MSSQL_MySQL(_ocur);
|
|
//Sync_VAT_MSSQL_MySQL(_ocur);
|
|
//Sync_VATYear_MSSQL_MySQL(_ocur);
|
|
//Sync_CustomersGLParameters_MSSQL_MySQL(_ocur);
|
|
//Sync_CustomersGLParametersYear_MSSQL_MySQL(_ocur);
|
|
|
|
//Sync_Party_CustomersFrom_MSSQL_MySQL(_ocur);
|
|
//Sync_PaymentOption_MSSQL_MySQL(_ocur);
|
|
//Sync_LiquidAssets_MSSQL_MySQL(_ocur);
|
|
}
|
|
|
|
}
|
|
private void Sync_XPObjectType_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MakeObjectType<Country>(_ocur);
|
|
MakeObjectType<HCategory>(_ocur);
|
|
MakeObjectType<ChartOfAccounts>(_ocur);
|
|
MakeObjectType<ChartOfAccountsYear>(_ocur);
|
|
MakeObjectType<Controlling>(_ocur);
|
|
MakeObjectType<ControllingYear>(_ocur);
|
|
MakeObjectType<VAT>(_ocur);
|
|
MakeObjectType<VATYear>(_ocur);
|
|
MakeObjectType<CustomersGLParameters>(_ocur);
|
|
MakeObjectType<CustomersGLParametersYear>(_ocur);
|
|
MakeObjectType<Party>(_ocur);
|
|
MakeObjectType<Organization>(_ocur);
|
|
MakeObjectType<Customers>(_ocur);
|
|
MakeObjectType<CustomersFrom>(_ocur);
|
|
MakeObjectType<CustomersOrder>(_ocur);
|
|
MakeObjectType<PaymentOption>(_ocur);
|
|
|
|
}
|
|
private void MakeObjectType<T>(XPObjectSpace _ocur)
|
|
{
|
|
XPObjectType _XPObjectType = _ocur.FindObject<XPObjectType>(CriteriaOperator.Parse("TypeName = ?", typeof(T).FullName));
|
|
if (_XPObjectType == null)
|
|
{
|
|
Assembly a = typeof(T).Assembly;
|
|
|
|
_XPObjectType = _ocur.CreateObject<XPObjectType>();
|
|
_XPObjectType.TypeName = typeof(T).FullName;
|
|
_XPObjectType.AssemblyName = a.FullName.Split(',')[0];
|
|
|
|
}
|
|
_ocur.CommitChanges();
|
|
}
|
|
private int GetObjectTypeOID<T>(XPObjectSpace _ocur)
|
|
{
|
|
int _return = -1;
|
|
XPObjectType _XPObjectType = _ocur.FindObject<XPObjectType>(CriteriaOperator.Parse("TypeName = ?", typeof(T).FullName));
|
|
if (_XPObjectType != null)
|
|
{
|
|
_return = _XPObjectType.Oid;
|
|
}
|
|
return _return;
|
|
}
|
|
private void Sync_Country_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from Country t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
int _ObjectType_OID = GetObjectTypeOID<Country>(_ocur);
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM Country WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO Country (Oid,Name,PhoneCode,ObjectType,EUMember,ISOCode)" +
|
|
" SELECT @Oid,@Name,@PhoneCode,@ObjectType,@EUMember,@ISOCode";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@PhoneCode", DbType = DbType.String, Value = _SqlDataReader["PhoneCode"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ObjectType", DbType = DbType.Int64, Value = _ObjectType_OID });
|
|
//_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Name_GB", DbType = DbType.String, Value = _SqlDataReader["Name_GB"] });
|
|
//_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName", DbType = DbType.String, Value = _SqlDataReader["ShortName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@EUMember", DbType = DbType.Boolean, Value = _SqlDataReader["EUMember"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ISOCode", DbType = DbType.String, Value = _SqlDataReader["ISOCode"] });
|
|
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_HCategory_ChartOfAccounts_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
int _ObjectType_OID = GetObjectTypeOID<ChartOfAccounts>(_ocur);
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from HCategory t1" +
|
|
" where oid in (select Oid from ChartOfAccounts) and t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM HCategory WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO HCategory (Oid,Parent,Name,ObjectType)" +
|
|
" SELECT @Oid,@Parent,@Name,@ObjectType;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ObjectType", DbType = DbType.Int64, Value = _ObjectType_OID });
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" UPDATE HCategory SET Name=@Name," +
|
|
" Parent=@Parent " +
|
|
" WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
_MySqlCommand_Update.ExecuteNonQuery();
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_HCategory_Controlling_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
int _ObjectType_OID = GetObjectTypeOID<Controlling>(_ocur);
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from HCategory t1" +
|
|
" where oid in (select Oid from Controlling) and t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM HCategory WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO HCategory (Oid,Parent,Name,ObjectType)" +
|
|
" SELECT @Oid,@Parent,@Name,@ObjectType;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ObjectType", DbType = DbType.Int64, Value = _ObjectType_OID });
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" UPDATE HCategory SET Name=@Name," +
|
|
" Parent=@Parent " +
|
|
" WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
_MySqlCommand_Update.ExecuteNonQuery();
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_ChartOfAccounts_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from ChartOfAccounts t1 left join HCategory t2 on" +
|
|
" t1.Oid=t2.Oid" +
|
|
" WHERE t2.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM ChartOfAccounts WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO ChartOfAccounts (Oid,AccountYear,AccountNumber,IsParent,ChartOfAccounts_Parent,ChartOfAccounts1," +
|
|
" ChartOfAccounts2,ChartOfAccounts3,ChartOfAccounts4,ChartOfAccounts5,ChartOfAccounts6,ChartOfAccounts7,ChartOfAccounts8)" +
|
|
" SELECT @Oid,@AccountYear,@AccountNumber,@IsParent,@ChartOfAccounts_Parent,@ChartOfAccounts1," +
|
|
" @ChartOfAccounts2,@ChartOfAccounts3,@ChartOfAccounts4,@ChartOfAccounts5,@ChartOfAccounts6,@ChartOfAccounts7,@ChartOfAccounts8;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@AccountYear", DbType = DbType.Int64, Value = _SqlDataReader["AccountYear"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@AccountNumber", DbType = DbType.String, Value = _SqlDataReader["AccountNumber"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsParent", DbType = DbType.Boolean, Value = _SqlDataReader["IsParent"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts_Parent", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts_Parent"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts1", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts1"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts2", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts2"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts3", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts3"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts4", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts4"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts5", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts5"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts6", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts6"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts7", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts7"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts8", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts8"] });
|
|
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_ChartOfAccountsYear_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from ChartOfAccountsYear t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM ChartOfAccountsYear WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO ChartOfAccountsYear (Oid,AccountYear,IsClosed)" +
|
|
" SELECT @Oid,@AccountYear,@IsClosed;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@AccountYear", DbType = DbType.Int64, Value = _SqlDataReader["AccountYear"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsClosed", DbType = DbType.Boolean, Value = _SqlDataReader["IsClosed"] });
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_Controlling_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from Controlling t1 left join HCategory t2 on" +
|
|
" t1.Oid=t2.Oid" +
|
|
" WHERE t2.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM Controlling WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO Controlling (Oid,IsActive,IsCOCRequired,COCAboveAmount," +
|
|
" IsParent,Controllingdirection,IsReinvoiceReason,ReinvoiceDescription,TechnicalContractLimit," +
|
|
" Controlling1,Controlling2,Controlling3,Controlling4,Controlling5,IsAdvancePayment," +
|
|
" IsEstateStatistics,RequireCars,RequireHRPerson,RequirePhones)" +
|
|
" SELECT @Oid,@IsActive,@IsCOCRequired,@COCAboveAmount," +
|
|
" @IsParent,@Controllingdirection,@IsReinvoiceReason,@ReinvoiceDescription,@TechnicalContractLimit," +
|
|
" @Controlling1,@Controlling2,@Controlling3,@Controlling4,@Controlling5,@IsAdnavcePayment," +
|
|
" @IsEstateStatistics,@RequireCars,@RequireHRPerson,@RequirePhones;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsCOCRequired", DbType = DbType.Boolean, Value = _SqlDataReader["IsCOCRequired"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsParent", DbType = DbType.Boolean, Value = _SqlDataReader["IsParent"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsActive", DbType = DbType.Boolean, Value = true });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsReinvoiceReason", DbType = DbType.Boolean, Value = _SqlDataReader["IsReinvoiceReason"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsAdvancePayment", DbType = DbType.Boolean, Value = _SqlDataReader["IsAdvancePayment"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsEstateStatistics", DbType = DbType.Boolean, Value = _SqlDataReader["IsEstateStatistics"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@RequireCars", DbType = DbType.Boolean, Value = _SqlDataReader["RequireCars"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@RequireHRPerson", DbType = DbType.Boolean, Value = _SqlDataReader["RequireHRPerson"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@RequirePhones", DbType = DbType.Boolean, Value = _SqlDataReader["RequirePhones"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@COCAboveAmount", DbType = DbType.Double, Value = _SqlDataReader["COCAboveAmount"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Controllingdirection", DbType = DbType.Int64, Value = _SqlDataReader["Controllingdirection"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ReinvoiceDescription", DbType = DbType.String, Value = _SqlDataReader["ReinvoiceDescription"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@TechnicalContractLimit", DbType = DbType.Double, Value = _SqlDataReader["TechnicalContractLimit"] });
|
|
for (int i = 1; i < 6; i++)
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Controlling" + i, DbType = DbType.Guid, Value = _SqlDataReader["Controlling" + i] });
|
|
}
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_ControllingYear_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from ControllingYear t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM ControllingYear WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO ControllingYear (Oid,Controlling,AccountYear,ChartOfAccounts,ChartOfAccounts2)" +
|
|
" SELECT @Oid,@Controlling,@AccountYear,@ChartOfAccounts,@ChartOfAccounts2;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Controlling", DbType = DbType.Guid, Value = _SqlDataReader["Controlling"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@AccountYear", DbType = DbType.Int64, Value = _SqlDataReader["AccountYear"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccounts2", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccounts2"] });
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_VAT_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from VAT t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM VAT WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO VAT (Oid,ShortName,Description,KeyValue,ClaimState,Active,InversState,InversKeyValue,DefaultForBusinnes," +
|
|
" DefaultForExternalEUNoVAT,DefaultForExternalOther,DefaultForInversVAT,VATBag)" +
|
|
" SELECT @Oid,@ShortName,@Description,@KeyValue,@ClaimState,@Active,@InversState,@InversKeyValue,@DefaultForBusinnes," +
|
|
" @DefaultForExternalEUNoVAT,@DefaultForExternalOther,@DefaultForInversVAT,@VATBag;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName", DbType = DbType.String, Value = _SqlDataReader["ShortName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Description", DbType = DbType.String, Value = _SqlDataReader["Description"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@KeyValue", DbType = DbType.Double, Value = _SqlDataReader["KeyValue"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ClaimState", DbType = DbType.Boolean, Value = _SqlDataReader["ClaimState"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Active", DbType = DbType.Boolean, Value = _SqlDataReader["Active"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@InversState", DbType = DbType.Boolean, Value = _SqlDataReader["InversState"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@InversKeyValue", DbType = DbType.Double, Value = _SqlDataReader["InversKeyValue"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@DefaultForBusinnes", DbType = DbType.Boolean, Value = _SqlDataReader["DefaultForBusinnes"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@DefaultForExternalEUNoVAT", DbType = DbType.Boolean, Value = _SqlDataReader["DefaultForExternalEUNoVAT"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@DefaultForExternalOther", DbType = DbType.Boolean, Value = _SqlDataReader["DefaultForExternalOther"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@DefaultForInversVAT", DbType = DbType.Boolean, Value = _SqlDataReader["DefaultForInversVAT"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@VATBag", DbType = DbType.String, Value = _SqlDataReader["VATBag"] });
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_VATYear_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
string _MySQL = "";
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from VATYear t1 " +
|
|
" WHERE t1.GCRecord is null AND t1.AccountYear >=2020";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
_MySQL = "SELECT count(*) FROM VATYear WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO VATYear (Oid,VAT,AccountYear,ChartOfAccountsOut,ChartOfAccountsIn)" +
|
|
" SELECT @Oid,@VAT,@AccountYear,@ChartOfAccountsOut,@ChartOfAccountsIn;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@VAT", DbType = DbType.Guid, Value = _SqlDataReader["VAT"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@AccountYear", DbType = DbType.Int64, Value = _SqlDataReader["AccountYear"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccountsOut", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccountsOut"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccountsIn", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccountsIn"] });
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_MySQL = " UPDATE VATYear set ChartOfAccountsOut=NULL where ChartOfAccountsOut NOT IN (select Oid from HCategory);" +
|
|
" UPDATE VATYear set ChartOfAccountsIn=NULL where ChartOfAccountsIn NOT IN (select Oid from HCategory);";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_CustomersGLParameters_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from CustomersGLParameters t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM CustomersGLParameters WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO CustomersGLParameters (Oid,ShortName,DefaultforBusiness)" +
|
|
" SELECT @Oid,@ShortName,@DefaultForBusiness;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName", DbType = DbType.String, Value = _SqlDataReader["ShortName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@DefaultForBusiness", DbType = DbType.Boolean, Value = _SqlDataReader["DefaultForBusiness"] });
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_CustomersGLParametersYear_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
string _MySQL = "";
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from CustomersGLParametersYear t1 " +
|
|
" WHERE t1.GCRecord is null AND t1.AccountYear >=2020";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
_MySQL = "SELECT count(*) FROM CustomersGLParametersYear WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO CustomersGLParametersYear (Oid,CustomersGLParameters,AccountYear,ChartOfAccountsOut,ChartOfAccountsIn,ChartOfAccountsNo)" +
|
|
" SELECT @Oid,@CustomersGLParameters,@AccountYear,@ChartOfAccountsOut,@ChartOfAccountsIn,@ChartOfAccountsNo;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@CustomersGLParameters", DbType = DbType.Guid, Value = _SqlDataReader["CustomersGLParameters"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@AccountYear", DbType = DbType.Int64, Value = _SqlDataReader["AccountYear"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccountsOut", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccountsOut"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccountsIn", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccountsIn"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ChartOfAccountsNo", DbType = DbType.Guid, Value = _SqlDataReader["ChartOfAccountsNo"] });
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_MySQL = " UPDATE CustomersGLParametersYear set ChartOfAccountsOut=NULL where ChartOfAccountsOut NOT IN (select Oid from HCategory);" +
|
|
" UPDATE CustomersGLParametersYear set ChartOfAccountsIn=NULL where ChartOfAccountsIn NOT IN (select Oid from HCategory);" +
|
|
" UPDATE CustomersGLParametersYear set ChartOfAccountsNo=NULL where ChartOfAccountsNo NOT IN (select Oid from HCategory);";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_LiquidAssets_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from LiquidAssets t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM LiquidAssets WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO LiquidAssets (Oid,ShortName,Description,CustomerFrom,LiquidAssetsType,Active,CurrencyName,IsRounding," +
|
|
" NoPrint,NoDocumentNumber,NoTimeCheck)" +
|
|
" SELECT @Oid,@ShortName,@Description,@CustomerFrom,@LiquidAssetsType,@Active,@CurrencyName,@IsRounding," +
|
|
" @NoPrint,@NoDocumentNumber,@NoTimeCheck;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName", DbType = DbType.String, Value = _SqlDataReader["ShortName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Description", DbType = DbType.String, Value = _SqlDataReader["Description"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@CustomerFrom", DbType = DbType.Guid, Value = _SqlDataReader["CustomerFrom"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@LiquidAssetsType", DbType = DbType.String, Value = _SqlDataReader["LiquidAssetsType"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Active", DbType = DbType.Boolean, Value = _SqlDataReader["Active"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@CurrencyName", DbType = DbType.Guid, Value = _SqlDataReader["CurrencyName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsRounding", DbType = DbType.Boolean, Value = _SqlDataReader["IsRounding"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@NoPrint", DbType = DbType.Boolean, Value = _SqlDataReader["NoPrint"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@NoDocumentNumber", DbType = DbType.Boolean, Value = _SqlDataReader["NoDocumentNumber"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@NoTimeCheck", DbType = DbType.Boolean, Value = _SqlDataReader["NoTimeCheck"] });
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_CurrencyName_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from CurrencyName t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM CurrencyName WHERE ShortName=@Shortname";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Shortname", DbType = DbType.Guid, Value = _SqlDataReader["ShortName"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO CurrencyName (Oid,ShortName)" +
|
|
" SELECT @Oid,@ShortName,@Description;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName", DbType = DbType.String, Value = _SqlDataReader["ShortName"] });
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" UPDATE CurrencyName SET Oid=@Oid" +
|
|
" WHERE ShortName=@ShortName";
|
|
using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName", DbType = DbType.String, Value = _SqlDataReader["ShortName"] });
|
|
|
|
_MySqlCommand_Update.ExecuteNonQuery();
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_PaymentOption_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
|
|
string _SQL = " select t1.* " +
|
|
" from PaymentOption t1 " +
|
|
" WHERE t1.GCRecord is null";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM PaymentOption WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO PaymentOption (Oid,ShortName,Description,PayMode,PayDay,DefaultForBusinnes,Shortname_GB,Description_GB," +
|
|
" DatePaymentMode,IsControlling_InCash,Controlling_InCash)" +
|
|
" SELECT @Oid,@ShortName,@Description,@PayMode,@PayDay,@DefaultForBusinnes,@ShortName_GB,@Description_GB," +
|
|
" @DatePaymentMode,@IsControlling_InCash,@Controlling_InCash;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName", DbType = DbType.String, Value = _SqlDataReader["ShortName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Description", DbType = DbType.String, Value = _SqlDataReader["Description"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@PayMode", DbType = DbType.Int64, Value = _SqlDataReader["PayMode"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@PayDay", DbType = DbType.Int64, Value = _SqlDataReader["PayDay"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@DefaultForBusinnes", DbType = DbType.Boolean, Value = _SqlDataReader["DefaultForBusinnes"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ShortName_GB", DbType = DbType.String, Value = _SqlDataReader["ShortName_GB"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Description_GB", DbType = DbType.String, Value = _SqlDataReader["Description_GB"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@DatePaymentMode", DbType = DbType.Int64, Value = _SqlDataReader["DatePaymentMode"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@IsControlling_InCash", DbType = DbType.Boolean, Value = _SqlDataReader["IsControlling_InCash"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Controlling_InCash", DbType = DbType.Guid, Value = _SqlDataReader["Controlling_InCash"] });
|
|
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private bool IsNumeric(object Expression, out double retNum)
|
|
{
|
|
bool isNum = Double.TryParse(Convert.ToString(Expression), NumberStyles.Any, NumberFormatInfo.InvariantInfo, out retNum);
|
|
return isNum;
|
|
}
|
|
|
|
private void aMigrateInvoiceOut_Execute(object sender, SimpleActionExecuteEventArgs e)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
XPObjectSpace _ocur = View.ObjectSpace as XPObjectSpace;
|
|
Sync_Invoice_Out_By_Date_MSSQL_MySQL(_ocur);
|
|
}
|
|
private void aMigrateInvoiceIn_Execute(object sender, SimpleActionExecuteEventArgs e)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
XPObjectSpace _ocur = View.ObjectSpace as XPObjectSpace;
|
|
Sync_Invoice_In_By_Date_MSSQL_MySQL(_ocur);
|
|
}
|
|
|
|
#region Vevői számlák
|
|
private void Sync_Invoice_Out_By_Date_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
WaitFormClass _WaitFormClass = new WaitFormClass();
|
|
UnitOfWork _uow_log = new UnitOfWork(_ocur.Session.DataLayer);
|
|
User _CurrentUser = _uow_log.GetObjectByKey<User>(SecuritySystem.CurrentUserId);
|
|
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
string _SQL = "";
|
|
object _Count = null;
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
_SQL = string.Format(" select count(*) FROM InvoiceHeader t1 WHERE DateExecution>='{0}' AND t1.DateExecution <='{1}' AND t1.GCRecord IS NULL AND t1.IsEditable<>1 AND ISNULL(t1.DocumentNumber,'')<>''",
|
|
_MigrationParameters.DateFrom.Date.ToString("yyyy-MM-dd"), _MigrationParameters.DateTo.Date.ToString("yyyy-MM-dd"));
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
_Count = _SqlCommand.ExecuteScalar();
|
|
}
|
|
|
|
if (_Count != null)
|
|
{
|
|
_SQL = string.Format(" select t1.Oid FROM InvoiceHeader t1 WHERE DateExecution>='{0}' AND t1.DateExecution <='{1}' AND t1.GCRecord IS NULL AND t1.IsEditable<>1 AND ISNULL(t1.DocumentNumber,'')<>''",
|
|
_MigrationParameters.DateFrom.Date.ToString("yyyy-MM-dd"), _MigrationParameters.DateTo.Date.ToString("yyyy-MM-dd"));
|
|
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
_WaitFormClass.Initwork(1, 1, (int)_Count);
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
try
|
|
{
|
|
this.Sync_Invoice_Out_MSSQL_MySQL(_ocur, _SqlDataReader["Oid"].ToString().Trim());
|
|
_WaitFormClass.DoWork();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR INVOICE OUT : InvoiceHeader {0}", _SqlDataReader["Oid"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
_WaitFormClass.FinalWork();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
}
|
|
private void Sync_Invoice_Out_MSSQL_MySQL(XPObjectSpace _ocur, string _InvoiceHeader_OID)
|
|
{
|
|
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
UnitOfWork _uow = new UnitOfWork(_ocur.Session.DataLayer);
|
|
UnitOfWork _uow_log = new UnitOfWork(_ocur.Session.DataLayer);
|
|
User _CurrentUser = _uow_log.GetObjectByKey<User>(SecuritySystem.CurrentUserId);
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=true;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
GLAccounts _GLAccounts = null;
|
|
GLRows _GLRows = null;
|
|
|
|
/// Számla fejléc adatok rögzítése
|
|
string _SQL = string.Format(" SELECT t1.Oid,t1.DocumentNumber,t1.PaymentOption,t1.ConnectInfo,t1.CustomersFrom,t1.Customers,t1.DateCreated,t1.DateExecution,t1.DatePayment,t1.TotalBruttoHUF,t1.TotalBruttoDEV," +
|
|
" t1.CurrencyName" +
|
|
" FROM InvoiceHeader t1 " +
|
|
" WHERE t1.GCRecord is null AND t1.Oid='{0}'", _InvoiceHeader_OID);
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
using (SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader())
|
|
{
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
this.Sync_Party_Customers_One_MSSQL_MySQL(_ocur, _SqlDataReader["Customers"].ToString().Trim());
|
|
|
|
try
|
|
{
|
|
|
|
|
|
_GLAccounts = _uow.FindObject<GLAccounts>(CriteriaOperator.Parse("Customers.Oid = ? AND CustomersFrom.Oid = ? AND PartnerType = 0 AND DocumentNumber = ? AND ConnectInfo = ?",
|
|
_SqlDataReader["Customers"], _SqlDataReader["CustomersFrom"], _SqlDataReader["DocumentNumber"], _SqlDataReader["ConnectInfo"]));
|
|
if (_GLAccounts == null)
|
|
{
|
|
_GLAccounts = new GLAccounts(_uow);
|
|
}
|
|
|
|
_GLAccounts.PartnerType = ePartnerType.eReceivables;
|
|
_GLAccounts.DocumentNumber = _SqlDataReader["DocumentNumber"].ToString().Trim();
|
|
_GLAccounts.ConnectInfo = _SqlDataReader["ConnectInfo"].ToString().Trim();
|
|
_GLAccounts.Customers = _uow.GetObjectByKey<Customers>(_SqlDataReader["Customers"]);
|
|
_GLAccounts.CustomersFrom = _uow.GetObjectByKey<CustomersFrom>(_SqlDataReader["CustomersFrom"]);
|
|
_GLAccounts.CurrencyName = _uow.GetObjectByKey<CurrencyName>(_SqlDataReader["CurrencyName"]);
|
|
_GLAccounts.IsEditable = false;
|
|
_GLAccounts.DateCreated = (DateTime)_SqlDataReader["DateCreated"];
|
|
_GLAccounts.DateExecution = (DateTime)_SqlDataReader["DateExecution"];
|
|
_GLAccounts.DatePayment = (DateTime)_SqlDataReader["DatePayment"];
|
|
_GLAccounts.AmountHUFBrutto = (double)_SqlDataReader["TotalBruttoHUF"];
|
|
_GLAccounts.AmountDEVBrutto = (double)_SqlDataReader["TotalBruttoDEV"];
|
|
_GLAccounts.PaymentOption = _uow.GetObjectByKey<PaymentOption>(_SqlDataReader["PaymentOption"]);
|
|
_uow.CommitChanges();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR GLAccounts: {0}", _SqlDataReader["DocumentNumber"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
_uow.ExecuteNonQuery(string.Format("DELETE FROM GLDetails WHERE GLRows IN (SELECT Oid FROM GLRows WHERE GLHeader = '{0}')", _GLAccounts.Oid.ToString()));
|
|
_uow.ExecuteNonQuery(string.Format("DELETE FROM GLRows WHERE GLHeader = '{0}'", _GLAccounts.Oid.ToString()));
|
|
/// Számla nettó sorok rögzítése
|
|
_SQL = string.Format(" SELECT sum(t1.SumPriceHUF) AS SumPriceHUF,sum(t1.SumPriceDEV) AS SumPriceDEV,t1.VAT,t1.Controlling,t3.Name,Max(t5.ChartOfAccountsOut) AS ChartOfAccountsT, " +
|
|
" MAX(t7.ChartOfAccounts) AS ChartOfAccountsK,Max(t4.DocumentNumber) AS DocumentNumber " +
|
|
|
|
" FROM InvoiceRows t1 left join Controlling t2 on " +
|
|
" t1.Controlling = t2.Oid left join HCategory t3 on " +
|
|
" t2.Oid = t3.Oid LEFT JOIN InvoiceHeader t4 on" +
|
|
" t1.InvoiceHeader = t4.Oid LEFT JOIN Customers t6 ON " +
|
|
" t4.Customers = t6.Oid LEFT JOIN CustomersGLParametersYear t5 on" +
|
|
" t5.AccountYear=Year(t4.DateExecution) AND t6.CustomersGLParameters = t5.CustomersGLParameters" +
|
|
" LEFT JOIN ControllingYear t7 ON" +
|
|
" t1.Controlling=t7.Controlling AND t7.AccountYear=Year(t4.DateExecution)" +
|
|
|
|
" WHERE t1.InvoiceHeader ='{0}' AND t1.GCRecord IS NULL AND t7.GCRecord IS NULL AND t5.GCRecord IS NULL" +
|
|
" GROUP BY t1.Controlling,t3.Name,t1.VAT", _InvoiceHeader_OID);
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
using (SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader())
|
|
{
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
try
|
|
{
|
|
_GLRows = new GLRows(_uow);
|
|
_GLRows.GLHeader = _GLAccounts;
|
|
_GLRows.NoteRows = "ÉRTÉKESÍTÉS NETTÓ";
|
|
_GLRows.DebitChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsT"]);
|
|
_GLRows.CreditChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsK"]);
|
|
_GLRows.AmountHUF = (double)_SqlDataReader["SumPriceHUF"];
|
|
_GLRows.AmountDEV = (double)_SqlDataReader["SumPriceDEV"];
|
|
_GLRows.VAT = _uow.GetObjectByKey<VAT>(_SqlDataReader["VAT"]);
|
|
_GLRows.GLRowsTypeVAT = eGLRowsTypeVAT.eNettoRow;
|
|
_GLRows.GLRowsType = eGLRowsType.eNormal;
|
|
_GLRows.PartnerType = ePartnerType.eReceivables;
|
|
_GLRows.Customer = _GLAccounts.Customers;
|
|
_GLRows.ConnectInfo = _GLAccounts.ConnectInfo;
|
|
_GLRows.DateVAT = _GLAccounts.DateExecution;
|
|
_GLRows.DateExecution = _GLAccounts.DateExecution;
|
|
_uow.CommitChanges();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR GLRows Netto: {0}", _SqlDataReader["DocumentNumber"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
/// Számla ÁFA rögzítése ...
|
|
_SQL = string.Format(" SELECT t1.VAT,t4.ChartOfAccountsOut As ChartOfAccountsT,t5.ChartOfAccountsOut As ChartOfAccountsK," +
|
|
" t1.AmountBruttoDEV,t1.AmountBruttoHUF,t1.AmountVATDEV,t1.AmountVATHUF,t1.AmountNettoHUF,t1.AmountNettoDEV" +
|
|
" FROM InvoiceVATRows t1 join InvoiceHeader t2 ON" +
|
|
" t1.InvoiceHeader = t2.Oid LEFT JOIN Customers t3 ON" +
|
|
" t2.Customers=t3.Oid LEFT JOIN CustomersGLParametersYear t4 ON" +
|
|
" Year(t2.DateExecution) = t4.AccountYear AND t4.CustomersGLParameters = t3.CustomersGLParameters LEFT JOIN VATYear t5 on" +
|
|
" Year(t2.DateExecution) = t5.AccountYear AND t1.VAT=t5.VAT" +
|
|
" WHERE t2.Oid = '{0}' AND t1.GCRecord IS NULL", _InvoiceHeader_OID);
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
using (SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader())
|
|
{
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
if ((double)_SqlDataReader["AmountVATHUF"] > 0.00)
|
|
{
|
|
try
|
|
{
|
|
|
|
|
|
_GLRows = new GLRows(_uow);
|
|
_GLRows.GLHeader = _GLAccounts;
|
|
_GLRows.NoteRows = "ÉRTÉKESÍTÉS ÁFA";
|
|
_GLRows.DebitChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsT"]);
|
|
_GLRows.CreditChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsK"]);
|
|
_GLRows.AmountHUF = (double)_SqlDataReader["AmountVATHUF"];
|
|
_GLRows.AmountDEV = (double)_SqlDataReader["AmountVATDEV"];
|
|
_GLRows.VAT = _uow.GetObjectByKey<VAT>(_SqlDataReader["VAT"]);
|
|
_GLRows.GLRowsTypeVAT = eGLRowsTypeVAT.eVATRow;
|
|
_GLRows.GLRowsType = eGLRowsType.eNormal;
|
|
_GLRows.PartnerType = ePartnerType.eReceivables;
|
|
_GLRows.Customer = _GLAccounts.Customers;
|
|
_GLRows.ConnectInfo = _GLAccounts.ConnectInfo;
|
|
_GLRows.DateVAT = _GLAccounts.DateExecution;
|
|
_GLRows.DateExecution = _GLAccounts.DateExecution;
|
|
_uow.CommitChanges();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR GLRows VAT: {0}", _SqlDataReader["DocumentNumber"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
GLFunctions.ReconfigurePCI(_uow, _GLAccounts.CustomersFrom, _GLAccounts.Customers, _GLAccounts.PartnerType, _GLAccounts.ConnectInfo);
|
|
_uow.CommitChanges();
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region Partner szinkronizálása
|
|
private void Sync_Party_CustomersFrom_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
int _ObjectType_OID = GetObjectTypeOID<CustomersFrom>(_ocur);
|
|
|
|
string _SQL = " SELECT t1.Oid,t1.ObjectType, " +
|
|
" t2.FullName,t2.Profile,t2.Email,t2.WebSite,t2.Description,t2.Name," +
|
|
" t3.CustomerNumber,t3.VATNumber,t3.VATNumberEU,t3.CustomersGLParameters " +
|
|
" from Party t1 JOIN Organization t2 on" +
|
|
" t1.Oid=t2.Oid JOIN Customers t3 on" +
|
|
" t1.Oid=t3.Oid JOIN CustomersFrom t4 on" +
|
|
" t1.Oid=t4.Oid" +
|
|
" where t1.GCRecord is null AND t1.Oid in (SELECT Oid FROM CustomersFrom)";
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM Party WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " START TRANSACTION; " +
|
|
" SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO Party (Oid,ObjectType)" +
|
|
" SELECT @Oid,@ObjectType;" +
|
|
" INSERT INTO Organization (Oid,FullName,Profile,Email,WebSite,Description,Name)" +
|
|
" SELECT @Oid,@FullName,@Profile,@Email,@WebSite,@Description,@Name;" +
|
|
" INSERT INTO Customers (Oid,CustomerNumber,VATNumber,VATNumberEU,CustomersGLParameters)" +
|
|
" SELECT @Oid,@CustomerNumber,@VATNumber,@VATNumberEU,@CustomersGLParameters;" +
|
|
" INSERT INTO CustomersFrom (Oid)" +
|
|
" SELECT @Oid;" +
|
|
" COMMIT;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ObjectType", DbType = DbType.Int64, Value = _ObjectType_OID });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@FullName", DbType = DbType.String, Value = _SqlDataReader["FullName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Profile", DbType = DbType.String, Value = _SqlDataReader["Profile"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Email", DbType = DbType.String, Value = _SqlDataReader["Email"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@WebSite", DbType = DbType.String, Value = _SqlDataReader["WebSite"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Description", DbType = DbType.String, Value = _SqlDataReader["Description"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@CustomerNumber", DbType = DbType.String, Value = _SqlDataReader["CustomerNumber"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@VATNumber", DbType = DbType.String, Value = _SqlDataReader["VATNumber"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@VATNumberEU", DbType = DbType.String, Value = _SqlDataReader["VATNumberEU"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@CustomersGLParameters", DbType = DbType.Guid, Value = _SqlDataReader["CustomersGLParameters"] });
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_Party_Customers_One_MSSQL_MySQL(XPObjectSpace _ocur, string _Customers_OID)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
int _ObjectType_OID = GetObjectTypeOID<Customers>(_ocur);
|
|
|
|
string _SQL = string.Format(" SELECT t1.Oid,t1.ObjectType, " +
|
|
" t2.FullName,t2.Profile,t2.Email,t2.WebSite,t2.Description,t2.Name," +
|
|
" t3.CustomerNumber,t3.VATNumber,t3.VATNumberEU,t3.CustomersGLParameters " +
|
|
" from Party t1 JOIN Organization t2 on" +
|
|
" t1.Oid=t2.Oid JOIN Customers t3 on" +
|
|
" t1.Oid=t3.Oid" +
|
|
" where t1.GCRecord is null AND t1.Oid in (SELECT Oid FROM Customers) AND t1.Oid ='{0}'", _Customers_OID);
|
|
|
|
SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection);
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
string _MySQL = "SELECT count(*) FROM Party WHERE Oid=@Oid";
|
|
using (MySqlCommand _MySqlCommand = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
|
|
object _Found = _MySqlCommand.ExecuteScalar();
|
|
|
|
if (_Found != null)
|
|
{
|
|
double _F;
|
|
IsNumeric(_Found, out _F);
|
|
if (_F == 0)
|
|
{
|
|
|
|
_MySQL = " START TRANSACTION; " +
|
|
" SET FOREIGN_KEY_CHECKS=0;" +
|
|
" INSERT INTO Party (Oid,ObjectType)" +
|
|
" SELECT @Oid,@ObjectType;" +
|
|
" INSERT INTO Organization (Oid,FullName,Profile,Email,WebSite,Description,Name)" +
|
|
" SELECT @Oid,@FullName,@Profile,@Email,@WebSite,@Description,@Name;" +
|
|
" INSERT INTO Customers (Oid,CustomerNumber,VATNumber,VATNumberEU,CustomersGLParameters)" +
|
|
" SELECT @Oid,@CustomerNumber,@VATNumber,@VATNumberEU,@CustomersGLParameters;" +
|
|
" COMMIT;";
|
|
using (MySqlCommand _MySqlCommand_Insert = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
{
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@ObjectType", DbType = DbType.Int64, Value = _ObjectType_OID });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@FullName", DbType = DbType.String, Value = _SqlDataReader["FullName"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Profile", DbType = DbType.String, Value = _SqlDataReader["Profile"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Email", DbType = DbType.String, Value = _SqlDataReader["Email"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@WebSite", DbType = DbType.String, Value = _SqlDataReader["WebSite"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Description", DbType = DbType.String, Value = _SqlDataReader["Description"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@CustomerNumber", DbType = DbType.String, Value = _SqlDataReader["CustomerNumber"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@VATNumber", DbType = DbType.String, Value = _SqlDataReader["VATNumber"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@VATNumberEU", DbType = DbType.String, Value = _SqlDataReader["VATNumberEU"] });
|
|
_MySqlCommand_Insert.Parameters.Add(new MySqlParameter() { ParameterName = "@CustomersGLParameters", DbType = DbType.Guid, Value = _SqlDataReader["CustomersGLParameters"] });
|
|
|
|
_MySqlCommand_Insert.ExecuteNonQuery();
|
|
}
|
|
}
|
|
else // Ha már VAN
|
|
{
|
|
//_MySQL = " SET FOREIGN_KEY_CHECKS=0;" +
|
|
// " UPDATE HCategory SET Name=@Name," +
|
|
// " Parent=@Parent " +
|
|
// " WHERE Oid=@Oid";
|
|
//using (MySqlCommand _MySqlCommand_Update = new MySqlCommand() { CommandText = _MySQL, Connection = _MySqlConnection })
|
|
//{
|
|
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Oid", DbType = DbType.Guid, Value = _SqlDataReader["Oid"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Parent", DbType = DbType.Guid, Value = _SqlDataReader["Parent"] });
|
|
// _MySqlCommand_Update.Parameters.Add(new MySqlParameter() { ParameterName = "@Name", DbType = DbType.String, Value = _SqlDataReader["Name"] });
|
|
|
|
// _MySqlCommand_Update.ExecuteNonQuery();
|
|
//}
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
private void Sync_Address_One_MSSQL_MySQL(XPObjectSpace _ocur, string _Address_OID)
|
|
{
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region Szállítói számlák
|
|
private void Sync_Invoice_In_By_Date_MSSQL_MySQL(XPObjectSpace _ocur)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
WaitFormClass _WaitFormClass = new WaitFormClass();
|
|
UnitOfWork _uow_log = new UnitOfWork(_ocur.Session.DataLayer);
|
|
User _CurrentUser = _uow_log.GetObjectByKey<User>(SecuritySystem.CurrentUserId);
|
|
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=false;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
string _SQL = "";
|
|
object _Count = null;
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
_SQL = string.Format(" select count(*) FROM GLHeader t1 join GLAccounts t2 on t1.Oid = t2.Oid WHERE t1.DateExecution>='{0}' AND t1.DateExecution <='{1}' AND t1.GCRecord IS NULL AND t1.IsEditable=False AND ISNULL(t1.DocumentNumber,'')<>''",
|
|
_MigrationParameters.DateFrom.Date.ToString("yyyy-MM-dd"), _MigrationParameters.DateTo.Date.ToString("yyyy-MM-dd"));
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
_Count = _SqlCommand.ExecuteScalar();
|
|
}
|
|
|
|
if (_Count != null)
|
|
{
|
|
_SQL = string.Format(" SELECT t1.Oid FROM GLHeader t1 join GLAccounts t2 on t1.Oid = t2.Oid " +
|
|
" WHERE t1.DateExecution>='{0}' AND t1.DateExecution <='{1}' AND t1.GCRecord IS NULL AND t1.IsEditable=False AND ISNULL(t1.DocumentNumber,'')<>''",
|
|
_MigrationParameters.DateFrom.Date.ToString("yyyy-MM-dd"), _MigrationParameters.DateTo.Date.ToString("yyyy-MM-dd"));
|
|
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader();
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
_WaitFormClass.Initwork(1, 1, (int)_Count);
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
try
|
|
{
|
|
this.Sync_Invoice_In_MSSQL_MySQL(_ocur, _SqlDataReader["Oid"].ToString().Trim());
|
|
_WaitFormClass.DoWork();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR INVOICE IN : GLHeader {0}", _SqlDataReader["Oid"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
_WaitFormClass.FinalWork();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
}
|
|
private void Sync_Invoice_In_MSSQL_MySQL(XPObjectSpace _ocur,string _GLHeader_OID)
|
|
{
|
|
MigrationParameters _MigrationParameters = View.SelectedObjects[0] as MigrationParameters;
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=0");
|
|
UnitOfWork _uow = new UnitOfWork(_ocur.Session.DataLayer);
|
|
UnitOfWork _uow_log = new UnitOfWork(_ocur.Session.DataLayer);
|
|
User _CurrentUser = _uow_log.GetObjectByKey<User>(SecuritySystem.CurrentUserId);
|
|
string _ConnectionStringMSSQL = string.Format(" User ID={0}; " +
|
|
" Password={1};" +
|
|
" Pooling=true;" +
|
|
" Data Source={2};" +
|
|
" Initial Catalog={3}",
|
|
_MigrationParameters.SQLUser,
|
|
_MigrationParameters.SQLPassword,
|
|
_MigrationParameters.SQLServer,
|
|
_MigrationParameters.SQLDatabase);
|
|
|
|
|
|
|
|
SqlConnection _SqlConnection = new SqlConnection(_ConnectionStringMSSQL);
|
|
MySqlConnection _MySqlConnection = new MySqlConnection(Nuvolar.Module.GeneralFunction.GLOBAL_ConnectionString.Replace("XpoProvider=MySql;", ""));
|
|
|
|
_SqlConnection.Open();
|
|
_MySqlConnection.Open();
|
|
if (_SqlConnection.State == ConnectionState.Open)
|
|
{
|
|
|
|
GLAccounts _GLAccounts = null;
|
|
GLRows _GLRows = null;
|
|
|
|
/// Számla fejléc adatok rögzítése
|
|
string _SQL = string.Format(" SELECT t1.Oid,t1.DocumentNumber,t1.PaymentOption,t1.ConnectInfo,t1.CustomersFrom,t1.Customers,t1.DateCreated,t1.DateExecution,t1.DatePayment,t1.TotalBruttoHUF,t1.TotalBruttoDEV," +
|
|
" t1.CurrencyName" +
|
|
" FROM GLHeader t1 JOIN GLAccounts t2 on t1.Oid=t2.Oid" +
|
|
" WHERE t1.GCRecord is null AND t1.Oid='{0}' AND t1.IsEditable=False", _GLHeader_OID);
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
using (SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader())
|
|
{
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
this.Sync_Party_Customers_One_MSSQL_MySQL(_ocur, _SqlDataReader["Customers"].ToString().Trim());
|
|
|
|
try
|
|
{
|
|
|
|
|
|
_GLAccounts = _uow.FindObject<GLAccounts>(CriteriaOperator.Parse("Customers.Oid = ? AND CustomersFrom.Oid = ? AND PartnerType = 0 AND DocumentNumber = ? AND ConnectInfo = ?",
|
|
_SqlDataReader["Customers"], _SqlDataReader["CustomersFrom"], _SqlDataReader["DocumentNumber"], _SqlDataReader["ConnectInfo"]));
|
|
if (_GLAccounts == null)
|
|
{
|
|
_GLAccounts = new GLAccounts(_uow);
|
|
}
|
|
|
|
_GLAccounts.PartnerType = ePartnerType.eReceivables;
|
|
_GLAccounts.DocumentNumber = _SqlDataReader["DocumentNumber"].ToString().Trim();
|
|
_GLAccounts.ConnectInfo = _SqlDataReader["ConnectInfo"].ToString().Trim();
|
|
_GLAccounts.Customers = _uow.GetObjectByKey<Customers>(_SqlDataReader["Customers"]);
|
|
_GLAccounts.CustomersFrom = _uow.GetObjectByKey<CustomersFrom>(_SqlDataReader["CustomersFrom"]);
|
|
_GLAccounts.CurrencyName = _uow.GetObjectByKey<CurrencyName>(_SqlDataReader["CurrencyName"]);
|
|
_GLAccounts.IsEditable = false;
|
|
_GLAccounts.DateCreated = (DateTime)_SqlDataReader["DateCreated"];
|
|
_GLAccounts.DateExecution = (DateTime)_SqlDataReader["DateExecution"];
|
|
_GLAccounts.DatePayment = (DateTime)_SqlDataReader["DatePayment"];
|
|
_GLAccounts.AmountHUFBrutto = (double)_SqlDataReader["TotalBruttoHUF"];
|
|
_GLAccounts.AmountDEVBrutto = (double)_SqlDataReader["TotalBruttoDEV"];
|
|
_GLAccounts.PaymentOption = _uow.GetObjectByKey<PaymentOption>(_SqlDataReader["PaymentOption"]);
|
|
_uow.CommitChanges();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR GLAccounts: {0}", _SqlDataReader["DocumentNumber"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
_uow.ExecuteNonQuery(string.Format("DELETE FROM GLDetails WHERE GLRows IN (SELECT Oid FROM GLRows WHERE GLHeader = '{0}')", _GLAccounts.Oid.ToString()));
|
|
_uow.ExecuteNonQuery(string.Format("DELETE FROM GLRows WHERE GLHeader = '{0}'", _GLAccounts.Oid.ToString()));
|
|
/// Számla nettó sorok rögzítése
|
|
_SQL = string.Format(" SELECT sum(t1.SumPriceHUF) AS SumPriceHUF,sum(t1.SumPriceDEV) AS SumPriceDEV,t1.VAT,t1.Controlling,t3.Name,Max(t5.ChartOfAccountsOut) AS ChartOfAccountsT, " +
|
|
" MAX(t7.ChartOfAccounts) AS ChartOfAccountsK,Max(t4.DocumentNumber) AS DocumentNumber " +
|
|
|
|
" FROM InvoiceRows t1 left join Controlling t2 on " +
|
|
" t1.Controlling = t2.Oid left join HCategory t3 on " +
|
|
" t2.Oid = t3.Oid LEFT JOIN InvoiceHeader t4 on" +
|
|
" t1.InvoiceHeader = t4.Oid LEFT JOIN Customers t6 ON " +
|
|
" t4.Customers = t6.Oid LEFT JOIN CustomersGLParametersYear t5 on" +
|
|
" t5.AccountYear=Year(t4.DateExecution) AND t6.CustomersGLParameters = t5.CustomersGLParameters" +
|
|
" LEFT JOIN ControllingYear t7 ON" +
|
|
" t1.Controlling=t7.Controlling AND t7.AccountYear=Year(t4.DateExecution)" +
|
|
|
|
" WHERE t1.InvoiceHeader ='{0}' AND t1.GCRecord IS NULL AND t7.GCRecord IS NULL AND t5.GCRecord IS NULL" +
|
|
" GROUP BY t1.Controlling,t3.Name,t1.VAT", _GLHeader_OID);
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
using (SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader())
|
|
{
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
try
|
|
{
|
|
_GLRows = new GLRows(_uow);
|
|
_GLRows.GLHeader = _GLAccounts;
|
|
_GLRows.NoteRows = "BESZERZÉS NETTÓ";
|
|
_GLRows.DebitChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsT"]);
|
|
_GLRows.CreditChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsK"]);
|
|
_GLRows.AmountHUF = (double)_SqlDataReader["SumPriceHUF"];
|
|
_GLRows.AmountDEV = (double)_SqlDataReader["SumPriceDEV"];
|
|
_GLRows.VAT = _uow.GetObjectByKey<VAT>(_SqlDataReader["VAT"]);
|
|
_GLRows.GLRowsTypeVAT = eGLRowsTypeVAT.eNettoRow;
|
|
_GLRows.GLRowsType = eGLRowsType.eNormal;
|
|
_GLRows.PartnerType = ePartnerType.eReceivables;
|
|
_GLRows.Customer = _GLAccounts.Customers;
|
|
_GLRows.ConnectInfo = _GLAccounts.ConnectInfo;
|
|
_GLRows.DateVAT = _GLAccounts.DateExecution;
|
|
_GLRows.DateExecution = _GLAccounts.DateExecution;
|
|
_uow.CommitChanges();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR GLRows Netto: {0}", _SqlDataReader["DocumentNumber"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
/// Számla ÁFA rögzítése ...
|
|
_SQL = string.Format(" SELECT t1.VAT,t4.ChartOfAccountsOut As ChartOfAccountsT,t5.ChartOfAccountsOut As ChartOfAccountsK," +
|
|
" t1.AmountBruttoDEV,t1.AmountBruttoHUF,t1.AmountVATDEV,t1.AmountVATHUF,t1.AmountNettoHUF,t1.AmountNettoDEV" +
|
|
" FROM InvoiceVATRows t1 join InvoiceHeader t2 ON" +
|
|
" t1.InvoiceHeader = t2.Oid LEFT JOIN Customers t3 ON" +
|
|
" t2.Customers=t3.Oid LEFT JOIN CustomersGLParametersYear t4 ON" +
|
|
" Year(t2.DateExecution) = t4.AccountYear AND t4.CustomersGLParameters = t3.CustomersGLParameters LEFT JOIN VATYear t5 on" +
|
|
" Year(t2.DateExecution) = t5.AccountYear AND t1.VAT=t5.VAT" +
|
|
" WHERE t2.Oid = '{0}' AND t1.GCRecord IS NULL", _GLHeader_OID);
|
|
using (SqlCommand _SqlCommand = new SqlCommand(_SQL, _SqlConnection))
|
|
{
|
|
using (SqlDataReader _SqlDataReader = _SqlCommand.ExecuteReader())
|
|
{
|
|
|
|
if (_SqlDataReader.HasRows)
|
|
{
|
|
while (_SqlDataReader.Read())
|
|
{
|
|
if ((double)_SqlDataReader["AmountVATHUF"] > 0.00)
|
|
{
|
|
try
|
|
{
|
|
|
|
|
|
_GLRows = new GLRows(_uow);
|
|
_GLRows.GLHeader = _GLAccounts;
|
|
_GLRows.NoteRows = "BESZERZÉS ÁFA";
|
|
_GLRows.DebitChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsT"]);
|
|
_GLRows.CreditChartOfAccounts = _uow.GetObjectByKey<ChartOfAccounts>(_SqlDataReader["ChartOfAccountsK"]);
|
|
_GLRows.AmountHUF = (double)_SqlDataReader["AmountVATHUF"];
|
|
_GLRows.AmountDEV = (double)_SqlDataReader["AmountVATDEV"];
|
|
_GLRows.VAT = _uow.GetObjectByKey<VAT>(_SqlDataReader["VAT"]);
|
|
_GLRows.GLRowsTypeVAT = eGLRowsTypeVAT.eVATRow;
|
|
_GLRows.GLRowsType = eGLRowsType.eNormal;
|
|
_GLRows.PartnerType = ePartnerType.eReceivables;
|
|
_GLRows.Customer = _GLAccounts.Customers;
|
|
_GLRows.ConnectInfo = _GLAccounts.ConnectInfo;
|
|
_GLRows.DateVAT = _GLAccounts.DateExecution;
|
|
_GLRows.DateExecution = _GLAccounts.DateExecution;
|
|
_uow.CommitChanges();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
MigrationLog _MigrationLog = new MigrationLog(_uow_log);
|
|
_MigrationLog.UserCreated = _CurrentUser;
|
|
_MigrationLog.ShortName = string.Format("ERROR GLRows VAT: {0}", _SqlDataReader["DocumentNumber"].ToString().Trim());
|
|
_MigrationLog.DateCreated = DateTime.Now;
|
|
_MigrationLog.Description = ex.Message;
|
|
_uow_log.CommitChanges();
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
GLFunctions.ReconfigurePCI(_uow, _GLAccounts.CustomersFrom, _GLAccounts.Customers, _GLAccounts.PartnerType, _GLAccounts.ConnectInfo);
|
|
_uow.CommitChanges();
|
|
}
|
|
_SqlConnection.Close();
|
|
_MySqlConnection.Close();
|
|
_ocur.Session.ExecuteNonQuery("SET FOREIGN_KEY_CHECKS=1");
|
|
}
|
|
#endregion
|
|
}
|
|
}
|