private static Guid GetAccountTypeCID(Guid?AccountID)
{
SqlConnection con = SyncHelper.NewCRMConnection();
SqlCommand com = new SqlCommand();
SqlDataAdapter ad = new SqlDataAdapter(com);
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.AppendLine("SELECT AccountTypeCID FROM Account WHERE AccountID = @AccountID");
com.CommandText = sql.ToString();
com.CommandType = CommandType.Text;
com.Connection = con;
com.CommandTimeout = int.MaxValue;
try
{
con.Open();
com.Parameters.Add(new SqlParameter("@AccountID", AccountID));
DataTable dt = new DataTable();
ad.Fill(dt);
return(new Guid(dt.Rows[0][0].ToString()));
}
catch (Exception ex)
{
throw;
}
finally
{
con.Close();
}
}
internal static bool IsNonMSC(Guid?AccountID)
{
Nullable <Guid> NonMSCAccountTypeCID = SyncHelper.GetCodeMasterID("Non MSC", BOL.AppConst.CodeType.AccountType);
if (NonMSCAccountTypeCID.HasValue)
{
Guid accountTypeCID = GetAccountTypeCID(AccountID);
return(accountTypeCID == NonMSCAccountTypeCID.Value);
}
else
{
return(false);
}
}
private static Guid CreateCodeMaster(string Value, string CodeType)
{
Guid codeMasterID = Guid.NewGuid();
SqlConnection con = SyncHelper.NewCRMConnection();
SqlCommand com = new SqlCommand();
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.AppendLine("INSERT INTO CodeMaster (CodeMasterID, SeqNo, CodeType, CodeName,");
sql.AppendLine("CreatedBy,CreatedByName,ModifiedBy,ModifiedByName,CreatedDate,ModifiedDate) ");
sql.AppendLine("VALUES (@CodeMasterID, 0, @CodeType, @CodeName, ");
sql.AppendLine("@CreatedBy, @CreatedByName, @ModifiedBy, @ModifiedByName,@CreatedDate,@ModifiedDate ) ");
com.CommandText = sql.ToString();
com.CommandType = CommandType.Text;
com.Connection = con;
codeMasterID = Guid.NewGuid();
com.Parameters.Add(new SqlParameter("@CodeMasterID", codeMasterID));
com.Parameters.Add(new SqlParameter("@CodeType", CodeType));
com.Parameters.Add(new SqlParameter("@CodeName", Value));
com.Parameters.Add(new SqlParameter("@CreatedBy", SyncHelper.AdminID));
com.Parameters.Add(new SqlParameter("@CreatedByName", SyncHelper.AdminName));
com.Parameters.Add(new SqlParameter("@ModifiedBy", SyncHelper.AdminID));
com.Parameters.Add(new SqlParameter("@ModifiedByName", SyncHelper.AdminName));
com.Parameters.Add(new SqlParameter("@CreatedDate", DateTime.Now));
com.Parameters.Add(new SqlParameter("@ModifiedDate", DateTime.Now));
try
{
con.Open();
com.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
con.Close();
}
return(codeMasterID);
}
internal static Guid?GetAccountIDByFileID(string FileID)
{
SqlConnection con = SyncHelper.NewCRMConnection();
SqlCommand com = new SqlCommand();
SqlDataAdapter ad = new SqlDataAdapter(com);
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.AppendLine("SELECT AccountID FROM Account WHERE MSCFileID = @FileID");
com.CommandText = sql.ToString();
com.CommandType = CommandType.Text;
com.Connection = con;
com.CommandTimeout = int.MaxValue;
//con.Open()
try
{
com.Parameters.Add(new SqlParameter("@FileID", FileID));
DataTable dt = new DataTable();
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
return(new Guid(dt.Rows[0][0].ToString()));
}
else
{
return(null);
}
}
catch (Exception ex)
{
throw;
}
finally
{
con.Close();
}
}
internal static void Start()
{
string Filename0 = getFileName();
DataTable Wizarddata = ExcelToDataTable(Filename0, "WizardData");
using (SqlConnection Connection = SQLHelper.GetConnection())
{
SqlTransaction Transaction = default(SqlTransaction);
Transaction = Connection.BeginTransaction("WizardSync");
foreach (DataRow row in Wizarddata.Rows)
{
string FileID = row["FileID"].ToString();
//1.Check whether shareholdername in View table existed in ShareHolder ?
DataTable dtShareHolder = GetShareHolder(Connection, Transaction, FileID);
//2, if existed in ShareHolder table
bool Existed = CheckDataExistedInShareHolder(Connection, Transaction, FileID);
if (Existed)
{
string ShareHolderName = dtShareHolder.Rows[0]["OwnershipSHName"].ToString();
Double OwnerShipPer = Convert.ToDouble(dtShareHolder.Rows[0]["OwnershipPer"].ToString());
//3. Get AccountID
Guid?AccountID = GetAccountIDByFileID(Connection, Transaction, FileID);
//4. Delete ShareHolder in ShareHolder table
DeleteShareholder(Connection, Transaction, AccountID, ShareHolderName);
}
//5.Check whether ShareHolder Name in View Table existed in ShareHolderDV, if not INSERT if yes just update
string ShareholderName = row["OwnershipSHName"].ToString();
Guid? AccountDVID = GetAccountDVIDIDByShareHolderName(Connection, Transaction, ShareholderName);
Nullable <decimal> Percentage = SyncHelper.ConvertToDecimal(row["OwnershipPer"]);
bool BumiShare = SyncHelper.ConvertToBoolean(row["OwnershipBumi"]);
Nullable <Guid> CountryRegionID = SyncHelper.GetRegionID(Connection, Transaction, row["OwnershipCName"].ToString());
CreateUpdateShareholderDV(Connection, Transaction, AccountDVID, ShareholderName, Percentage, BumiShare, CountryRegionID);
}
//Transaction.Commit();
}
}
private static void CreateUpdateShareholderDV(SqlConnection Connection, SqlTransaction Transaction, Guid?AccountDVID, string ShareholderName, Nullable <decimal> Percentage, bool BumiShare, Nullable <Guid> CountryRegionID)
{
if (!string.IsNullOrEmpty(ShareholderName))
{
SqlCommand com = new SqlCommand();
StringBuilder sql = new StringBuilder();
Guid? ShareHolderDVID = GetShareHolderDVID(Connection, Transaction, AccountDVID, ShareholderName);
if (ShareHolderDVID.HasValue)
{
try
{
sql.AppendLine("UPDATE ShareHolderDV SET ");
sql.AppendLine("Percentage = @Percentage,");
sql.AppendLine("BumiShare = @BumiShare,");
sql.AppendLine("Status = @Status,");
sql.AppendLine("CountryRegionID = @CountryRegionID");
sql.AppendLine("WHERE ShareHolderDVID = @ShareHolderDVID");
com.Parameters.Add(new SqlParameter("@ShareHolderDVID", ShareHolderDVID));
com.Parameters.Add(new SqlParameter("@Percentage", SyncHelper.ReturnNull(Percentage)));
com.Parameters.Add(new SqlParameter("@BumiShare", BumiShare));
com.Parameters.Add(new SqlParameter("@Status", EnumSync.Status.Active));
com.Parameters.Add(new SqlParameter("@CountryRegionID", SyncHelper.ReturnNull(CountryRegionID)));
com.CommandText = sql.ToString();
com.CommandType = CommandType.Text;
com.Connection = Connection;
com.Transaction = Transaction;
com.CommandTimeout = int.MaxValue;
//con.Open()
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
else
{
try
{
sql.AppendLine("INSERT INTO ShareHolderDV ");
sql.AppendLine("(ShareHolderDVID, AccountDVID, ShareHolderID, ShareholderName, Percentage, BumiShare, Status, CountryRegionID)");
sql.AppendLine("VALUES");
sql.AppendLine("(@ShareHolderDVID, @AccountDVID, NULL, @ShareholderName, @Percentage, @BumiShare, @Status, @CountryRegionID)");
ShareHolderDVID = Guid.NewGuid();
com.Parameters.Add(new SqlParameter("@ShareHolderDVID", ShareHolderDVID));
com.Parameters.Add(new SqlParameter("@AccountDVID", AccountDVID));
com.Parameters.Add(new SqlParameter("@ShareholderName", ShareholderName));
com.Parameters.Add(new SqlParameter("@Percentage", SyncHelper.ReturnNull(Percentage)));
com.Parameters.Add(new SqlParameter("@BumiShare", BumiShare));
com.Parameters.Add(new SqlParameter("@Status", EnumSync.Status.Active));
com.Parameters.Add(new SqlParameter("@CountryRegionID", SyncHelper.ReturnNull(CountryRegionID)));
com.CommandText = sql.ToString();
com.CommandType = CommandType.Text;
com.Connection = Connection;
com.Transaction = Transaction;
com.CommandTimeout = int.MaxValue;
//con.Open()
com.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
//Finally
// con.Close()
}
}
}
}
