public static void Delete(string[] args, bool exit = false)
{
if (args.Length == 2) args = (string.Join(" ", args) + " *").Split(" ");
if (args[1].Trim().ToLower() == "smtp") WriteLog("delete(d) smtp * 删除SMTP数据");
if (args[1].Trim().ToLower() == "email") WriteLog("delete(d) email * 删除邮箱数据");
WriteLog("===============================================================================");
if (args[1].Trim().ToLower() == "smtp") {
args = string.Join(" ", args, 2, args.Length - 2).Split(' ');
int len = 0;
if (args.Length == 1 && args [0] == "*")
len = new SQL().Delete(SmtpList._).ToExec();
else
foreach (string s in args) {
string[] ss = s.Split(',');
if (ss.Length != 3) continue;
len += SmtpListHelper.DeleteByID(ss[0], ss[1].ToInt(25), ss[2]) ? 1 : 0;
}
WriteLog("删除影响行数：{0}", len);
SmtpListHelper.ClearCacheAll();
} else if (args[1].Trim().ToLower() == "email") {
args = string.Join(" ", args, 2, args.Length - 2).Split(' ');
int len = 0;
if (args.Length == 1 && args [0] == "*")
len = new SQL().Delete(EmailList._).ToExec();
else
foreach (string s in args) len += EmailListHelper.DeleteByID(s) ? 1 : 0;
WriteLog("删除影响行数：{0}", len);
EmailListHelper.ClearCacheAll();
}
WriteLog("END");
Input(exit);
}
public Exception Create(NumeroControl numControl)
{
//using (SqlConnection conn = sql.GetConnection())
using (SqlConnection conn = new SQL().GetLocalConnection())
{
SqlCommand cmd = new SqlCommand(@"UPDATE NumeroControl SET NumControl = @NumControl WHERE AnoCorriente = @Year;", conn);
cmd.Parameters.AddWithValue("Year", numControl.Year);
cmd.Parameters.AddWithValue("NumControl", numControl.NumControl);
conn.Open();
using (SqlTransaction transaction = conn.BeginTransaction())
{
cmd.Transaction = transaction;
try
{
cmd.ExecuteNonQuery();
transaction.Commit();
return null;
}
catch (Exception ex)
{
transaction.Rollback();
return ex;
}
}
}
}
/// --------------------------------------------------------------------------------
/// <summary>
/// Performs loading an object marked with the FieldMappingObjectHook attribute.
/// Loads an object with fields or properties marked with DatabaseObjects.FieldMapping attributes
/// with the fields from the database.
/// </summary>
/// <param name="objObject">
/// The generic object that does not implement IDatabaseObject
/// but contains FieldMapping attributes.
/// </param>
/// <remarks>
/// Typically used from within the overridden LoadFields function when loading a generic object normally
/// marked with the FieldMappingObjectHook attribute.
/// </remarks>
/// --------------------------------------------------------------------------------
public static void LoadFieldsForObject(object objObject, SQL.SQLFieldValues objFields)
{
if (objObject == null)
throw new ArgumentNullException();
LoadFieldsForObject(objObject, objObject.GetType(), objFields);
}
public static String GetUserTopic(String Author)
{
String commStr = "select * from Topic where Author = '" + Author + "'";
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public static bool ModifyMessage(int TopicId, String key, String values)
{
String commStr = "update Topic set " + key + " = '" + values + "' where id = " + TopicId;
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public static String GetXml(int id)
{
String commStr = "select * from Topic where id = " + id;
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public static bool DeleteTopic(int TopicId)
{
String commStr = "delete from Topic where id = "+TopicId;
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public static String GetRecommandTopic(String Part)
{
String commStr = "select * from Topic where Recommand = 1 and Part in ('" + Part.Substring(0,Part.Length-1).Replace(",","','") + "')";
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public static String GetPartTopic(String Part)
{
String commStr = "select * from Topic where Part = '" + Part + "'";
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public static String GetAllRecommandTopic()
{
String commStr = "select * from Topic where Recommand = 1";
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public void Should_Return_Simple_Delete_From_Table()
{
var expected = "DELETE FROM table";
var sql = new SQL();
sql.Delete().From("table");
Assert.AreEqual(expected, sql.ToString());
}
public static String GetRecommandAnswer()
{
String commStr = "select * from Answer where Recommand = 1";
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public static bool AddPoint(String UserName, int Point)
{
String commStr = "update Users set AccumulatePoint = " + (int.Parse(GetMessage(UserName, "AccumulatePoint"))+Point) + " where UserName = '******'";
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public string GetAllAirDefenseInfo4android(string GUID)
{
SQL sql = new SQL();
if (sql.checkGUID(GUID))
return sql.getinfo();
else
return "false@未授权的访问@NONE";
}
public static String GetXml(String UserName)
{
String commStr = "select UserName,Email,StudentNumber,Major,AccumulatePoint,FocusPart from Users where UserName = '******'";
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public static bool DeleteFocusPart(String UserName, String Part)
{
String commStr = "update Users set FocusPart = '" + GetMessage(UserName, "FocusPart").Replace(Part+",","") + "' where UserName = '******'";
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public static bool ModifyMessage(String UserName,String key,String values)
{
String commStr = "update Users set " + key + " = '" + values + "' where UserName = '******'";
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public static bool Login(String UserName,String Password)
{
String commStr = "select * from Users where UserName = '******' and Password = '******'";
SQL sql = new SQL();
bool f = sql.ExecuteReader(commStr).Read();
sql.Dispose();
return f;
}
public static bool Register(String UserName,String Password)
{
String commStr = "insert into Users values ('" + UserName + "','" + Password + "','','','',0,'')";
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public static bool DeleteAnswer(int id)
{
String commStr = "delete from Answer where id = " + id;
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public static String GetTopicAnswer(int TopicId)
{
String commStr = "select * from Answer where TopicId = " + TopicId;
SQL sql = new SQL();
String ans = sql.ExecuteXml(commStr);
sql.Dispose();
return ans;
}
public static bool CancelRecommandAnswer(int id)
{
String commStr = "update Answer set Recommand = 0 where id = " + id;
SQL sql = new SQL();
bool f = sql.ExecuteNonQuery(commStr);
sql.Dispose();
return f;
}
public void Should_Return_Update_On_One_Field_From_One_Table_With_Where()
{
var expected = "UPDATE table SET f = ? WHERE (FieldOne = ValueOne)";
var sql = new SQL();
sql.Update().Table("table").Set("f", "?").Where("FieldOne = ValueOne");
Assert.AreEqual(expected, sql.ToString());
}
public void Should_Return_Update_On_One_Field_From_One_Table()
{
var expected = "UPDATE table SET f = ?";
var sql = new SQL();
sql.Update().Table("table").Set("f","?");
Assert.AreEqual(expected, sql.ToString());
}
public override string SerializeDataType(SQL.DataType dataType, int size, int precision, int scale)
{
switch (dataType)
{
case DataType.TinyInteger:
return "TINYINT";
case DataType.SmallInteger:
return "SMALLINT";
case DataType.Integer:
return "INTEGER";
case DataType.BigInteger:
return "BIGINT";
case DataType.Character:
return "CHARACTER(" + size.ToString() + ")";
case DataType.UnicodeCharacter:
return "NCHAR(" + size.ToString() + ")";
case DataType.VariableCharacter:
return "VARCHAR(" + size.ToString() + ")";
case DataType.UnicodeVariableCharacter:
return "NVARCHAR(" + size.ToString() + ")";
case DataType.Decimal:
return "DECIMAL(" + precision.ToString() + "," + scale.ToString() + ")";
case DataType.Real:
return "FLOAT";
case DataType.Float:
return "DOUBLE";
case DataType.SmallMoney:
return "DECIMAL(10,4)";
case DataType.Money:
return "DECIMAL(19,4)";
case DataType.Boolean:
return "INTEGER";
case DataType.SmallDateTime:
// TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
return "DATETIME ";
case DataType.DateTime:
// TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
return "DATETIME";
case DataType.TimeStamp:
return "DATETIME";
case DataType.Text:
return "TEXT";
case DataType.UnicodeText:
return "TEXT";
case DataType.Binary:
return "BLOB";
case DataType.VariableBinary:
return "BLOB";
case DataType.Image:
return "BLOB";
case DataType.UniqueIdentifier:
return "GUID";
default:
return base.SerializeDataType(dataType, size, precision, scale);
}
}
public void Should_Return_Insert_In_One_Table()
{
var expected = "INSERT INTO students (name) VALUES (Thomas)";
var sql = new SQL();
sql.Insert()
.Into("students")
.Set("name", "Thomas");
Assert.AreEqual(expected, sql.ToString());
}
public void WriteLog(string Msg)
{
string username = WindowsIdentity.GetCurrent().Name;
SQL ErrSQL = new SQL(ConfigurationManager.AppSettings["UIFS.Log"].ToString());
ErrSQL.OpenDatabase();
ErrSQL.Query = string.Format(ErrSQL.SQLQuery.WriteLOG, 0, ErrSQL.ParseInput(Msg), username);
ErrSQL.cmd = ErrSQL.Command(ErrSQL.Data);
ErrSQL.cmd.ExecuteNonQuery();
ErrSQL.CloseDatabase();
ErrSQL = null;
}
public override string SerializeDataType(SQL.DataType dataType, int size, int precision, int scale)
{
switch (dataType)
{
case DataType.TinyInteger:
return "TINYINT";
case DataType.SmallInteger:
return "SMALLINT";
case DataType.Integer:
return "INT";
case DataType.BigInteger:
return "BIGINT";
case DataType.Character:
return "CHAR(" + size.ToString() + ")";
case DataType.UnicodeCharacter:
return "CHAR(" + size.ToString() + ")";
case DataType.VariableCharacter:
return "VARCHAR(" + size.ToString() + ")";
case DataType.UnicodeVariableCharacter:
return "VARCHAR(" + size.ToString() + ")";
case DataType.Decimal:
return "NUMBER(" + precision.ToString() + "," + scale.ToString() + ")";
case DataType.Real:
return "DOUBLE";
case DataType.Float:
return "DOUBLE";
case DataType.SmallMoney:
return "NUMBER(10,4)";
case DataType.Money:
return "NUMBER(19,4)";
case DataType.Boolean:
return "BOOLEAN";
case DataType.SmallDateTime:
return "DATE";
case DataType.DateTime:
return "DATE";
case DataType.TimeStamp:
return "TIMESTAMP";
case DataType.Text:
return "CLOB(2G)";
case DataType.UnicodeText:
return "CLOB(2G)";
case DataType.Binary:
return "BINARY(8000)";
case DataType.VariableBinary:
return "VARBINARY(8000)";
case DataType.Image:
return "BLOB(2G)";
case DataType.UniqueIdentifier:
return "UNIQUEIDENTIFIER";
}
return base.SerializeDataType(dataType, size, precision, scale);
}
public EDDN.ShipyardObject GetShipFromFDevIDs(SQL.Datasets.dsEliteDB.tbshipyardbaseDataTable basedata, Newtonsoft.Json.Linq.JToken shipyardItem, bool param1)
{
EDDN.ShipyardObject shipyardObject = null;
String skuString = null;
try
{
if(shipyardItem != null)
{
if (shipyardItem.SelectToken("id", false) == null)
throw new NotSupportedException(String.Format("Missing id : {0}", shipyardItem));
SQL.Datasets.dsEliteDB.tbshipyardbaseRow itemData = basedata.FindByid((Int32)shipyardItem.SelectToken("id", false));
if(itemData != null)
{
shipyardObject = new EDDN.ShipyardObject();
shipyardObject.Id = itemData.id;
shipyardObject.Name = itemData.name;
shipyardObject.BaseValue = shipyardItem.SelectToken("basevalue").ToString();
shipyardObject.Sku = skuString = (shipyardItem.SelectToken("sku", false) ?? "").ToString();
if(shipyardItem.SelectToken("unavailableReason", false) != null)
shipyardObject.UnavailableReason = shipyardItem.SelectToken("unavailableReason", false).ToString();
if(shipyardItem.SelectToken("factionId", false) != null)
shipyardObject.FactionID = shipyardItem.SelectToken("factionId", false).ToString();
if(shipyardItem.SelectToken("requiredRank", false) != null)
shipyardObject.RequiredRank = shipyardItem.SelectToken("requiredRank", false).ToString();
}
else
throw new NotSupportedException(String.Format("{0}: Unknown ship", shipyardItem));
}
else
Debug.Print("!");
return shipyardObject;
}
catch (NotSupportedException ex)
{
Program.MainLog.Log(String.Format("Converting error: {0}", ex.Message));
}
catch (Exception ex)
{
throw new Exception("Error while converting companion data to shipyard object", ex);
}
return null;
}
public void Should_Return_Simple_Delete_From_Table_With_Some_Where()
{
var expected = "DELETE FROM students WHERE (id > 5) AND (id < 102)";
var sql = new SQL();
sql.Delete()
.From("students")
.Where("id > 5")
.Where("id < 102");
Assert.AreEqual(expected, sql.ToString());
}
public void CartaCorrecao_NFe(IList <Dados_Arquivo_NFe> ilNotas)
{
CompSoft.Tools.frmWait f = new CompSoft.Tools.frmWait("Aguarde, Enviado Carta de Correção da NF-e para o SEFAZ.", true, ilNotas.Count);
foreach (Dados_Arquivo_NFe daNFe in ilNotas)
{
//-- Busca informações no WebService
CompSoft.NFe.TrataWebService.NFeWebService wb_NFe = new CompSoft.NFe.TrataWebService.NFeWebService();
XmlDocument doc = wb_NFe.CartaCorrecao_NFe(daNFe);
if (doc != null)
{
StringBuilder sb = new StringBuilder(300);
//-- Verifica se a mensagem é de confirmação do cancelamento da NF-e
if (Convert.ToInt32(doc.GetElementsByTagName("cStat")[0].InnerText) == 101)
{
sb.Append("update notas_fiscais_lotes set ");
sb.AppendFormat(" codigo_mensagem_retorno_nfe = {0}", doc.GetElementsByTagName("cStat")[0].InnerText);
sb.AppendFormat(" , protocolo_cancelamento_nfe = '{0}'", doc.GetElementsByTagName("nProt")[0].InnerText);
sb.AppendFormat(" , data_cancelamento_Nfe = '{0}'", doc.GetElementsByTagName("dhRecbto")[0].InnerText.Replace("T", " ").Replace("-", "").Substring(0, 17));
sb.AppendFormat(" where nota_fiscal_lote = {0}", daNFe.Cod_Nota_Fiscal_Lote);
SQL.ExecuteNonQuery(sb.ToString());
sb.Clear();
sb.AppendFormat("update notas_fiscais set cancelada = 1 where nota_fiscal = {0}", daNFe.Nota_Fiscal);
SQL.ExecuteNonQuery(sb.ToString());
ERP.NFe.Enviar_XML_Email envMail_Nfe;
envMail_Nfe.Cancelar_NF(daNFe, doc.GetElementsByTagName("nProt")[0].InnerText);
}
else
{
int iStatusExiste = Convert.ToInt32(SQL.ExecuteScalar(string.Format("SELECT COUNT(*) FROM MENSAGENS_RETORNO_NFE WHERE CODIGO_MENSAGEM_RETORNO = {0}", doc.GetElementsByTagName("cStat")[1].InnerText)));
if (iStatusExiste == 0)
{
string sQuery = string.Format(@"insert MENSAGENS_RETORNO_NFE values({0}, '{1}')"
, doc.GetElementsByTagName("cStat")[1].InnerText
, doc.GetElementsByTagName("xMotivo")[1].InnerText.Replace("'", " "));
SQL.ExecuteNonQuery(sQuery);
}
sb.Append("update notas_fiscais_lotes set ");
sb.AppendFormat(" codigo_mensagem_retorno_nfe = {0}", doc.GetElementsByTagName("cStat")[1].InnerText);
sb.AppendFormat(" where nota_fiscal_lote = {0}", daNFe.Cod_Nota_Fiscal_Lote);
SQL.ExecuteNonQuery(sb.ToString());
sb.Clear();
sb.AppendFormat("update notas_fiscais set NFe_ResultadoCartaCorrecaoXML = @NFe_ResultadoCartaCorrecaoXML where nota_fiscal = {0}", daNFe.Nota_Fiscal);
List <System.Data.SqlClient.SqlParameter> _param = new List <System.Data.SqlClient.SqlParameter>();
_param.Add(new System.Data.SqlClient.SqlParameter("@NFe_ResultadoCartaCorrecaoXML", doc.OuterXml));
SQL.Execute(sb.ToString(), _param);
}
}
f.Atual_Progresso++;
}
f.Close();
f.Dispose();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Util.SetRebindOnWindowClose(this, false);
if (Request.QueryString["mode"] != null && !String.IsNullOrEmpty(Request.QueryString["mode"]))
{
hf_mode.Value = Request.QueryString["mode"];
if (hf_mode.Value == "new" && Request.QueryString["user_id"] != null && !String.IsNullOrEmpty(Request.QueryString["user_id"]))
{
hf_user_id.Value = Request.QueryString["user_id"];
}
else if (hf_mode.Value == "edit" && Request.QueryString["proj_id"] != null && !String.IsNullOrEmpty(Request.QueryString["proj_id"]))
{
hf_project_id.Value = Request.QueryString["proj_id"];
hf_parent_project_id.Value = LeadsUtil.GetProjectParentIDFromID(hf_project_id.Value);
// Shared project stuff
if (Request.QueryString["shared"] != null && Request.QueryString["shared"] == "False")
{
btn_move_or_share.OnClientClicking = "function(button, args){ var rw = GetRadWindow(); var rwm = rw.get_windowManager(); " +
"rwm.open('assignproject.aspx?project_id=" + Server.UrlEncode(hf_parent_project_id.Value) + "&cca_action=1','rw_modify_project').set_title('Move or Share Project'); return false; }";
String qry = "SELECT dbl_project_share.UserID, fullname FROM dbl_project_share, db_userpreferences WHERE dbl_project_share.UserId=db_userpreferences.userid AND ProjectID=@ParentProjID";
DataTable dt_shared_recipients = SQL.SelectDataTable(qry, "@ParentProjID", hf_parent_project_id.Value);
bool IsShared = dt_shared_recipients.Rows.Count > 0;
if (IsShared)
{
tr_sharing_management.Visible = true;
for (int i = 0; i < dt_shared_recipients.Rows.Count; i++)
{
RadTreeNode n = new RadTreeNode(Server.HtmlEncode(dt_shared_recipients.Rows[i]["fullname"].ToString()), dt_shared_recipients.Rows[i]["UserID"].ToString());
n.Checkable = true;
n.Checked = true;
n.CssClass = "RadTreeNode";
rtv_share_recipients.Nodes[0].Nodes.Add(n);
}
Util.PageMessageSuccess(this, "Note, this Project is shared with other staff members. Manage sharing at the top.");
}
}
else
{
// ensure shared recipient can't delete the project (but can remove it from their list)
lbl_header_delete.Text = "Remove this shared <b>Project</b> from your list..";
lbl_title_delete.Text = "If you click Remove Project you will no longer see this shared Project in your Project list, however no Leads will be affected and there will be no effect to the owner of the Project.";
tr_delete_select.Visible = tr_delete_select_2.Visible = false;
btn_delete_project.Text = "Remove Project";
btn_delete_project.OnClientClicking = "function(button, args){ AlertifyConfirm('Are you sure?', 'Sure?', 'Body_btn_remove_project_serv', false);}";
btn_move_or_share.OnClientClicking = "function(button, args){ Alertify('You cannot move or share a Project that has been shared with you','Nope'); return false; }";
}
}
BindDropDowns();
SetFormMode();
}
else
{
Util.PageMessageAlertify(this, LeadsUtil.LeadsGenericError, "Error");
}
}
}
protected void SendManagingDirectorConfirmationMail()
{
DataTable dt_sale_info = Util.GetSalesBookSaleFromID(hf_sale_id.Value);
if (dt_sale_info.Rows.Count > 0)
{
String book_name = Util.GetSalesBookNameFromID(dt_sale_info.Rows[0]["sb_id"].ToString());
String office = Util.GetSalesBookOfficeFromID(dt_sale_info.Rows[0]["sb_id"].ToString());
// Build mail
MailMessage mail = new MailMessage();
mail = Util.EnableSMTP(mail);
mail.Priority = MailPriority.High;
mail.BodyFormat = MailFormat.Html;
mail.From = "[email protected];";
mail.To = Util.GetUserEmailFromUserName(hf_requester.Value);
mail.Cc = Util.GetUserEmailAddress();
if (Security.admin_receives_all_mails)
{
mail.Bcc = Security.admin_email;
}
mail.Subject = "Red-Line Request Approved";
mail.Body =
"<html><head></head><body>" +
"<table style=\"font-family:Verdana; font-size:8pt;\">" +
"<tr><td>Your red-line request has been approved by " + Util.GetUserFullNameFromUserName(User.Identity.Name) + " for <b>"
+ dt_sale_info.Rows[0]["feature"] + " - " + dt_sale_info.Rows[0]["advertiser"] + "</b> from the " + office + " - " + book_name + " book " +
"to the " + lbl_destination_book.Text + " book.<br/><br/></td></tr>" +
"The red-line has been automatically added to the " + lbl_destination_book.Text + " book, go there to view/edit/cancel it at any time.<br/><br/>" +
"<ul><li><b>Invoice: </b>" + dt_sale_info.Rows[0]["invoice"] + "</li>" +
"<li><b>Price:</b> " + Util.TextToCurrency(dt_sale_info.Rows[0]["price"].ToString(), office) + "</li></ul></td></tr>" +
"<tr><td><b><i>Approved by " + User.Identity.Name + " at " + DateTime.Now.ToString().Substring(0, 16) + " (GMT).</i></b><br/><hr/></td></tr>" +
"<tr><td>This is an automated message from the Dashboard Finance page.</td></tr>" +
"<tr><td><br>This message contains confidential information and is intended only for the " +
"individual named. If you are not the named addressee you should not disseminate, distribute " +
"or copy this e-mail. Please notify the sender immediately by e-mail if you have received " +
"this e-mail by mistake and delete this e-mail from your system. E-mail transmissions may contain " +
"errors and may not be entirely secure as information could be intercepted, corrupted, lost, " +
"destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept " +
"liability for any errors or omissions in the contents of this message which arise as a result of " +
"e-mail transmission.</td></tr> " +
"</table>" +
"</body></html>";
// Send mail
try
{
SmtpMail.Send(mail);
// Update request
String uqry = "UPDATE db_redlinerequests SET ApprovedByManagingDirector=1, DateApprovedByManagingDirector=NOW() WHERE RedLineRequestID=@rl_rq_id";
SQL.Update(uqry, "@rl_rq_id", hf_rl_rq_id.Value);
// Update fnotes
uqry = "UPDATE db_salesbook SET " +
"fnotes=CONCAT('Managing Director approved red-line request and e-mail sent to original requester. ("
+ DateTime.Now + " " + HttpContext.Current.User.Identity.Name + ")" + Environment.NewLine + "',fnotes) WHERE ent_id=@ent_id";
SQL.Update(uqry, "@ent_id", hf_sale_id.Value);
String msg = "Red-line request has been approved and confirmation e-mail sent to original requester for sale "
+ dt_sale_info.Rows[0]["feature"] + " - " + dt_sale_info.Rows[0]["advertiser"] + " in the "
+ office + " - " + book_name + " book.";
Util.PageMessage(this, msg
+ "\\n\\nThe red-line has been automatically added to the " + lbl_destination_book.Text + " book, go there to view/edit/cancel it at any time.");
Util.WriteLogWithDetails(msg + " [Step 3 of 3]", "finance_log");
}
catch
{
Util.PageMessage(this, "There was an error approving the request and sending the e-mail, please try again.");
}
}
else
{
Util.PageMessage(this, "There was an error approving this request.");
}
}
protected void BindData(object sender, EventArgs e)
{
String view_expr = "Q = " + dd_quarter.SelectedItem.Text;
if (dd_calmonth.SelectedItem.Text != String.Empty)
{
view_expr = "Cal Month = " + dd_calmonth.SelectedItem.Text;
}
Util.WriteLogWithDetails("Viewing Quarterly Report " + dd_territory.SelectedItem.Text + " - " + dd_year.SelectedItem.Text + " - " + view_expr, "quarterlyreport_log");
String territoryExpr = String.Empty;
if (dd_territory.SelectedItem.Text != "All Territories")
{
territoryExpr = " AND Office=@office ";
}
String yearExpr = " AND YEAR(ent_date)=@year ";
String dateExpr = String.Empty;
if (sender is DropDownList)
{
DropDownList date = sender as DropDownList;
if (date.ID == "dd_quarter")
{
if (dd_quarter.SelectedItem.Text != String.Empty)
{
dd_calmonth.SelectedIndex = 0;
}
else
{
dd_calmonth.SelectedIndex = 1;
}
}
else if (date.ID == "dd_calmonth")
{
if (dd_calmonth.SelectedItem.Text != String.Empty)
{
dd_quarter.SelectedIndex = 0;
}
else
{
dd_quarter.SelectedIndex = 1;
}
}
}
if (dd_quarter.SelectedItem.Text != String.Empty)
{
dateExpr = " AND ent_date BETWEEN " + dd_quarter.SelectedItem.Value.Replace("YRR", dd_year.SelectedItem.Text);
}
else
{
dateExpr = " AND MONTH(ent_date)=@month ";
}
int limit = 25;
if (dd_topfeatures.SelectedItem.Text != "-")
{
Int32.TryParse(dd_topfeatures.SelectedItem.Text, out limit);
}
String price_expr = "CONVERT(SUM(price*conversion),SIGNED)";
// CCA REP STATS
String qry = "SELECT 0 as rank, rep as cca, Office, " + price_expr + " as sum_price, 0.0 as 'change' " +
"FROM db_salesbook sb, db_salesbookhead sbh " +
"WHERE sb.sb_id = sbh.SalesBookID " +
"AND deleted=0 AND IsDeleted=0 AND (red_lined=0 OR (red_lined=1 AND rl_price IS NOT NULL AND rl_price < price)) " + territoryExpr + dateExpr + yearExpr + " " +
"GROUP BY rep, Office " +
"ORDER BY SUM(price) DESC"; // + topExpr;
DataTable dt_rep = SQL.SelectDataTable(qry,
new String[] { "@office", "@year", "@month" },
new Object[] { dd_territory.SelectedItem.Text, dd_year.SelectedItem.Text, dd_calmonth.SelectedIndex });
gv_rep.DataSource = Limit(GroupByNameSortAndRank(dt_rep, (String)ViewState["rep_sortField"], (String)ViewState["rep_sortDir"], GetPreviousData(qry, dateExpr, yearExpr)), limit);
gv_rep.DataBind();
// CCA LIST GEN STATS
qry = qry.Replace("rep", "list_gen");
DataTable dt_lg = SQL.SelectDataTable(qry,
new String[] { "@office", "@year", "@month" },
new Object[] { dd_territory.SelectedItem.Text, dd_year.SelectedItem.Text, dd_calmonth.SelectedIndex });
gv_lg.DataSource = Limit(GroupByNameSortAndRank(dt_lg, (String)ViewState["lg_sortField"], (String)ViewState["lg_sortDir"], GetPreviousData(qry, dateExpr, yearExpr)), limit);
gv_lg.DataBind();
//String topfeatExpr = " LIMIT " + (dt_rep.Rows.Count + dt_lg.Rows.Count + 3);
// CCA FEATURE STATS
qry = "SELECT 0 as rank, feature, Office, " + price_expr + " as sum_price, " +
"GROUP_CONCAT(DISTINCT list_gen) as cca " +
"FROM db_salesbook sb, db_salesbookhead sbh " +
"WHERE sb.sb_id = sbh.SalesBookID " +
"AND deleted=0 AND IsDeleted=0 AND (red_lined=0 OR (red_lined=1 AND rl_price IS NOT NULL AND rl_price < price)) " + territoryExpr + dateExpr + yearExpr + " " +
"GROUP BY feature, Office " +
"ORDER BY SUM(price) DESC";
DataTable dt_feature = SQL.SelectDataTable(qry,
new String[] { "@office", "@year", "@month" },
new Object[] { dd_territory.SelectedItem.Text, dd_year.SelectedItem.Text, dd_calmonth.SelectedIndex });
// JP Exception
qry = "SELECT 0 as rank, feature, Office, " + price_expr + " as sum_price, " +
"GROUP_CONCAT(DISTINCT list_gen) as cca " +
"FROM db_salesbook sb, db_salesbookhead sbh " +
"WHERE sb.sb_id = sbh.SalesBookID " +
"AND deleted=0 AND IsDeleted=0 AND (red_lined=0 OR (red_lined=1 AND rl_price IS NOT NULL AND rl_price < price)) " + territoryExpr + dateExpr + yearExpr + " " +
"AND list_gen='JP'" +
"GROUP BY feature " +
"ORDER BY SUM(price) DESC";
DataTable dt_tmp_feature = SQL.SelectDataTable(qry,
new String[] { "@office", "@year", "@month" },
new Object[] { dd_territory.SelectedItem.Text, dd_year.SelectedItem.Text, dd_calmonth.SelectedIndex });
// JP Exception
dt_feature.Merge(dt_tmp_feature, true, MissingSchemaAction.Ignore);
gv_feature.DataSource = Limit(GroupByNameSortAndRank(dt_feature, (String)ViewState["feature_sortField"], (String)ViewState["feature_sortDir"], String.Empty), gv_lg.Rows.Count + gv_rep.Rows.Count);
gv_feature.DataBind();
// Print
if (sender is ImageButton)
{
String date = dd_quarter.SelectedItem.Text + " Quarter";
if (dd_quarter.SelectedItem.Text == String.Empty)
{
date = dd_calmonth.SelectedItem.Text;
}
Panel print_data = new Panel();
String title = "<h3>Quarterly Report - " + Server.HtmlEncode(dd_territory.SelectedItem.Text)
+ " - " + Server.HtmlEncode(date) + " " + Server.HtmlEncode(dd_year.SelectedItem.Text) + " ("
+ DateTime.Now.ToString().Replace(" ", "-").Replace("/", "_").Replace(":", "_")
.Substring(0, (DateTime.Now.ToString().Length - 3)) + DateTime.Now.ToString("tt") + ")<h3/>";
print_data.Controls.AddAt(0, new Label()
{
Text = title
});
print_data.Controls.Add(gv_feature);
print_data.Controls.Add(new Label()
{
Text = "<br/>"
});
print_data.Controls.Add(gv_lg);
print_data.Controls.Add(new Label()
{
Text = "<br/>"
});
print_data.Controls.Add(gv_rep);
Util.WriteLogWithDetails("Print preview for Quarterly Report successfully generated", "quarterlyreport_log");
Session["qr_print_data"] = print_data;
Response.Redirect("~/Dashboard/PrinterVersion/PrinterVersion.aspx?sess_name=qr_print_data", false);
}
}
protected void BindAppointment(object sender, EventArgs e)
{
ImageButton imbtn_edit = (ImageButton)sender;
GridDataItem gdi = (GridDataItem)imbtn_edit.Parent.Parent;
String AppointmentID = gdi["AppointmentID"].Text;
String qry = "SELECT * FROM dbl_appointments WHERE AppointmentID=@app_id";
DataTable dt_app = SQL.SelectDataTable(qry, "@app_id", AppointmentID);
if (dt_app.Rows.Count > 0)
{
DateTime AppointmentStart = new DateTime();
DateTime AppointmentEnd = new DateTime();
rdp_app_start.SelectedDate = rdp_app_end.SelectedDate = null;
if (DateTime.TryParse(dt_app.Rows[0]["AppointmentStart"].ToString(), out AppointmentStart))
{
rdp_app_start.SelectedDate = AppointmentStart;
}
if (DateTime.TryParse(dt_app.Rows[0]["AppointmentEnd"].ToString(), out AppointmentEnd))
{
rdp_app_end.SelectedDate = AppointmentEnd;
}
tb_app_location.Text = dt_app.Rows[0]["Location"].ToString().Trim();
String Summary = dt_app.Rows[0]["Summary"].ToString().Trim();
rcb_app_subject.Text = Summary;
if (rcb_app_subject.FindItemByText(Summary) == null)
{
BindNextActionTypes();
rcb_app_subject.Items.Insert(1, (new RadComboBoxItem(dt_app.Rows[0]["Summary"].ToString().Trim(), String.Empty)));
rcb_app_subject.SelectedIndex = 1;
}
else if (Summary != String.Empty)
{
rcb_app_subject.SelectedIndex = rcb_app_subject.FindItemByText(Summary).Index;
}
String Description = dt_app.Rows[0]["Description"].ToString().Trim();
if (Description != String.Empty && Description.Contains("Company: "))
{
Description = Description.Substring(0, Description.IndexOf("Company: ")).Trim();
}
tb_app_body.Text = Description;
btn_create_appointment.Visible = false;
btn_update_appointment.Visible = true;
btn_cancel_update_appointment.Visible = true;
lbl_create_or_update_title.Text = "Update an existing <b>Appointment</b>..";
hf_bound_appointment_id.Value = dt_app.Rows[0]["AppointmentID"].ToString();
hf_bound_appointment_google_event_id.Value = dt_app.Rows[0]["GoogleEventID"].ToString();
}
else
{
Util.PageMessageAlertify(this, "Couldn't get information for this appointment, please try again.");
}
BindAppointments();
}
// Binding
protected void BindSelectedUserProfile(object sender, EventArgs e)
{
dd_user_office.Enabled = RoleAdapter.IsUserInRole("db_Admin");
ae_select_user.Enabled = false;
if (dd_select_office.Items.Count > 0 && dd_select_user.Items.Count > 0 && dd_select_user.SelectedItem.Text != String.Empty)
{
String user_id = dd_select_user.SelectedItem.Value;
String office = dd_select_office.SelectedItem.Text;
String qry = "SELECT * " +
"FROM my_aspnet_Users, my_aspnet_Membership, db_userpreferences " +
"WHERE my_aspnet_Users.id = db_userpreferences.UserId " +
"AND my_aspnet_Membership.UserId = my_aspnet_Users.id " +
"AND my_aspnet_Users.id=@user_id";
DataTable dt_user = SQL.SelectDataTable(qry, "@user_id", dd_select_user.SelectedItem.Value);
tbl_user_profile.Visible = dt_user.Rows.Count > 0;
cb_user_employed.Text = Server.HtmlEncode("Unchecking this box disables the account.");
tr_user_date_added.Visible = tr_user_date_last_updated.Visible = tr_user_date_last_login.Visible = true;
btn_force_new_pw.Visible = btn_update_user.Visible = true;
if (dt_user.Rows.Count > 0)
{
String username = dt_user.Rows[0]["name"].ToString().Trim();
String fullname = dt_user.Rows[0]["fullname"].ToString().Trim();
String friendlyname = dt_user.Rows[0]["friendlyname"].ToString().Trim();
String magazine = dt_user.Rows[0]["region"].ToString().Trim();
String sector = dt_user.Rows[0]["channel"].ToString().Trim();
String sub_sector = dt_user.Rows[0]["sector"].ToString().Trim();
String cca_type_id = dt_user.Rows[0]["ccalevel"].ToString().Trim();
String cca_team_id = dt_user.Rows[0]["ccateam"].ToString().Trim();
bool employed = dt_user.Rows[0]["employed"].ToString() == "1";
bool starter = dt_user.Rows[0]["starter"].ToString() == "1";
Color user_colour = Util.ColourTryParse(dt_user.Rows[0]["user_colour"].ToString());
String phone = dt_user.Rows[0]["phone"].ToString().Trim();
String email = dt_user.Rows[0]["email"].ToString().Trim();
String date_added = dt_user.Rows[0]["creationdate"].ToString().Trim();
String last_updated = dt_user.Rows[0]["last_updated"].ToString().Trim();
String last_login = dt_user.Rows[0]["lastlogindate"].ToString().Trim();
bool is_locked_out = dt_user.Rows[0]["islockedout"].ToString() == "True";
lbl_add_edit_user_title.Text = "Viewing <b>" + Server.HtmlEncode(fullname) + "'s</b> profile..";
int idx = dd_user_office.Items.IndexOf(dd_user_office.Items.FindByText(office));
if (idx > -1)
{
dd_user_office.SelectedIndex = idx;
}
hf_user_office.Value = office; // used to check whether office is being changed on update
tb_user_full_name.Text = fullname;
tb_user_friendly_name.Text = friendlyname;
hf_user_friendly_name.Value = friendlyname; // used to check whether friendlyname is being changed on update
tb_user_email.Text = email;
tb_user_phone.Text = phone;
idx = dd_user_magazine.Items.IndexOf(dd_user_magazine.Items.FindByText(magazine));
if (idx > -1)
{
dd_user_magazine.SelectedIndex = idx;
}
idx = dd_user_sector.Items.IndexOf(dd_user_sector.Items.FindByText(sector));
if (idx > -1)
{
dd_user_sector.SelectedIndex = idx;
}
tb_user_sub_sector.Text = sub_sector;
// Set acc. type
for (int i = 0; i < dd_user_account_type.Items.Count; i++)
{
if (RoleAdapter.IsUserInRole(username, dd_user_account_type.Items[i].Value))
{
dd_user_account_type.SelectedIndex = i;
break;
}
}
bool is_cca = dd_user_account_type.SelectedItem.Text == "CCA";
hf_user_cca_type.Value = dd_user_account_type.SelectedItem.Value; // used to check whether user type is being changed on update
// Set CCA team (when not a CCA, cca_team_id = 1
idx = dd_user_cca_team.Items.IndexOf(dd_user_cca_team.Items.FindByValue(cca_team_id));
if (idx > -1)
{
dd_user_cca_team.SelectedIndex = idx;
}
cb_user_employed.Checked = employed;
cb_user_starter.Checked = starter;
lbl_user_date_added.Text = Server.HtmlEncode(date_added);
lbl_user_date_last_updated.Text = Server.HtmlEncode(last_updated);
lbl_user_date_last_login.Text = Server.HtmlEncode(last_login);
rcp_user_colour.SelectedColor = user_colour;
// Set t&d commission
qry = "SELECT * FROM db_commission_t_and_d WHERE user_id=@user_id";
DataTable dt_tnd_comm = SQL.SelectDataTable(qry, "@user_id", user_id);
cb_t_and_d_commission_toggle.Checked = dt_tnd_comm.Rows.Count > 0;
tr_cca_t_and_d_commission.Visible = dt_tnd_comm.Rows.Count > 0;
if (dt_tnd_comm.Rows.Count > 0)
{
String recipient_id = dt_tnd_comm.Rows[0]["trainer_user_id"].ToString();
tb_t_and_d_percentage.Text = dt_tnd_comm.Rows[0]["percentage"].ToString();
Util.MakeOfficeCCASDropDown(dd_t_and_d_recipient, dd_user_office.SelectedItem.Text, false, false, String.Empty, true);
for (int i = 0; i < dd_t_and_d_recipient.Items.Count; i++)
{
// remove this user from list
if (dd_t_and_d_recipient.Items[i].Value == user_id)
{
dd_t_and_d_recipient.Items.RemoveAt(i);
break;
}
}
int recipient_idx = dd_t_and_d_recipient.Items.IndexOf(dd_t_and_d_recipient.Items.FindByValue(recipient_id));
if (recipient_idx != -1)
{
dd_t_and_d_recipient.SelectedIndex = recipient_idx;
}
}
/// set team leader here??
///
// Visibility
tr_cca_type.Visible = tr_cca_team.Visible = tr_cca_t_and_d_commission_toggle.Visible = is_cca;
tr_cca_t_and_d_commission.Visible = cb_t_and_d_commission_toggle.Checked;
btn_update_user.Visible = true;
btn_unlock_user.Visible = is_locked_out;
if (is_locked_out)
{
Util.PageMessageAlertify(this, "This user is currently locked out.<br/><br/>Consider unlocking their account with the Unlock Account button.", "User is Locked Out");
}
}
else
{
Util.PageMessageAlertify(this, "There was an error getting the user information. Please try again", "Error");
}
}
}
/// <include file='../../../../../../../doc/snippets/Microsoft.Data.SqlClient/SqlColumnEncryptionCspProvider.xml' path='docs/members[@name="SqlColumnEncryptionCspProvider"]/DecryptColumnEncryptionKey/*' />
public override byte[] DecryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] encryptedColumnEncryptionKey)
{
// Validate the input parameters
ValidateNonEmptyCSPKeyPath(masterKeyPath, isSystemOp: true);
if (null == encryptedColumnEncryptionKey)
{
throw SQL.NullEncryptedColumnEncryptionKey();
}
if (0 == encryptedColumnEncryptionKey.Length)
{
throw SQL.EmptyEncryptedColumnEncryptionKey();
}
// Validate encryptionAlgorithm
ValidateEncryptionAlgorithm(encryptionAlgorithm, isSystemOp: true);
// Create RSA Provider with the given CSP name and key name
RSACryptoServiceProvider rsaProvider = CreateRSACryptoProvider(masterKeyPath, isSystemOp: true);
// Validate whether the key is RSA one or not and then get the key size
int keySizeInBytes = GetKeySize(rsaProvider);
// Validate and decrypt the EncryptedColumnEncryptionKey
// Format is
// version + keyPathLength + ciphertextLength + keyPath + ciphervtext + signature
//
// keyPath is present in the encrypted column encryption key for identifying the original source of the asymmetric key pair and
// we will not validate it against the data contained in the CMK metadata (masterKeyPath).
// Validate the version byte
if (encryptedColumnEncryptionKey[0] != _version[0])
{
throw SQL.InvalidAlgorithmVersionInEncryptedCEK(encryptedColumnEncryptionKey[0], _version[0]);
}
// Get key path length
int currentIndex = _version.Length;
UInt16 keyPathLength = BitConverter.ToUInt16(encryptedColumnEncryptionKey, currentIndex);
currentIndex += sizeof(UInt16);
// Get ciphertext length
UInt16 cipherTextLength = BitConverter.ToUInt16(encryptedColumnEncryptionKey, currentIndex);
currentIndex += sizeof(UInt16);
// Skip KeyPath
// KeyPath exists only for troubleshooting purposes and doesnt need validation.
currentIndex += keyPathLength;
// validate the ciphertext length
if (cipherTextLength != keySizeInBytes)
{
throw SQL.InvalidCiphertextLengthInEncryptedCEKCsp(cipherTextLength, keySizeInBytes, masterKeyPath);
}
// Validate the signature length
// Signature length should be same as the key side for RSA PKCSv1.5
int signatureLength = encryptedColumnEncryptionKey.Length - currentIndex - cipherTextLength;
if (signatureLength != keySizeInBytes)
{
throw SQL.InvalidSignatureInEncryptedCEKCsp(signatureLength, keySizeInBytes, masterKeyPath);
}
// Get ciphertext
byte[] cipherText = new byte[cipherTextLength];
Buffer.BlockCopy(encryptedColumnEncryptionKey, currentIndex, cipherText, 0, cipherText.Length);
currentIndex += cipherTextLength;
// Get signature
byte[] signature = new byte[signatureLength];
Buffer.BlockCopy(encryptedColumnEncryptionKey, currentIndex, signature, 0, signature.Length);
// Compute the hash to validate the signature
byte[] hash;
using (SHA256 sha256 = SHA256.Create())
{
sha256.TransformFinalBlock(encryptedColumnEncryptionKey, 0, encryptedColumnEncryptionKey.Length - signature.Length);
hash = sha256.Hash;
}
Debug.Assert(hash != null, @"hash should not be null while decrypting encrypted column encryption key.");
// Validate the signature
if (!RSAVerifySignature(hash, signature, rsaProvider))
{
throw SQL.InvalidSignature(masterKeyPath);
}
// Decrypt the CEK
return(RSADecrypt(rsaProvider, cipherText));
}
public OrderByAnalysis(SQL sql)
{
this._Sql = sql;
}
public void DeletePicture(PictureInfo info)
{
SQL.DeletePicture(info.PictureId);
}
public DataTable SelectPicture()
{
var pictures = CBO.FillCollection <PictureInfo>(SQL.ListPicture());
return(CBO.ConvertToDataTable(pictures, typeof(PictureInfo)));
}
protected GridView BuildLHAGridView(String user_id, bool new_rep, bool due)
{
GridView gv = new GridView();
gv.ID = "gv_" + user_id + "_" + new_rep + "_" + due;
// Behaviours
gv.AllowSorting = true;
gv.AutoGenerateColumns = false;
gv.AutoGenerateEditButton = false;
gv.EnableViewState = false;
// Event Handlers
gv.RowDataBound += new GridViewRowEventHandler(gv_RowDataBound);
gv.Sorting += new GridViewSortEventHandler(gv_Sorting);
// Formatting
String date_format = "{0:dd/MM/yyyy}"; //{0:MMM dd} // //"{0:dd MMM}"
gv.HeaderStyle.BackColor = Util.ColourTryParse("#444444");
gv.HeaderStyle.ForeColor = Color.White;
gv.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
gv.HeaderStyle.Font.Size = 8;
gv.RowStyle.HorizontalAlign = HorizontalAlign.Center;
gv.RowStyle.BackColor = Util.ColourTryParse("#f0f0f0");
gv.RowStyle.CssClass = "gv_hover";
gv.HeaderStyle.CssClass = "gv_h_hover";
gv.CssClass = "BlackGridHead";
gv.BorderWidth = 1;
gv.CellPadding = 2;
gv.Width = 1280; // 3690;
gv.ForeColor = Color.Black;
gv.Font.Size = 7;
gv.Font.Name = "Verdana";
// Define Columns
// 0
CommandField cf = new CommandField();
cf.ShowEditButton = true;
cf.ShowDeleteButton = false;
cf.ShowCancelButton = true;
cf.ItemStyle.BackColor = Color.White;
cf.ButtonType = System.Web.UI.WebControls.ButtonType.Image;
cf.EditImageUrl = @"~\images\Icons\gridView_Edit.png";
cf.HeaderText = "";
cf.ItemStyle.Width = 18;
gv.Columns.Add(cf);
// 1
BoundField lha_id = new BoundField();
lha_id.DataField = "LHAID";
gv.Columns.Add(lha_id);
// 2
BoundField rep = new BoundField();
rep.HeaderText = "Rep";
rep.DataField = "Rep";
rep.SortExpression = "Rep";
rep.ItemStyle.Width = 70;
gv.Columns.Add(rep);
// 3
BoundField date_added = new BoundField();
date_added.HeaderText = "Added";
date_added.DataField = "DateAdded";
date_added.SortExpression = "DateAdded";
date_added.DataFormatString = "{0:dd/MM/yy HH:mm}";
date_added.ItemStyle.Width = 90;
gv.Columns.Add(date_added);
// 4
BoundField month_worked = new BoundField();
month_worked.HeaderText = "Month Worked";
month_worked.DataField = "MonthWorked";
month_worked.SortExpression = "MonthWorked";
month_worked.ItemStyle.Width = 90;
gv.Columns.Add(month_worked);
// 5
BoundField association = new BoundField();
association.HeaderText = "Association";
association.DataField = "Association";
association.SortExpression = "Association";
association.ItemStyle.Width = 440;
gv.Columns.Add(association);
// 6
BoundField email = new BoundField();
email.HeaderText = "E-mail";
email.DataField = "Email";
gv.Columns.Add(email);
// 7
BoundField tel = new BoundField();
tel.HeaderText = "Tel";
tel.DataField = "Phone";
gv.Columns.Add(tel);
// 8
BoundField mobile = new BoundField();
mobile.HeaderText = "Mobile";
mobile.DataField = "Mobile";
gv.Columns.Add(mobile);
// 9
BoundField website = new BoundField();
website.HeaderText = "Website";
website.DataField = "Website";
gv.Columns.Add(website);
// 10
BoundField mem_list_due_date = new BoundField();
mem_list_due_date.HeaderText = "Due Date";
mem_list_due_date.DataField = "MemListDueDate";
mem_list_due_date.SortExpression = "MemListDueDate";
mem_list_due_date.DataFormatString = date_format;
mem_list_due_date.ItemStyle.Width = 72;
gv.Columns.Add(mem_list_due_date);
// 11
BoundField lh_due_date = new BoundField();
lh_due_date.HeaderText = "LH Due Date";
lh_due_date.DataField = "LetterheadDueDate";
lh_due_date.SortExpression = "LetterheadDueDate";
lh_due_date.DataFormatString = date_format;
lh_due_date.ItemStyle.Width = 72;
gv.Columns.Add(lh_due_date);
// 12
BoundField main_contact_name = new BoundField();
main_contact_name.HeaderText = "Main Contact";
main_contact_name.DataField = "MainContactName";
main_contact_name.SortExpression = "MainContactName";
main_contact_name.ItemStyle.Width = 155;
gv.Columns.Add(main_contact_name);
// 13
BoundField main_contact_position = new BoundField();
main_contact_position.DataField = "MainContactPosition";
gv.Columns.Add(main_contact_position);
// 14
BoundField list_contact_name = new BoundField();
list_contact_name.HeaderText = "List Contact";
list_contact_name.DataField = "ListContactName";
list_contact_name.SortExpression = "ListContactName";
list_contact_name.ItemStyle.Width = 155;
gv.Columns.Add(list_contact_name);
// 15
BoundField list_contact_position = new BoundField();
list_contact_position.DataField = "ListContactPosition";
gv.Columns.Add(list_contact_position);
// 16
BoundField l_level = new BoundField();
l_level.HeaderText = "L";
l_level.DataField = "LLevel";
l_level.SortExpression = "LLevel";
l_level.ItemStyle.Width = 20;
gv.Columns.Add(l_level);
// 17
BoundField notes = new BoundField();
notes.HeaderText = "N";
notes.DataField = "Notes";
notes.SortExpression = "Notes";
notes.ItemStyle.Width = 20;
gv.Columns.Add(notes);
// 18
CheckBoxField blown = new CheckBoxField();
blown.DataField = "IsBlown";
blown.HeaderText = "B";
blown.ItemStyle.Width = 15;
blown.ControlStyle.BackColor = Color.Red;
gv.Columns.Add(blown);
// 19
CheckBoxField approved = new CheckBoxField();
approved.DataField = "IsApproved";
approved.HeaderText = "A";
approved.ItemStyle.Width = 15;
approved.ControlStyle.BackColor = Color.Lime;
gv.Columns.Add(approved);
// Due expr
String due_expr = "NOT NULL ";
if (due)
{
due_expr = "NULL ";
}
// Blown / Approved
String status_expr = "AND IsBlown=0 AND IsApproved=0";
if (rts.SelectedTab.Text == "Blown")
{
status_expr = "AND IsBlown=1 AND IsApproved=0";
}
else if (rts.SelectedTab.Text == "Approved")
{
status_expr = "AND IsBlown=0 AND IsApproved=1";
}
// Get grid data
String qry = "SELECT LHAID, FriendlyName as Rep, DateAdded, MonthWorked, Association, Email, db_lhas.Phone, Mobile, Website, MemListDueDate, LetterheadDueDate, " +
"MainContactName, MainContactPosition, ListContactName, ListContactPosition, LLevel, Notes, IsBlown, IsApproved " +
"FROM db_lhas, db_userpreferences " +
"WHERE db_lhas.UserID = db_userpreferences.UserID " +
"AND db_lhas.UserID=@userid AND IsDeleted=0 " + status_expr + " AND MemListDueDate IS " + due_expr;
DataTable dt_lhas = SQL.SelectDataTable(qry, "@userid", user_id);
dt_lhas.DefaultView.Sort = (String)ViewState["sort_field"] + " " + (String)ViewState["sort_dir"];
// Bind and add to div
gv.DataSource = dt_lhas;
gv.DataBind();
div_gv.Controls.Add(gv);
// Add break label
if (new_rep && gv.Rows.Count > 0)
{
div_gv.Controls.Add(new Label()
{
Text = "<br/>;"
});
}
return(gv);
}
// New user control
protected String AddNewUserPreferences()
{
try
{
CreateUserWizard.UserName = CreateUserWizard.UserName.Trim();
tb_user_full_name.Text = tb_user_full_name.Text.Trim();
tb_user_friendly_name.Text = tb_user_friendly_name.Text.Trim();
if (newUserFullNameBox.Text == String.Empty || newUserOfficeDropDown.SelectedItem.Text == String.Empty || newUserFriendlyNameBox.Text == String.Empty)
{
pageMessage("Error adding user to Dashboard system. You must fill in at least Full Name, Friendly Name and Office.");
return(insertStatus);
}
else if (newUserFullNameBox.Text.Contains("<") || newUserFullNameBox.Text.Contains(">") || newUserFriendlyNameBox.Text.Contains("<") || newUserFriendlyNameBox.Text.Contains(">"))
{
pageMessage("Error adding user to Dashboard system.");
return(insertStatus);
}
else if (CreateUserWizard.UserName.Contains(" ") || CreateUserWizard.UserName.Contains("<") || CreateUserWizard.UserName.Contains(">"))
{
Util.PageMessage(this, "Error adding user to Dashboard system. Username must be in the format of first initial followed by surname (with no whitespaces or special characters), e.g. jsmith.");
return(insertStatus);
}
else
{
String qry = String.Empty;
try
{
// Check whether user in same territory has same Full Name
qry = "SELECT fullname FROM db_userpreferences WHERE fullname=@fullname AND office=@office";
DataTable dt10 = SQL.SelectDataTable(qry,
new String[] { "@fullname", "@office" },
new Object[] { newUserFullNameBox.Text, newUserOfficeDropDown.SelectedItem.Text });
if (dt10.Rows.Count > 0)
{
pageMessage("Error adding user to Dashboard system. There is already a user in this territory with that Fullname.");
return(insertStatus);
}
// Check whether there's a user in the same territory/secondary ter/commission participation that has same Friendlyname
qry = "SELECT friendlyname FROM db_userpreferences WHERE friendlyname=@friendlyname AND (office=@office OR secondary_office=@office) " +
"UNION SELECT friendlyname FROM db_commissionoffices, db_dashboardoffices, db_userpreferences " +
"WHERE db_commissionoffices.office_id = db_dashboardoffices.office_id " +
"AND db_userpreferences.userid = db_commissionoffices.user_id " +
"AND db_dashboardoffices.office=@office AND friendlyname=@friendlyname";
DataTable dt11 = SQL.SelectDataTable(qry,
new String[] { "@friendlyname", "@office" },
new Object[] { newUserFriendlyNameBox.Text, newUserOfficeDropDown.SelectedItem.Text });
if (dt11.Rows.Count > 0)
{
pageMessage("Error adding user to Dashboard system. There is already a user that sells in this territory with that Friendlyname. Friendlynames must be unique.");
return(insertStatus);
}
}
catch { }
String secondary_office = newUserOfficeDropDown.SelectedItem.Text;
if (newUserOfficeDropDown.SelectedItem.Text == "Norwich" || newUserOfficeDropDown.SelectedItem.Text == "Africa")
{
secondary_office = "Europe";
}
else if (newUserOfficeDropDown.SelectedItem.Text == "Europe")
{
secondary_office = "Africa";
}
int ccalvl = 0;
//Set CCA Group
if (newUserCCAGroupRadioList.SelectedIndex == 3)
{
ccalvl = -1;
}
else
{
ccalvl = newUserCCAGroupRadioList.SelectedIndex;
}
int teamID = 1;
// Get teamID from team name
qry = "SELECT team_id FROM db_ccateams WHERE team_name=@team_name AND office=@office";
DataTable dt1 = SQL.SelectDataTable(qry,
new String[] { "@team_name", "@office" },
new Object[] { Server.HtmlDecode(newUserCCATeamRadioList.Text), newUserOfficeDropDown.SelectedItem.Text });
if (dt1.Rows.Count > 0)
{
Int32.TryParse(dt1.Rows[0]["team_id"].ToString(), out teamID);
}
// Get user_id from userName
String userid = Util.GetUserIdFromName(CreateUserWizard.UserName);
String user_colour = "#777777"; // defauly colour
if (Util.ColourTryParse(newUserColourBox.Text) != Color.Transparent)
{
user_colour = newUserColourBox.Text;
}
// Add new user
String iqry = "INSERT INTO db_userpreferences " +
"(userid, fullname, friendlyname, region, channel, sector, office, ccalevel, ccaTeam, " +
"employed, starter, user_colour, is_team_leader, phone, secondary_office) " +
"VALUES(" +
"@userid," +
"@fullname," +
"@friendlyname," +
"@region," +
"@channel," +
"@sector, " +
"@office, " +
"@ccalevel, " +
"@team_id, " +
"@employed, " +
"@starter, " +
"@user_colour, " +
"0, " + //is_team_leader
"@phone, " +
"@secondary_office)";
String[] pn = new String[] { "@userid", "@fullname", "@friendlyname", "@region", "@channel", "@sector", "@office",
"@ccalevel", "@team_id", "@employed", "@starter", "@user_colour", "@phone", "@secondary_office" };
Object[] pv = new Object[] { userid,
newUserFullNameBox.Text.Trim(),
newUserFriendlyNameBox.Text.Trim(),
dd_new_region.SelectedItem.Text,
dd_sector.SelectedItem.Text,
tb_new_sub_sector.Text.Trim(),
newUserOfficeDropDown.SelectedItem.Text,
ccalvl,
teamID,
Convert.ToInt32(newUserCurrentlyEmployedCheckbox.Checked),
Convert.ToInt32(newUserStarterCheckbox.Checked),
user_colour,
newUserPhoneNumberBox.Text.Trim(),
secondary_office };
SQL.Insert(iqry, pn, pv);
// Stop user from being forced to change their pw on login
if (!cb_new_user_force_new_pw.Checked)
{
String t_iqry = "INSERT INTO db_passwordresetusers VALUES (@username)";
SQL.Insert(t_iqry, "@username", CreateUserWizard.UserName.ToString().Trim().ToLower());
}
// If user is CCA, add commission rules
if (ccalvl != 0)
{
ConfigureCommissionRules(userid, ccalvl, newUserOfficeDropDown.SelectedItem.Text, true);
}
// Add user roles
try
{
removeUserFromAllRoles(CreateUserWizard.UserName, newUserOfficeDropDown.SelectedItem.Text.Replace(" ", String.Empty));
if (dd_NewUserPermission.SelectedIndex != (dd_NewUserPermission.Items.Count - 1))
{
Roles.AddUserToRole(CreateUserWizard.UserName, dd_NewUserPermission.SelectedItem.Value);
}
}
catch { }
try
{
// Lock/unlock account if unemployed/employed
String uqry;
if (!newUserCurrentlyEmployedCheckbox.Checked)
{
uqry = "UPDATE my_aspnet_Membership SET IsLockedOut=1, LastLockedOutDate=NOW(), isApproved=0 WHERE userid = (SELECT id FROM my_aspnet_Users WHERE name=@name)";
}
else
{
uqry = "UPDATE my_aspnet_Membership SET IsLockedOut=0, isApproved=1, FailedPasswordAttemptCount=0 WHERE userid = (SELECT id FROM my_aspnet_Users WHERE name=@name)";
}
SQL.Update(uqry, "@name", CreateUserWizard.UserName);
}
catch { }
String noneExpr = String.Empty;
if (dd_NewUserPermission.Text == "None")
{
noneExpr = " This user will not be able to access any pages as their account type is now set to None.";
}
pageMessage("User " + newUserFullNameBox.Text + " (" + CreateUserWizard.UserName + ") successfully added to the Dashboard system (" + newUserOfficeDropDown.SelectedItem.Text + ")" + noneExpr);
closeUserPanel(null, null);
insertStatus = 1;
return(insertStatus);
}
}
catch (Exception g)
{
}
return(String.Empty);
}
public void Create(SQL _Sql, LambdaExpression _LambdaExpression)
{
if (_LambdaExpression.Body is ConstantExpression)//如果是字符串
{
var body = (_LambdaExpression.Body as ConstantExpression);
this.AddCode(_Sql, body.Value);
}
else if (_LambdaExpression.Body is MemberExpression)
{
var _MemberExpression = _LambdaExpression.Body as MemberExpression;
if (_MemberExpression.Expression is ConstantExpression)
{
var value = Parser.Eval(_LambdaExpression.Body);
this.AddCode(_Sql, value);
}
else if (_MemberExpression.Expression is ParameterExpression)
{
var _ParameterExpression = _MemberExpression.Expression as ParameterExpression;
var _TabName = this.AddAlias(_ParameterExpression.Name, _Sql);
var _DisplayName = DbSettings.KeywordHandle(_MemberExpression.Member.Name);
var column = new List <string>();
column.Add(_TabName + _DisplayName + " AS " + _DisplayName);
this.AddCode(_Sql, string.Join(",", column));
}
}
else if (_LambdaExpression.Body is NewExpression)//如果是匿名对象
{
var body = (_LambdaExpression.Body as NewExpression);
var values = body.Arguments;
var member = body.Members;
var column = new List <string>();
var list_member = member.ToList();
foreach (var item in values)
{
if (item is MemberExpression)
{
var it = item as MemberExpression;
//检查是否有别名
var DisplayName = DbSettings.KeywordHandle(list_member[values.IndexOf(item)].Name);
var _TabName = this.AddAlias((it.Expression as ParameterExpression).Name, _Sql);
column.Add(_TabName + DbSettings.KeywordHandle(it.Member.Name) + " AS " + DisplayName);
}
else if (item is ConstantExpression)
{
var it = item as ConstantExpression;
var val = it.Value;
//检查是否有别名 ''
var DisplayName = list_member[values.IndexOf(item)].Name;
if (!string.IsNullOrEmpty(DisplayName))
{
//判断别名是否 有 SqlString 关键字
if (DisplayName.StartsWith("SqlString"))
{
column.Add(val.ToString());
}
else
{
column.Add(DbSettings.KeywordHandle(val.ToString()) + " AS " + DbSettings.KeywordHandle(DisplayName));
}
}
}
else if (item.Type == typeof(string))
{
//检查是否有别名 ''
var value = Parser.Eval(item).ToString();
var DisplayName = list_member[values.IndexOf(item)].Name;
if (!string.IsNullOrEmpty(DisplayName) && DisplayName.StartsWith("SqlString"))
{
column.Add(value);
}
}
else if (item is ParameterExpression)
{
this.AnalysisParameterExpression(item as ParameterExpression, column);
}
}
this.AddCode(_Sql, string.Join(",", column));
}
else if (_LambdaExpression.Body is ParameterExpression)
{
var column = new List <string>();
this.AnalysisParameterExpression(_LambdaExpression.Body as ParameterExpression, column);
this.AddCode(_Sql, string.Join(",", column));
}
else
{
throw new DbFrameException(" SELECT 语法不支持！");
}
}
private readonly string _expandedAttachDBFilename; // expanded during construction so that CreatePermissionSet & Expand are consistent
internal SqlConnectionString(string connectionString) : base(connectionString, GetParseSynonyms())
{
ThrowUnsupportedIfKeywordSet(KEY.AsynchronousProcessing);
ThrowUnsupportedIfKeywordSet(KEY.Connection_Reset);
ThrowUnsupportedIfKeywordSet(KEY.Context_Connection);
// Network Library has its own special error message
if (ContainsKey(KEY.Network_Library))
{
throw SQL.NetworkLibraryKeywordNotSupported();
}
_integratedSecurity = ConvertValueToIntegratedSecurity();
#if NETCOREAPP
_poolBlockingPeriod = ConvertValueToPoolBlockingPeriod();
#endif
_encrypt = ConvertValueToBoolean(KEY.Encrypt, DEFAULT.Encrypt);
_tdss = ConvertValueToBoolean(KEY.TDSS, DEFAULT.TDSS);
_enlist = ConvertValueToBoolean(KEY.Enlist, DEFAULT.Enlist);
_mars = ConvertValueToBoolean(KEY.MARS, DEFAULT.MARS);
_persistSecurityInfo = ConvertValueToBoolean(KEY.Persist_Security_Info, DEFAULT.Persist_Security_Info);
_pooling = ConvertValueToBoolean(KEY.Pooling, DEFAULT.Pooling);
_replication = ConvertValueToBoolean(KEY.Replication, DEFAULT.Replication);
_userInstance = ConvertValueToBoolean(KEY.User_Instance, DEFAULT.User_Instance);
_multiSubnetFailover = ConvertValueToBoolean(KEY.MultiSubnetFailover, DEFAULT.MultiSubnetFailover);
_commandTimeout = ConvertValueToInt32(KEY.Command_Timeout, DEFAULT.Command_Timeout);
_connectTimeout = ConvertValueToInt32(KEY.Connect_Timeout, DEFAULT.Connect_Timeout);
_loadBalanceTimeout = ConvertValueToInt32(KEY.Load_Balance_Timeout, DEFAULT.Load_Balance_Timeout);
_maxPoolSize = ConvertValueToInt32(KEY.Max_Pool_Size, DEFAULT.Max_Pool_Size);
_minPoolSize = ConvertValueToInt32(KEY.Min_Pool_Size, DEFAULT.Min_Pool_Size);
_packetSize = ConvertValueToInt32(KEY.Packet_Size, DEFAULT.Packet_Size);
_connectRetryCount = ConvertValueToInt32(KEY.Connect_Retry_Count, DEFAULT.Connect_Retry_Count);
_connectRetryInterval = ConvertValueToInt32(KEY.Connect_Retry_Interval, DEFAULT.Connect_Retry_Interval);
_applicationIntent = ConvertValueToApplicationIntent();
_applicationName = ConvertValueToString(KEY.Application_Name, DEFAULT.Application_Name);
_attachDBFileName = ConvertValueToString(KEY.AttachDBFilename, DEFAULT.AttachDBFilename);
_currentLanguage = ConvertValueToString(KEY.Current_Language, DEFAULT.Current_Language);
_dataSource = ConvertValueToString(KEY.Data_Source, DEFAULT.Data_Source);
_localDBInstance = LocalDBAPI.GetLocalDbInstanceNameFromServerName(_dataSource);
_failoverPartner = ConvertValueToString(KEY.FailoverPartner, DEFAULT.FailoverPartner);
_initialCatalog = ConvertValueToString(KEY.Initial_Catalog, DEFAULT.Initial_Catalog);
_password = ConvertValueToString(KEY.Password, DEFAULT.Password);
_trustServerCertificate = ConvertValueToBoolean(KEY.TrustServerCertificate, DEFAULT.TrustServerCertificate);
_authType = ConvertValueToAuthenticationType();
_columnEncryptionSetting = ConvertValueToColumnEncryptionSetting();
_enclaveAttestationUrl = ConvertValueToString(KEY.EnclaveAttestationUrl, DEFAULT.EnclaveAttestationUrl);
_attestationProtocol = ConvertValueToAttestationProtocol();
// Temporary string - this value is stored internally as an enum.
string typeSystemVersionString = ConvertValueToString(KEY.Type_System_Version, null);
string transactionBindingString = ConvertValueToString(KEY.TransactionBinding, null);
_userID = ConvertValueToString(KEY.User_ID, DEFAULT.User_ID);
_workstationId = ConvertValueToString(KEY.Workstation_Id, null);
if (_loadBalanceTimeout < 0)
{
throw ADP.InvalidConnectionOptionValue(KEY.Load_Balance_Timeout);
}
if (_connectTimeout < 0)
{
throw ADP.InvalidConnectionOptionValue(KEY.Connect_Timeout);
}
if (_commandTimeout < 0)
{
throw ADP.InvalidConnectionOptionValue(KEY.Command_Timeout);
}
if (_maxPoolSize < 1)
{
throw ADP.InvalidConnectionOptionValue(KEY.Max_Pool_Size);
}
if (_minPoolSize < 0)
{
throw ADP.InvalidConnectionOptionValue(KEY.Min_Pool_Size);
}
if (_maxPoolSize < _minPoolSize)
{
throw ADP.InvalidMinMaxPoolSizeValues();
}
if ((_packetSize < TdsEnums.MIN_PACKET_SIZE) || (TdsEnums.MAX_PACKET_SIZE < _packetSize))
{
throw SQL.InvalidPacketSizeValue();
}
ValidateValueLength(_applicationName, TdsEnums.MAXLEN_APPNAME, KEY.Application_Name);
ValidateValueLength(_currentLanguage, TdsEnums.MAXLEN_LANGUAGE, KEY.Current_Language);
ValidateValueLength(_dataSource, TdsEnums.MAXLEN_SERVERNAME, KEY.Data_Source);
ValidateValueLength(_failoverPartner, TdsEnums.MAXLEN_SERVERNAME, KEY.FailoverPartner);
ValidateValueLength(_initialCatalog, TdsEnums.MAXLEN_DATABASE, KEY.Initial_Catalog);
ValidateValueLength(_password, TdsEnums.MAXLEN_CLIENTSECRET, KEY.Password);
ValidateValueLength(_userID, TdsEnums.MAXLEN_CLIENTID, KEY.User_ID);
if (null != _workstationId)
{
ValidateValueLength(_workstationId, TdsEnums.MAXLEN_HOSTNAME, KEY.Workstation_Id);
}
if (!string.Equals(DEFAULT.FailoverPartner, _failoverPartner, StringComparison.OrdinalIgnoreCase))
{
// fail-over partner is set
if (_multiSubnetFailover)
{
throw SQL.MultiSubnetFailoverWithFailoverPartner(serverProvidedFailoverPartner: false, internalConnection: null);
}
if (string.Equals(DEFAULT.Initial_Catalog, _initialCatalog, StringComparison.OrdinalIgnoreCase))
{
throw ADP.MissingConnectionOptionValue(KEY.FailoverPartner, KEY.Initial_Catalog);
}
}
// expand during construction so that CreatePermissionSet and Expand are consistent
_expandedAttachDBFilename = ExpandDataDirectory(KEY.AttachDBFilename, _attachDBFileName);
if (null != _expandedAttachDBFilename)
{
if (0 <= _expandedAttachDBFilename.IndexOf('|'))
{
throw ADP.InvalidConnectionOptionValue(KEY.AttachDBFilename);
}
ValidateValueLength(_expandedAttachDBFilename, TdsEnums.MAXLEN_ATTACHDBFILE, KEY.AttachDBFilename);
if (_localDBInstance == null)
{
// fail fast to verify LocalHost when using |DataDirectory|
// still must check again at connect time
string host = _dataSource;
VerifyLocalHostAndFixup(ref host, true, false /*don't fix-up*/);
}
}
else if (0 <= _attachDBFileName.IndexOf('|'))
{
throw ADP.InvalidConnectionOptionValue(KEY.AttachDBFilename);
}
else
{
ValidateValueLength(_attachDBFileName, TdsEnums.MAXLEN_ATTACHDBFILE, KEY.AttachDBFilename);
}
_typeSystemAssemblyVersion = constTypeSystemAsmVersion10;
if (true == _userInstance && !string.IsNullOrEmpty(_failoverPartner))
{
throw SQL.UserInstanceFailoverNotCompatible();
}
if (string.IsNullOrEmpty(typeSystemVersionString))
{
typeSystemVersionString = DbConnectionStringDefaults.TypeSystemVersion;
}
if (typeSystemVersionString.Equals(TYPESYSTEMVERSION.Latest, StringComparison.OrdinalIgnoreCase))
{
_typeSystemVersion = TypeSystem.Latest;
}
else if (typeSystemVersionString.Equals(TYPESYSTEMVERSION.SQL_Server_2000, StringComparison.OrdinalIgnoreCase))
{
_typeSystemVersion = TypeSystem.SQLServer2000;
}
else if (typeSystemVersionString.Equals(TYPESYSTEMVERSION.SQL_Server_2005, StringComparison.OrdinalIgnoreCase))
{
_typeSystemVersion = TypeSystem.SQLServer2005;
}
else if (typeSystemVersionString.Equals(TYPESYSTEMVERSION.SQL_Server_2008, StringComparison.OrdinalIgnoreCase))
{
_typeSystemVersion = TypeSystem.SQLServer2008;
}
else if (typeSystemVersionString.Equals(TYPESYSTEMVERSION.SQL_Server_2012, StringComparison.OrdinalIgnoreCase))
{
_typeSystemVersion = TypeSystem.SQLServer2012;
_typeSystemAssemblyVersion = constTypeSystemAsmVersion11;
}
else
{
throw ADP.InvalidConnectionOptionValue(KEY.Type_System_Version);
}
if (string.IsNullOrEmpty(transactionBindingString))
{
transactionBindingString = DbConnectionStringDefaults.TransactionBinding;
}
if (transactionBindingString.Equals(TRANSACTIONBINDING.ImplicitUnbind, StringComparison.OrdinalIgnoreCase))
{
_transactionBinding = TransactionBindingEnum.ImplicitUnbind;
}
else if (transactionBindingString.Equals(TRANSACTIONBINDING.ExplicitUnbind, StringComparison.OrdinalIgnoreCase))
{
_transactionBinding = TransactionBindingEnum.ExplicitUnbind;
}
else
{
throw ADP.InvalidConnectionOptionValue(KEY.TransactionBinding);
}
if (_applicationIntent == ApplicationIntent.ReadOnly && !string.IsNullOrEmpty(_failoverPartner))
{
throw SQL.ROR_FailoverNotSupportedConnString();
}
if ((_connectRetryCount < 0) || (_connectRetryCount > 255))
{
throw ADP.InvalidConnectRetryCountValue();
}
if ((_connectRetryInterval < 1) || (_connectRetryInterval > 60))
{
throw ADP.InvalidConnectRetryIntervalValue();
}
if (Authentication != SqlAuthenticationMethod.NotSpecified && _integratedSecurity == true)
{
throw SQL.AuthenticationAndIntegratedSecurity();
}
if (Authentication == SqlClient.SqlAuthenticationMethod.ActiveDirectoryIntegrated && HasPasswordKeyword)
{
throw SQL.IntegratedWithPassword();
}
if (Authentication == SqlAuthenticationMethod.ActiveDirectoryInteractive && HasPasswordKeyword)
{
throw SQL.InteractiveWithPassword();
}
if (Authentication == SqlAuthenticationMethod.ActiveDirectoryDeviceCodeFlow && (HasUserIdKeyword || HasPasswordKeyword))
{
throw SQL.DeviceFlowWithUsernamePassword();
}
if (Authentication == SqlAuthenticationMethod.ActiveDirectoryManagedIdentity && HasPasswordKeyword)
{
throw SQL.ManagedIdentityWithPassword(DbConnectionStringBuilderUtil.ActiveDirectoryManagedIdentityString);
}
if (Authentication == SqlAuthenticationMethod.ActiveDirectoryMSI && HasPasswordKeyword)
{
throw SQL.ManagedIdentityWithPassword(DbConnectionStringBuilderUtil.ActiveDirectoryMSIString);
}
}
// Bound user control
protected void UpdateUser(object sender, EventArgs e)
{
try
{
bool update_user = true;
String update_message = String.Empty;
if (!Util.IsValidEmail(tb_user_email.Text.Trim()))
{
Util.PageMessage(this, "The specified e-mail address is not valid, please try again with a valid address. The user was not updated.");
}
else if (tb_user_full_name.Text.Trim() == String.Empty || tb_user_friendly_name.Text.Trim() == String.Empty || dd_user_office.SelectedItem.Text.Trim() == String.Empty)
{
Util.PageMessage(this, "Error updating user. You must specify at least Full Name, Friendlyname and Office.");
}
else
{
// Check whether there's a user in the same territory/secondary ter/commission participation that has same Friendlyname
String user_id = dd_select_user.SelectedItem.Value;
String friendlyname = tb_user_friendly_name.Text.Trim();
String original_friendlyname = hf_user_friendly_name.Value;
String original_office = hf_user_office.Value;
String original_cca_type_id = hf_user_cca_type.Value;
String qry = String.Empty;
if (friendlyname != original_friendlyname)
{
qry = "SELECT userid FROM db_userpreferences WHERE friendlyname=@friendlyname AND friendlyname != '' AND (office=@office OR secondary_office=@office) " +
"UNION SELECT db_userpreferences.userid FROM db_commissionoffices, db_dashboardoffices, db_userpreferences " +
"WHERE db_commissionoffices.office_id = db_dashboardoffices.office_id " +
"AND db_userpreferences.userid = db_commissionoffices.user_id " +
"AND db_dashboardoffices.office=@office AND friendlyname=@friendlyname";
DataTable dt_friendlyname_dupe = SQL.SelectDataTable(qry,
new String[] { "@friendlyname", "@office" },
new Object[] { tb_user_friendly_name.Text.Trim(), dd_user_office.SelectedItem.Text });
if (dt_friendlyname_dupe.Rows.Count > 0 || (friendlyname == "KC" && dd_user_office.SelectedItem.Text != "kchavda")) // reserve KC for kiron
{
update_user = false;
Util.PageMessageAlertify(this, "There is already a user with this friendlyname that sells in " + dd_user_office.SelectedItem.Text + "." +
"Friendlynames must be unique. Please try again with a different friendlyname.", "Duplicate Friendlyname!");
tb_user_friendly_name.Text = hf_user_friendly_name.Value;
}
}
if (update_user)
{
// Prepare data
String user_colour = "#" + rcp_user_colour.SelectedColor.Name;
String cca_type_id = "0";
String cca_team_id = "1"; // NoTeam
if (tr_cca_type.Visible) // if this is a CCA
{
if (dd_user_cca_type.Items.Count > 0)
{
cca_type_id = dd_user_cca_type.SelectedItem.Value;
}
if (dd_user_cca_team.Items.Count > 0)
{
cca_team_id = dd_user_cca_team.SelectedItem.Value;
}
}
String secondary_office = dd_user_office.SelectedItem.Text; /// this is temp until we add office participation
if (dd_user_office.SelectedItem.Text == "Africa")
{
secondary_office = "Europe";
}
else if (dd_user_office.SelectedItem.Text == "Europe" || dd_user_office.SelectedItem.Text == "Middle East")
{
secondary_office = "Africa";
}
String[] pn = new String[] {
"@fullname",
"@friendlyname",
"@magazine",
"@sector",
"@sub_sector",
"@phone",
"@email",
"@cca_type_id",
"@cca_team_id",
"@user_colour",
"@employed",
"@starter",
"@office",
"@secondary_office", /// this is temp until we add office participation
"@user_id"
};
Object[] pv = new Object[] {
tb_user_full_name.Text.Trim(),
tb_user_friendly_name.Text.Trim(),
dd_user_magazine.SelectedItem.Text, /// should use ID
dd_user_sector.SelectedItem.Text, /// should use ID
tb_user_sub_sector.Text.Trim(),
tb_user_phone.Text.Trim(),
tb_user_email.Text.Trim(),
cca_type_id,
cca_team_id,
user_colour,
cb_user_employed.Checked,
cb_user_starter.Checked,
dd_user_office.SelectedItem.Text, /// should use ID
secondary_office, /// this is temp until we add office participation
user_id
};
///"is_team_leader=@is_team_leader," +
///need to make userid the unique identifier
// Update user preferences and membership
String uqry = "UPDATE db_userpreferences, my_aspnet_membership " +
"SET fullname=@fullname,friendlyname=@friendlyname,region=@magazine,channel=@sector,sector=@sub_sector,phone=@phone,email=@email,ccalevel=@cca_type_id," +
"ccateam=@cca_team_id,user_colour=@user_colour,employed=@employed,starter=@starter,office=@office,secondary_office=@secondary_office,last_updated=CURRENT_TIMESTAMP " +
"WHERE db_userpreferences.userid = my_aspnet_membership.userid AND db_userpreferences.userid=@user_id";
SQL.Update(uqry, pn, pv);
// Update references to old friendlyname if friendlyname has been changed
if (friendlyname != original_friendlyname)
{
update_message += "Friendlyname changed from " + original_friendlyname + " to " + friendlyname + ".";
pn = new String[] { "@newname", "@oldname", "@office", };
pv = new Object[] { friendlyname, original_friendlyname, dd_user_office.SelectedItem.Text };
// Update SB rep
uqry = "UPDATE db_salesbook SET rep=@newname WHERE sb_id IN (SELECT sb_id FROM db_salesbookhead WHERE centre=@office) AND rep=@oldname";
SQL.Update(uqry, pn, pv);
// Update SB list_Gen
uqry = "UPDATE db_salesbook SET list_gen=@newname WHERE sb_id IN (SELECT sb_id FROM db_salesbookhead WHERE centre=@office) AND list_gen=@oldname";
SQL.Update(uqry, pn, pv);
// Update LD listcca
uqry = "UPDATE db_listdistributionlist SET " +
" listcca=@newname WHERE listIssue_id IN (SELECT listIssue_id FROM db_listdistributionhead WHERE office=@office) AND listcca=@oldname";
SQL.Update(uqry, pn, pv);
// Update LD cca (can't update all 'cca' fields as may contain "/")
uqry = "UPDATE db_listdistributionlist SET " +
" cca=@newname WHERE listIssue_id IN (SELECT listIssue_id FROM db_listdistributionhead WHERE office=@office) AND cca=@oldname";
SQL.Update(uqry, pn, pv);
// Update LD list_gen
uqry = "UPDATE db_listdistributionlist SET " +
" list_gen=@newname WHERE listIssue_id IN (SELECT listIssue_id FROM db_listdistributionhead WHERE office=@office) AND list_gen=@oldname";
SQL.Update(uqry, pn, pv);
// Update Pros Reports rep
uqry = "UPDATE db_prospectreport SET " +
" rep=@newname WHERE team_id IN (SELECT team_id FROM db_ccateams WHERE office=@office) AND rep=@oldname ";
SQL.Update(uqry, pn, pv);
// Update OLD Comm Forms friendlyname
uqry = "UPDATE IGNORE old_db_commforms SET friendlyname=@newname WHERE centre=@office AND friendlyname=@oldname";
SQL.Update(uqry, pn, pv);
// UPDATE OLD commforms outstanding
uqry = "UPDATE IGNORE old_db_commformsoutstanding SET friendlyname=@newname WHERE centre=@office AND friendlyname=@oldname";
SQL.Update(uqry, pn, pv);
}
// Update commission rules if cca_type has changed (if applicable)
bool updated_commission = false;
if (original_cca_type_id != cca_type_id && cca_type_id != "0")
{
ConfigureUserCommissionRules(dd_select_user.SelectedItem.Value, cca_type_id, dd_user_office.SelectedItem.Text, false);
updated_commission = true;
// Update type in current progress report
uqry = "UPDATE db_progressreport SET ccaLevel=@new_cca_level WHERE userid=@user_id " +
"AND pr_id IN (SELECT pr_id FROM db_progressreporthead WHERE centre=@office AND NOW() BETWEEN start_date AND DATE_ADD(start_date, INTERVAL 5 DAY))";
SQL.Update(uqry,
new String[] { "@new_cca_level", "@user_id", "@office" },
new Object[] { cca_type_id, user_id, dd_user_office.SelectedItem.Text.Trim() }
);
}
// Update t&d commission
String dqry = "DELETE FROM db_commission_t_and_d WHERE user_id=@user_id";
SQL.Delete(dqry, "@user_id", dd_select_user.SelectedItem.Value);
double td_percentage = 0;
if (cb_t_and_d_commission_toggle.Checked && dd_t_and_d_recipient.Items.Count > 0 && Double.TryParse(tb_t_and_d_percentage.Text, out td_percentage))
{
String iqry = "INSERT INTO db_commission_t_and_d (user_id, trainer_user_id, percentage) VALUES (@user_id, @trainer_id, @percentage)";
SQL.Insert(iqry,
new String[] { "@user_id", "@trainer_id", "@percentage" },
new Object[] { user_id, dd_t_and_d_recipient.SelectedItem.Value, td_percentage });
// Update any existing deals with these new rules (if selected)
if (cb_t_and_d_update.Checked)
{
// Update existing percentages
uqry = "UPDATE db_commission_t_and_d_sales SET percentage=@percentage WHERE sale_user_id=@user_id";
SQL.Update(uqry, new String[] { "@percentage", "@user_id" }, new Object[] { td_percentage, user_id });
// Update which Trainer will receive the commission (update comm. form IDs)
qry = "SELECT DISTINCT db_commission_t_and_d_sales.form_id, year, month " +
"FROM db_commission_t_and_d_sales, db_commissionforms " +
"WHERE db_commission_t_and_d_sales.form_id = db_commissionforms.form_id AND sale_user_id=@user_id";
DataTable dt_tnd_sales = SQL.SelectDataTable(qry, "@user_id", user_id);
for (int i = 0; i < dt_tnd_sales.Rows.Count; i++)
{
String old_form_id = dt_tnd_sales.Rows[i]["form_id"].ToString();
String form_month = dt_tnd_sales.Rows[i]["month"].ToString();
String form_year = dt_tnd_sales.Rows[i]["year"].ToString();
qry = "SELECT form_id FROM db_commissionforms WHERE year=@year AND month=@month AND user_id=@new_trainer_id";
String new_form_id = SQL.SelectString(qry, "form_id",
new String[] { "@year", "@month", "@new_trainer_id" },
new Object[] { form_year, form_month, dd_t_and_d_recipient.SelectedItem.Value });
if (new_form_id != String.Empty)
{
uqry = "UPDATE db_commission_t_and_d_sales SET form_id=@new_form_id WHERE form_id=@old_form_id AND sale_user_id=@user_id";
SQL.Update(uqry,
new String[] { "@new_form_id", "@old_form_id", "@user_id" },
new Object[] { new_form_id, old_form_id, user_id });
}
}
}
}
// User account locking (where applicable)
if (!cb_user_employed.Checked)
{
qry = "SELECT IsLockedOut FROM my_aspnet_Users, my_aspnet_Membership WHERE my_aspnet_Users.id = my_aspnet_Membership.userid AND my_aspnet_Users.id=@user_id";
DataTable dt_user_lockedout = SQL.SelectDataTable(qry, "@user_id", user_id);
if (dt_user_lockedout.Rows.Count > 0 && dt_user_lockedout.Rows[0]["IsLockedOut"].ToString() == "False")
{
update_message += "Set unemployed (also locked out - account is now disabled). ";
uqry = "UPDATE my_aspnet_Membership SET IsLockedOut=1, LastLockedOutDate=NOW(), isApproved=0 WHERE userid=@user_id";
SQL.Update(uqry, "@user_id", user_id);
}
}
// CCA Teams data
// Prospect Reports data
uqry = "UPDATE db_prospectreport SET " +
"team_id=@team_id WHERE rep=@original_friendlyname AND blown=0 AND listin=0 AND team_id IN (SELECT team_id FROM db_ccateams WHERE office=@original_office OR team_name='NoTeam')";
SQL.Update(uqry,
new String[] { "@team_id", "@original_office", "@original_friendlyname" },
new Object[] { cca_team_id, original_office, original_friendlyname });
/// need to do team leader stuff here ??
///
// If office has changed
if (original_office != dd_user_office.SelectedItem.Text)
{
/// ApplyRolesTemplate("change_office");
///
if (!updated_commission) // re-run comission changes to add new rule based on new office's default comm values
{
ConfigureUserCommissionRules(user_id, cca_type_id, dd_user_office.SelectedItem.Text, false);
}
// Re-bind user list, but make sure we keep showing the user profile we just updated
BindUsersAndTeamsInSelectedOffice("showprofile", null);
}
// Reset original values to new values
hf_user_friendly_name.Value = tb_user_friendly_name.Text.Trim();
hf_user_cca_type.Value = cca_type_id;
hf_user_office.Value = dd_user_office.SelectedItem.Text;
// Refresh profile
BindSelectedUserProfile(null, null);
if (update_message != String.Empty)
{
update_message = "<br/><br/>" + update_message;
}
Util.PageMessageAlertify(this, tb_user_full_name.Text + "'s profile updated!" + update_message, "Updated!");
/// log changes
}
}
}
catch (Exception r) { Util.Debug(r.Message + " " + r.StackTrace); }/// remove try
}
protected void CreateOrUpdateAppointment(object sender, EventArgs e)
{
if (CheckAuthenticated())
{
RadButton btn = (RadButton)sender;
bool CreatingAppointment = btn.Text.Contains("Create");
DateTime AppointmentStart = new DateTime();
DateTime AppointmentEnd = new DateTime();
if (rdp_app_start.SelectedDate != null && rdp_app_end.SelectedDate != null &&
DateTime.TryParse(rdp_app_start.SelectedDate.ToString(), out AppointmentStart) && DateTime.TryParse(rdp_app_end.SelectedDate.ToString(), out AppointmentEnd) &&
AppointmentStart <= AppointmentEnd)
{
String tz = "America/Los_Angeles";
if (Util.GetOfficeRegion(Util.GetUserTerritory()) == "UK")
{
tz = "Europe/London";
}
// Craft appointment (event)
Event Appointment = new Event();
Appointment.Created = DateTime.Now;
Appointment.Start = new EventDateTime()
{
DateTime = AppointmentStart, TimeZone = tz
};
Appointment.End = new EventDateTime()
{
DateTime = AppointmentEnd, TimeZone = tz
};
Appointment.Status = dd_app_status.SelectedItem.Value; // confirmed / tentative / cancelled
Appointment.Location = tb_app_location.Text.Trim();
Appointment.Summary = rcb_app_subject.Text.Trim();
Appointment.Description = tb_app_body.Text.Trim();
// Add Lead information to the appointment by default
String qry = "SELECT * 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=@LeadID";
DataTable dt_lead_info = SQL.SelectDataTable(qry, "@LeadID", hf_lead_id.Value);
if (dt_lead_info.Rows.Count > 0)
{
String LeadAppend = String.Empty;
String CompanyName = dt_lead_info.Rows[0]["company_name"].ToString().Trim();
String ContactName = (dt_lead_info.Rows[0]["first_name"] + " " + dt_lead_info.Rows[0]["last_name"]).Trim();
String Country = dt_lead_info.Rows[0]["country"].ToString().Trim();
String Industry = dt_lead_info.Rows[0]["industry"].ToString().Trim();
String CompanyPhone = dt_lead_info.Rows[0]["phone"].ToString().Trim();
String CompanyPhoneCode = dt_lead_info.Rows[0]["phone_code"].ToString().Trim();
String Website = dt_lead_info.Rows[0]["website"].ToString().Trim();
String JobTitle = dt_lead_info.Rows[0]["job_title"].ToString().Trim();
String Email = dt_lead_info.Rows[0]["email"].ToString().Trim();
String PersonalEmail = dt_lead_info.Rows[0]["personal_email"].ToString().Trim();
String Phone = dt_lead_info.Rows[0]["phone"].ToString().Trim();
String Mobile = dt_lead_info.Rows[0]["mobile"].ToString().Trim();
String CpyPhone = CompanyPhone;
if (CompanyPhone != String.Empty)
{
CpyPhone = "(" + CompanyPhoneCode + ")" + CompanyPhone;
}
if (CompanyName == String.Empty)
{
CompanyName = "None";
}
if (ContactName == String.Empty)
{
ContactName = "None";
}
if (Country == String.Empty)
{
Country = "None";
}
if (Industry == String.Empty)
{
Industry = "None";
}
if (Website == String.Empty)
{
Website = "None";
}
if (JobTitle == String.Empty)
{
JobTitle = "None";
}
if (Email == String.Empty)
{
Email = "None";
}
if (PersonalEmail == String.Empty)
{
PersonalEmail = "None";
}
if (Phone == String.Empty)
{
Phone = "None";
}
if (Mobile == String.Empty)
{
Mobile = "None";
}
if (CpyPhone == String.Empty)
{
CpyPhone = "None";
}
String br = Environment.NewLine + Environment.NewLine;
if (Appointment.Description == String.Empty)
{
br = String.Empty;
}
LeadAppend = br +
"Company: " + CompanyName + Environment.NewLine +
"Country: " + Country + Environment.NewLine +
"Industry: " + Industry + Environment.NewLine +
"Company Phone: " + CpyPhone + Environment.NewLine +
"Website: " + Website + Environment.NewLine +
"Contact: " + ContactName + Environment.NewLine +
"Job Title: " + JobTitle + Environment.NewLine +
"E-mail: " + Email + Environment.NewLine +
"Personal E-mail: " + PersonalEmail + Environment.NewLine +
"Phone: " + Phone + Environment.NewLine +
"Mobile: " + Mobile;
if (Appointment.Summary == String.Empty)
{
Appointment.Summary += "App. w/ " + ContactName;
}
else if (!Appointment.Summary.Contains(ContactName))
{
Appointment.Summary += " w/ " + ContactName;
}
// Attendees
if (btn_include_attendees.SelectedToggleState.Value == "True" && Util.IsValidEmail(tb_app_attendees.Text))
{
String[] AttendeesArray = tb_app_attendees.Text.Trim().Split(';');
Appointment.Attendees = new List <EventAttendee>();
foreach (String ae in AttendeesArray)
{
String AttendeeEmail = ae.Trim().Replace(";", String.Empty);
if (AttendeeEmail != String.Empty)
{
EventAttendee Attendee = new EventAttendee()
{
Email = AttendeeEmail
};
Appointment.Attendees.Add(Attendee);
}
}
}
else
{
Appointment.Description += LeadAppend;
}
}
// Get Calendar service
CalendarService service = LeadsUtil.GetCalendarService(hf_uri.Value, hf_user_id.Value);
if (service != null)
{
if (CreatingAppointment) // creating appointment
{
Appointment = service.Events.Insert(Appointment, LeadsUtil.GoogleCalendarID).Execute();
if (Appointment != null)
{
// insert into db
String iqry = "INSERT INTO dbl_appointments (LeadID, GoogleEventID, AppointmentStart, AppointmentEnd, Summary, Description, Location) VALUES (@LeadID, @GoogleEventID, @AppointmentStart, @AppointmentEnd, @Summary, @Description, @Location)";
SQL.Insert(iqry,
new String[] { "@LeadID", "@GoogleEventID", "@AppointmentStart", "@AppointmentEnd", "@Summary", "@Description", "@Location" },
new Object[] { hf_lead_id.Value, Appointment.Id, AppointmentStart, AppointmentEnd, Appointment.Summary, Appointment.Description, Appointment.Location });
// Log
LeadsUtil.AddLeadHistoryEntry(hf_lead_id.Value, "Adding Google appointment: " + Appointment.Summary);
RefreshNextAppointment();
Util.PageMessageSuccess(this, "Appointment Created!");
}
else
{
Util.PageMessageError(this, "Something went wrong!", "bottom-right");
}
}
else // updating existing appointment
{
String AppointmentID = hf_bound_appointment_id.Value;
String GoogleEventID = hf_bound_appointment_google_event_id.Value;
// Update
service.Events.Update(Appointment, LeadsUtil.GoogleCalendarID, GoogleEventID).Execute();
String uqry = "UPDATE dbl_appointments SET AppointmentStart=@AppointmentStart, AppointmentEnd=@AppointmentEnd, Summary=@Summary, Description=@Description, Location=@Location, DateUpdated=CURRENT_TIMESTAMP WHERE AppointmentID=@AppointmentID";
SQL.Update(uqry,
new String[] { "@AppointmentID", "@AppointmentStart", "@AppointmentEnd", "@Summary", "@Description", "@Location" },
new Object[] { AppointmentID, AppointmentStart, AppointmentEnd, Appointment.Summary, Appointment.Description, Appointment.Location });
// Log
LeadsUtil.AddLeadHistoryEntry(hf_lead_id.Value, "Updating Google appointment: " + Appointment.Summary);
RefreshNextAppointment();
Util.PageMessageSuccess(this, "Appointment Updated!");
CancelUpdateAppointment(null, null);
}
LeadsUtil.SetNoRebindOnWindowClose(this, false);
}
else
{
Util.PageMessageAlertify(this, "Error getting calendar service from Google, please try again.");
}
}
else
{
Util.PageMessageAlertify(this, "Please pick a valid datespan!", "Dates Aren't Right");
}
}
BindAppointments();
}
override protected DbConnectionInternal CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
{
SqlConnectionString opt = (SqlConnectionString)options;
SqlConnectionPoolKey key = (SqlConnectionPoolKey)poolKey;
SqlInternalConnection result = null;
SessionData recoverySessionData = null;
SqlConnection sqlOwningConnection = owningConnection as SqlConnection;
bool applyTransientFaultHandling = sqlOwningConnection != null ? sqlOwningConnection._applyTransientFaultHandling : false;
SqlConnectionString userOpt = null;
if (userOptions != null)
{
userOpt = (SqlConnectionString)userOptions;
}
else if (sqlOwningConnection != null)
{
userOpt = (SqlConnectionString)(sqlOwningConnection.UserConnectionOptions);
}
if (sqlOwningConnection != null)
{
recoverySessionData = sqlOwningConnection._recoverySessionData;
}
if (opt.ContextConnection)
{
result = GetContextConnection(opt, poolGroupProviderInfo);
}
else
{
bool redirectedUserInstance = false;
DbConnectionPoolIdentity identity = null;
// Pass DbConnectionPoolIdentity to SqlInternalConnectionTds if using integrated security.
// Used by notifications.
if (opt.IntegratedSecurity || opt.UsesCertificate || opt.Authentication == SqlAuthenticationMethod.ActiveDirectoryIntegrated)
{
if (pool != null)
{
identity = pool.Identity;
}
else
{
identity = DbConnectionPoolIdentity.GetCurrent();
}
}
// FOLLOWING IF BLOCK IS ENTIRELY FOR SSE USER INSTANCES
// If "user instance=true" is in the connection string, we're using SSE user instances
if (opt.UserInstance)
{
// opt.DataSource is used to create the SSE connection
redirectedUserInstance = true;
string instanceName;
if ((null == pool) ||
(null != pool && pool.Count <= 0))
{ // Non-pooled or pooled and no connections in the pool.
SqlInternalConnectionTds sseConnection = null;
try
{
// What about a failure - throw? YES!
// BUG (VSTFDevDiv) 479687: Using TransactionScope with Linq2SQL against user instances fails with "connection has been broken" message
// NOTE: Cloning connection option opt to set 'UserInstance=True' and 'Enlist=False'
// This first connection is established to SqlExpress to get the instance name
// of the UserInstance.
SqlConnectionString sseopt = new SqlConnectionString(opt, opt.DataSource, true /* user instance=true */, false /* set Enlist = false */);
sseConnection = new SqlInternalConnectionTds(identity, sseopt, key.Credential, null, "", null, false, applyTransientFaultHandling: applyTransientFaultHandling);
// NOTE: Retrieve <UserInstanceName> here. This user instance name will be used below to connect to the Sql Express User Instance.
instanceName = sseConnection.InstanceName;
if (!instanceName.StartsWith("\\\\.\\", StringComparison.Ordinal))
{
throw SQL.NonLocalSSEInstance();
}
if (null != pool)
{ // Pooled connection - cache result
SqlConnectionPoolProviderInfo providerInfo = (SqlConnectionPoolProviderInfo)pool.ProviderInfo;
// No lock since we are already in creation mutex
providerInfo.InstanceName = instanceName;
}
}
finally
{
if (null != sseConnection)
{
sseConnection.Dispose();
}
}
}
else
{ // Cached info from pool.
SqlConnectionPoolProviderInfo providerInfo = (SqlConnectionPoolProviderInfo)pool.ProviderInfo;
// No lock since we are already in creation mutex
instanceName = providerInfo.InstanceName;
}
// NOTE: Here connection option opt is cloned to set 'instanceName=<UserInstanceName>' that was
// retrieved from the previous SSE connection. For this UserInstance connection 'Enlist=True'.
// options immutable - stored in global hash - don't modify
opt = new SqlConnectionString(opt, instanceName, false /* user instance=false */, null /* do not modify the Enlist value */);
poolGroupProviderInfo = null; // null so we do not pass to constructor below...
}
result = new SqlInternalConnectionTds(identity, opt, key.Credential, poolGroupProviderInfo, "", null, redirectedUserInstance, userOpt, recoverySessionData, key.ServerCertificateValidationCallback, key.ClientCertificateRetrievalCallback, pool, key.AccessToken, key.OriginalNetworkAddressInfo, applyTransientFaultHandling: applyTransientFaultHandling);
}
return(result);
}
protected void BindRequestInfo()
{
// Get request information
bool error = false;
String qry = "SELECT * FROM db_redlinerequests WHERE RedLineRequestID=@rl_rq_id";
DataTable dt_request = SQL.SelectDataTable(qry, "@rl_rq_id", hf_rl_rq_id.Value);
if (dt_request.Rows.Count > 0)
{
// Set request info
bool is_rd_copy = dt_request.Rows[0]["ApprovedByRegionalDirector"].ToString() == "False";
bool is_md_copy = (dt_request.Rows[0]["ApprovedByManagingDirector"].ToString() == "False" && !is_rd_copy);
bool is_user_valid = false;
// Allow Glen to confirm receipts on any of his accounts
is_user_valid = IsValidCrossAccountRequest("gwhite", "Glen White", "Glen", dt_request.Rows[0]["RequestedToRegionalDirectorName"].ToString(), dt_request.Rows[0]["RequestedToManagingDirectorName"].ToString());
// Validate this user as correct recipient
if (is_rd_copy && User.Identity.Name == dt_request.Rows[0]["RequestedToRegionalDirectorName"].ToString() ||
is_md_copy && User.Identity.Name == dt_request.Rows[0]["RequestedToManagingDirectorName"].ToString() ||
is_user_valid)
{
if (is_rd_copy && !is_md_copy) // for RD copy
{
hf_approval_type.Value = "rd";
lbl_requested_by.Text = "<b>" + Server.HtmlEncode(dt_request.Rows[0]["RequestedBy"].ToString())
+ "</b> is requesting your approval to red-line the following sale:";
lbl_requested_for.Text = "<h2>Regional Director Request Receipt</h2>";
lbl_request_info.Text = "Once approved, an e-mail request will be sent to the Group Managing Director ("
+ Server.HtmlEncode(dt_request.Rows[0]["RequestedToManagingDirectorName"].ToString()) + ") for final approval.";
}
else if (!is_rd_copy && is_md_copy) // for MD copy
{
hf_approval_type.Value = "md";
lbl_requested_by.Text = "<b>" + Server.HtmlEncode(dt_request.Rows[0]["RequestedBy"].ToString())
+ "</b> and <b>" + Server.HtmlEncode(dt_request.Rows[0]["RequestedToRegionalDirectorName"].ToString()) + "</b> are requesting your approval to red-line the following sale:";
lbl_requested_for.Text = "<h2>Managing Director Request Receipt</h2>";
lbl_request_info.Text = "Once approved, an alert e-mail will be sent back to the original requester ("
+ Server.HtmlEncode(dt_request.Rows[0]["RequestedBy"].ToString()) + ").";
}
else
{
error = true;
}
hf_reason.Value = dt_request.Rows[0]["ReasonForRequest"].ToString();
hf_md_email.Value = dt_request.Rows[0]["RequestedToManagingDirectorEmail"].ToString();
hf_requester.Value = dt_request.Rows[0]["RequestedBy"].ToString();
hf_rl_dest_book_id.Value = dt_request.Rows[0]["DestinationSalesBookID"].ToString();
hf_red_line_value.Value = dt_request.Rows[0]["RedLineValue"].ToString();
lbl_reason.Text = Server.HtmlEncode(dt_request.Rows[0]["ReasonForRequest"].ToString());
lbl_red_line_value.Text = Server.HtmlEncode(dt_request.Rows[0]["RedLineValue"].ToString())
+ " of " + Server.HtmlEncode(dt_request.Rows[0]["CurrentOutstanding"].ToString()) + " outstanding";
lbl_destination_book.Text = Server.HtmlEncode(Util.GetSalesBookNameFromID(dt_request.Rows[0]["DestinationSalesBookID"].ToString()));
lbl_time_requested.Text = Server.HtmlEncode(dt_request.Rows[0]["DateRequested"].ToString());
// Set sale information
String sale_id = dt_request.Rows[0]["SaleID"].ToString();
hf_sale_id.Value = sale_id;
qry = "SELECT * " +
"FROM db_salesbook sb, db_salesbookhead sbh, db_financesales fs " +
"WHERE sb.sb_id = sbh.SalesBookID " +
"AND sb.ent_id = fs.SaleID " +
"AND sb.ent_id=@sale_id";
DataTable dt_rl = SQL.SelectDataTable(qry, "@sale_id", sale_id);
bool has_publication_date = false;
if (dt_rl.Rows.Count > 0)
{
lbl_region.Text = Server.HtmlEncode(dt_rl.Rows[0]["Office"].ToString());
lbl_date.Text = Server.HtmlEncode(dt_rl.Rows[0]["ent_date"].ToString().Substring(0, 10));
lbl_rep.Text = Server.HtmlEncode(dt_rl.Rows[0]["rep"].ToString());
lbl_list_gen.Text = Server.HtmlEncode(dt_rl.Rows[0]["list_gen"].ToString());
lbl_publication_month.Text = Server.HtmlEncode(Util.GetIssuePublicationDate(dt_rl.Rows[0]["IssueName"].ToString(), out has_publication_date).ToString().Substring(0, 10));
lbl_advertiser.Text = Server.HtmlEncode(dt_rl.Rows[0]["Advertiser"].ToString());
lbl_feature.Text = Server.HtmlEncode(dt_rl.Rows[0]["Feature"].ToString());
lbl_invoice.Text = Server.HtmlEncode(dt_rl.Rows[0]["Invoice"].ToString());
lbl_outstanding.Text = Util.TextToDecimalCurrency(dt_rl.Rows[0]["Outstanding"].ToString(), dt_rl.Rows[0]["Office"].ToString());
}
else
{
error = true;
}
}
else
{
String msg = String.Empty;
if (is_rd_copy)
{
msg = "This request cannot be handled by your Dashboard account.\\n\\nThis request can only be serviced by " + dt_request.Rows[0]["RequestedToRegionalDirectorName"] + ".";
}
else if (is_md_copy)
{
if (User.Identity.Name == dt_request.Rows[0]["RequestedToRegionalDirectorName"].ToString())
{
msg = "This red-line request has already been handled. A red-line request e-mail has been sent to the Managing Director (" + dt_request.Rows[0]["RequestedToRegionalDirectorName"] + ").";
}
else
{
msg = "This red-line request cannot be handled by your Dashboard account.\\n\\nThis red-line request can only be serviced by " + dt_request.Rows[0]["RequestedToManagingDirectorName"] + ".";
}
}
else if (dt_request.Rows[0]["ApprovedByRegionalDirector"].ToString() == "True" && dt_request.Rows[0]["ApprovedByManagingDirector"].ToString() == "True")
{
msg = "This red-line request has already been handled and approved.";
}
Util.PageMessage(this, msg);
ShowReturnPage();
}
}
else
{
error = true;
}
if (error)
{
Util.PageMessage(this, "Error getting the request/sale information. Please contact the person who sent the request mail.");
ShowReturnPage();
}
}
public PictureInfo GetPicture(int pictureId)
{
return(CBO.FillObject <PictureInfo>(SQL.GetPicture(pictureId)));
}
// This is a modified version of SmiMetaDataFromSchemaTableRow above
// Since CoreCLR doesn't have GetSchema, we need to infer the MetaData from the CLR Type alone
internal static SmiExtendedMetaData SmiMetaDataFromType(string colName, Type colType)
{
// Determine correct SqlDbType.
SqlDbType colDbType = InferSqlDbTypeFromType_Katmai(colType);
if (InvalidSqlDbType == colDbType)
{
// Unknown through standard mapping, use VarBinary for columns that are Object typed, otherwise we error out.
if (typeof(object) == colType)
{
colDbType = SqlDbType.VarBinary;
}
else
{
throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString());
}
}
// Determine metadata modifier values per type (maxlength, precision, scale, etc)
long maxLength = 0;
byte precision = 0;
byte scale = 0;
switch (colDbType)
{
case SqlDbType.BigInt:
case SqlDbType.Bit:
case SqlDbType.DateTime:
case SqlDbType.Float:
case SqlDbType.Image:
case SqlDbType.Int:
case SqlDbType.Money:
case SqlDbType.NText:
case SqlDbType.Real:
case SqlDbType.UniqueIdentifier:
case SqlDbType.SmallDateTime:
case SqlDbType.SmallInt:
case SqlDbType.SmallMoney:
case SqlDbType.Text:
case SqlDbType.Timestamp:
case SqlDbType.TinyInt:
case SqlDbType.Variant:
case SqlDbType.Xml:
case SqlDbType.Date:
// These types require no metadata modifiers
break;
case SqlDbType.Binary:
case SqlDbType.VarBinary:
// source isn't specifying a size, so assume the Maximum
if (SqlDbType.Binary == colDbType)
{
maxLength = SmiMetaData.MaxBinaryLength;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
break;
case SqlDbType.Char:
case SqlDbType.VarChar:
// source isn't specifying a size, so assume the Maximum
if (SqlDbType.Char == colDbType)
{
maxLength = SmiMetaData.MaxANSICharacters;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
break;
case SqlDbType.NChar:
case SqlDbType.NVarChar:
// source isn't specifying a size, so assume the Maximum
if (SqlDbType.NChar == colDbType)
{
maxLength = SmiMetaData.MaxUnicodeCharacters;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
break;
case SqlDbType.Decimal:
// Decimal requires precision and scale
precision = SmiMetaData.DefaultDecimal.Precision;
scale = SmiMetaData.DefaultDecimal.Scale;
break;
case SqlDbType.Time:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
// requires scale
scale = SmiMetaData.DefaultTime.Scale;
break;
case SqlDbType.Udt:
case SqlDbType.Structured:
default:
// These types are not supported from SchemaTable
throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString());
}
return(new SmiExtendedMetaData(
colDbType,
maxLength,
precision,
scale,
Locale.GetCurrentCultureLcid(),
SmiMetaData.GetDefaultForType(colDbType).CompareOptions,
false, // no support for multi-valued columns in a TVP yet
null, // no support for structured columns yet
null,
colName,
null,
null,
null));
}
protected void BindLHAInfo()
{
String qry = "SELECT LHAID, FriendlyName as Rep, DateAdded, MonthWorked, Association, Email, db_lhas.Phone, Mobile, Website, MemListDueDate, LetterheadDueDate, " +
"MainContactName, MainContactPosition, ListContactName, ListContactPosition, LLevel, Notes " +
"FROM db_lhas, db_userpreferences " +
"WHERE db_lhas.UserID = db_userpreferences.UserID " +
"AND LHAID=@lha_id";
DataTable dt_lhainfo = SQL.SelectDataTable(qry, "@lha_id", hf_lha_id.Value);
if (dt_lhainfo.Rows.Count > 0)
{
String association = dt_lhainfo.Rows[0]["Association"].ToString().Trim();
String rep = dt_lhainfo.Rows[0]["Rep"].ToString().Trim();
String date_added = dt_lhainfo.Rows[0]["DateAdded"].ToString().Trim().Substring(0, 10);
String month_worked = dt_lhainfo.Rows[0]["MonthWorked"].ToString().Trim();
String email = dt_lhainfo.Rows[0]["Email"].ToString().Trim();
String tel = dt_lhainfo.Rows[0]["Phone"].ToString().Trim();
String mobile = dt_lhainfo.Rows[0]["Mobile"].ToString().Trim();
String website = dt_lhainfo.Rows[0]["Website"].ToString().Trim();
String due_date = dt_lhainfo.Rows[0]["MemListDueDate"].ToString().Trim();
String lh_due_date = dt_lhainfo.Rows[0]["LetterheadDueDate"].ToString().Trim();
String main_contact = dt_lhainfo.Rows[0]["MainContactName"].ToString().Trim();
String main_contact_pos = dt_lhainfo.Rows[0]["MainContactPosition"].ToString().Trim();
String list_contact = dt_lhainfo.Rows[0]["ListContactName"].ToString().Trim();
String list_contact_position = dt_lhainfo.Rows[0]["ListContactPosition"].ToString().Trim();
String level = dt_lhainfo.Rows[0]["LLevel"].ToString().Trim();
String notes = dt_lhainfo.Rows[0]["Notes"].ToString().Trim();
tb_association.Text = association;
tb_rep.Text = rep;
tb_added.Text = date_added;
tb_month_worked.Text = month_worked;
tb_email.Text = email;
tb_tel.Text = tel;
tb_mob.Text = mobile;
tb_website.Text = website;
tb_main_contact.Text = main_contact;
tb_main_contact_pos.Text = main_contact_pos;
tb_list_contact.Text = list_contact;
tb_list_contact_pos.Text = list_contact_position;
tb_notes.Text = notes;
// set level
dd_level.SelectedIndex = dd_level.Items.IndexOf(dd_level.Items.FindByText(level));
// set dates
if (due_date != String.Empty)
{
DateTime dt_due_date = new DateTime();
if (DateTime.TryParse(due_date, out dt_due_date))
{
dp_due_date.SelectedDate = dt_due_date;
}
}
if (lh_due_date != String.Empty)
{
DateTime dt_lh_due_date = new DateTime();
if (DateTime.TryParse(lh_due_date, out dt_lh_due_date))
{
dp_lh_due_date.SelectedDate = dt_lh_due_date;
}
}
lbl_lha.Text = "Currently editing <b>" + Server.HtmlEncode(association) + "</b>.";
}
else
{
lbl_lha.Text = "Error";
}
}
public void populerListeFilms()
{
lstExemplaires = SQL.FindAllUserExemplairesEmpruntes(noUtilisateurCourrant);
}
protected void UpdateLHA(object sender, EventArgs e)
{
try
{
// due_date
String due_date = null;
if (dp_due_date.SelectedDate != null)
{
DateTime dt_due_date = new DateTime();
if (DateTime.TryParse(dp_due_date.SelectedDate.ToString(), out dt_due_date))
{
due_date = dt_due_date.ToString("yyyy/MM/dd");
}
}
// lh_due_date
String lh_due_date = null;
if (dp_lh_due_date.SelectedDate != null)
{
DateTime dt_lh_due_date = new DateTime();
if (DateTime.TryParse(dp_lh_due_date.SelectedDate.ToString(), out dt_lh_due_date))
{
lh_due_date = dt_lh_due_date.ToString("yyyy/MM/dd");
}
}
// Update LHA
String uqry = "UPDATE db_lhas SET " +
"Association=@association, " +
"Email=@email," +
"Website=@website, " +
"Phone=@tel, " +
"Mobile=@mobile," +
"MainContactName=@main_ctc, " +
"MainContactPosition=@main_ctc_pos, " +
"ListContactName=@list_ctc, " +
"ListContactPosition=@list_ctc_pos, " +
"LLevel=@l_level, " +
"MemListDueDate=@due_date, " +
"LetterheadDueDate=@lh_due_date, " +
"Notes=@notes " +
"WHERE LHAID=@lha_id";
String[] pn = new String[] { "@association", "@email", "@website", "@tel", "@mobile", "@main_ctc", "@main_ctc_pos", "@list_ctc",
"@list_ctc_pos", "@l_level", "@due_date", "@lh_due_date", "@notes", "@lha_id" };
Object[] pv = new Object[] { tb_association.Text.Trim(),
tb_email.Text.Trim(),
tb_website.Text.Trim(),
tb_tel.Text.Trim(),
tb_mob.Text.Trim(),
tb_main_contact.Text.Trim(),
tb_main_contact_pos.Text.Trim(),
tb_list_contact.Text.Trim(),
tb_list_contact_pos.Text.Trim(),
dd_level.SelectedItem.Text,
due_date,
lh_due_date,
Util.DateStamp(tb_notes.Text.Trim()),
hf_lha_id.Value };
SQL.Update(uqry, pn, pv);
Util.CloseRadWindow(this, "LHA '" + tb_association.Text + "' (" + tb_rep.Text + ") successfully updated in", false);
}
catch (Exception r)
{
if (Util.IsTruncateError(this, r))
{
}
else
{
Util.WriteLogWithDetails(r.Message + Environment.NewLine + r.Message + Environment.NewLine + r.StackTrace, "lha_report_log");
Util.PageMessage(this, "An error occured, please try again.");
}
}
}
public void UpdatePicture(PictureInfo info)
{
SQL.UpdatePicture(info.PictureId, info.ImagePath, info.Title, info.Description, info.Tags, info.SeoTitle, info.SeoDescription, info.IsHome, info.StartDate, info.EndDate, info.CreatedBy, info.UpdatedBy, info.Slug, info.UrlPath);
}
/// <include file='../../../../../../../doc/snippets/Microsoft.Data.SqlClient/SqlColumnEncryptionCspProvider.xml' path='docs/members[@name="SqlColumnEncryptionCspProvider"]/EncryptColumnEncryptionKey/*' />
public override byte[] EncryptColumnEncryptionKey(string masterKeyPath, string encryptionAlgorithm, byte[] columnEncryptionKey)
{
// Validate the input parameters
ValidateNonEmptyCSPKeyPath(masterKeyPath, isSystemOp: false);
if (null == columnEncryptionKey)
{
throw SQL.NullColumnEncryptionKey();
}
else if (0 == columnEncryptionKey.Length)
{
throw SQL.EmptyColumnEncryptionKey();
}
// Validate encryptionAlgorithm
ValidateEncryptionAlgorithm(encryptionAlgorithm, isSystemOp: false);
// Create RSA Provider with the given CSP name and key name
RSACryptoServiceProvider rsaProvider = CreateRSACryptoProvider(masterKeyPath, isSystemOp: false);
// Validate whether the key is RSA one or not and then get the key size
int keySizeInBytes = GetKeySize(rsaProvider);
// Construct the encryptedColumnEncryptionKey
// Format is
// version + keyPathLength + ciphertextLength + ciphertext + keyPath + signature
//
// We currently only support one version
byte[] version = new byte[] { _version[0] };
// Get the Unicode encoded bytes of cultureinvariant lower case masterKeyPath
byte[] masterKeyPathBytes = Encoding.Unicode.GetBytes(masterKeyPath.ToLowerInvariant());
byte[] keyPathLength = BitConverter.GetBytes((Int16)masterKeyPathBytes.Length);
// Encrypt the plain text
byte[] cipherText = RSAEncrypt(rsaProvider, columnEncryptionKey);
byte[] cipherTextLength = BitConverter.GetBytes((Int16)cipherText.Length);
Debug.Assert(cipherText.Length == keySizeInBytes, @"cipherText length does not match the RSA key size");
// Compute hash
// SHA-2-256(version + keyPathLength + ciphertextLength + keyPath + ciphertext)
byte[] hash;
using (SHA256 sha256 = SHA256.Create())
{
sha256.TransformBlock(version, 0, version.Length, version, 0);
sha256.TransformBlock(keyPathLength, 0, keyPathLength.Length, keyPathLength, 0);
sha256.TransformBlock(cipherTextLength, 0, cipherTextLength.Length, cipherTextLength, 0);
sha256.TransformBlock(masterKeyPathBytes, 0, masterKeyPathBytes.Length, masterKeyPathBytes, 0);
sha256.TransformFinalBlock(cipherText, 0, cipherText.Length);
hash = sha256.Hash;
}
// Sign the hash
byte[] signedHash = RSASignHashedData(hash, rsaProvider);
Debug.Assert(signedHash.Length == keySizeInBytes, @"signed hash length does not match the RSA key size");
Debug.Assert(RSAVerifySignature(hash, signedHash, rsaProvider), @"Invalid signature of the encrypted column encryption key computed.");
// Construct the encrypted column encryption key
// EncryptedColumnEncryptionKey = version + keyPathLength + ciphertextLength + keyPath + ciphertext + signature
int encryptedColumnEncryptionKeyLength = version.Length + cipherTextLength.Length + keyPathLength.Length + cipherText.Length + masterKeyPathBytes.Length + signedHash.Length;
byte[] encryptedColumnEncryptionKey = new byte[encryptedColumnEncryptionKeyLength];
// Copy version byte
int currentIndex = 0;
Buffer.BlockCopy(version, 0, encryptedColumnEncryptionKey, currentIndex, version.Length);
currentIndex += version.Length;
// Copy key path length
Buffer.BlockCopy(keyPathLength, 0, encryptedColumnEncryptionKey, currentIndex, keyPathLength.Length);
currentIndex += keyPathLength.Length;
// Copy ciphertext length
Buffer.BlockCopy(cipherTextLength, 0, encryptedColumnEncryptionKey, currentIndex, cipherTextLength.Length);
currentIndex += cipherTextLength.Length;
// Copy key path
Buffer.BlockCopy(masterKeyPathBytes, 0, encryptedColumnEncryptionKey, currentIndex, masterKeyPathBytes.Length);
currentIndex += masterKeyPathBytes.Length;
// Copy ciphertext
Buffer.BlockCopy(cipherText, 0, encryptedColumnEncryptionKey, currentIndex, cipherText.Length);
currentIndex += cipherText.Length;
// copy the signature
Buffer.BlockCopy(signedHash, 0, encryptedColumnEncryptionKey, currentIndex, signedHash.Length);
return(encryptedColumnEncryptionKey);
}
private void ListofExams_Load(object sender, EventArgs e)
{
//-----------------
DoExam doexam = new DoExam();
doexam.Close();
ListViewItem item;
ColumnHeader header1, header2, header3, header4;
header1 = new ColumnHeader();
header2 = new ColumnHeader();
header3 = new ColumnHeader();
header4 = new ColumnHeader();
// ListofExams.SmallImageList = imageList1;
/*
* materialListView1.Columns.Add("ExamTitle");
* materialListView1.Columns.Add("Question Count");
* materialListView1.Columns.Add("Category");
* materialListView1.Columns.Add("Creator");
*/
header1.Text = "Title";
header1.TextAlign = HorizontalAlignment.Left;
header1.Width = 500;
header2.Text = "Count";
header2.TextAlign = HorizontalAlignment.Left;
header2.Width = 200;
header3.Text = "Category";
header3.TextAlign = HorizontalAlignment.Left;
header3.Width = 240;
header4.Text = "Creator";
header4.TextAlign = HorizontalAlignment.Left;
header4.Width = 200;
examListview.Columns.Add(header1);
examListview.Columns.Add(header2);
examListview.Columns.Add(header3);
examListview.Columns.Add(header4);
// data;
var TupleExamList = SQL.GetExamInformation();
ArrayList examTitle = TupleExamList.Item1;
ArrayList QuestionCount = TupleExamList.Item2;
ArrayList Category = TupleExamList.Item3;
ArrayList Creator = TupleExamList.Item4;
for (int i = 0; i < examTitle.Count; i++)
{
item = examListview.Items.Add(examTitle[i].ToString());
item.SubItems.Add(QuestionCount[i].ToString());
item.SubItems.Add(Category[i].ToString());
item.SubItems.Add(Creator[i].ToString());
}
if (examTitle.Count <= 0)
{
//mean there is no exam..
// works
// exam list visible. false;
// show label
examListview.Visible = false;
examListview.Enabled = false;
warning_lbl.Visible = true;
warning_lbl.Location = new Point(ClientSize.Width / 2 - warning_lbl.Width / 2, ClientSize.Height / 2 - warning_lbl.Height / 2); // set to center of parent screen
}
}
// Extract metadata for a single DataColumn
internal static SmiExtendedMetaData SmiMetaDataFromDataColumn(DataColumn column, DataTable parent)
{
SqlDbType dbType = InferSqlDbTypeFromType_Katmai(column.DataType);
if (InvalidSqlDbType == dbType)
{
throw SQL.UnsupportedColumnTypeForSqlProvider(column.ColumnName, column.DataType.Name);
}
long maxLength = AdjustMaxLength(dbType, column.MaxLength);
if (InvalidMaxLength == maxLength)
{
throw SQL.InvalidColumnMaxLength(column.ColumnName, maxLength);
}
byte precision;
byte scale;
if (column.DataType == typeof(SqlDecimal))
{
// Must scan all values in column to determine best-fit precision & scale
Debug.Assert(null != parent);
scale = 0;
byte nonFractionalPrecision = 0; // finds largest non-Fractional portion of precision
foreach (DataRow row in parent.Rows)
{
object obj = row[column];
if (!(obj is DBNull))
{
SqlDecimal value = (SqlDecimal)obj;
if (!value.IsNull)
{
byte tempNonFractPrec = checked ((byte)(value.Precision - value.Scale));
if (tempNonFractPrec > nonFractionalPrecision)
{
nonFractionalPrecision = tempNonFractPrec;
}
if (value.Scale > scale)
{
scale = value.Scale;
}
}
}
}
precision = checked ((byte)(nonFractionalPrecision + scale));
if (SqlDecimal.MaxPrecision < precision)
{
throw SQL.InvalidTableDerivedPrecisionForTvp(column.ColumnName, precision);
}
else if (0 == precision)
{
precision = 1;
}
}
else if (dbType == SqlDbType.DateTime2 || dbType == SqlDbType.DateTimeOffset || dbType == SqlDbType.Time)
{
// Time types care about scale, too. But have to infer maximums for these.
precision = 0;
scale = SmiMetaData.DefaultTime.Scale;
}
else if (dbType == SqlDbType.Decimal)
{
// Must scan all values in column to determine best-fit precision & scale
Debug.Assert(null != parent);
scale = 0;
byte nonFractionalPrecision = 0; // finds largest non-Fractional portion of precision
foreach (DataRow row in parent.Rows)
{
object obj = row[column];
if (!(obj is DBNull))
{
SqlDecimal value = (SqlDecimal)(Decimal)obj;
byte tempNonFractPrec = checked ((byte)(value.Precision - value.Scale));
if (tempNonFractPrec > nonFractionalPrecision)
{
nonFractionalPrecision = tempNonFractPrec;
}
if (value.Scale > scale)
{
scale = value.Scale;
}
}
}
precision = checked ((byte)(nonFractionalPrecision + scale));
if (SqlDecimal.MaxPrecision < precision)
{
throw SQL.InvalidTableDerivedPrecisionForTvp(column.ColumnName, precision);
}
else if (0 == precision)
{
precision = 1;
}
}
else
{
precision = 0;
scale = 0;
}
// In Net Core, since DataColumn.Locale is not accessible because it is internal and in a separate assembly,
// we try to get the Locale from the parent
CultureInfo columnLocale = ((null != parent) ? parent.Locale : CultureInfo.CurrentCulture);
return(new SmiExtendedMetaData(
dbType,
maxLength,
precision,
scale,
columnLocale.LCID,
SmiMetaData.DefaultNVarChar.CompareOptions,
null,
false, // no support for multi-valued columns in a TVP yet
null, // no support for structured columns yet
null, // no support for structured columns yet
column.ColumnName,
null,
null,
null));
}
protected void ModifyThisProject(object sender, EventArgs e)
{
String mode = hf_mode.Value;
String project_name = tb_project_name.Text.Trim();
if (project_name == String.Empty)
{
Util.PageMessageAlertify(this, "Project name must not be blank!", "Blank Name!");
}
else if (project_name.Length > 100)
{
Util.PageMessageAlertify(this, "Project name must not be more than 100 characters!", "100 Character Limit");
}
else
{
String user_id = Util.GetUserId();
// Check to see if this is user's first list
bool first_project = false;
String qry = "SELECT ProjectID FROM dbl_project WHERE UserID=@UserID AND Active=1;";
if (SQL.SelectDataTable(qry, "@UserID", user_id).Rows.Count == 0)
{
first_project = true;
}
String TargetTerritoryID = null;
if (dd_target_territory.Items.Count > 0 && dd_target_territory.SelectedItem != null && dd_target_territory.SelectedItem.Text != String.Empty)
{
TargetTerritoryID = dd_target_territory.SelectedItem.Value;
}
String TargetIndustryID = null;
if (dd_target_industry.Items.Count > 0 && dd_target_industry.SelectedItem != null && dd_target_industry.SelectedItem.Text != String.Empty)
{
TargetIndustryID = dd_target_industry.SelectedItem.Value;
}
String[] pn = new String[] { "@ProjectID", "@UserID", "@Name", "@TargetTerritoryID", "@TargetIndustryID", "@Description" };
Object[] pv = new Object[] { hf_project_id.Value, user_id, project_name, TargetTerritoryID, TargetIndustryID, tb_project_description.Text.Trim() };
String action = "Project added.";
if (mode == "new")
{
String iqry = "INSERT INTO dbl_project (UserID, Name, TargetTerritoryID, TargetIndustryID, DateCreated, DateModified, Description) " +
"VALUES (@UserID, @Name, @TargetTerritoryID, @TargetIndustryID, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, @Description);";
hf_project_id.Value = SQL.Insert(iqry, pn, pv).ToString();
if (first_project)
{
// Set this as user's 'last viewed' project
String nuqry = "UPDATE dbl_preferences SET LastViewedProjectID=@project_id WHERE UserID=@user_id";
SQL.Update(nuqry,
new String[] { "@project_id", "@user_id" },
new Object[] { hf_project_id.Value, user_id });
}
qry = "SELECT * FROM dbl_bucket_defaults";
DataTable dt_buckets = SQL.SelectDataTable(qry, null, null);
for (int i = 0; i < dt_buckets.Rows.Count; i++)
{
String bucket_name = dt_buckets.Rows[i]["Name"].ToString();
iqry = "INSERT INTO dbl_project (UserID, Name, DateCreated, DateModified, IsBucket, IsModifiable, ParentProjectID) VALUES (@UserID, @Name, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1, 0, @ParentProjID);";
SQL.Insert(iqry,
new String[] { "@UserID", "@Name", "@ParentProjID" },
new Object[] { user_id, bucket_name, hf_project_id.Value });
}
}
else if (mode == "edit")
{
// Determine log text for update
action = "Project updated ";
String existing_project_name = String.Empty;
String existing_description = String.Empty;
String existing_target_territory = String.Empty;
String existing_target_industry = String.Empty;
qry = "SELECT * FROM dbl_project WHERE ProjectID=@ProjectID";
DataTable dt_project = SQL.SelectDataTable(qry, "@ProjectID", hf_project_id.Value);
if (dt_project.Rows.Count > 0)
{
existing_project_name = dt_project.Rows[0]["Name"].ToString();
existing_description = dt_project.Rows[0]["Description"].ToString();
existing_target_territory = dt_project.Rows[0]["TargetTerritoryID"].ToString();
existing_target_industry = dt_project.Rows[0]["TargetIndustryID"].ToString();
if (existing_project_name != tb_project_name.Text)
{
action += "- Project Renamed ";
}
if (existing_description != tb_project_description.Text)
{
action += "- Description Updated ";
}
if (existing_target_territory != dd_target_territory.SelectedItem.Value)
{
action += "- Target Territory Updated ";
}
if (existing_target_industry != dd_target_industry.SelectedItem.Value)
{
action += "- Target Industry Updated ";
}
}
action = action.Trim() + ".";
// Update Project
String uqry = "UPDATE dbl_project SET Name=@Name, TargetTerritoryID=@TargetTerritoryID, TargetIndustryID=@TargetIndustryID, Description=@Description, " +
"DateModified=CURRENT_TIMESTAMP WHERE ProjectID=@ProjectID;";
SQL.Update(uqry, pn, pv);
// Update Project sharing
if (tr_sharing_management.Visible)
{
for (int i = 0; i < rtv_share_recipients.Nodes[0].Nodes.Count; i++)
{
RadTreeNode n = rtv_share_recipients.Nodes[0].Nodes[i];
bool Share = n.Checked;
String RecipientUserID = n.Value;
pn = new String[] { "@project_id", "@recipient_userid" };
pv = new Object[] { hf_parent_project_id.Value, RecipientUserID };
if (Share)
{
String iqry = "INSERT IGNORE INTO dbl_project_share (ProjectID, UserID) VALUES (@project_id, @recipient_userid)";
SQL.Insert(iqry, pn, pv);
}
else
{
String dqry = "DELETE FROM dbl_project_share WHERE ProjectID=@project_id AND UserID=@recipient_userid";
SQL.Delete(dqry, pn, pv);
}
}
}
}
// Log
LeadsUtil.AddProjectHistoryEntry(hf_project_id.Value, null, tb_project_name.Text.Trim(), action);
Util.SetRebindOnWindowClose(this, true);
Util.CloseRadWindowFromUpdatePanel(this, String.Empty, false);
}
}
// Map SmiMetaData from a schema table.
// DEVNOTE: Since we're using SchemaTable, we can assume that we aren't directly using a SqlDataReader
// so we don't support the Sql-specific stuff, like collation.
internal static SmiExtendedMetaData SmiMetaDataFromSchemaTableRow(DataRow schemaRow)
{
string colName = "";
object temp = schemaRow[SchemaTableColumn.ColumnName];
if (DBNull.Value != temp)
{
colName = (string)temp;
}
// Determine correct SqlDbType.
temp = schemaRow[SchemaTableColumn.DataType];
if (DBNull.Value == temp)
{
throw SQL.NullSchemaTableDataTypeNotSupported(colName);
}
Type colType = (Type)temp;
SqlDbType colDbType = InferSqlDbTypeFromType_Katmai(colType);
if (InvalidSqlDbType == colDbType)
{
// Unknown through standard mapping, use VarBinary for columns that are Object typed, otherwise error
if (typeof(object) == colType)
{
colDbType = SqlDbType.VarBinary;
}
else
{
throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString());
}
}
// Determine metadata modifier values per type (maxlength, precision, scale, etc)
long maxLength = 0;
byte precision = 0;
byte scale = 0;
switch (colDbType)
{
case SqlDbType.BigInt:
case SqlDbType.Bit:
case SqlDbType.DateTime:
case SqlDbType.Float:
case SqlDbType.Image:
case SqlDbType.Int:
case SqlDbType.Money:
case SqlDbType.NText:
case SqlDbType.Real:
case SqlDbType.UniqueIdentifier:
case SqlDbType.SmallDateTime:
case SqlDbType.SmallInt:
case SqlDbType.SmallMoney:
case SqlDbType.Text:
case SqlDbType.Timestamp:
case SqlDbType.TinyInt:
case SqlDbType.Variant:
case SqlDbType.Xml:
case SqlDbType.Date:
// These types require no metadata modifies
break;
case SqlDbType.Binary:
case SqlDbType.VarBinary:
// These types need a binary max length
temp = schemaRow[SchemaTableColumn.ColumnSize];
if (DBNull.Value == temp)
{
// source isn't specifying a size, so assume the worst
if (SqlDbType.Binary == colDbType)
{
maxLength = SmiMetaData.MaxBinaryLength;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
}
else
{
// We (should) have a valid maxlength, so use it.
maxLength = Convert.ToInt64(temp, null);
// Max length must be 0 to MaxBinaryLength or it can be UnlimitedMAX if type is varbinary.
// If it's greater than MaxBinaryLength, just promote it to UnlimitedMAX, if possible.
if (maxLength > SmiMetaData.MaxBinaryLength)
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
if ((maxLength < 0 &&
(maxLength != SmiMetaData.UnlimitedMaxLengthIndicator ||
SqlDbType.Binary == colDbType)))
{
throw SQL.InvalidColumnMaxLength(colName, maxLength);
}
}
break;
case SqlDbType.Char:
case SqlDbType.VarChar:
// These types need an ANSI max length
temp = schemaRow[SchemaTableColumn.ColumnSize];
if (DBNull.Value == temp)
{
// source isn't specifying a size, so assume the worst
if (SqlDbType.Char == colDbType)
{
maxLength = SmiMetaData.MaxANSICharacters;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
}
else
{
// We (should) have a valid maxlength, so use it.
maxLength = Convert.ToInt64(temp, null);
// Max length must be 0 to MaxANSICharacters or it can be UnlimitedMAX if type is varbinary.
// If it's greater than MaxANSICharacters, just promote it to UnlimitedMAX, if possible.
if (maxLength > SmiMetaData.MaxANSICharacters)
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
if ((maxLength < 0 &&
(maxLength != SmiMetaData.UnlimitedMaxLengthIndicator ||
SqlDbType.Char == colDbType)))
{
throw SQL.InvalidColumnMaxLength(colName, maxLength);
}
}
break;
case SqlDbType.NChar:
case SqlDbType.NVarChar:
// These types need a unicode max length
temp = schemaRow[SchemaTableColumn.ColumnSize];
if (DBNull.Value == temp)
{
// source isn't specifying a size, so assume the worst
if (SqlDbType.NChar == colDbType)
{
maxLength = SmiMetaData.MaxUnicodeCharacters;
}
else
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
}
else
{
// We (should) have a valid maxlength, so use it.
maxLength = Convert.ToInt64(temp, null);
// Max length must be 0 to MaxUnicodeCharacters or it can be UnlimitedMAX if type is varbinary.
// If it's greater than MaxUnicodeCharacters, just promote it to UnlimitedMAX, if possible.
if (maxLength > SmiMetaData.MaxUnicodeCharacters)
{
maxLength = SmiMetaData.UnlimitedMaxLengthIndicator;
}
if ((maxLength < 0 &&
(maxLength != SmiMetaData.UnlimitedMaxLengthIndicator ||
SqlDbType.NChar == colDbType)))
{
throw SQL.InvalidColumnMaxLength(colName, maxLength);
}
}
break;
case SqlDbType.Decimal:
// Decimal requires precision and scale
temp = schemaRow[SchemaTableColumn.NumericPrecision];
if (DBNull.Value == temp)
{
precision = SmiMetaData.DefaultDecimal.Precision;
}
else
{
precision = Convert.ToByte(temp, null);
}
temp = schemaRow[SchemaTableColumn.NumericScale];
if (DBNull.Value == temp)
{
scale = SmiMetaData.DefaultDecimal.Scale;
}
else
{
scale = Convert.ToByte(temp, null);
}
if (precision < SmiMetaData.MinPrecision ||
precision > SqlDecimal.MaxPrecision ||
scale < SmiMetaData.MinScale ||
scale > SqlDecimal.MaxScale ||
scale > precision)
{
throw SQL.InvalidColumnPrecScale();
}
break;
case SqlDbType.Time:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
// requires scale
temp = schemaRow[SchemaTableColumn.NumericScale];
if (DBNull.Value == temp)
{
scale = SmiMetaData.DefaultTime.Scale;
}
else
{
scale = Convert.ToByte(temp, null);
}
if (scale > SmiMetaData.MaxTimeScale)
{
throw SQL.InvalidColumnPrecScale();
}
else if (scale < 0)
{
scale = SmiMetaData.DefaultTime.Scale;
}
break;
case SqlDbType.Udt:
case SqlDbType.Structured:
default:
// These types are not supported from SchemaTable
throw SQL.UnsupportedColumnTypeForSqlProvider(colName, colType.ToString());
}
return(new SmiExtendedMetaData(
colDbType,
maxLength,
precision,
scale,
System.Globalization.CultureInfo.CurrentCulture.LCID,
SmiMetaData.GetDefaultForType(colDbType).CompareOptions,
null,
false, // no support for multi-valued columns in a TVP yet
null, // no support for structured columns yet
null, // no support for structured columns yet
colName,
null,
null,
null));
}
public List <RegisterInfo> ListRegisterByStatus(int status)
{
return(CBO.FillCollection <RegisterInfo>(SQL.ListRegisterByStatus(status)));
}
