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() } } } }