private void BindTemplate()
{
String qry = "SELECT dbl_lead.*, db_company.*, db_contact.ctc_id " +
"FROM dbl_lead, db_contact, db_company " +
"WHERE dbl_lead.ContactID = db_contact.ctc_id " +
"AND db_contact.new_cpy_id = db_company.cpy_id " +
"AND dbl_lead.LeadID=@LeadID;";
DataTable dt_lead = SQL.SelectDataTable(qry, "@LeadID", hf_lead_id.Value);
if (dt_lead.Rows.Count > 0)
{
String cpy_id = dt_lead.Rows[0]["cpy_id"].ToString();
String ctc_id = dt_lead.Rows[0]["ctc_id"].ToString();
String project_id = dt_lead.Rows[0]["ProjectID"].ToString();
hf_project_id.Value = project_id;
hf_parent_project_id.Value = LeadsUtil.GetProjectParentIDFromID(hf_project_id.Value);
hf_cpy_id.Value = cpy_id;
hf_ctc_id.Value = ctc_id;
// Bind Company and Contacts
CompanyManager.BindCompany(cpy_id);
ContactManager.BindContact(ctc_id);
// Bind Notes and Next Action
ContactNotesManager.LeadID = hf_lead_id.Value;
ContactNotesManager.Bind(ctc_id);
// Bind Other Contacts
BindOtherContacts(null, null);
// Bind destination projects
LeadsUtil.BindProjects(dd_projects, dd_buckets, hf_parent_project_id.Value, hf_project_id.Value, true);
}
}
protected void UpdateCompany(object sender, EventArgs e)
{
bool is_dupe = CompanyManager.UpdateCompany(hf_cpy_id.Value);
if (div_contacts.Visible)
{
ContactManager.UpdateContacts(hf_cpy_id.Value);
}
// If company name is a duplicate AFTER we've updated, alert
if (is_dupe && hf_original_company_name.Value != CompanyManager.CompanyName)
{
CompanyManager.BindCompany(hf_cpy_id.Value);
if (((HtmlTable)CompanyManager.FindControl("tbl_company_merger")).Visible)
{
RadButton btn_open_merger = (RadButton)CompanyManager.FindControl("btn_open_merger");
btn_open_merger.OnClientClicking = "function(button, args){var rw = GetRadWindow(); var rwm = rw.get_windowManager(); setTimeout(function ()" +
"{rwm.open('companymerger.aspx?cpy_id=" + Server.UrlEncode(hf_cpy_id.Value) + "', 'rw_merge_companies'); rw.Close();}, 0);}";
}
ScriptManager.RegisterStartupScript(this, this.GetType(), "Resize",
"var rw = GetRadWindow(); rw.set_height(490); rw.center();", true);
hf_original_company_name.Value = CompanyManager.CompanyName;
Util.PageMessageAlertify(this, "Multiple companies with the name you've specified exist in the database. This company's name has been updated but please consider using the merger tool to merge any duplicate companies together." +
"<br/><br/>Alternatively, if you think the duplicates are unique companies, try to pick a more appropriate name for this company, e.g. 'Subway' in Canada could become 'Subway Canada'.", "Duplicate Name Detected!");
}
// Otherwise simply close window
else
{
Util.CloseRadWindow(this, String.Empty, false);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Util.SetRebindOnWindowClose(this, false);
if (Request.QueryString["cpy_id"] != null && !String.IsNullOrEmpty(Request.QueryString["cpy_id"]) &&
Request.QueryString["cpy_type"] != null && !String.IsNullOrEmpty(Request.QueryString["cpy_type"]) &&
Request.QueryString["issue"] != null && !String.IsNullOrEmpty(Request.QueryString["issue"]))
{
hf_cpy_id.Value = Request.QueryString["cpy_id"];
hf_cpy_type.Value = Request.QueryString["cpy_type"];
hf_issue.Value = Request.QueryString["issue"];
switch (hf_cpy_type.Value)
{
case "f": hf_cpy_type.Value = "Feature"; ContactManager.IncludeContactTypes = false; ContactManager.TargetSystem = String.Empty; break;
case "a": hf_cpy_type.Value = "Advert"; break;
}
CompanyManager.SmartSocialIssue = hf_issue.Value;
CompanyManager.SmartSocialCompanyType = hf_cpy_type.Value;
CompanyManager.BindCompany(hf_cpy_id.Value);
if (Request.QueryString["ctc_id"] != null && !String.IsNullOrEmpty(Request.QueryString["ctc_id"]))
{
hf_ctc_id.Value = Request.QueryString["ctc_id"];
ContactManager.FocusContactID = hf_ctc_id.Value;
}
ContactManager.BindContacts(hf_cpy_id.Value);
}
else
{
Util.PageMessageAlertify(this, LeadsUtil.LeadsGenericError, "Error");
}
}
}
protected void UpdateCompanyAndContacts(object sender, EventArgs e)
{
ContactManager.UpdateContacts(hf_cpy_id.Value); // also merges as AutoContactMergingEnabled is true
CompanyManager.UpdateCompany(); // also merges as AutoCompanyMergingEnabled is true
CompanyManager.BindCompany(hf_cpy_id.Value); // rebind things like date last updated
ContactManager.BindContacts(hf_cpy_id.Value);
Util.PageMessageSuccess(this, "Updated!", "top-right");
Util.SetRebindOnWindowClose(this, true);
}
private void BindCompanyInfo()
{
String qry = "SELECT cpy_id, ProjectID FROM dbl_lead, db_contact, db_company WHERE dbl_lead.ContactID=db_contact.ctc_id AND db_contact.new_cpy_id=db_company.cpy_id AND LeadID=@lead_id";
DataTable dt_cpy = SQL.SelectDataTable(qry, "@lead_id", hf_lead_id.Value);
if (dt_cpy.Rows.Count > 0)
{
hf_company_id.Value = dt_cpy.Rows[0]["cpy_id"].ToString();
hf_project_id.Value = LeadsUtil.GetProjectParentIDFromID(dt_cpy.Rows[0]["ProjectID"].ToString());
CompanyManager.BindCompany(hf_company_id.Value);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Util.SetRebindOnWindowClose(this, false);
Security.BindPageValidatorExpressions(this);
if (Request.QueryString["lead_id"] != null && !String.IsNullOrEmpty(Request.QueryString["lead_id"]))
{
hf_lead_id.Value = Request.QueryString["lead_id"];
String qry = "SELECT TeamID, TeamName, db_userpreferences.UserID, db_userpreferences.Office, db_contact.ContactID, db_company.CompanyID " +
"FROM dbl_lead, dbl_project, db_userpreferences, db_ccateams, db_contact, db_company " +
"WHERE dbl_lead.ProjectID = dbl_project.ProjectID " +
"AND dbl_project.UserID = db_userpreferences.userid " +
"AND db_userpreferences.ccaTeam = db_ccateams.TeamID " +
"AND dbl_lead.ContactID = db_contact.ContactID " +
"AND db_contact.CompanyID = db_company.CompanyID " +
"AND LeadID=@lead_id";
DataTable dt_user_info = SQL.SelectDataTable(qry, "@lead_id", hf_lead_id.Value);
if (dt_user_info.Rows.Count > 0)
{
hf_user_id.Value = dt_user_info.Rows[0]["UserID"].ToString();
hf_team_id.Value = dt_user_info.Rows[0]["TeamID"].ToString();
hf_office.Value = dt_user_info.Rows[0]["Office"].ToString();
hf_team_name.Value = dt_user_info.Rows[0]["TeamName"].ToString();
hf_company_id.Value = dt_user_info.Rows[0]["CompanyID"].ToString();
hf_contact_id.Value = dt_user_info.Rows[0]["ContactID"].ToString();
String[] forced_selected = new String[] { hf_contact_id.Value };
CompanyManager.BindCompany(hf_company_id.Value);
ContactManager.BindContacts(hf_company_id.Value, forced_selected); // for multiple
// Bind Destination Prospect teams
BindDestinationTeams();
if (CompanyManager.Turnover == String.Empty)
{
Util.PageMessageAlertify(this, "Company turnover is not specified and is required to push this Lead to Prospect.<br/><br/>Click the company edit pencil and specify a turnover value. ", "Turnover Required");
}
}
BindRepDropDown();
}
else
{
Util.PageMessageAlertify(this, "There was an error getting the team information. Please close this window and retry.", "Error");
}
Util.ResizeRadWindow(this);
}
}
protected void BindCompanyAndContacts(object sender, EventArgs e)
{
if (hf_clicked_company_id.Value != String.Empty)
{
div_company.Visible = div_contacts.Visible = true;
//CompanyManager.ShowCompanyNameHeader = !CompanyManager.AddingNewCompany;
CompanyManager.BindCompany(hf_clicked_company_id.Value);
ContactManager.BindContacts(hf_clicked_company_id.Value);
ScriptManager.RegisterStartupScript(this, this.GetType(), "Resize", "var rw = GetRadWindow(); rw.autoSize(); rw.center();", true);
tbl_add_lead.Visible = true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString["cpy_id"] != null && !String.IsNullOrEmpty(Request.QueryString["cpy_id"]))
{
hf_cpy_id.Value = Request.QueryString["cpy_id"];
CompanyManager.BindCompany(hf_cpy_id.Value);
hf_original_company_name.Value = CompanyManager.CompanyName;
div_contacts.Visible = Request.QueryString["showcontacts"] == "true";
if (div_contacts.Visible)
{
ContactManager.BindContacts(hf_cpy_id.Value);
}
}
else
{
Util.PageMessageAlertify(this, LeadsUtil.LeadsGenericError, "Error");
}
}
}
protected void BindProspectInfo()
{
String qry = "SELECT PLevel, ListGeneratorFriendlyname, Emails, Grade, Notes, IsHot, pr.DateAdded, DateListDue, DateLetterHeadDue, LeadHookAngle, BenchmarkNotes, " + // prospect info
"cpy.CompanyName, cpy.CompanyID, cpy.Industry, cpy.SubIndustry, cpy.Turnover, cpy.Employees, cpy.Phone, cpy.Website " + // company info
"FROM db_prospectreport pr, db_company cpy WHERE pr.CompanyID = cpy.CompanyID AND ProspectID=@pros_id";
DataTable dt_pros_info = SQL.SelectDataTable(qry, "@pros_id", hf_pros_id.Value);
if (dt_pros_info.Rows.Count > 0)
{
lbl_prospect.Text = "Currently editing <b>" + dt_pros_info.Rows[0]["CompanyName"] + "</b>";
// LH due
String lh_due = dt_pros_info.Rows[0]["DateLetterHeadDue"].ToString();
if (lh_due.Trim() != String.Empty)
{
DateTime dt_lh_due = new DateTime();
if (DateTime.TryParse(lh_due, out dt_lh_due))
{
dp_lh_due.SelectedDate = dt_lh_due;
}
}
// List due
String list_due = dt_pros_info.Rows[0]["DateListDue"].ToString();
if (list_due.Trim() != String.Empty)
{
DateTime dt_list_due = new DateTime();
if (DateTime.TryParse(list_due, out dt_list_due))
{
dp_list_due.SelectedDate = dt_list_due;
}
}
// Rep
String rep = dt_pros_info.Rows[0]["ListGeneratorFriendlyname"].ToString();
int rep_idx = dd_rep.Items.IndexOf(dd_rep.Items.FindByText(rep));
if (rep_idx == -1)
{
dd_rep.Items.Insert(0, new ListItem(rep)); // in case user is no longer employed
dd_rep.SelectedIndex = 0;
}
else
{
dd_rep.SelectedIndex = rep_idx;
}
int idx = dd_emails.Items.IndexOf(dd_emails.Items.FindByValue(dt_pros_info.Rows[0]["Emails"].ToString()));
if (idx != -1)
{
dd_emails.SelectedIndex = idx;
}
dd_p1_p2.Text = dt_pros_info.Rows[0]["PLevel"].ToString();
dd_grade.Text = dt_pros_info.Rows[0]["Grade"].ToString();
tb_notes.Text = dt_pros_info.Rows[0]["Notes"].ToString();
tb_lha.Text = dt_pros_info.Rows[0]["LeadHookAngle"].ToString();
tb_benchmark_data.Text = dt_pros_info.Rows[0]["BenchmarkNotes"].ToString();
// Bind company
hf_cpy_id.Value = dt_pros_info.Rows[0]["CompanyID"].ToString();
CompanyManager.BindCompany(hf_cpy_id.Value);
// Bind contacts
DateTime ContactContextCutOffDate = new DateTime(2017, 4, 6);
DateTime ProspectAdded = Convert.ToDateTime(dt_pros_info.Rows[0]["DateAdded"].ToString());
if (ProspectAdded >= ContactContextCutOffDate)
{
ContactManager.TargetSystemID = hf_pros_id.Value;
}
ContactManager.BindContacts(hf_cpy_id.Value);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Util.SetRebindOnWindowClose(this, false);
bool error = false;
if (Request.QueryString["cpy_id"] != null && !String.IsNullOrEmpty(Request.QueryString["cpy_id"]))
{
hf_cpy_id.Value = Request.QueryString["cpy_id"];
}
else if (Request.QueryString["ctc_id"] != null && !String.IsNullOrEmpty(Request.QueryString["ctc_id"]))
{
hf_ctc_id.Value = Request.QueryString["ctc_id"];
String qry = "SELECT CompanyID FROM db_contact WHERE ContactID=@ContactID";
hf_cpy_id.Value = SQL.SelectString(qry, "CompanyID", "@ContactID", hf_ctc_id.Value);
}
else
{
Util.PageMessageAlertify(this, LeadsUtil.LeadsGenericError, "Error");
error = true;
}
// Disable adding to Leads system/selecting contacts when add_leads=false
bool InLeadsMode = true;
if (Request.QueryString["add_leads"] != null && Request.QueryString["add_leads"].ToString() == "false")
{
div_add_leads.Visible = lbl_pad.Visible = false;
InLeadsMode = false;
ContactManager.SelectableContacts = false;
}
if (!error)
{
if (hf_ctc_id.Value != String.Empty)
{
ContactManager.FocusContactID = hf_ctc_id.Value;
}
String[] ForcedSelectedContactIDs = null;
if (hf_ctc_id.Value != String.Empty)
{
ForcedSelectedContactIDs = new String[] { hf_ctc_id.Value }
}
;
CompanyManager.BindCompany(hf_cpy_id.Value);
ContactManager.BindContacts(hf_cpy_id.Value, ForcedSelectedContactIDs);
if (InLeadsMode)
{
// Allow adding Leads to projects if this user has projects (or has used Leads system)
hf_project_id.Value = LeadsUtil.GetLastViewedProjectID();
if (hf_project_id.Value != String.Empty)
{
div_add_leads.Visible = true;
hf_parent_project_id.Value = LeadsUtil.GetProjectParentIDFromID(hf_project_id.Value);
// Bind destination projects
LeadsUtil.BindProjects(dd_projects, dd_buckets, hf_parent_project_id.Value, hf_project_id.Value, true);
}
// Otherwise disable adding Leads and allow updates to CTC/CPY only
else
{
ContactManager.SelectableContacts = false;
}
}
}
}
Util.ResizeRadWindow(this);
}
protected void UpdateSale(object sender, EventArgs e)
{
// Update sale.
int ms_id = 0;
try
{
bool update = true;
// Check rep
if (dd_rep.SelectedItem.Text.Trim() == String.Empty)
{
Util.PageMessage(this, "You must select a rep!"); update = false;
}
// ms_id
if (hf_ms_id.Value.Trim() == String.Empty)
{
update = false;
}
else if (!Int32.TryParse(hf_ms_id.Value.Trim(), out ms_id))
{
update = false;
}
int test_int = 0;
// Confidence
if (tb_confidence.Text.Trim() != String.Empty && !Int32.TryParse(tb_confidence.Text.Trim(), out test_int))
{
Util.PageMessage(this, "Confidence is not a valid number!"); update = false;
}
double test_double = 0;
// discount
if (tb_discount.Text.Trim() != String.Empty && !Double.TryParse(tb_discount.Text.Trim(), out test_double))
{
Util.PageMessage(this, "Discount is not a valid number!"); update = false;
}
// unit price
if (tb_unit_price.Text.Trim() != String.Empty && !Double.TryParse(tb_unit_price.Text.Trim(), out test_double))
{
Util.PageMessage(this, "Unit Price is not a valid number!"); update = false;
}
// prospect
if (tb_prospect_price.Text.Trim() != String.Empty && !Double.TryParse(tb_prospect_price.Text.Trim(), out test_double))
{
Util.PageMessage(this, "Prospect price is not a valid number!"); update = false;
}
// start_date
String start_date = null;
DateTime dt_start_date = new DateTime();
if (dp_start_date.SelectedDate != null)
{
if (DateTime.TryParse(dp_start_date.SelectedDate.ToString(), out dt_start_date))
{
start_date = dt_start_date.ToString("yyyy/MM/dd");
}
}
// end_date
String end_date = null;
DateTime dt_end_date = new DateTime();
if (dp_end_date.SelectedDate != null)
{
if (DateTime.TryParse(dp_end_date.SelectedDate.ToString(), out dt_end_date))
{
end_date = dt_end_date.ToString("yyyy/MM/dd");
}
}
if (start_date != null && end_date != null)
{
if (dt_end_date < dt_start_date)
{
Util.PageMessage(this, "Sale Start Date must be before the End Date!");
update = false;
}
if (dt_end_date.Subtract(dt_start_date).Days > 365)
{
Util.PageMessage(this, "Date span for this sale cannot be larger than a year!");
update = false;
}
}
//// automatic invoice date
//String invoice_date = GetInvoiceDate(hf_msp_id.Value);
//if (invoice_date.Trim() == "" && tb_invoice.Text.Trim() != "")
// invoice_date = DateTime.Now.ToString("yyyy/MM/dd");
//else if(tb_invoice.Text.Trim() == "")
// invoice_date = null;
//else
// invoice_date = Convert.ToDateTime(invoice_date).ToString("yyyy/MM/dd");
String invoice_date = null;
if (dp_invoice_date.SelectedDate != null)
{
invoice_date = Convert.ToDateTime(dp_invoice_date.SelectedDate).ToString("yyyy/MM/dd");
}
if (update)
{
// Update Sale
String uqry = "UPDATE db_mediasales SET " +
"Client=@client, " +
"Agency=@agency, " +
"Channel=@channel, " +
"Country=@country, " +
"MediaType=@media_type, " +
"Size=@size, " +
"Rep=@rep, " +
"Units=@units, " +
"UnitPrice=@unit_price, " +
"Discount=@discount, " +
"DiscountType=@discount_type, " +
"Confidence=@confidence, " +
"ProspectPrice=@prospect, " +
"StartDate=@start_date, " +
"EndDate=@end_date, " +
"SaleNotes=@s_notes, " +
"FinanceNotes=@f_notes " +
"WHERE MediaSaleID=@ms_id";
String[] pn = new String[] { "@client", "@agency", "@channel", "@country", "@media_type", "@size", "@rep", "@units", "@unit_price",
"@discount", "@discount_type", "@confidence", "@prospect", "@start_date", "@end_date", "@s_notes", "@f_notes", "@ms_id" };
Object[] pv = new Object[] {
tb_client.Text.Trim(),
tb_agency.Text.Trim(),
tb_channel.Text.Trim(),
tb_country.Text.Trim(),
tb_media_type.Text.Trim(),
tb_size.Text.Trim(),
dd_rep.SelectedItem.Text.Trim(),
tb_units.Text.Trim(),
tb_unit_price.Text.Trim(),
tb_discount.Text.Trim(),
dd_discount_type.SelectedItem.Value,
tb_confidence.Text.Trim(),
tb_prospect_price.Text.Trim(),
start_date,
end_date,
tb_s_notes.Text.Trim(),
tb_f_notes.Text.Trim(),
ms_id
};
SQL.Update(uqry, pn, pv);
// Update contacts
ContactManager.UpdateContacts(hf_cpy_id.Value);
String company_name = tb_client.Text.Trim();
if (company_name == String.Empty)
{
company_name = null;
}
String industry = tb_channel.Text.Trim();
if (industry == String.Empty)
{
industry = null;
}
String country = tb_country.Text.Trim();
if (country == String.Empty)
{
country = null;
}
// Update company (temp)
uqry = "UPDATE db_company SET CompanyName=@CompanyName, CompanyNameClean=(SELECT GetCleanCompanyName(@CompanyName,@Country)), Industry=@Industry, Country=@Country, LastUpdated=CURRENT_TIMESTAMP WHERE CompanyID=@CompanyID";
SQL.Update(uqry,
new String[] { "@CompanyID", "@CompanyName", "@Industry", "@Country" },
new Object[] {
hf_cpy_id.Value,
company_name,
industry,
country
});
CompanyManager.BindCompany(hf_cpy_id.Value);
CompanyManager.PerformMergeCompaniesAfterUpdate(hf_cpy_id.Value);
// Update child data if child
if (hf_type.Value == "child")
{
update = true;
test_double = 0;
// price
if (tb_price.Text.Trim() != String.Empty && !Double.TryParse(tb_price.Text.Trim(), out test_double))
{
Util.PageMessage(this, "Price is not a valid number!"); update = false;
}
// outstanding
if (tb_outstanding.Text.Trim() != String.Empty && !Double.TryParse(tb_outstanding.Text.Trim(), out test_double))
{
Util.PageMessage(this, "Outstanding price is not a valid number!"); update = false;
}
// date_paid
bool paidemail = false;
String date_paid = null;
DateTime dt_date_paid = new DateTime();
if (dp_date_paid.SelectedDate != null)
{
if (DateTime.TryParse(dp_date_paid.SelectedDate.ToString(), out dt_date_paid))
{
date_paid = dt_date_paid.ToString("yyyy/MM/dd HH:mm");
if (!HasDatePaid(hf_msp_id.Value))
{
tb_outstanding.Text = "0"; // set outstanding to 0
paidemail = true;
}
}
}
if (update)
{
uqry = "UPDATE db_mediasalespayments SET Price=@price, Outstanding=@outstanding, DatePaid=@date_paid, " +
"Invoice=@invoice, InvoiceDate=@invoice_date, Terms=@terms WHERE MediaSalePaymentID=@msp_id";
SQL.Update(uqry,
new String[] { "@price", "@outstanding", "@date_paid", "@invoice", "@invoice_date", "@terms", "@msp_id" },
new Object[] { tb_price.Text.Trim(),
tb_outstanding.Text.Trim(),
date_paid,
tb_invoice.Text.Trim(),
invoice_date,
dd_terms.SelectedItem.Value,
hf_msp_id.Value });
String username = HttpContext.Current.User.Identity.Name;
String msp_id = hf_msp_id.Value;
String user_email = Util.GetUserEmailAddress();
System.Threading.ThreadPool.QueueUserWorkItem(delegate
{
// Set culture of new thread
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-GB");
if (paidemail)
{
SendDatePaidEmail(msp_id, username, user_email);
}
});
}
}
String part = String.Empty;
String part_issue = String.Empty;
if (hf_type.Value == "child")
{
part = "part ";
part_issue = " - " + hf_issue_name.Value;
}
Util.CloseRadWindow(this, part + tb_client.Text + " (" + tb_agency.Text + ")" + part_issue, false);
}
}
catch (Exception r)
{
if (Util.IsTruncateError(this, r))
{
}
else
{
Util.WriteLogWithDetails(r.Message + Environment.NewLine + r.StackTrace + Environment.NewLine + "<b>Sale ID:</b> " + ms_id, "mediasales_log");
Util.PageMessage(this, "An error occured, please try again.");
}
}
}
protected void UpdateSale(object sender, EventArgs e)
{
// Update sale.
try
{
bool update = true;
bool paidemail = false;
String date_paid_expr = String.Empty;
DateTime date_paid = new DateTime();
DateTime earliest_finalise_date = new DateTime();
if (dp_date_paid.SelectedDate != null && DateTime.TryParse(dp_date_paid.SelectedDate.ToString(), out date_paid))
{
date_paid_expr = ", date_paid=@date_paid ";
DataTable dt_sale_info = Util.GetSalesBookSaleFromID(hf_ent_id.Value);
if (dt_sale_info.Rows.Count > 0)
{
DateTime dt_date_added = new DateTime();
if (DateTime.TryParse(dt_sale_info.Rows[0]["ent_date"].ToString(), out dt_date_added))
{
// check to see if date paid is going to conflict with finalised commission forms relating to the rep/list gen
String qry = "SELECT MIN(DateFormFinalised) as 'df' FROM db_commissionforms WHERE Year=YEAR(@date_added) AND Month=MONTH(@date_added) " +
"AND UserID IN (SELECT DISTINCT co.UserID " +
"FROM db_userpreferences up, db_commissionoffices co " +
"WHERE up.UserID = co.UserID " +
"AND (FriendlyName=@lg OR FriendlyName=@rep)) " +
"AND DateFormFinalised IS NOT NULL";
String finalise_date = SQL.SelectString(qry, "df",
new String[] { "@date_added", "@lg", "@rep" },
new Object[] { dt_date_added.ToString("yyyy/MM/dd"), dt_sale_info.Rows[0]["list_gen"].ToString(), dt_sale_info.Rows[0]["rep"].ToString() });
if (DateTime.TryParse(finalise_date, out earliest_finalise_date) && date_paid < earliest_finalise_date)
{
update = false;
}
}
}
if (!Util.HasDatePaid(hf_ent_id.Value))
{
paidemail = true;
}
}
if (update)
{
String invoice = tb_invoice.Text.Trim();
if (invoice == String.Empty)
{
invoice = null;
}
String vat_no = tb_vat_number.Text.Trim();
if (vat_no == String.Empty)
{
vat_no = null;
}
else if (vat_no.Length > 50)
{
vat_no = vat_no.Substring(0, 49);
}
String br_page_no = tb_br_pageno.Text.Trim();
if (br_page_no == String.Empty)
{
br_page_no = null;
}
String ch_page_no = tb_ch_pageno.Text.Trim();
if (ch_page_no == String.Empty)
{
ch_page_no = null;
}
String fnotes = Util.ConvertStringToUTF8(tb_fnotes.Text.Trim());
String dnotes = Util.ConvertStringToUTF8(tb_dnotes.Text.Trim());
// Update Sales Book
String uqry = "UPDATE db_salesbook SET " +
"invoice=@invoice, " +
"BP=@BP, " +
"fnotes=@fnotes, " +
"al_notes=@dnotes, " +
"territory_magazine=@territory_magazine, " +
"channel_magazine=@channel_magazine, " +
"br_page_no=@br_page_no, " +
"ch_page_no=@ch_page_no, " +
"links_mail_cc=@links_mail_cc, " +
"f_last_updated=CURRENT_TIMESTAMP " +
date_paid_expr +
"WHERE ent_id=@ent_id";
String[] pn = new String[] { "@BP", "@fnotes", "@dnotes", "@territory_magazine", "@channel_magazine",
"@date_paid", "@invoice", "@br_page_no", "@ch_page_no", "@links_mail_cc", "@ent_id" };
Object[] pv = new Object[] { cb_bp.Checked,
fnotes,
dnotes,
dd_magazine.SelectedItem.Text.Trim(),
dd_channel.SelectedItem.Text.Trim(),
date_paid.ToString("yyyy/MM/dd HH:mm"),
invoice,
br_page_no,
ch_page_no,
tb_links_mail_cc.Text.Replace(" ;", ";").Trim(),
hf_ent_id.Value };
SQL.Update(uqry, pn, pv);
//Update Finance Sales
String invoice_date_expr = String.Empty;
String date_promised_expr = String.Empty;
String ptp_expr = String.Empty;
DateTime invoice_date = new DateTime();
DateTime date_promised = new DateTime();
bool assigning_promised_date = false;
bool move_to_ptp = false;
if (dp_invoice_date.SelectedDate != null)
{
if (DateTime.TryParse(dp_invoice_date.SelectedDate.ToString(), out invoice_date))
{
invoice_date_expr = ", InvoiceDate=@invoice_date ";
}
}
else
{
invoice_date_expr = ", InvoiceDate=NULL ";
}
if (dp_date_promised.SelectedDate != null)
{
if (DateTime.TryParse(dp_date_promised.SelectedDate.ToString(), out date_promised))
{
date_promised_expr = ", PromisedDate=@promised_date ";
assigning_promised_date = true;
}
}
else
{
date_promised_expr = ", PromisedDate=NULL ";
}
if (assigning_promised_date)
{
move_to_ptp = !Util.HasDatePromised(hf_ent_id.Value);
if (move_to_ptp)
{
ptp_expr = ", FinanceTabID=(SELECT FinanceTabID FROM db_financesalestabs WHERE TabName='Promise to Pay') ";
}
}
// Outstanding
String outstanding = tb_outstanding.Text.Trim();
if (outstanding == String.Empty)
{
outstanding = "0";
}
String country = String.Empty;
if (dd_country.Items.Count > 0)
{
country = dd_country.SelectedItem.Text.Trim();
}
if (country == String.Empty)
{
country = null;
}
String timezone = tb_timezone.Text.Trim();
if (timezone == String.Empty)
{
timezone = null;
}
String foreign_price = tb_foreign_price.Text.Trim();
if (foreign_price == String.Empty)
{
foreign_price = null;
}
uqry = "UPDATE db_financesales SET " +
"ForeignPrice=@foreign_price, " +
"Country=@country, timezone=@timezone, " +
"Outstanding=@outstanding, " +
"Bank=@bank, VATNumber=@vat " +
invoice_date_expr + date_promised_expr + ptp_expr +
"WHERE SaleID=@ent_id";
pn = new String[] { "@foreign_price", "@country", "@timezone", "@outstanding", "@invoice_date", "@promised_date", "@bank", "@vat", "@ent_id" };
pv = new Object[] {
foreign_price,
country,
timezone,
outstanding,
invoice_date.ToString("yyyy/MM/dd"),
date_promised.ToString("yyyy/MM/dd"),
dd_bank.SelectedItem.Value,
vat_no,
hf_ent_id.Value
};
SQL.Update(uqry, pn, pv);
// Update contacts
ContactManager.UpdateContacts(hf_ad_cpy_id.Value);
// Update company
// Temp, hijack Sector Mag name as a sector indicator
String industry = dd_channel.SelectedItem.Text.Trim();
industry = industry.Replace("Food", "Food & Drink").Replace("World", String.Empty).Replace("Exec", String.Empty).Replace("Gigabit", "Technology");
if (industry == String.Empty)
{
industry = null;
}
uqry = "UPDATE db_company SET Country=@Country, TimeZone=@TimeZone, Industry=@Industry, LastUpdated=CURRENT_TIMESTAMP WHERE CompanyID=@CompanyID";
SQL.Update(uqry,
new String[] { "@Country", "@TimeZone", "@Industry", "@CompanyID" },
new Object[] { country, timezone, industry, hf_ad_cpy_id.Value });
CompanyManager.BindCompany(hf_ad_cpy_id.Value);
CompanyManager.PerformMergeCompaniesAfterUpdate(hf_ad_cpy_id.Value);
String paid_updated_expr = "successfully updated";
if (paidemail)
{
paid_updated_expr = "successfully paid";
if (paidemail)
{
Util.SendSalePaidEmail(hf_ent_id.Value, hf_office.Value, "Finance");
}
}
//Util.SendSaleUpdateEmail(hf_ent_id.Value, hf_office.Value, username, "Finance");
Util.CloseRadWindow(this, tb_advertiser.Text + " (" + tb_feature.Text + ") " + paid_updated_expr, false);
}
else
{
Util.PageMessage(this, "You cannot set the date paid for this sale any earlier than " + earliest_finalise_date + " as it would conflict with finalised commission forms." +
"\\n\\nIf the related CCAs are eligible for commission on this sale then they will still receive payment as an outstanding sale once you set the date paid, but payment will not appear in the form where the sale originated (by date added).");
}
}
catch (Exception r)
{
if (Util.IsTruncateError(this, r))
{
}
else
{
Util.WriteLogWithDetails(r.Message + Environment.NewLine + r.StackTrace + Environment.NewLine + "<b>Sale ID:</b> " + hf_ent_id.Value
+ Environment.NewLine + "<b>Finance Notes:</b> " + tb_fnotes.Text.Trim(), "finance_log");
Util.PageMessage(this, "An error occured, please try again.");
}
}
}
protected void UpdateList(object sender, EventArgs e)
{
// Update list.
bool update = true;
// List out
String list_out = null;
if (dp_list_out.SelectedDate != null)
{
DateTime dt_list_out = new DateTime();
if (DateTime.TryParse(dp_list_out.SelectedDate.ToString(), out dt_list_out))
{
list_out = dt_list_out.ToString("yyyy/MM/dd");
}
}
// Rep
String rep = dd_rep_working.SelectedItem.Text;
if (cb_multiple_reps_working.Checked)
{
if (tb_rep_working.Text.Contains("/"))
{
rep = tb_rep_working.Text.Replace("/ ", "/").Replace(" /", "/");
}
else
{
Util.PageMessage(this, "Error setting rep working.\\n\\nIf you want to set multiple reps working please ensure you specify two or more reps separated by forward slashes e.g. KC/John");
div_multiple_reps.Attributes.Add("style", "display:block;");
dd_rep_working.Attributes.Add("disabled", "true;");
update = false;
}
}
String rep_working_expr = String.Empty;
if (rep == "WAITING")
{
rep_working_expr = "ListAssignedToFriendlyname='LIST', ";
rep = null;
}
else if (hf_edit_mode.Value != "LIST" && !cb_multiple_reps_working.Checked)
{
rep_working_expr = "ListAssignedToFriendlyname=@rep, ";
}
// Int values
int suppliers = 0;
int mao_names = 0;
int orig_pred = 0;
int value_pred = 0;
Int32.TryParse(tb_suppliers.Text.Trim(), out suppliers);
Int32.TryParse(tb_mao_names.Text.Trim(), out mao_names);
Int32.TryParse(tb_orig_pred.Text.Trim(), out orig_pred);
Int32.TryParse(tb_value_pred.Text.Trim(), out value_pred);
String turnover = tb_turnover.Text + " " + dd_turnover_denomination.SelectedItem.Text;
if (String.IsNullOrEmpty(turnover))
{
turnover = null;
}
String employees = tb_no_employees.Text.Trim();
if (String.IsNullOrEmpty(employees))
{
employees = null;
}
String list_notes = tb_mao_notes.Text.Trim();
if (String.IsNullOrEmpty(list_notes))
{
list_notes = null;
}
if (update)
{
try
{
// Update list dist
String uqry = "UPDATE db_listdistributionlist SET " +
"CompanyName=@company_name, " +
"ListStatus=@list_status, " +
"ListGeneratorFriendlyname=@list_gen, " +
"ListWorkedByFriendlyname=@rep," +
"DateListAssigned=@list_out, " +
"Suppliers=@suppliers, " +
"MaONames=@mao_names, " +
"OriginalValuePrediction=@orig_prediction, " +
"CurrentValuePrediction=@value_predicted, " +
"Turnover=@annual_sales, " +
"Employees=@no_employees, " +
"WithAdmin=@with_admin, " +
"Parachute=@parachute, " +
"Synopsis=@synopsis, " +
"CribSheet=@crib_sheet, " +
"OptMail=@opt_mail, " +
rep_working_expr +
"ListNotes=@mao_notes, " +
"LastUpdated=CURRENT_TIMESTAMP " +
"WHERE ListID=@list_id ";
String[] pn = new String[] { "@company_name", "@list_status", "@list_gen", "@rep", "@list_out", "@suppliers", "@mao_names", "@orig_prediction", "@value_predicted",
"@annual_sales", "@no_employees", "@with_admin", "@parachute", "@synopsis", "@crib_sheet", "@opt_mail", "@mao_notes", "@list_id", "@cpy_id" };
Object[] pv = new Object[] { tb_company.Text.Trim(),
dd_list_status.SelectedItem.Text,
dd_list_gen.SelectedItem.Text,
rep,
list_out,
suppliers,
mao_names,
orig_pred,
value_pred,
turnover,
employees,
cb_with_admin.Checked,
cb_parachute.Checked,
cb_synopsis.Checked,
cb_crib_sheet.Checked,
cb_opt_mail.Checked,
list_notes,
hf_list_id.Value,
hf_cpy_id.Value // update where shared company and non-unique
};
SQL.Update(uqry, pn, pv);
// Update potentially shared info between non-unique lists
uqry = "UPDATE db_listdistributionlist SET CompanyName=@company_name, Suppliers=@suppliers, Turnover=@annual_sales, Employees=@no_employees, LastUpdated=CURRENT_TIMESTAMP WHERE CompanyID=@cpy_id";
SQL.Update(uqry, pn, pv);
turnover = tb_turnover.Text.Trim();
if (turnover == String.Empty || turnover == "0")
{
turnover = null;
}
String company_size = tb_no_employees.Text.Trim();
if (company_size == String.Empty || company_size == "0")
{
company_size = null;
}
String cpy_suppliers = null;
if (suppliers > 0)
{
cpy_suppliers = suppliers.ToString();
}
// Determine company size bracket
String company_size_bracket = "-";
int int_company_size = 0;
if (company_size != null && Int32.TryParse(company_size, out int_company_size))
{
if (int_company_size >= 1 && int_company_size <= 10)
{
company_size_bracket = "1-10";
}
else if (int_company_size >= 11 && int_company_size <= 50)
{
company_size_bracket = "11-50";
}
else if (int_company_size >= 51 && int_company_size <= 200)
{
company_size_bracket = "51-200";
}
else if (int_company_size >= 201 && int_company_size <= 500)
{
company_size_bracket = "201-500";
}
else if (int_company_size >= 501 && int_company_size <= 1000)
{
company_size_bracket = "501-1000";
}
else if (int_company_size >= 1001 && int_company_size <= 5000)
{
company_size_bracket = "1001-5000";
}
else if (int_company_size >= 5001 && int_company_size <= 10000)
{
company_size_bracket = "5001-10,000";
}
else if (int_company_size > 10000)
{
company_size_bracket = "10,000+";
}
}
// Update company (temp)
uqry = "UPDATE db_company SET CompanyName=@CompanyName, CompanyNameClean=(SELECT GetCleanCompanyName(@CompanyName,Country)), Suppliers=@Suppliers, Turnover=@Turnover, TurnoverDenomination=@TurnoverDenomination, " +
"Employees=@Employees, EmployeesBracket=@EmployeesBracket, LastUpdated=CURRENT_TIMESTAMP WHERE CompanyID=@CompanyID";
SQL.Update(uqry,
new String[] { "@CompanyID", "@CompanyName", "@Suppliers", "@Turnover", "@TurnoverDenomination", "@Employees", "@EmployeesBracket" },
new Object[] {
hf_cpy_id.Value,
tb_company.Text.Trim(),
cpy_suppliers,
turnover,
dd_turnover_denomination.SelectedItem.Text,
company_size,
company_size_bracket
});
CompanyManager.BindCompany(hf_cpy_id.Value);
CompanyManager.PerformMergeCompaniesAfterUpdate(hf_cpy_id.Value);
// Update synopsis of any Editorial Tracker features
uqry = "UPDATE db_editorialtracker SET Synopsis=@synopsis WHERE ListID=@list_id";
SQL.Update(uqry,
new String[] { "@synopsis", "@list_id" },
new Object[] { cb_synopsis.Checked, hf_list_id.Value });
Util.CloseRadWindow(this, "List '" + tb_company.Text + "' successfully updated", false);
}
catch (Exception r)
{
if (Util.IsTruncateError(this, r))
{
}
else
{
Util.Debug(r.Message + " " + r.StackTrace);
Util.WriteLogWithDetails(r.Message + Environment.NewLine + r.StackTrace + Environment.NewLine + "<b>List ID:</b> " + hf_list_id.Value, "listdistribution_log");
Util.PageMessage(this, "An error occured, please try again.");
}
}
}
}