public static void CreditNoteTransactionTableDataMigration()
{
int index = 0;
try
{
GetMasterClientIdListForCreditNotesTransaction();
using (MySqlConnection connection = new MySqlConnection(cs1))
{
connection.Open();
using (MySqlCommand command = new MySqlCommand("SELECT DISTINCT TransactionID FROM creditnotestransaction ORDER BY TransactionID ASC", connection))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
datas = "";
TransactionId = reader.GetValue(0);
MasterDBClientId = clientIdListMasterDatabase.ElementAt(index++);
using (MySqlConnection connection2 = new MySqlConnection(cs1))
{
connection2.Open();
using (MySqlCommand command1 = new MySqlCommand("SELECT creditnotestransaction.TransactionType,creditnotestransaction.ReferenceNumber,creditnotestransaction.PaymentType,creditnotestransaction.Notes,transaction.Date,transaction.GrossAmount,transaction.VATReportingDate,transaction.ReferenceNo,transaction.PaymentDate,transaction.ChequeNumber,transaction.BankName,transaction.AcNumber,transaction.CCType,transaction.IsEC,transaction.IsPaid FROM creditnotestransaction LEFT JOIN transaction ON creditnotestransaction.TransactionID = transaction.TransactionID Where creditnotestransaction.TransactionID =" + TransactionId, connection2))
{
using (MySqlDataReader reader1 = command1.ExecuteReader())
{
while (reader1.Read())
{
datas = "";
for (int i = 0; i < reader1.FieldCount; i++)
{
data = reader1.GetValue(i);
if (datas != "")
{
datas += ",";
}
if (data.GetType() == typeof(DateTime))
{
// Changing the format of date
var date = (DateTime)data;
datas += '"' + date.ToString("yyyy-MM-dd hh:mm:ss") + '"';
}
else if (data.ToString() == "Purchases")
{
data = "Purchase";
datas += '"' + Convert.ToString(data) + '"';
}
else if (data.ToString() == "Expenses")
{
data = "Expense";
datas += '"' + Convert.ToString(data) + '"';
}
else
{
datas += '"' + Convert.ToString(data) + '"';
}
}
var reportTypeID = (int)AppSettingsUtil.REPORT_TYPE.CREDITNOTE_REPORT;
var q = "INSERT INTO creditnote(TransactionType,CreditNoteNumber,PaymentMethod,Notes,CreditNoteDate,TotalGrossAmount,VATReportingDate,ReferenceNumber,PaymentDate,ChequeNumber,Bank,AccountNumber,CreditCardType,IsEC,IsPaid,AccountantID,ReportTypeID,ScannedInvoiceID) Values(" + datas + "," + AccountantId + "," + reportTypeID + "," + 0 + "); ";
RunQueryClientDatabase(q);
string cs5 = cs2 + MasterDBClientId;
string cs6 = cs2 + MasterDBClientId;
using (MySqlConnection connection4 = new MySqlConnection(cs5))
{
connection4.Open();
using (MySqlCommand command4 = new MySqlCommand("SELECT CreditNoteID FROM creditnote Where SupplierID IS NULL AND CustomerID IS NULL ORDER BY CreditNoteID ASC", connection4))
{
using (MySqlDataReader reader4 = command4.ExecuteReader())
{
while (reader4.Read())
{
datas = "";
data = reader4.GetValue(0);
if (datas != "")
{
datas += ",";
}
datas += '"' + Convert.ToString(data) + '"';
CreditNoteId = datas;
var encodedID = AppSettingsUtil.Encode(Convert.ToString(CreditNoteId));
var encodedIDValue = '"' + encodedID + '"';
using (MySqlConnection connection3 = new MySqlConnection(cs1))
{
connection3.Open();
using (MySqlCommand command3 = new MySqlCommand("SELECT CustomerSupplierID FROM creditnotestransaction Where TransactionID =" + TransactionId + " AND (TransactionType = 'Purchases' OR TransactionType = 'Expenses')", connection3))
{
using (MySqlDataReader reader3 = command3.ExecuteReader())
{
while (reader3.Read())
{
datas = "";
data = reader3.GetValue(0);
if (datas != "")
{
datas += ",";
}
datas += '"' + Convert.ToString(data) + '"';
SourceDBSupplierId = datas;
using (MySqlConnection connection8 = new MySqlConnection(cs6))
{
connection8.Open();
using (MySqlCommand command8 = new MySqlCommand("SELECT ClientSupplierID FROM temporarysupplieridassociation Where SourceSupplierID =" + SourceDBSupplierId, connection8))
{
using (MySqlDataReader reader8 = command8.ExecuteReader())
{
while (reader8.Read())
{
datas = "";
data = reader8.GetValue(0);
datas += '"' + Convert.ToString(data) + '"';
ClientDBSupplierId = datas;
var q3 = "UPDATE creditnote set SupplierID = " + ClientDBSupplierId + "Where CreditNoteID = " + CreditNoteId + " ";
RunQueryClientDatabase(q3);
var q4 = "UPDATE creditnote set EncodedID = " + encodedIDValue + " Where CreditNoteID = " + CreditNoteId + " ";
RunQueryClientDatabase(q4);
}
}
}
}
}
}
}
}
using (MySqlConnection connection5 = new MySqlConnection(cs1))
{
connection5.Open();
using (MySqlCommand command5 = new MySqlCommand("SELECT CustomerSupplierID FROM creditnotestransaction Where TransactionID =" + TransactionId + " AND TransactionType = 'Sales' ", connection5))
{
using (MySqlDataReader reader5 = command5.ExecuteReader())
{
while (reader5.Read())
{
datas = "";
data = reader5.GetValue(0);
if (datas != "")
{
datas += ",";
}
datas += '"' + Convert.ToString(data) + '"';
SourceDBCustomerId = datas;
using (MySqlConnection connection9 = new MySqlConnection(cs6))
{
connection9.Open();
using (MySqlCommand command9 = new MySqlCommand("SELECT ClientCustomerID FROM temporarycustomeridassociation Where SourceCustomerID =" + SourceDBCustomerId, connection9))
{
using (MySqlDataReader reader9 = command9.ExecuteReader())
{
while (reader9.Read())
{
datas = "";
data = reader9.GetValue(0);
datas += '"' + Convert.ToString(data) + '"';
ClientDBCustomerId = datas;
var q3 = "UPDATE creditnote set SupplierID = " + ClientDBCustomerId + "Where CreditNoteID = " + CreditNoteId + " ";
RunQueryClientDatabase(q3);
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
TransactionTableDataMigration(TransactionId, MasterDBClientId);
TransactionHistoryTableDataMigration(TransactionId, MasterDBClientId);
TransactionVATTableDataMigration(TransactionId, MasterDBClientId);
TransactionVATHistoryTableDataMigration(TransactionId, MasterDBClientId);
CreditNoteVATDetailsTableDataEntry(TransactionId, MasterDBClientId);
}
}
}
}
}
catch (Exception ex)
{
Program.ErrorLogging(ex);
}
}
public static void PurchaseTransactionTableDataMigration()
{
int index = 0;
try
{
GetMasterClientIdListForPurchaseTransaction();
using (MySqlConnection connection = new MySqlConnection(cs1))
{
connection.Open();
using (MySqlCommand command = new MySqlCommand("SELECT DISTINCT TransactionID FROM purchasetransaction ORDER BY TransactionID ASC", connection))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
datas = "";
TransactionId = reader.GetValue(0);
MasterDBClientId = clientIdListMasterDatabase.ElementAt(index++);
using (MySqlConnection connection2 = new MySqlConnection(cs1))
{
connection2.Open();
using (MySqlCommand command1 = new MySqlCommand("SELECT purchasetransaction.OrderNumber,purchasetransaction.PurchaseType,purchasetransaction.IsCapitalExpenditure,purchasetransaction.Notes,transaction.Date,transaction.GrossAmount,transaction.VATReportingDate,transaction.ReferenceNo,transaction.IsImport,transaction.PaymentDate,transaction.ChequeNumber,transaction.BankName,transaction.AcNumber,transaction.CCType,transaction.IsEC,transaction.IsPaid FROM purchasetransaction LEFT JOIN transaction ON purchasetransaction.TransactionID = transaction.TransactionID Where purchasetransaction.TransactionID =" + TransactionId, connection2))
{
using (MySqlDataReader reader1 = command1.ExecuteReader())
{
while (reader1.Read())
{
datas = "";
for (int i = 0; i < reader1.FieldCount; i++)
{
data = reader1.GetValue(i);
if (datas != "")
{
datas += ",";
}
if (data.GetType() == typeof(DateTime))
{
// Changing the format of date
var date = (DateTime)data;
datas += '"' + date.ToString("yyyy-MM-dd hh:mm:ss") + '"';
}
else
{
datas += '"' + Convert.ToString(data) + '"';
}
}
object value;
string values;
object info;
string infos;
using (MySqlConnection connection3 = new MySqlConnection(cs1))
{
connection3.Open();
using (MySqlCommand command3 = new MySqlCommand("SELECT SupplierID FROM purchasetransaction Where TransactionID =" + TransactionId, connection3))
{
using (MySqlDataReader reader3 = command3.ExecuteReader())
{
while (reader3.Read())
{
values = "";
value = reader3.GetValue(0);
if (values != "")
{
values += ",";
}
values += '"' + Convert.ToString(value) + '"';
SourceDBSupplierId = values;
string cs20 = cs2 + MasterDBClientId;
using (MySqlConnection connection8 = new MySqlConnection(cs20))
{
connection8.Open();
using (MySqlCommand command8 = new MySqlCommand("SELECT ClientSupplierID FROM temporarysupplieridassociation Where SourceSupplierID =" + SourceDBSupplierId, connection8))
{
using (MySqlDataReader reader8 = command8.ExecuteReader())
{
while (reader8.Read())
{
infos = "";
info = reader8.GetValue(0);
infos += '"' + Convert.ToString(info) + '"';
ClientDBSupplierId = infos;
var reportTypeId = (int)AppSettingsUtil.REPORT_TYPE.DATA_REPORT;
var q = "INSERT INTO purchase(InvoiceNumber,PaymentMethod,IsCapitalExpenditure,Notes,InvoiceDate,TotalGrossAmount,VATReportingDate,ReferenceNumber,IsImport,PaymentDate,ChequeNumber,Bank,AccountNumber,CreditCardType,IsEC,IsPaid,AccountantID,ReportTypeID,SupplierID,ScannedInvoiceID) Values(" + datas + "," + AccountantId + "," + reportTypeId + "," + ClientDBSupplierId + "," + 0 + "); ";
RunQueryClientDatabase(q);
}
}
}
}
}
}
}
}
string cs5 = cs2 + MasterDBClientId;
object purchaseTransactionId;
using (MySqlConnection connection5 = new MySqlConnection(cs1))
{
connection5.Open();
using (MySqlCommand command15 = new MySqlCommand("SELECT PurchaseTransactionID FROM purchasetransaction Where TransactionID =" + TransactionId, connection5))
{
using (MySqlDataReader reader15 = command15.ExecuteReader())
{
while (reader15.Read())
{
purchaseTransactionId = reader15.GetValue(0);
PurchaseTransactionItemTableDataMigration(purchaseTransactionId, TransactionId, MasterDBClientId);
}
}
}
}
object purchaseItemMemoryPurchaseTransactionID;
using (MySqlConnection connection5 = new MySqlConnection(cs1))
{
connection5.Open();
using (MySqlCommand command15 = new MySqlCommand("SELECT PurchaseTransactionID FROM purchasetransaction Where TransactionID =" + TransactionId, connection5))
{
using (MySqlDataReader reader15 = command15.ExecuteReader())
{
while (reader15.Read())
{
purchaseItemMemoryPurchaseTransactionID = reader15.GetValue(0);
PurchaseTransactionItemMemoryTableDataMigration(purchaseItemMemoryPurchaseTransactionID, TransactionId, MasterDBClientId);
}
}
}
}
string cs6 = cs2 + MasterDBClientId;
using (MySqlConnection connection4 = new MySqlConnection(cs5))
{
connection4.Open();
using (MySqlCommand command4 = new MySqlCommand("SELECT PurchaseID FROM purchase ORDER BY PurchaseID DESC LIMIT 1", connection4))
{
using (MySqlDataReader reader4 = command4.ExecuteReader())
{
while (reader4.Read())
{
datas = "";
data = reader4.GetValue(0);
if (datas != "")
{
datas += ",";
}
datas += '"' + Convert.ToString(data) + '"';
PurchaseId = datas;
var encodedID = AppSettingsUtil.Encode(Convert.ToString(PurchaseId));
var encodedIDValue = '"' + Convert.ToString(encodedID) + '"';
var q3 = "UPDATE purchase set EncodedPurchaseID = " + encodedIDValue + " Where PurchaseID = " + PurchaseId + " ";
RunQueryClientDatabase(q3);
}
}
}
}
}
}
}
}
TransactionTableDataMigration(TransactionId, MasterDBClientId);
TransactionHistoryTableDataMigration(TransactionId, MasterDBClientId);
TransactionVATTableDataMigration(TransactionId, MasterDBClientId);
TransactionVATHistoryTableDataMigration(TransactionId, MasterDBClientId);
}
}
}
}
}
catch (Exception ex)
{
Program.ErrorLogging(ex);
}
}