protected void SaveColumnSelection(object sender, EventArgs e)
{
String user_id = Util.GetUserId();
String dqry = "DELETE FROM dbl_user_columns WHERE UserID=@user_id";
SQL.Delete(dqry, "@user_id", user_id);
// Add selected company columns
String iqry = "INSERT INTO dbl_user_columns (UserID, ColumnID) VALUES (@user_id, @column_id)";
for (int i = 0; i < rtv_cpy.Nodes[0].Nodes.Count; i++)
{
RadTreeNode n = rtv_cpy.Nodes[0].Nodes[i];
if (n.Checked)
{
String column_id = n.Value;
SQL.Insert(iqry, new String[] { "@user_id", "@column_id" }, new Object[] { user_id, column_id });
}
}
// Add selected contact columns
for (int i = 0; i < rtv_ctc.Nodes[0].Nodes.Count; i++)
{
RadTreeNode n = rtv_ctc.Nodes[0].Nodes[i];
if (n.Checked)
{
String column_id = n.Value;
SQL.Insert(iqry, new String[] { "@user_id", "@column_id" }, new Object[] { user_id, column_id });
}
}
// Add selected lead columns
for (int i = 0; i < rtv_lead.Nodes[0].Nodes.Count; i++)
{
RadTreeNode n = rtv_lead.Nodes[0].Nodes[i];
if (n.Checked)
{
String column_id = n.Value;
SQL.Insert(iqry, new String[] { "@user_id", "@column_id" }, new Object[] { user_id, column_id });
}
}
// Log
LeadsUtil.AddLogEntry("Visible columns updated.");
Util.SetRebindOnWindowClose(this, true);
Util.CloseRadWindowFromUpdatePanel(this, String.Empty, false);
}
private void MergeCompanies(String cpy_id_master, String cpy_id_slave)
{
String qry = "SELECT * FROM db_company WHERE CompanyID=@CompanyID";
DataTable dt_keep_company = SQL.SelectDataTable(qry, "@CompanyID", cpy_id_master);
DataTable dt_del_company = SQL.SelectDataTable(qry, "@CompanyID", cpy_id_slave);
if (dt_keep_company.Rows.Count > 0 && dt_del_company.Rows.Count > 0)
{
// Decide which data to keep
String country = dt_keep_company.Rows[0]["Country"].ToString().Trim();
//if (country == String.Empty)
// country = dt_del_company.Rows[0]["country"].ToString().Trim();
String city = dt_keep_company.Rows[0]["City"].ToString().Trim();
if (city == String.Empty)
{
city = dt_del_company.Rows[0]["City"].ToString().Trim();
}
String timezone = dt_keep_company.Rows[0]["TimeZone"].ToString().Trim();
if (timezone == String.Empty)
{
timezone = dt_del_company.Rows[0]["TimeZone"].ToString().Trim();
}
String region = dt_keep_company.Rows[0]["DashboardRegion"].ToString().Trim();
if (region == String.Empty)
{
region = dt_del_company.Rows[0]["DashboardRegion"].ToString().Trim();
}
String industry = dt_keep_company.Rows[0]["Industry"].ToString().Trim();
if (industry == String.Empty)
{
industry = dt_del_company.Rows[0]["Industry"].ToString().Trim();
}
String sub_industry = dt_keep_company.Rows[0]["SubIndustry"].ToString().Trim();
if (sub_industry == String.Empty)
{
sub_industry = dt_del_company.Rows[0]["SubIndustry"].ToString().Trim();
}
String description = dt_keep_company.Rows[0]["Description"].ToString().Trim();
if (description == String.Empty)
{
description = dt_del_company.Rows[0]["Description"].ToString().Trim();
}
else if (dt_del_company.Rows[0]["Description"].ToString().Trim() != String.Empty)
{
description += ". " + dt_del_company.Rows[0]["Description"].ToString().Trim();
}
String turnover = dt_keep_company.Rows[0]["Turnover"].ToString().Trim();
if (turnover == String.Empty || turnover == "0")
{
turnover = dt_del_company.Rows[0]["Turnover"].ToString().Trim();
}
String turnover_denomination = dt_keep_company.Rows[0]["TurnoverDenomination"].ToString().Trim();
if (turnover_denomination == String.Empty)
{
turnover_denomination = dt_del_company.Rows[0]["TurnoverDenomination"].ToString().Trim();
}
String employees = dt_keep_company.Rows[0]["Employees"].ToString().Trim();
if (employees == String.Empty || employees == "0")
{
employees = dt_del_company.Rows[0]["Employees"].ToString().Trim();
}
String suppliers = dt_keep_company.Rows[0]["Suppliers"].ToString().Trim();
if (suppliers == String.Empty)
{
suppliers = dt_del_company.Rows[0]["Suppliers"].ToString().Trim();
}
String phone = dt_keep_company.Rows[0]["Phone"].ToString().Trim();
if (phone == String.Empty)
{
phone = dt_del_company.Rows[0]["Phone"].ToString().Trim();
}
String phone_code = dt_keep_company.Rows[0]["PhoneCode"].ToString().Trim();
if (phone_code == String.Empty)
{
phone_code = dt_del_company.Rows[0]["PhoneCode"].ToString().Trim();
}
String website = dt_keep_company.Rows[0]["Website"].ToString().Trim();
if (website == String.Empty)
{
website = dt_del_company.Rows[0]["Website"].ToString().Trim();
}
String LogoImgUrl = dt_keep_company.Rows[0]["LogoImgUrl"].ToString().Trim();
if (LogoImgUrl == String.Empty)
{
LogoImgUrl = dt_del_company.Rows[0]["LogoImgUrl"].ToString().Trim();
}
String orig_cpy_id = dt_keep_company.Rows[0]["OriginalSystemEntryID"].ToString().Trim();
if (orig_cpy_id == String.Empty)
{
orig_cpy_id = dt_del_company.Rows[0]["OriginalSystemEntryID"].ToString().Trim();
}
String system_name = dt_del_company.Rows[0]["OriginalSystemName"].ToString();
String system_name_like = "%" + dt_del_company.Rows[0]["OriginalSystemName"] + "%";
// Calculate new completion %
String[] fields = new String[] { hf_company_name.Value, country, industry, sub_industry, turnover, employees, phone, website };
int num_fields = fields.Length;
double score = 0;
foreach (String field in fields)
{
if (!String.IsNullOrEmpty(field))
{
score++;
}
}
int completion = Convert.ToInt32(((score / num_fields) * 100));
String[] pn = new String[] {
"@Country",
"@City",
"@TimeZone",
"@DashboardRegion",
"@Industry",
"@SubIndustry",
"@Description",
"@Turnover",
"@TurnoverDenomination",
"@Employees",
"@Suppliers",
"@Phone",
"@PhoneCode",
"@Website",
"@Completion",
"@LogoImgUrl",
"@OriginalSystemEntryID",
"@OriginalSystemName",
"@OriginalSystemNameLike",
"@cpy_id_master",
"@cpy_id_slave"
};
Object[] pv = new Object[] {
country,
city,
timezone,
region,
industry,
sub_industry,
description,
turnover,
turnover_denomination,
employees,
suppliers,
phone,
phone_code,
website,
completion,
LogoImgUrl,
orig_cpy_id,
system_name,
system_name_like,
cpy_id_master,
cpy_id_slave
};
// Make blanks null
for (int i = 0; i < pv.Length; i++)
{
if (pv[i] != null && pv[i].ToString().Trim() == String.Empty)
{
pv[i] = null;
}
}
// Update master company to have all info
String uqry = "UPDATE db_company SET Country=@Country, City=@City, TimeZone=@TimeZone, DashboardRegion=@DashboardRegion, Industry=@Industry, SubIndustry=@SubIndustry, Description=@Description, " +
"Turnover=@Turnover, TurnoverDenomination=@TurnoverDenomination, Employees=@Employees, Suppliers=@Suppliers, Phone=@Phone, PhoneCode=@PhoneCode, Website=@Website, Completion=@Completion, " +
"LogoImgUrl=@LogoImgUrl, OriginalSystemEntryID=@OriginalSystemEntryID, DeDuped=1, DateDeduped=CURRENT_TIMESTAMP, " +
"OriginalSystemName=CASE WHEN OriginalSystemName LIKE @OriginalSystemNameLike THEN OriginalSystemName ELSE CONCAT(OriginalSystemName,'&',@OriginalSystemName) END, LastUpdated=CURRENT_TIMESTAMP WHERE CompanyID=@cpy_id_master";
SQL.Update(uqry, pn, pv);
// Update referencing tables (enforce cascade soon)
uqry =
"UPDATE db_salesbook SET ad_cpy_id=@cpy_id_master WHERE ad_cpy_id=@cpy_id_slave; " +
"UPDATE db_salesbook SET feat_cpy_id=@cpy_id_master WHERE feat_cpy_id=@cpy_id_slave; " +
"UPDATE db_prospectreport SET cpy_id=@cpy_id_master WHERE cpy_id=@cpy_id_slave; " +
"UPDATE db_listdistributionlist SET cpy_id=@cpy_id_master WHERE cpy_id=@cpy_id_slave; " +
"UPDATE db_mediasales SET cpy_id=@cpy_id_master WHERE cpy_id=@cpy_id_slave; " +
"UPDATE db_editorialtracker SET cpy_id=@cpy_id_master WHERE cpy_id=@cpy_id_slave;";
SQL.Update(uqry, pn, pv);
// Move contacts from slave to master
uqry = "UPDATE db_contact SET CompanyID=@cpy_id_master WHERE CompanyID=@cpy_id_slave";
SQL.Update(uqry, pn, pv);
// Delete slave company
String dqry = "DELETE FROM db_company WHERE CompanyID=@cpy_id_slave";
SQL.Delete(dqry, "@cpy_id_slave", cpy_id_slave);
// Log
LeadsUtil.AddLogEntry("Company " + hf_company_name.Value + " (" + country + ", " + cpy_id_slave + ") merged into selected company (" + cpy_id_master + ").");
}
}