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 + ")."); } }