public static bool IsConnectionAvailable(string connectionString, DbProviderFactory factory)
{
var connection = factory?.CreateConnection();
if (connection == null)
{
throw new InvalidOperationException($"Could not create a connection for provider \"{factory}\".");
}
connection.ConnectionString = connectionString;
using (connection)
{
return(connection.IsAvailable());
}
}
/// <summary>
/// <inheritdoc />
/// </summary>
/// <returns></returns>
protected override IDbConnection Create()
{
var conn = _factory?.CreateConnection();
if (conn == null)
{
throw new DbCoreException("连接创建失败！请检查驱动是否正常");
}
conn.ConnectionString = _connectionString;
conn.Open();
return(conn);
}
public RelationalDatabaseWriter(DocumentDatabase database, SqlReplicationConfig cfg, SqlReplicationStatistics replicationStatistics)
{
this.database = database;
this.cfg = cfg;
this.replicationStatistics = replicationStatistics;
providerFactory = GetDbProviderFactory(cfg);
commandBuilder = providerFactory.CreateCommandBuilder();
connection = providerFactory.CreateConnection();
Debug.Assert(connection != null);
Debug.Assert(commandBuilder != null);
connection.ConnectionString = cfg.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
database.AddAlert(new Alert
{
AlertLevel = AlertLevel.Error,
CreatedAt = SystemTime.UtcNow,
Exception = e.ToString(),
Title = "Sql Replication could not open connection",
Message = "Sql Replication could not open connection to " + connection.ConnectionString,
UniqueKey = "Sql Replication Connection Error: " + connection.ConnectionString
});
throw;
}
tx = connection.BeginTransaction();
stringParserList = new List <Func <DbParameter, string, bool> > {
(colParam, value) => {
if (char.IsDigit(value[0]))
{
DateTime dateTime;
if (DateTime.TryParseExact(value, Default.OnlyDateTimeFormat, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTime))
{
switch (providerFactory.GetType( ).Name)
{
case "MySqlClientFactory":
colParam.Value = dateTime.ToString("yyyy-MM-dd HH:mm:ss.ffffff");
break;
default:
colParam.Value = dateTime;
break;
}
return(true);
}
}
return(false);
},
(colParam, value) => {
if (char.IsDigit(value[0]))
{
DateTimeOffset dateTimeOffset;
if (DateTimeOffset.TryParseExact(value, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture,
DateTimeStyles.RoundtripKind, out dateTimeOffset))
{
switch (providerFactory.GetType( ).Name)
{
case "MySqlClientFactory":
colParam.Value = dateTimeOffset.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.ffffff");
break;
default:
colParam.Value = dateTimeOffset;
break;
}
return(true);
}
}
return(false);
}
};
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
try
{
if (this.IsMobile && grdMain.Columns.Count > 0)
{
grdMain.Columns[0].Visible = false;
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwINVOICES_List" + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 11/24/2006 Paul. Use new Security.Filter() function to apply Team and ACL security rules.
Security.Filter(cmd, m_sMODULE, "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
// Insert function will insert given parameters into record in SQL Server
public bool Insert(SittersEntity entity)
{
try
{
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [Sitters] can't be null. ");
}
//CODE BELOW INSERTS SITTER USER INFORMATION INTO MULTIPLE TABLES USING STORED PROCEDURE
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "Sitter_Insert";
// PASSWORD HASHING
string hashedpassword = Crypto.HashPassword(entity.Password);
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@chnUsername", CsType.String, ParameterDirection.Input, entity.Username);
_dataHandler.AddParameterToCommand(dbCommand, "@chnFirstName", CsType.String, ParameterDirection.Input, entity.FirstName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnLastName", CsType.String, ParameterDirection.Input, entity.LastName);
_dataHandler.AddParameterToCommand(dbCommand, "@chnEmail", CsType.String, ParameterDirection.Input, entity.Email);
_dataHandler.AddParameterToCommand(dbCommand, "@chnPassword", CsType.String, ParameterDirection.Input, hashedpassword);
_dataHandler.AddParameterToCommand(dbCommand, "@binIsActive", CsType.Boolean, ParameterDirection.Input, entity.IsActive);
_dataHandler.AddParameterToCommand(dbCommand, "@intAge", CsType.Int, ParameterDirection.Input, entity.Age);
_dataHandler.AddParameterToCommand(dbCommand, "@chnRole", CsType.String, ParameterDirection.Input, entity.Role);
_dataHandler.AddParameterToCommand(dbCommand, "@chnName", CsType.String, ParameterDirection.Input, entity.Name);
_dataHandler.AddParameterToCommand(dbCommand, "@decFee", CsType.Decimal, ParameterDirection.Input, entity.Fee);
_dataHandler.AddParameterToCommand(dbCommand, "@chnBio", CsType.String, ParameterDirection.Input, entity.Bio);
_dataHandler.AddParameterToCommand(dbCommand, "@dtmHiringDate", CsType.DateTime, ParameterDirection.Input, entity.HiringDate);
_dataHandler.AddParameterToCommand(dbCommand, "@decGrossSalary", CsType.Decimal, ParameterDirection.Input, entity.GrossSalary);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [Sitters] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("SittersRepository::Insert::Error occured.", ex);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "edit") >= 0);
if (!this.Visible)
{
return;
}
try
{
gID = Sql.ToGuid(Request["ID"]);
if (!IsPostBack)
{
// 07/29/2005 Paul. SugarCRM 3.0 does not allow the NONE option.
//lstPARENT_TYPE .Items.Insert(0, new ListItem(L10n.Term(".LBL_NONE"), ""));
Guid gDuplicateID = Sql.ToGuid(Request["DuplicateID"]);
if (!Sql.IsEmptyGuid(gID) || !Sql.IsEmptyGuid(gDuplicateID))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwTASKS_Edit" + ControlChars.CrLf
+ " where ID = @ID " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
if (!Sql.IsEmptyGuid(gDuplicateID))
{
Sql.AddParameter(cmd, "@ID", gDuplicateID);
gID = Guid.Empty;
}
else
{
Sql.AddParameter(cmd, "@ID", gID);
}
con.Open();
#if DEBUG
Page.RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
#endif
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
ctlModuleHeader.Title = Sql.ToString(rdr["NAME"]);
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
Utils.UpdateTracker(Page, m_sMODULE, gID, ctlModuleHeader.Title);
ViewState["ctlModuleHeader.Title"] = ctlModuleHeader.Title;
this.AppendEditViewFields(m_sMODULE + ".EditView", tblMain, rdr);
// 07/15/2006 Paul. Contacts are not valid parents for a Task.
// Manually remove them as the list record_type_display is common across all other parents.
DropDownList lstPARENT_TYPE = FindControl("PARENT_TYPE") as DropDownList;
if (lstPARENT_TYPE != null)
{
lstPARENT_TYPE.Items.Remove("Contacts");
}
// 03/04/2006 Paul. The close button on the Tasks List is used to edit and set STATUS to Completed.
// 06/21/2006 Paul. Change parameter to Close so that the same parameter can be used for Calls, Meetings and Tasks.
// 08/08/2006 Paul. SugarCRM uses Completed in its URL, so we will do the same.
if (Sql.ToString(Request["Status"]) == "Completed")
{
new DynamicControl(this, "STATUS").SelectedValue = "Completed";
}
}
}
}
}
}
else
{
this.AppendEditViewFields(m_sMODULE + ".EditView", tblMain, null);
// 07/15/2006 Paul. Contacts are not valid parents for a Task.
// Manually remove them as the list record_type_display is common across all other parents.
DropDownList lstPARENT_TYPE = FindControl("PARENT_TYPE") as DropDownList;
if (lstPARENT_TYPE != null)
{
lstPARENT_TYPE.Items.Remove("Contacts");
}
Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]);
Guid gCONTACT_ID = Sql.ToGuid(Request["CONTACT_ID"]);
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
string sMODULE = String.Empty;
string sPARENT_TYPE = String.Empty;
string sPARENT_NAME = String.Empty;
SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME);
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
// 07/15/2006 Paul. If the parent is a contact, then convert to a contact.
if (sPARENT_TYPE == "Contacts")
{
gCONTACT_ID = gPARENT_ID;
}
else
{
new DynamicControl(this, "PARENT_ID").ID = gPARENT_ID;
new DynamicControl(this, "PARENT_NAME").Text = sPARENT_NAME;
new DynamicControl(this, "PARENT_TYPE").SelectedValue = sPARENT_TYPE;
}
}
}
if (!Sql.IsEmptyGuid(gCONTACT_ID))
{
string sMODULE = String.Empty;
string sCONTACT_TYPE = String.Empty;
string sCONTACT_NAME = String.Empty;
SqlProcs.spPARENT_Get(ref gCONTACT_ID, ref sMODULE, ref sCONTACT_TYPE, ref sCONTACT_NAME);
if (!Sql.IsEmptyGuid(gCONTACT_ID))
{
new DynamicControl(this, "CONTACT_ID").ID = gCONTACT_ID;
new DynamicControl(this, "CONTACT_NAME").Text = sCONTACT_NAME;
}
}
try
{
// 12/04/2005 Paul. Default value is Medium.
new DynamicControl(this, "PRIORITY").SelectedValue = "Medium";
}
catch (Exception ex)
{
SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), ex.Message);
}
try
{
// 12/04/2005 Paul. Default value is Not Started.
new DynamicControl(this, "STATUS").SelectedValue = "Not Started";
}
catch (Exception ex)
{
SplendidError.SystemWarning(new StackTrace(true).GetFrame(0), ex.Message);
}
}
}
else
{
// 12/02/2005 Paul. When validation fails, the header title does not retain its value. Update manually.
ctlModuleHeader.Title = Sql.ToString(ViewState["ctlModuleHeader.Title"]);
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term("Terminology.LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = SplendidCRM.Security.IS_ADMIN;
if (!this.Visible)
{
return;
}
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwTERMINOLOGY_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
// 01/12/2006 Paul. ctlSearch.LANGUAGE is not working.
if (!IsPostBack)
{
Sql.AppendParameter(cmd, L10N.NormalizeCulture(L10n.NAME), 10, Sql.SqlFilterMode.Exact, "LANG"); //ctlSearch.LANGUAGE = L10n.NAME;
}
else
{
ctlSearch.SqlSearchClause(cmd);
}
if (ctlSearch.GLOBAL_TERMS)
{
cmd.CommandText += " and MODULE_NAME is null" + ControlChars.CrLf;
}
if (!ctlSearch.INCLUDE_LISTS)
{
cmd.CommandText += " and LIST_NAME is null" + ControlChars.CrLf;
}
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
private void button1_Click(object sender, EventArgs e)
{
Adauga add = new Adauga();
add.ShowDialog();
Activitate a = add.GetActivitate();
activitati.Add(a);
if (add.getStatus())
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = ConnString;
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT MAX(Nr_activitate) FROM Activitati";
int nrAct = (int)command.ExecuteScalar();
command.CommandText = "INSERT INTO Activitati VALUES(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8)";
command.Parameters.Clear();
DbParameter p1 = command.CreateParameter();
p1.DbType = DbType.Int32;
p1.Value = nrAct + 1;
command.Parameters.Add(p1);
DbParameter p2 = command.CreateParameter();
p2.DbType = DbType.String;
p2.Value = a.getTip();
command.Parameters.Add(p2);
DbParameter p3 = command.CreateParameter();
p3.DbType = DbType.String;
p3.Value = a.getMaterie();
command.Parameters.Add(p3);
DbParameter p4 = command.CreateParameter();
p4.DbType = DbType.String;
p4.Value = a.getOra();
command.Parameters.Add(p4);
DbParameter p5 = command.CreateParameter();
p5.DbType = DbType.String;
p5.Value = a.getGrupa();
command.Parameters.Add(p5);
DbParameter p6 = command.CreateParameter();
p6.DbType = DbType.String;
p6.Value = a.getZiua();
command.Parameters.Add(p6);
DbParameter p7 = command.CreateParameter();
p7.DbType = DbType.String;
p7.Value = a.getSala();
command.Parameters.Add(p7);
DbParameter p8 = command.CreateParameter();
p8.DbType = DbType.Int32;
p8.Value = add.getIdProfesor();
command.Parameters.Add(p8);
command.ExecuteNonQuery();
MessageBox.Show("S-a adaugat: " + a.ToString());
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Utils.SetPageTitle(Page, L10n.Term("Manufacturers.LBL_NAME"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = SplendidCRM.Security.IS_ADMIN;
if (!this.Visible)
{
return;
}
try
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind(); // 09/03/2005 Paul. DataBind is required in order for the RequiredFieldValidators to work.
// 07/02/2006 Paul. The required fields need to be bound manually.
reqNAME.DataBind();
reqLIST_ORDER.DataBind();
gID = Sql.ToGuid(Request["ID"]);
if (!IsPostBack)
{
lstSTATUS.DataSource = SplendidCache.List("manufacturer_status_dom");
lstSTATUS.DataBind();
Guid gDuplicateID = Sql.ToGuid(Request["DuplicateID"]);
if (!Sql.IsEmptyGuid(gID))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwMANUFACTURERS" + ControlChars.CrLf
+ " where ID = @ID " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@ID", gID);
con.Open();
#if DEBUG
Page.RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
#endif
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
txtNAME.Text = Sql.ToString(rdr["NAME"]);
ctlListHeader.Title = L10n.Term("Manufacturers.LBL_NAME") + " " + txtNAME.Text;
txtLIST_ORDER.Text = Sql.ToString(rdr["LIST_ORDER"]);
try
{
lstSTATUS.SelectedValue = Sql.ToString(rdr["STATUS"]);
}
catch
{
}
}
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
public bool Insert(StateEntity entity)
{
try
{
var sb = new StringBuilder();
sb.Append("INSERT [dbo].[State] ");
sb.Append("( ");
sb.Append("[StateName]");
sb.Append(") ");
sb.Append("VALUES ");
sb.Append("( ");
sb.Append(" @StateName");
sb.Append(") ");
sb.Append("SELECT @intErrorCode=@@ERROR; ");
var commandText = sb.ToString();
sb.Clear();
using (var dbConnection = _dbProviderFactory.CreateConnection())
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection", "The db connection can't be null.");
}
dbConnection.ConnectionString = _connectionString;
using (var dbCommand = _dbProviderFactory.CreateCommand())
{
if (dbCommand == null)
{
throw new ArgumentNullException("dbCommand" + " The db Insert command for entity [State] can't be null. ");
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = commandText;
//Input Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@StateName", CsType.String, ParameterDirection.Input, entity.StateName);
//Output Parameters
_dataHandler.AddParameterToCommand(dbCommand, "@intErrorCode", CsType.Int, ParameterDirection.Output, null);
//Open Connection
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
//Execute query.
_rowsAffected = dbCommand.ExecuteNonQuery();
_errorCode = int.Parse(dbCommand.Parameters["@intErrorCode"].Value.ToString());
if (_errorCode != 0)
{
// Throw error.
throw new Exception("The Insert method for entity [State] reported the Database ErrorCode: " + _errorCode);
}
}
}
return(true);
}
catch (Exception ex)
{
//Log exception error
_loggingHandler.LogEntry(ExceptionHandler.GetExceptionMessageFormatted(ex), true);
//Bubble error to caller and encapsulate Exception object
throw new Exception("StateRepository::Insert::Error occured.", ex);
}
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
if (e.CommandName == "Save")
{
// 01/31/2006 Paul. Enable validator before validating page.
this.ValidateEditViewFields(m_sMODULE + ".ConvertView");
if (Page.IsValid)
{
// 02/27/2006 Paul. Fix condition on notes. Enable only if text exists.
txtCONTACT_NOTES_NAME_DESCRIPTION.Text = txtCONTACT_NOTES_NAME_DESCRIPTION.Text.Trim();
reqCONTACT_NOTES_NAME.Enabled = !Sql.IsEmptyString(txtCONTACT_NOTES_NAME_DESCRIPTION.Text);
reqCONTACT_NOTES_NAME.Validate();
txtACCOUNT_NOTES_NAME_DESCRIPTION.Text = txtACCOUNT_NOTES_NAME_DESCRIPTION.Text.Trim();
reqACCOUNT_NOTES_NAME.Enabled = !Sql.IsEmptyString(txtACCOUNT_NOTES_NAME_DESCRIPTION.Text);
reqACCOUNT_NOTES_NAME.Validate();
txtOPPORTUNITY_NOTES_NAME_DESCRIPTION.Text = txtOPPORTUNITY_NOTES_NAME_DESCRIPTION.Text.Trim();
reqOPPORTUNITY_NOTES_NAME.Enabled = !Sql.IsEmptyString(txtOPPORTUNITY_NOTES_NAME_DESCRIPTION.Text);
reqOPPORTUNITY_NOTES_NAME.Validate();
// 01/31/2006 Paul. SelectAccount is required if not creating an account but creating an opportunity.
reqSELECT_ACCOUNT_ID.Enabled = !chkCreateAccount.Checked && chkCreateOpportunity.Checked;
reqSELECT_ACCOUNT_ID.Validate();
reqACCOUNT_NAME.Enabled = chkCreateAccount.Checked;
reqACCOUNT_NAME.Validate();
reqOPPORTUNITY_NAME.Enabled = chkCreateOpportunity.Checked;
reqOPPORTUNITY_NAME.Validate();
reqOPPORTUNITY_AMOUNT.Enabled = chkCreateOpportunity.Checked;
reqOPPORTUNITY_AMOUNT.Validate();
reqAPPOINTMENT_NAME.Enabled = chkCreateAppointment.Checked;
reqAPPOINTMENT_NAME.Validate();
reqAPPOINTMENT_TIME_START.Enabled = chkCreateAppointment.Checked;
reqAPPOINTMENT_TIME_START.Validate();
if (chkCreateAppointment.Checked)
{
ctlAPPOINTMENT_DATE_START.Validate();
}
}
if (Page.IsValid)
{
string sCUSTOM_MODULE = "LEADS";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
Guid gCONTACT_ID = Guid.Empty;
Guid gACCOUNT_ID = Guid.Empty;
Guid gOPPORTUNITY_ID = Guid.Empty;
Guid gAPPOINTMENT_ID = Guid.Empty;
// 01/31/2006 Paul. Create the contact first so that it can be used as the parent of the related records.
// We would normally create the related records second, but then it will become a pain to update the Contact ACCOUNT_ID field.
SqlProcs.spCONTACTS_New
(ref gCONTACT_ID
, new DynamicControl(this, "FIRST_NAME").Text
, new DynamicControl(this, "LAST_NAME").Text
, new DynamicControl(this, "PHONE_WORK").Text
, new DynamicControl(this, "EMAIL1").Text
, trn
);
if (chkCreateAccount.Checked)
{
SqlProcs.spACCOUNTS_Update
(ref gACCOUNT_ID
, Security.USER_ID
, txtACCOUNT_NAME.Text
, String.Empty
, Guid.Empty
, String.Empty
, String.Empty
, Sql.ToString(ViewState["PHONE_FAX"])
, Sql.ToString(ViewState["BILLING_ADDRESS_STREET"])
, Sql.ToString(ViewState["BILLING_ADDRESS_CITY"])
, Sql.ToString(ViewState["BILLING_ADDRESS_STATE"])
, Sql.ToString(ViewState["BILLING_ADDRESS_POSTALCODE"])
, Sql.ToString(ViewState["BILLING_ADDRESS_COUNTRY"])
, txtACCOUNT_DESCRIPTION.Text
, String.Empty
, txtACCOUNT_PHONE_WORK.Text
, Sql.ToString(ViewState["PHONE_OTHER"])
, Sql.ToString(ViewState["EMAIL1"])
, Sql.ToString(ViewState["EMAIL2"])
, txtACCOUNT_WEBSITE.Text
, String.Empty
, String.Empty
, String.Empty
, String.Empty
, Sql.ToString(ViewState["SHIPPING_ADDRESS_STREET"])
, Sql.ToString(ViewState["SHIPPING_ADDRESS_CITY"])
, Sql.ToString(ViewState["SHIPPING_ADDRESS_STATE"])
, Sql.ToString(ViewState["SHIPPING_ADDRESS_POSTALCODE"])
, Sql.ToString(ViewState["SHIPPING_ADDRESS_COUNTRY"])
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
if (!Sql.IsEmptyString(txtACCOUNT_NOTES_NAME.Text))
{
Guid gNOTE_ID = Guid.Empty;
SqlProcs.spNOTES_Update
(ref gNOTE_ID
, txtACCOUNT_NOTES_NAME.Text
, "Accounts"
, gACCOUNT_ID
, Guid.Empty
, txtACCOUNT_NOTES_NAME_DESCRIPTION.Text
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
}
}
else
{
gACCOUNT_ID = Sql.ToGuid(txtSELECT_ACCOUNT_ID.Value);
}
if (chkCreateOpportunity.Checked)
{
SqlProcs.spOPPORTUNITIES_Update
(ref gOPPORTUNITY_ID
, Security.USER_ID
, gACCOUNT_ID
, txtOPPORTUNITY_NAME.Text
, String.Empty
, new DynamicControl(this, "LEAD_SOURCE").SelectedValue
, Sql.ToDecimal(txtOPPORTUNITY_AMOUNT.Text)
, Guid.Empty
, T10n.ToServerTime(ctlOPPORTUNITY_DATE_CLOSED.Value)
, String.Empty
, lstOPPORTUNITY_SALES_STAGE.SelectedValue
, (float)0.0
, txtOPPORTUNITY_DESCRIPTION.Text
, String.Empty
, Guid.Empty
, String.Empty // 11/02/2006 Paul. ACCOUNT_NAME is only used for import.
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
if (!Sql.IsEmptyString(txtOPPORTUNITY_NOTES_NAME.Text))
{
Guid gNOTE_ID = Guid.Empty;
SqlProcs.spNOTES_Update
(ref gNOTE_ID
, txtOPPORTUNITY_NOTES_NAME.Text
, "Opportunities"
, gOPPORTUNITY_ID
, Guid.Empty
, txtOPPORTUNITY_NOTES_NAME_DESCRIPTION.Text
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
}
// 03/04/2006 Paul. Must be included in the transaction, otherwise entire operation will fail with a timeout message.
SqlProcs.spOPPORTUNITIES_CONTACTS_Update(gOPPORTUNITY_ID, gCONTACT_ID, String.Empty, trn);
}
if (chkCreateAppointment.Checked)
{
DateTime dtDATE_START = T10n.ToServerTime(Sql.ToDateTime(ctlAPPOINTMENT_DATE_START.DateText + " " + txtAPPOINTMENT_TIME_START.Text));
if (radScheduleCall.Checked)
{
SqlProcs.spCALLS_Update
(ref gAPPOINTMENT_ID
, Security.USER_ID
, txtAPPOINTMENT_NAME.Text
, 1
, 0
, dtDATE_START
, "Accounts"
, Guid.Empty
, "Planned"
, "Outbound"
, -1
, txtAPPOINTMENT_DESCRIPTION.Text
, gCONTACT_ID.ToString() // 01/31/2006 Paul. This is were we relate this call to the contact.
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
}
else
{
SqlProcs.spMEETINGS_Update
(ref gAPPOINTMENT_ID
, Security.USER_ID
, txtAPPOINTMENT_NAME.Text
, String.Empty
, 1
, 0
, dtDATE_START
, "Planned"
, "Accounts"
, Guid.Empty
, -1
, txtAPPOINTMENT_DESCRIPTION.Text
, gCONTACT_ID.ToString() // 01/31/2006 Paul. This is were we relate this meeting to the contact.
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
}
}
SqlProcs.spCONTACTS_ConvertLead
(ref gCONTACT_ID
, gID // 01/31/2006 Paul. Update the Lead with this contact.
, Security.USER_ID
, new DynamicControl(this, "SALUTATION").SelectedValue
, new DynamicControl(this, "FIRST_NAME").Text
, new DynamicControl(this, "LAST_NAME").Text
, gACCOUNT_ID
, new DynamicControl(this, "LEAD_SOURCE").SelectedValue
, new DynamicControl(this, "TITLE").Text
, new DynamicControl(this, "DEPARTMENT").Text
, new DynamicControl(this, "DO_NOT_CALL").Checked
, new DynamicControl(this, "PHONE_HOME").Text
, new DynamicControl(this, "PHONE_MOBILE").Text
, new DynamicControl(this, "PHONE_WORK").Text
, new DynamicControl(this, "PHONE_OTHER").Text
, new DynamicControl(this, "PHONE_FAX").Text
, new DynamicControl(this, "EMAIL1").Text
, new DynamicControl(this, "EMAIL2").Text
, new DynamicControl(this, "EMAIL_OPT_OUT").Checked
, new DynamicControl(this, "INVALID_EMAIL").Checked
, new DynamicControl(this, "PRIMARY_ADDRESS_STREET").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_CITY").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_STATE").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_POSTALCODE").Text
, new DynamicControl(this, "PRIMARY_ADDRESS_COUNTRY").Text
, Sql.ToString(ViewState["ALT_ADDRESS_STREET"])
, Sql.ToString(ViewState["ALT_ADDRESS_CITY"])
, Sql.ToString(ViewState["ALT_ADDRESS_STATE"])
, Sql.ToString(ViewState["ALT_ADDRESS_POSTALCODE"])
, Sql.ToString(ViewState["ALT_ADDRESS_COUNTRY"])
, new DynamicControl(this, "DESCRIPTION").Text
, gOPPORTUNITY_ID
, txtOPPORTUNITY_NAME.Text
, txtOPPORTUNITY_AMOUNT.Text
, Sql.ToGuid(ViewState["CAMPAIGN_ID"]) // 09/10/2007 Paul. Add Campaign tracking.
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
if (!Sql.IsEmptyString(txtCONTACT_NOTES_NAME.Text))
{
Guid gNOTE_ID = Guid.Empty;
SqlProcs.spNOTES_Update
(ref gNOTE_ID
, txtCONTACT_NOTES_NAME.Text
, String.Empty
, Guid.Empty
, gCONTACT_ID
, txtCONTACT_NOTES_NAME_DESCRIPTION.Text
, new DynamicControl(this, "TEAM_ID").ID
, trn
);
}
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
else if (e.CommandName == "Cancel")
{
if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
try
{
gID = Sql.ToGuid(Request["ID"]);
SetPageTitle(L10n.Term(".moduleList." + m_sMODULE));
if (!IsPostBack)
{
lblDATEFORMAT.Text = "(" + Session["USER_SETTINGS/DATEFORMAT"] + ")";
lstOPPORTUNITY_SALES_STAGE.DataSource = SplendidCache.List("sales_stage_dom");
lstOPPORTUNITY_SALES_STAGE.DataBind();
chkCreateAccount.Attributes.Add("onclick", "return ToggleCreateAccount();");
chkCreateOpportunity.Attributes.Add("onclick", "return toggleDisplay('divCreateOpportunity');");
chkCreateAppointment.Attributes.Add("onclick", "return toggleDisplay('divCreateAppointment');");
Guid gDuplicateID = Sql.ToGuid(Request["DuplicateID"]);
if (!Sql.IsEmptyGuid(gID) || !Sql.IsEmptyGuid(gDuplicateID))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwLEADS_Convert" + ControlChars.CrLf
+ " where ID = @ID " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
if (!Sql.IsEmptyGuid(gDuplicateID))
{
Sql.AddParameter(cmd, "@ID", gDuplicateID);
gID = Guid.Empty;
}
else
{
Sql.AddParameter(cmd, "@ID", gID);
}
con.Open();
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
ctlModuleHeader.Title = L10n.Term("Leads.LBL_CONVERTLEAD");
SetPageTitle(L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
txtACCOUNT_NAME.Text = Sql.ToString(rdr["ACCOUNT_NAME"]);
txtACCOUNT_PHONE_WORK.Text = Sql.ToString(rdr["PHONE_WORK"]);
// 01/31/2006 Paul. Default start date and time is now.
ctlAPPOINTMENT_DATE_START.Value = T10n.FromServerTime(DateTime.Now);
txtAPPOINTMENT_TIME_START.Text = T10n.FromServerTime(DateTime.Now).ToShortTimeString();
this.AppendEditViewFields(m_sMODULE + ".ConvertView", tblMain, rdr);
// 01/31/2006 Paul. Save all data to be used later.
for (int i = 0; i < rdr.FieldCount; i++)
{
ViewState[rdr.GetName(i)] = rdr.GetValue(i);
}
}
}
}
}
}
else
{
this.AppendEditViewFields(m_sMODULE + ".ConvertView", tblMain, null);
}
}
else
{
// 12/02/2005 Paul. When validation fails, the header title does not retain its value. Update manually.
ctlModuleHeader.Title = L10n.Term("Leads.LBL_CONVERTLEAD");
SetPageTitle(L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
ctlEditButtons.ErrorText = ex.Message;
}
}
public override int GetNextIdentity_Int(DbProviderFactory factory, string connStr, string key, bool rollBack = false)
{
object obj2 = -1;
Exception exception = null;
TransactionScope scope = null;
if (!rollBack)
{
scope = new TransactionScope(TransactionScopeOption.Suppress);
}
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connStr;
connection.Open();
if (string.IsNullOrEmpty(key))
{
key = "CommonIdentity";
}
DbParameter parameter = this.CreateParameter();
this.SetDbParameter(parameter, this.CreateParameterName("Key"), key, 0x77);
string sql = string.Format("select CurrentId from SysIdSequence where IdKey = {0} for update wait 100", parameter.ParameterName);
try
{
DbParameter parameter4;
DbParameter parameter2 = this.CreateParameter();
this.SetDbParameter(parameter2, this.CreateParameterName("Key"), key, 0x77);
DbParameter parameter3 = this.CreateParameter();
this.SetDbParameter(parameter3, this.CreateParameterName("UpdateTime"), DateTime.Now, 0x7b);
object obj3 = base.ExecuteScalar(connection, null, sql, new DbParameter[] { parameter });
if (obj3 == null)
{
obj2 = 1;
parameter4 = this.CreateParameter();
this.SetDbParameter(parameter4, this.CreateParameterName("CurrentId"), obj2, 0x70);
sql = string.Format("insert into SysIdSequence (IdKey,CurrentId,UpdateTime) values ({0},{1},{2})", parameter2.ParameterName, parameter4.ParameterName, parameter3.ParameterName);
base.ExecuteNonQuery(connection, null, sql, new DbParameter[] { parameter2, parameter4, parameter3 });
}
else
{
obj2 = Convert.ToInt32(obj3) + 1;
parameter4 = this.CreateParameter();
this.SetDbParameter(parameter4, this.CreateParameterName("CurrentId"), obj2, 0x70);
sql = string.Format("update SysIdSequence Set CurrentId = {1},UpdateTime = {2} where IdKey = {0}", parameter2.ParameterName, parameter4.ParameterName, parameter3.ParameterName);
base.ExecuteNonQuery(connection, null, sql, new DbParameter[] { parameter4, parameter3, parameter2 });
}
}
catch (Exception exception2)
{
exception = exception2;
}
connection.Close();
}
if (!rollBack)
{
scope.Complete();
scope.Dispose();
}
if (exception != null)
{
throw exception;
}
return(Convert.ToInt32(obj2));
}
private static List <object[]> GetObjectArrayFromSQL(string strQuery, string strConnection, string strProvider, DataTable dt, string[] operators, out SqlDataRecord record, bool addLevel)
{
record = null;
List <object[]> listObjects = new List <object[]>();
DbProviderFactory factory = DbProviderFactories.GetFactory(strProvider);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection(); // will return the connection object (i.e. SqlConnection ...)
if (connection != null)
{
connection.ConnectionString = strConnection;
try
{
connection.Open();
DbDataAdapter dap = factory.CreateDataAdapter();
DbCommand selectCommand = connection.CreateCommand();
selectCommand.CommandTimeout = 0; //infinite timeout
selectCommand.CommandText = strQuery;
if (dap != null)
{
DbDataReader reader = selectCommand.ExecuteReader();
List <SqlMetaData> outputColumns = new List <SqlMetaData>();
object[] recordObjectStart = new object[reader.FieldCount];
//only if data is available
if (reader.Read())
{
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
recordObjectStart[iCol] = (object)reader[iCol];
if (iCol >= operators.Length)
{
DataColumn col = new DataColumn(reader.GetName(iCol), reader.GetFieldType(iCol));
SqlMetaData outputColumn;
if (col.DataType == typeof(Int32) || col.DataType == typeof(Int64) || col.DataType == typeof(DateTime))
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType));
}
else
{
outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), col.MaxLength);
}
outputColumns.Add(outputColumn);
//Check if column name already exists
if (!dt.Columns.Contains(col.ColumnName))
{
dt.Columns.Add(col);
}
else
{
throw new Exception("Column name '" + col.ColumnName + "' already exists. Use an alias instead.");
}
}
}
}
listObjects.Add(recordObjectStart);
//add level column for multiple skyline algorithms
if (addLevel)
{
SqlMetaData outputColumnLevel = new SqlMetaData("level", TypeConverter.ToSqlDbType(typeof(Int32)));
outputColumns.Add(outputColumnLevel);
}
record = new SqlDataRecord(outputColumns.ToArray());
//Now save all records to array (Profiling: faster than working with the reader in the algorithms)
while (reader.Read())
{
object[] recordObject = new object[reader.FieldCount];
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
recordObject[iCol] = (object)reader[iCol];
}
listObjects.Add(recordObject);
}
reader.Close();
}
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(listObjects);
}
private void AddInvitee(Guid gUSER_ID)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
string sSQL;
string sFULL_NAME = String.Empty;
string sINVITEE_TYPE = String.Empty;
sSQL = "select * " + ControlChars.CrLf
+ " from vwINVITEES" + ControlChars.CrLf
+ " where ID = @ID " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@ID", gUSER_ID);
#if DEBUG
Page.RegisterClientScriptBlock("vwINVITEES", Sql.ClientScriptBlock(cmd));
#endif
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
sFULL_NAME = Sql.ToString(rdr["FULL_NAME"]);
sINVITEE_TYPE = Sql.ToString(rdr["INVITEE_TYPE"]);
}
}
}
sSQL = "select * " + ControlChars.CrLf
+ " from vwACTIVITIES_List " + ControlChars.CrLf
+ " where ASSIGNED_USER_ID = @ASSIGNED_USER_ID " + ControlChars.CrLf
+ " and ( DATE_START >= @DATE_START and DATE_START < @DATE_END" + ControlChars.CrLf
+ " or DATE_END >= @DATE_START and DATE_END < @DATE_END" + ControlChars.CrLf
+ " or DATE_START < @DATE_START and DATE_END > @DATE_END" + ControlChars.CrLf
+ " ) " + ControlChars.CrLf
+ " order by DATE_START asc, NAME asc " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@ASSIGNED_USER_ID", gUSER_ID);
Sql.AddParameter(cmd, "@DATE_START", T10n.ToServerTime(dtSCHEDULE_START));
Sql.AddParameter(cmd, "@DATE_END", T10n.ToServerTime(dtSCHEDULE_END));
#if DEBUG
Page.RegisterClientScriptBlock("vwACTIVITIES_List", Sql.ClientScriptBlock(cmd));
#endif
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
HtmlTableRow rowInvitee = new HtmlTableRow();
tblSchedule.Rows.Add(rowInvitee);
rowInvitee.Attributes.Add("class", "schedulerAttendeeRow");
HtmlTableCell cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
cellInvitee.Attributes.Add("class", "schedulerAttendeeCell");
Literal litFULL_NAME = new Literal();
Image imgInvitee = new Image();
cellInvitee.Controls.Add(imgInvitee);
cellInvitee.Controls.Add(litFULL_NAME);
imgInvitee.Width = 16;
imgInvitee.Height = 16;
imgInvitee.ImageAlign = ImageAlign.AbsMiddle;
imgInvitee.ImageUrl = Session["themeURL"] + "images/" + sINVITEE_TYPE + ".gif";
litFULL_NAME.Text = sFULL_NAME;
if (dt.Rows.Count > 0)
{
DataView vwMain = new DataView(dt);
CultureInfo ciEnglish = CultureInfo.CreateSpecificCulture("en-US");
for (DateTime dtHOUR_START = dtSCHEDULE_START; dtHOUR_START < dtSCHEDULE_END; dtHOUR_START = dtHOUR_START.AddMinutes(15))
{
DateTime dtHOUR_END = dtHOUR_START.AddMinutes(15);
DateTime dtHOUR_START_ServerTime = T10n.ToServerTime(dtHOUR_START);
DateTime dtHOUR_END_ServerTime = T10n.ToServerTime(dtHOUR_END);
// 09/27/2005 Paul. System.Data.DataColumn.Expression documentation has description how to define dates and strings.
// 08/08/2006 Paul. Use the same ServerTime logic as DayGrid.ascx.cs to solve date formatting issues on international systems.
string sHOUR_START_ServerTime = dtHOUR_START_ServerTime.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
string sHOUR_END_ServerTime = dtHOUR_END_ServerTime.ToString(CalendarControl.SqlDateTimeFormat, ciEnglish.DateTimeFormat);
vwMain.RowFilter = " DATE_START >= #" + sHOUR_START_ServerTime + "# and DATE_START < #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf
+ "or DATE_END > #" + sHOUR_START_ServerTime + "# and DATE_END <= #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf
+ "or DATE_START < #" + sHOUR_START_ServerTime + "# and DATE_END > #" + sHOUR_END_ServerTime + "#" + ControlChars.CrLf;
#if DEBUG
// Page.RegisterClientScriptBlock("vwACTIVITIES_List" + dtHOUR_START.ToOADate().ToString(), Sql.EscapeJavaScript(vwMain.RowFilter));
#endif
cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
if (dtHOUR_START == dtDATE_END)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellEndTime");
}
else if (dtHOUR_START == dtDATE_START)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellStartTime");
}
else
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellHour");
}
if (vwMain.Count > 0)
{
if (dtHOUR_START >= dtDATE_START && dtHOUR_START < dtDATE_END)
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #aa4d4d");
}
else
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #4d5eaa");
}
}
else
{
if (dtHOUR_START >= dtDATE_START && dtHOUR_START < dtDATE_END)
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #ffffff");
}
}
}
}
else
{
for (DateTime dtHOUR_START = dtSCHEDULE_START; dtHOUR_START < dtSCHEDULE_END; dtHOUR_START = dtHOUR_START.AddMinutes(15))
{
DateTime dtHOUR_END = dtHOUR_START.AddMinutes(15);
cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
if (dtHOUR_START == dtDATE_END)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellEndTime");
}
else if (dtHOUR_START == dtDATE_START)
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellStartTime");
}
else
{
cellInvitee.Attributes.Add("class", "schedulerSlotCellHour");
}
if (dtHOUR_START >= dtDATE_START && dtHOUR_START < dtDATE_END)
{
cellInvitee.Attributes.Add("style", "BACKGROUND-COLOR: #ffffff");
}
}
}
cellInvitee = new HtmlTableCell();
rowInvitee.Cells.Add(cellInvitee);
cellInvitee.Attributes.Add("class", "schedulerAttendeeDeleteCell");
ImageButton btnDelete = new ImageButton();
Literal litSpace = new Literal();
LinkButton lnkDelete = new LinkButton();
btnDelete.CommandName = "Invitees.Delete";
lnkDelete.CommandName = "Invitees.Delete";
btnDelete.CommandArgument = gUSER_ID.ToString();
lnkDelete.CommandArgument = gUSER_ID.ToString();
btnDelete.Command += new CommandEventHandler(this.Page_Command);
lnkDelete.Command += new CommandEventHandler(this.Page_Command);
btnDelete.CssClass = "listViewTdToolsS1";
lnkDelete.CssClass = "listViewTdToolsS1";
Guid gID = Sql.ToGuid(Request["ID"]);
if (Sql.IsEmptyGuid(gID))
{
btnDelete.AlternateText = L10n.Term(".LNK_REMOVE");
lnkDelete.Text = L10n.Term(".LNK_REMOVE");
}
else
{
btnDelete.AlternateText = L10n.Term(".LNK_DELETE");
lnkDelete.Text = L10n.Term(".LNK_DELETE");
}
litSpace.Text = " ";
btnDelete.ImageUrl = Session["themeURL"] + "images/delete_inline.gif";
btnDelete.BorderWidth = 0;
btnDelete.Width = 12;
btnDelete.Height = 12;
btnDelete.ImageAlign = ImageAlign.AbsMiddle;
cellInvitee.Controls.Add(btnDelete);
cellInvitee.Controls.Add(litSpace);
cellInvitee.Controls.Add(lnkDelete);
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
}
}
protected override DbConnection CreateConnection()
{
return(dbFactory.CreateConnection());
}
private void Page_Load(object sender, System.EventArgs e)
{
gID = Sql.ToGuid(Request["ID"]);
Guid gCONTACT_ID = Sql.ToGuid(txtCONTACT_ID.Value);
if (!Sql.IsEmptyGuid(gCONTACT_ID))
{
try
{
SqlProcs.spACCOUNTS_CONTACTS_Update(gID, gCONTACT_ID);
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwACCOUNTS_CONTACTS " + ControlChars.CrLf
+ " where ACCOUNT_ID = @ACCOUNT_ID" + ControlChars.CrLf
+ " order by DATE_ENTERED asc " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AddParameter(cmd, "@ACCOUNT_ID", gID);
#if DEBUG
Page.RegisterClientScriptBlock("vwACCOUNTS_CONTACTS", Sql.ClientScriptBlock(cmd));
#endif
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
// 09/05/2005 Paul. LinkButton controls will not fire an event unless the the grid is bound.
//if ( !IsPostBack )
{
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
protected override bool LoadObjects()
{
DbConnection cnn = null;
DbCommand cmd = null;
DbDataReader rdr = null;
try
{
DbProviderFactory factory =
DbProviderFactories.GetFactory(
connection.Provider.InvariantName);
cnn = factory.CreateConnection();
cnn.ConnectionString = connection.ConnectionString;
cnn.Open();
cmd = factory.CreateCommand();
cmd.Connection = cnn;
cmd.CommandText =
"SELECT C.TABLE_SCHEMA, C.TABLE_NAME, T.TABLE_TYPE, " +
"C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH " +
"FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN " +
"INFORMATION_SCHEMA.TABLES T ON " +
"T.TABLE_SCHEMA = C.TABLE_SCHEMA AND " +
"T.TABLE_NAME = C.TABLE_NAME";
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
/*
* Need to make keys lowercase for case-insensitive
* matching but retain case for object properties.
*/
string schemaName = rdr.GetString(0);
string schemaNameNC = schemaName.ToLower();
schemaNameNC = schemaNameNC.Replace(' ', '~');
string tableName = rdr.GetString(1);
string tableNameNC = tableName.ToLower();
tableNameNC = tableNameNC.Replace(' ', '~');
bool isView = (rdr.GetString(2) == "VIEW");
string columnName = rdr.GetString(3);
string columnType = rdr.GetString(4);
int columnSize = (rdr.IsDBNull(5) ? -1 : rdr.GetInt32(5));
string columnTypeName = GetColumnTypeName(
columnType, columnSize);
if (!schemata.ContainsKey(schemaNameNC))
{
schemata[schemaNameNC] = new Schema(schemaName);
}
if (!schemata[schemaNameNC].Tables.ContainsKey(tableNameNC))
{
schemata[schemaNameNC].Tables[tableNameNC] =
new Table(tableName, schemaName, isView);
}
schemata[schemaNameNC].Tables[tableNameNC].Columns.Add(
new TableColumn(columnName, columnTypeName));
}
return(true);
}
catch
{
return(false);
}
finally
{
if (rdr != null)
{
rdr.Dispose();
}
if (cnn != null)
{
cnn.Dispose();
}
}
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term("Contacts.LBL_LIST_FORM_TITLE"));
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
using (IDbCommand cmd = con.CreateCommand())
{
try
{
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dtCombined = new DataTable())
{
// 12/19/2006 Paul. As much as we would like to combine the threee separate queries into
// a single query using a union, we cannot because the Security.Filter rules must be applied separately.
// We simply combine three DataTables as quickly and efficiently as possible.
cmd.CommandText = "select * " + ControlChars.CrLf
+ " , N'Contacts' as ADDRESS_TYPE" + ControlChars.CrLf
+ " from vwCONTACTS_EmailList " + ControlChars.CrLf;
Security.Filter(cmd, "Contacts", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "vwCONTACTS_EmailList", Sql.ClientScriptBlock(cmd));
}
da.Fill(dtCombined);
cmd.Parameters.Clear();
cmd.CommandText = "select * " + ControlChars.CrLf
+ " , N'Leads' as ADDRESS_TYPE" + ControlChars.CrLf
+ " from vwLEADS_EmailList " + ControlChars.CrLf;
Security.Filter(cmd, "Leads", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "vwLEADS_EmailList", Sql.ClientScriptBlock(cmd));
}
using (DataTable dt = new DataTable())
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
DataRow rowNew = dtCombined.NewRow();
//rowNew.ItemArray = row.ItemArray;
// 12/19/2006 Paul. Using the ItemArray would certainly be faster,
// but someone may accidentally modify one of the columns of the three views,
// so we shall be safe and check each column before setting its value.
foreach (DataColumn col in dt.Columns)
{
if (dtCombined.Columns.Contains(col.ColumnName))
{
rowNew[col.ColumnName] = row[col.ColumnName];
}
}
dtCombined.Rows.Add(rowNew);
}
}
cmd.Parameters.Clear();
cmd.CommandText = "select * " + ControlChars.CrLf
+ " , N'Prospects' as ADDRESS_TYPE" + ControlChars.CrLf
+ " from vwPROSPECTS_EmailList " + ControlChars.CrLf;
Security.Filter(cmd, "Prospects", "list");
ctlSearchView.SqlSearchClause(cmd);
if (bDebug)
{
Page.ClientScript.RegisterClientScriptBlock(System.Type.GetType("System.String"), "vwPROSPECTS_EmailList", Sql.ClientScriptBlock(cmd));
}
using (DataTable dt = new DataTable())
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
DataRow rowNew = dtCombined.NewRow();
//rowNew.ItemArray = row.ItemArray;
// 12/19/2006 Paul. Using the ItemArray would certainly be faster,
// but someone may accidentally modify one of the columns of the three views,
// so we shall be safe and check each column before setting its value.
foreach (DataColumn col in dt.Columns)
{
if (dtCombined.Columns.Contains(col.ColumnName))
{
rowNew[col.ColumnName] = row[col.ColumnName];
}
}
dtCombined.Rows.Add(rowNew);
}
}
vwMain = dtCombined.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
// 12/14/2007 Paul. Only set the default sort if it is not already set. It may have been set by SearchView.
if (String.IsNullOrEmpty(grdMain.SortColumn))
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
}
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
}
if (!IsPostBack)
{
Page.DataBind();
}
}
private IList <IList <dynamic> > ExecuteCommands()
{
Contract.Requires(this.Source != null);
if (Commands == null)
{
return(new List <IList <dynamic> >());
}
var ReturnValue = new List <IList <dynamic> >();
if (Commands.Count == 0)
{
ReturnValue.Add(new List <dynamic>());
return(ReturnValue);
}
DbProviderFactory Factory = DbProviderFactories.GetFactory(Source.SourceType);
using (DbConnection Connection = Factory.CreateConnection())
{
Connection.ConnectionString = Source.Connection;
using (DbCommand ExecutableCommand = Factory.CreateCommand())
{
ExecutableCommand.Connection = Connection;
ExecutableCommand.CommandType = CommandType.Text;
if (Commands.Count > 1 &&
!Commands.Any(x => x.SQLCommand.Contains("ALTER DATABASE")) &&
!Commands.Any(x => x.SQLCommand.Contains("CREATE DATABASE")))
{
ExecutableCommand.BeginTransaction();
}
ExecutableCommand.Open();
try
{
int Count = 0;
while (true)
{
var FinalParameters = new List <IParameter>();
string FinalSQLCommand = "";
int ParameterTotal = 0;
ExecutableCommand.Parameters.Clear();
for (int y = Count; y < Commands.Count; ++y)
{
ICommand Command = Commands[y];
if (ParameterTotal + Command.Parameters.Count > 2100)
{
break;
}
ParameterTotal += Command.Parameters.Count;
if (Command.CommandType == System.Data.CommandType.Text)
{
FinalSQLCommand += string.IsNullOrEmpty(Command.SQLCommand) ?
"" :
ParameterRegex.Replace(Command.SQLCommand, x =>
{
if (!x.Value.StartsWith("@@", StringComparison.Ordinal))
{
return(x.Value + "Command" + Count.ToString(CultureInfo.InvariantCulture));
}
return(x.Value);
}) + System.Environment.NewLine;
foreach (IParameter Parameter in Command.Parameters)
{
FinalParameters.Add(Parameter.CreateCopy("Command" + Count.ToString(CultureInfo.InvariantCulture)));
}
}
else
{
FinalSQLCommand += Command.SQLCommand + System.Environment.NewLine;
foreach (IParameter Parameter in Command.Parameters)
{
FinalParameters.Add(Parameter.CreateCopy(""));
}
}
++Count;
}
ExecutableCommand.CommandText = FinalSQLCommand;
FinalParameters.ForEach(x => x.AddParameter(ExecutableCommand));
using (DbDataReader TempReader = ExecutableCommand.ExecuteReader())
{
ReturnValue.Add(GetValues(TempReader));
while (TempReader.NextResult())
{
ReturnValue.Add(GetValues(TempReader));
}
}
if (Count >= CommandCount)
{
break;
}
}
ExecutableCommand.Commit();
}
catch { ExecutableCommand.Rollback(); throw; }
finally { ExecutableCommand.Close(); }
}
}
for (int x = 0, y = 0; x < Commands.Count(); ++x)
{
if (Commands[x].Finalizable)
{
Commands[x].Finalize(ReturnValue[y]);
++y;
}
else
{
Commands[x].Finalize(new List <dynamic>());
}
}
return(ReturnValue);
}
private void Page_Load(object sender, System.EventArgs e)
{
SetPageTitle(L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
nACLACCESS_Export = Security.GetUserAccess(m_sMODULE, "export");
try
{
sMODULE_NAME = Sql.ToString(Request["MODULE_NAME"]);
ctlListHeaderMySaved.Title = ".saved_reports_dom." + sMODULE_NAME;
ctlListHeaderPublished.Title = ".published_reports_dom." + sMODULE_NAME;
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwREPORTS_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
Sql.AppendParameter(cmd, sMODULE_NAME, "MODULE_NAME");
if (bDebug)
{
RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
}
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
// 06/18/2006 Paul. Translate the report type.
foreach (DataRow row in dt.Rows)
{
row["REPORT_TYPE"] = L10n.Term(".dom_report_types.", row["REPORT_TYPE"]);
}
vwMySaved = new DataView(dt);
vwMySaved.RowFilter = "PUBLISHED = 0 and ASSIGNED_USER_ID = '" + Security.USER_ID.ToString() + "'";
grdMySaved.DataSource = vwMySaved;
if (!IsPostBack)
{
grdMySaved.SortColumn = "NAME";
grdMySaved.SortOrder = "asc";
grdMySaved.ApplySort();
grdMySaved.DataBind();
}
vwPublished = new DataView(dt);
// 05/18/2006 Paul. Lets include unassigned so that they don't get lost.
vwPublished.RowFilter = "PUBLISHED = 1 or ASSIGNED_USER_ID is null";
grdPublished.DataSource = vwPublished;
if (!IsPostBack)
{
grdPublished.SortColumn = "NAME";
grdPublished.SortOrder = "asc";
grdPublished.ApplySort();
grdPublished.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
// 08/21/2005 Paul. Redirect to parent if that is where the note was originated.
Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]);
Guid gCONTACT_ID = Sql.ToGuid(Request["CONTACT_ID"]);
string sMODULE = String.Empty;
string sPARENT_TYPE = String.Empty;
string sPARENT_NAME = String.Empty;
try
{
SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
// The only possible error is a connection failure, so just ignore all errors.
gPARENT_ID = Guid.Empty;
}
if (e.CommandName == "Save")
{
// 01/16/2006 Paul. Enable validator before validating page.
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditView", this);
if (Page.IsValid)
{
string sCUSTOM_MODULE = "TASKS";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
SqlProcs.spTASKS_Update
(ref gID
, new DynamicControl(this, "ASSIGNED_USER_ID").ID
, new DynamicControl(this, "NAME").Text
, new DynamicControl(this, "STATUS").SelectedValue
, new DynamicControl(this, "DATE_DUE").DateValue
, new DynamicControl(this, "DATE_START").DateValue
, new DynamicControl(this, "PARENT_TYPE").SelectedValue
, new DynamicControl(this, "PARENT_ID").ID
, new DynamicControl(this, "CONTACT_ID").ID
, new DynamicControl(this, "PRIORITY").SelectedValue
, new DynamicControl(this, "DESCRIPTION").Text
, trn
);
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else if (!Sql.IsEmptyGuid(gCONTACT_ID))
{
Response.Redirect("~/Contacts/view.aspx?ID=" + gCONTACT_ID.ToString());
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
else if (e.CommandName == "Cancel")
{
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else if (!Sql.IsEmptyGuid(gCONTACT_ID))
{
Response.Redirect("~/Contacts/view.aspx?ID=" + gCONTACT_ID.ToString());
}
else if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
/// <summary>
/// 讀商品檔
/// </summary>
public void readCsv()
{
//暫存字串,讀取商品檔存到後暫存變數方便存取
string temp = string.Empty;
//檔案目前所在行數
int rowCount = 1;
//重設商品檔總數.目前新增數量
TotalCount = 0;
Count = 0;
list.Clear();
Action makeBtNotEnable = delegate()
{
this.btImportSQL.IsEnabled = false;
this.btImportCsv.IsEnabled = false;
this.btChooseCsv.IsEnabled = false;
this.btChooseSQL.IsEnabled = false;
};
Action makeBtEnable = delegate()
{
this.btImportSQL.IsEnabled = true;
this.btImportCsv.IsEnabled = true;
this.btChooseCsv.IsEnabled = true;
this.btChooseSQL.IsEnabled = true;
};
this.Dispatcher.BeginInvoke(makeBtNotEnable);
try
{
if (System.IO.File.Exists(FilePath))
{
System.IO.StreamReader CalTotalCount = new System.IO.StreamReader(FilePath);
while ((temp = CalTotalCount.ReadLine()) != null)
{
if (!string.IsNullOrEmpty(temp))
{
TotalCount++;
}
}
System.IO.StreamReader file = new System.IO.StreamReader(FilePath);
if (Mode.Equals("AUTO"))
{
while ((temp = file.ReadLine()) != null)
{
if (!string.IsNullOrEmpty(temp) && temp.Contains(","))
{
list.Add(new Data {
ID = temp.Substring(0, temp.IndexOf(",")).Replace("'", "’"), Value = temp.Substring(temp.IndexOf(","), temp.Length - temp.IndexOf(",")).Replace("'", "’")
});
}
else
{
WriteLog(DateTime.Now + " 商品檔第 " + rowCount + "行 資料格式不正確沒有新增 :" + temp);
}
}
}
else
{
lock (obj)
{
while ((temp = file.ReadLine()) != null)
{
if (!string.IsNullOrEmpty(temp) && temp.Contains(","))
{
list.Add(new Data {
ID = temp.Substring(0, temp.IndexOf(",")).Replace("'", "’"), Value = temp.Substring(temp.IndexOf(","), temp.Length - temp.IndexOf(",")).Replace("'", "’")
});
}
else
{
WriteLog(DateTime.Now + " Row " + rowCount + "資料格式不正確沒有新增 :" + temp);
}
}
}
}
lock (obj)
{
SQLiteConnection dbConnection = new SQLiteConnection(ConnectionString);
DbProviderFactory factory = SQLiteFactory.Instance;
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
DbTransaction trans = conn.BeginTransaction();
ConcurrentQueue <Data> Queue = new ConcurrentQueue <Data>();
foreach (var item in list)
{
try
{
cmd.CommandText = string.Format("Replace Into CommodityInfo Values('{0}','{1}',datetime('now', 'localtime'));", item.ID, item.Value);
cmd.ExecuteNonQuery();
Count++;
Console.WriteLine(Count + "/" + TotalCount);
}
catch (Exception ie)
{
WriteLog(DateTime.Now + "新增失敗 : \n" + cmd.CommandText);
}
}
trans.Commit();
if (Mode == "AUTO")
{
Console.WriteLine("done");
}
else
{
MessageBox.Show("done");
this.Dispatcher.BeginInvoke(makeBtEnable);
}
}
}
}
else
{
if (Mode.Equals("AUTO"))
{
Console.WriteLine("找不到商品檔。");
}
else
{
MessageBox.Show("找不到商品檔。");
}
}
}
catch (Exception ie)
{
if (Mode.Equals("AUTO"))
{
Console.WriteLine("讀取商品檔發生例外 " + ie.StackTrace);
Console.ReadLine();
}
else
{
MessageBox.Show("讀取商品檔發生例外 " + ie.StackTrace);
}
this.Dispatcher.BeginInvoke(makeBtEnable);
}
finally
{
this.Dispatcher.BeginInvoke(makeBtEnable);
}
}
/// <summary>
/// Asynchronous function that accepts the client and processes its requests
/// </summary>
public async void Connect()
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
factory.CreateConnection();
using (SqlConnection conn = new SqlConnection(ServConnect.strConnectionString))
{
TcpListener serv = new TcpListener(IPAddress.Any, 10000);
serv.Start();
while (true)
{
conn.ConnectionString = ServConnect.strConnectionString;
conn.Open();
DbCommand comm = factory.CreateCommand();
comm.Connection = conn;
TcpClient client = await Task.Run(() => serv.AcceptTcpClient());
byte[] buf = new byte[4096];
var iRead = client.GetStream().Read(buf, 0, buf.Length);
string[] stringSeparators = new string[] { "\r\n" };
string str = Encoding.Unicode.GetString(buf, 0, iRead);
int codding = int.Parse(str.Remove(3));
str = str.Remove(0, 3);
Console.WriteLine("\r\n" + codding + " " + str);
/// login to the program with a username and password
if (codding == 001)
{
try
{
string[] log_pass = str.Split(stringSeparators, StringSplitOptions.None);
string log = log_pass[0];
string pass = log_pass[1];
Console.WriteLine(log + " " + pass);
comm.CommandText = string.Format("Select id,name,surname from Users where Users.login='******' AND Users.pass = '******'",
log, pass);
DbDataReader reader = comm.ExecuteReader();
int id_user = 0;
string Name_user = null;
string Surname_user = null;
while (reader.Read())
{
id_user = int.Parse(reader.GetValue(0).ToString());
Name_user = reader.GetValue(1).ToString();
Surname_user = reader.GetValue(2).ToString();
}
buf = Encoding.Unicode.GetBytes(string.Format("{0}\r\n{1}\r\n{2}", id_user, Name_user, Surname_user));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// New User Registration
if (codding == 002)
{
try
{
string[] name_surname_log_pass = str.Split(stringSeparators, StringSplitOptions.None);
string name = name_surname_log_pass[0];
string surname = name_surname_log_pass[1];
string log = name_surname_log_pass[2];
string pass = name_surname_log_pass[3];
comm.CommandText = string.Format("Select login from Users where Users.login='******'", log);
string logg = null;
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
logg = reader.GetValue(0).ToString();
}
reader.Close();
reader = null;
if (logg != null)
{
throw new Exception("Пользователь с таким лоогином уже существует");
}
comm.CommandText = string.Format("INSERT INTO Users (name, surname, login, pass) VALUES ('{0}', '{1}', '{2}', '{3}');",
name, surname, log, pass);
DbDataReader reader1 = comm.ExecuteReader();
reader1.Close();
reader1 = null;
comm.CommandText = string.Format("Select id,name,surname from Users where Users.login='******' AND Users.pass = '******'",
log, pass);
DbDataReader proverka = comm.ExecuteReader();
int id_user = 0;
string Name_user = null;
string Surname_user = null;
while (proverka.Read())
{
id_user = int.Parse(proverka.GetValue(0).ToString());
Name_user = proverka.GetValue(1).ToString();
Surname_user = proverka.GetValue(2).ToString();
}
if (id_user == 0 || Name_user == null || Surname_user == null)
{
throw new Exception("Не удалось зарегестрировать пользователя");
}
proverka.Close();
proverka = null;
comm.CommandText = string.Format("CREATE TABLE Friends{0} (id INT NOT NULL PRIMARY KEY,name nchar(15) NOT NULL,surname nchar(15) NOT NULL)",
id_user);
DbDataReader CreatreTable = comm.ExecuteReader();
CreatreTable.Close();
CreatreTable = null;
comm.CommandText = string.Format("CREATE TABLE Rooms{0} (id_Friend INT NOT NULL PRIMARY KEY)", id_user);
DbDataReader CreatreTableMessage = comm.ExecuteReader();
CreatreTableMessage.Close();
CreatreTableMessage = null;
buf = Encoding.Unicode.GetBytes("Поздравляем, вы успешно зарегестрировались");
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// Sending a list of friends to the user
if (codding == 003)
{
try
{
comm.CommandText = string.Format("Select id,name,surname from Friends{0}", str);
string lstfriens = null;
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
lstfriens += string.Format("{0} {1} {2}\r\n", reader.GetValue(0).ToString().Trim(' '), reader.GetValue(1).ToString().Trim(' '), reader.GetValue(2).ToString().Trim(' '));
}
if (lstfriens == null)
{
lstfriens = "0";
}
buf = Encoding.Unicode.GetBytes(lstfriens);
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
reader.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// search for new friends
if (codding == 004)
{
try
{
string[] log_pass = str.Split(stringSeparators, StringSplitOptions.None);
int codSerch = int.Parse(log_pass[0]);
if (codSerch == 0)
{
int id_serch = int.Parse(log_pass[1]);
Console.WriteLine(codSerch + " " + id_serch);
comm.CommandText = string.Format("Select id,name,surname from Users where Users.id='{0}'", id_serch);
}
else if (codSerch == 1)
{
string name_serch = log_pass[1];
Console.WriteLine(codSerch + " " + name_serch);
comm.CommandText = string.Format("Select id,name,surname from Users where Users.name='{0}'", name_serch);
}
else if (codSerch == 2)
{
string surname_serch = log_pass[1];
Console.WriteLine(codSerch + " " + surname_serch);
comm.CommandText = string.Format("Select id,name,surname from Users where Users.surname='{0}'", surname_serch);
}
else if (codSerch == 3)
{
string[] name_surname = log_pass[1].Split(' ');
string name = name_surname[0];
string surname = name_surname[1];
Console.WriteLine(codSerch + " " + name + " " + surname);
comm.CommandText = string.Format("Select id,name,surname from Users where Users.name='{0}' AND Users.surname = '{1}'", name, surname);
}
string lstfriens = null;
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
lstfriens += string.Format("{0} {1} {2}\r\n", reader.GetValue(0).ToString().Trim(' '), reader.GetValue(1).ToString().Trim(' '), reader.GetValue(2).ToString().Trim(' '));
}
if (lstfriens == null)
{
lstfriens = "0";
}
buf = Encoding.Unicode.GetBytes(lstfriens);
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
reader.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// add user to friends
if (codding == 005)
{
try
{
string[] log_pass = str.Split(stringSeparators, StringSplitOptions.None);
string id_friend = log_pass[0];
string my_id = log_pass[1];
Console.WriteLine(id_friend + " " + my_id);
comm.CommandText = string.Format("Select name from Friends{0} where id='{1}'", my_id, id_friend);
string Name_friend = null;
string Surname_friend = null;
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
Name_friend = reader.GetValue(0).ToString();
}
reader.Close();
reader = null;
if (Name_friend != null)
{
throw new Exception("Этот пользователь уже находится у вас в списке друзей");
}
comm.CommandText = string.Format("Select id,name,surname from Users where Users.id='{0}'", id_friend);
reader = comm.ExecuteReader();
while (reader.Read())
{
id_friend = reader.GetValue(0).ToString();
Name_friend = reader.GetValue(1).ToString();
Surname_friend = reader.GetValue(2).ToString();
}
reader.Close();
reader = null;
comm.CommandText = string.Format("INSERT INTO Friends{3} (id,name, surname) VALUES ('{0}', '{1}', '{2}');", id_friend, Name_friend, Surname_friend, my_id);
reader = comm.ExecuteReader();
reader.Close();
reader = null;
buf = Encoding.Unicode.GetBytes(string.Format("Пользователь {0} {1} успешно добавлен в список друзей", Name_friend.Trim(' '), Surname_friend.Trim(' ')));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// creating a "room" of two users for communication
if (codding == 006)
{
try
{
string[] log_pass = str.Split(stringSeparators, StringSplitOptions.None);
string my_id = log_pass[0];
string friend_id = log_pass[1];
Console.WriteLine(my_id + " " + friend_id);
string id = null;
comm.CommandText = string.Format("Select id_Friend from Rooms{0} where id_Friend='{1}'", my_id, friend_id);
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
id = reader.GetValue(0).ToString();
}
reader.Close();
reader = null;
string name_table_message = null;
if (id != null)
{
try
{
if (int.Parse(my_id) < int.Parse(friend_id))
{
throw new Exception(name_table_message = "Message_" + my_id + "_" + friend_id);
}
else
{
throw new Exception(name_table_message = "Message_" + friend_id + "_" + my_id);
}
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format(ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
comm.CommandText = string.Format("INSERT INTO Rooms{0} (id_Friend) VALUES ('{1}');", my_id, friend_id);
DbDataReader reader1 = comm.ExecuteReader();
reader1.Close();
reader1 = null;
comm.CommandText = string.Format("INSERT INTO Rooms{0} (id_Friend) VALUES ('{1}');", friend_id, my_id);
DbDataReader reader2 = comm.ExecuteReader();
reader2.Close();
reader2 = null;
if (int.Parse(my_id) < int.Parse(friend_id))
{
comm.CommandText = string.Format("CREATE TABLE Message_{0}_{1} (id_Message INT NOT NULL PRIMARY KEY IDENTITY,Text nvarchar(1000) NOT NULL,id_sender int NOT NULL)", my_id, friend_id);
name_table_message = "Message_" + my_id + "_" + friend_id;
}
else
{
comm.CommandText = string.Format("CREATE TABLE Message_{0}_{1} (id_Message INT NOT NULL PRIMARY KEY IDENTITY,Text nvarchar(1000) NOT NULL,id_sender int NOT NULL)", friend_id, my_id);
}
name_table_message = "Message_" + friend_id + "_" + my_id;
DbDataReader CreatreTableMessage = comm.ExecuteReader();
CreatreTableMessage.Close();
CreatreTableMessage = null;
buf = Encoding.Unicode.GetBytes(name_table_message);
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// return the user a list of all his chat rooms
if (codding == 007)
{
try
{
string lstfriens = null;
comm.CommandText = string.Format("Select id_Friend from Rooms{0}", str);
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
lstfriens += string.Format("{0} ", reader.GetValue(0).ToString().Trim(' '));
}
reader.Close();
reader = null;
if (lstfriens == null)
{
throw new Exception("0");
}
string[] lstfriendinroom = lstfriens.Split(' ');
lstfriens = null;
for (int i = 0; i < lstfriendinroom.Length - 1; i++)
{
comm.CommandText = string.Format("Select id,name,surname from Users where id='{0}'", lstfriendinroom[i]);
reader = comm.ExecuteReader();
while (reader.Read())
{
lstfriens += string.Format("{0} {1} {2}\r\n", reader.GetValue(0).ToString().Trim(' '),
reader.GetValue(1).ToString().Trim(' '),
reader.GetValue(2).ToString().Trim(' '));
}
reader.Close();
reader = null;
}
buf = Encoding.Unicode.GetBytes(lstfriens);
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// send a chat history to the user
if (codding == 008)
{
try
{
string[] log_pass = str.Split(stringSeparators, StringSplitOptions.None);
string NameChat = log_pass[0];
string lstMessage = null;
comm.CommandText = string.Format("Select Text,id_sender from {0}", NameChat);
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
lstMessage += string.Format("{0}_{1}\\", reader.GetValue(0).ToString(), reader.GetValue(1).ToString());
}
reader.Close();
reader = null;
buf = Encoding.Unicode.GetBytes(lstMessage);
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
/// chat room posting
if (codding == 009)
{
try
{
string[] log_pass = str.Split('\\');
string NameChat = log_pass[0];
string id_user = log_pass[1];
string text = log_pass[2];
Console.WriteLine(codding + " " + NameChat);
comm.CommandText = string.Format("INSERT INTO {0} (Text,id_sender) VALUES ('{1}', '{2}');", NameChat, text, id_user);
DbDataReader reader = comm.ExecuteReader();
reader.Close();
reader = null;
buf = Encoding.Unicode.GetBytes("1");
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
catch (Exception ex)
{
buf = Encoding.Unicode.GetBytes(string.Format("Произошла ошибка: {0}", ex.Message));
client.GetStream().Write(buf, 0, buf.Length);
conn.Close();
}
}
}
}
}
catch (Exception ex)
{ Console.WriteLine(ex); }
}
/// <summary>
/// 获取数据源
/// </summary>
/// <returns></returns>
protected virtual object GetDataSource()
{
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings[this.Connection.GetTextValue()];
if (setting == null)
{
return(null);
}
DbProviderFactory dbFactory = Utility.CreateDbProviderFactory(setting.ProviderName);
if (dbFactory == null)
{
return(null);
}
object result = null;
using (DbConnection dbConnection = dbFactory.CreateConnection())
{
dbConnection.ConnectionString = setting.ConnectionString;
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = this.CommandType == null ? System.Data.CommandType.Text : (System.Data.CommandType)Utility.ConvertTo(this.CommandType.GetTextValue(), typeof(System.Data.CommandType));
dbCommand.CommandText = this.CommandText.GetTextValue();
if (this.Parameters.Count > 0)
{
string format = this.ParameterFormat == null ? "@p{0}" : this.ParameterFormat.GetTextValue();
List <object> expParams = new List <object>();
for (int i = 0; i < this.Parameters.Count; i++)
{
IExpression exp = this.Parameters[i];
DbParameter dbParameter = dbFactory.CreateParameter();
object value = exp.GetValue();
dbParameter.ParameterName = string.IsNullOrEmpty(format) ? "?" : string.Format(format, i);
dbParameter.DbType = Utility.GetObjectDbType(value);
dbParameter.Value = value;
dbCommand.Parameters.Add(dbParameter);
}
}
using (DbDataAdapter dbAdapter = dbFactory.CreateDataAdapter())
{
dbAdapter.SelectCommand = dbCommand;
DataTable table = new DataTable();
dbAdapter.Fill(table);
if (this.RowIndex != null)
{
//只获取其中的某行数据
int row = Utility.ConverToInt32(this.RowIndex.GetTextValue());
if (table.Rows.Count > row)
{
result = table.Rows[row];
}
}
else
{
result = table;
}
}
}
}
return(result);
}
protected void Page_Command(object sender, CommandEventArgs e)
{
try
{
if (e.CommandName == "Search")
{
// 10/13/2005 Paul. Make sure to clear the page index prior to applying search.
grdMain.CurrentPageIndex = 0;
grdMain.ApplySort();
grdMain.DataBind();
}
// 12/14/2007 Paul. We need to capture the sort event from the SearchView.
else if (e.CommandName == "SortGrid")
{
grdMain.SetSortFields(e.CommandArgument as string[]);
}
else if (e.CommandName == "MassUpdate")
{
string[] arrID = Request.Form.GetValues("chkMain");
if (arrID != null)
{
// 10/26/2007 Paul. Use a stack to run the update in blocks of under 200 IDs.
//string sIDs = Utils.ValidateIDs(arrID);
System.Collections.Stack stk = Utils.FilterByACL_Stack(m_sMODULE, "edit", arrID, "INVOICES");
if (stk.Count > 0)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
while (stk.Count > 0)
{
string sIDs = Utils.BuildMassIDs(stk);
// 07/09/2006 Paul. The date conversion was moved out of the MassUpdate control.
// 09/11/2007 Paul. Mass update of teams is now available.
SqlProcs.spINVOICES_MassUpdate(sIDs, ctlMassUpdate.ASSIGNED_USER_ID, ctlMassUpdate.PAYMENT_TERMS, ctlMassUpdate.INVOICE_STAGE, T10n.ToServerTime(ctlMassUpdate.DUE_DATE), ctlMassUpdate.TEAM_ID, trn);
}
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
throw(new Exception(ex.Message, ex.InnerException));
}
}
}
Response.Redirect("default.aspx");
}
}
}
else if (e.CommandName == "MassDelete")
{
string[] arrID = Request.Form.GetValues("chkMain");
if (arrID != null)
{
// 10/26/2007 Paul. Use a stack to run the update in blocks of under 200 IDs.
//string sIDs = Utils.ValidateIDs(arrID);
System.Collections.Stack stk = Utils.FilterByACL_Stack(m_sMODULE, "delete", arrID, "INVOICES");
if (stk.Count > 0)
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
while (stk.Count > 0)
{
string sIDs = Utils.BuildMassIDs(stk);
SqlProcs.spINVOICES_MassDelete(sIDs, trn);
}
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
throw(new Exception(ex.Message, ex.InnerException));
}
}
}
Response.Redirect("default.aspx");
}
}
}
else if (e.CommandName == "Export")
{
// 11/03/2006 Paul. Apply ACL rules to Export.
int nACLACCESS = SplendidCRM.Security.GetUserAccess(m_sMODULE, "export");
if (nACLACCESS >= 0)
{
if (nACLACCESS == ACL_ACCESS.OWNER)
{
vwMain.RowFilter = "ASSIGNED_USER_ID = '" + Security.USER_ID.ToString() + "'";
}
string[] arrID = Request.Form.GetValues("chkMain");
SplendidExport.Export(vwMain, m_sMODULE, ctlExportHeader.ExportFormat, ctlExportHeader.ExportRange, grdMain.CurrentPageIndex, grdMain.PageSize, arrID);
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
lblError.Text = ex.Message;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "edit") >= 0);
if (!this.Visible)
{
return;
}
try
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
gID = Sql.ToGuid(Request["ID"]);
if (!IsPostBack)
{
CURRENCY_ID.DataSource = SplendidCache.Currencies();
CURRENCY_ID.DataBind();
TAXRATE_ID.DataSource = SplendidCache.TaxRates();
TAXRATE_ID.DataBind();
TAXRATE_ID.Items.Insert(0, new ListItem(L10n.Term(".LBL_NONE"), ""));
SHIPPER_ID.DataSource = SplendidCache.Shippers();
SHIPPER_ID.DataBind();
SHIPPER_ID.Items.Insert(0, new ListItem(L10n.Term(".LBL_NONE"), ""));
Guid gDuplicateID = Sql.ToGuid(Request["DuplicateID"]);
if (!Sql.IsEmptyGuid(gID) || !Sql.IsEmptyGuid(gDuplicateID))
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwQUOTES_Edit" + ControlChars.CrLf
+ " where ID = @ID " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
if (!Sql.IsEmptyGuid(gDuplicateID))
{
Sql.AddParameter(cmd, "@ID", gDuplicateID);
gID = Guid.Empty;
}
else
{
Sql.AddParameter(cmd, "@ID", gID);
}
con.Open();
#if DEBUG
Page.RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
#endif
using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rdr.Read())
{
ctlModuleHeader.Title = Sql.ToString(rdr["NAME"]);
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
Utils.UpdateTracker(Page, m_sMODULE, gID, ctlModuleHeader.Title);
ViewState["ctlModuleHeader.Title"] = ctlModuleHeader.Title;
ViewState["BILLING_ACCOUNT_ID"] = Sql.ToGuid(rdr["BILLING_ACCOUNT_ID"]);
ViewState["SHIPPING_ACCOUNT_ID"] = Sql.ToGuid(rdr["SHIPPING_ACCOUNT_ID"]);
this.AppendEditViewFields(m_sMODULE + ".EditView", tblMain, rdr);
this.AppendEditViewFields(m_sMODULE + ".EditAddress", tblAddress, rdr);
this.AppendEditViewFields(m_sMODULE + ".EditDescription", tblDescription, rdr);
new DynamicControl(this, "SHOW_LINE_NUMS").Checked = Sql.ToBoolean(rdr["SHOW_LINE_NUMS"]);
new DynamicControl(this, "CALC_GRAND_TOTAL").Checked = Sql.ToBoolean(rdr["CALC_GRAND_TOTAL"]);
try
{
new DynamicControl(this, "CURRENCY_ID").SelectedValue = Sql.ToString(rdr["CURRENCY_ID"]);
}
catch
{
}
try
{
new DynamicControl(this, "TAXRATE_ID").SelectedValue = Sql.ToString(rdr["TAXRATE_ID"]);
}
catch
{
}
try
{
new DynamicControl(this, "SHIPPER_ID").SelectedValue = Sql.ToString(rdr["SHIPPER_ID"]);
}
catch
{
}
}
}
}
}
}
else
{
this.AppendEditViewFields(m_sMODULE + ".EditView", tblMain, null);
this.AppendEditViewFields(m_sMODULE + ".EditAddress", tblAddress, null);
this.AppendEditViewFields(m_sMODULE + ".EditDescription", tblDescription, null);
// 06/08/2006 Paul. Prepopulate the Account.
Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]);
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
string sMODULE = String.Empty;
string sPARENT_TYPE = String.Empty;
string sPARENT_NAME = String.Empty;
SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME);
if (!Sql.IsEmptyGuid(gPARENT_ID) && sMODULE == "Accounts")
{
UpdateAccount(gPARENT_ID, true, true);
}
if (!Sql.IsEmptyGuid(gPARENT_ID) && sMODULE == "Contacts")
{
UpdateContact(gPARENT_ID, true, true);
}
else if (!Sql.IsEmptyGuid(gPARENT_ID) && sMODULE == "Opportunities")
{
new DynamicControl(this, "OPPORTUNITY_ID").ID = gPARENT_ID;
new DynamicControl(this, "OPPORTUNITY_NAME").Text = sPARENT_NAME;
}
}
}
}
else
{
// 12/02/2005 Paul. When validation fails, the header title does not retain its value. Update manually.
ctlModuleHeader.Title = Sql.ToString(ViewState["ctlModuleHeader.Title"]);
Utils.SetPageTitle(Page, L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
DynamicControl ctlBILLING_ACCOUNT_ID = new DynamicControl(this, "BILLING_ACCOUNT_ID");
DynamicControl ctlSHIPPING_ACCOUNT_ID = new DynamicControl(this, "SHIPPING_ACCOUNT_ID");
if (Sql.ToGuid(ViewState["BILLING_ACCOUNT_ID"]) != ctlBILLING_ACCOUNT_ID.ID)
{
UpdateAccount(ctlBILLING_ACCOUNT_ID.ID, true, true);
ViewState["BILLING_ACCOUNT_ID"] = ctlBILLING_ACCOUNT_ID.ID;
ViewState["SHIPPING_ACCOUNT_ID"] = ctlBILLING_ACCOUNT_ID.ID;
}
if (Sql.ToGuid(ViewState["SHIPPING_ACCOUNT_ID"]) != ctlSHIPPING_ACCOUNT_ID.ID)
{
UpdateAccount(ctlSHIPPING_ACCOUNT_ID.ID, false, true);
ViewState["SHIPPING_ACCOUNT_ID"] = ctlSHIPPING_ACCOUNT_ID.ID;
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
Utils.SetPageTitle(Page, L10n.Term(m_sMODULE + ".LBL_LIST_FORM_TITLE"));
// 06/04/2006 Paul. Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load.
this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "list") >= 0);
if (!this.Visible)
{
return;
}
try
{
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
string sSQL;
sSQL = "select * " + ControlChars.CrLf
+ " from vwPROJECTS_List" + ControlChars.CrLf
+ " where 1 = 1 " + ControlChars.CrLf;
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sSQL;
int nACLACCESS = Security.GetUserAccess(m_sMODULE, "list");
if (nACLACCESS == ACL_ACCESS.OWNER)
{
Sql.AppendParameter(cmd, Security.USER_ID, "ASSIGNED_USER_ID", false);
}
ctlSearch.SqlSearchClause(cmd);
#if DEBUG
Page.RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));
#endif
using (DbDataAdapter da = dbf.CreateDataAdapter())
{
((IDbDataAdapter)da).SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
da.Fill(dt);
vwMain = dt.DefaultView;
grdMain.DataSource = vwMain;
if (!IsPostBack)
{
grdMain.SortColumn = "NAME";
grdMain.SortOrder = "asc";
grdMain.ApplySort();
grdMain.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
lblError.Text = ex.Message;
}
if (!IsPostBack)
{
// 06/09/2006 Paul. Remove data binding in the user controls. Binding is required, but only do so in the ASPX pages.
//Page.DataBind();
}
}
protected void Page_Command(Object sender, CommandEventArgs e)
{
// 06/08/2006 Paul. Redirect to parent if that is where the note was originated.
Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]);
string sMODULE = String.Empty;
string sPARENT_TYPE = String.Empty;
string sPARENT_NAME = String.Empty;
try
{
SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME);
}
catch (Exception ex)
{
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
// The only possible error is a connection failure, so just ignore all errors.
gPARENT_ID = Guid.Empty;
}
if (e.CommandName == "Save")
{
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditView", this);
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditAddress", this);
SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditDescription", this);
if (Page.IsValid)
{
string sCUSTOM_MODULE = "QUOTES";
DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE);
DbProviderFactory dbf = DbProviderFactories.GetFactory();
using (IDbConnection con = dbf.CreateConnection())
{
con.Open();
using (IDbTransaction trn = con.BeginTransaction())
{
try
{
SqlProcs.spQUOTES_Update
(ref gID
, new DynamicControl(this, "ASSIGNED_USER_ID").ID
, new DynamicControl(this, "NAME").Text
, new DynamicControl(this, "OPPORTUNITY_ID").ID
, new DynamicControl(this, "QUOTE_TYPE").SelectedValue
, new DynamicControl(this, "PAYMENT_TERMS").SelectedValue
, new DynamicControl(this, "ORDER_STAGE").SelectedValue
, new DynamicControl(this, "QUOTE_STAGE").SelectedValue
, new DynamicControl(this, "PURCHASE_ORDER_NUM").Text
, new DynamicControl(this, "ORIGINAL_PO_DATE").DateValue
, new DynamicControl(this, "DATE_QUOTE_CLOSED").DateValue
, new DynamicControl(this, "DATE_QUOTE_EXPECTED_CLOSED").DateValue
, new DynamicControl(this, "DATE_ORDER_SHIPPED").DateValue
, new DynamicControl(this, "SHOW_LINE_NUMS").Checked
, new DynamicControl(this, "CALC_GRAND_TOTAL").Checked
, new DynamicControl(this, "CURRENCY_ID").ID
, new DynamicControl(this, "TAXRATE_ID").ID
, new DynamicControl(this, "SHIPPER_ID").ID
, new DynamicControl(this, "SUBTOTAL").DecimalValue
, new DynamicControl(this, "SHIPPING").DecimalValue
, new DynamicControl(this, "TAX").DecimalValue
, new DynamicControl(this, "TOTAL").DecimalValue
, new DynamicControl(this, "BILLING_ACCOUNT_ID").ID
, new DynamicControl(this, "BILLING_CONTACT_ID").ID
, new DynamicControl(this, "BILLING_ADDRESS_STREET").Text
, new DynamicControl(this, "BILLING_ADDRESS_CITY").Text
, new DynamicControl(this, "BILLING_ADDRESS_STATE").Text
, new DynamicControl(this, "BILLING_ADDRESS_POSTALCODE").Text
, new DynamicControl(this, "BILLING_ADDRESS_COUNTRY").Text
, new DynamicControl(this, "SHIPPING_ACCOUNT_ID").ID
, new DynamicControl(this, "SHIPPING_CONTACT_ID").ID
, new DynamicControl(this, "SHIPPING_ADDRESS_STREET").Text
, new DynamicControl(this, "SHIPPING_ADDRESS_CITY").Text
, new DynamicControl(this, "SHIPPING_ADDRESS_STATE").Text
, new DynamicControl(this, "SHIPPING_ADDRESS_POSTALCODE").Text
, new DynamicControl(this, "SHIPPING_ADDRESS_COUNTRY").Text
, new DynamicControl(this, "DESCRIPTION").Text
, trn
);
SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields);
trn.Commit();
}
catch (Exception ex)
{
trn.Rollback();
SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message);
ctlEditButtons.ErrorText = ex.Message;
return;
}
}
}
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
else if (e.CommandName == "Cancel")
{
if (!Sql.IsEmptyGuid(gPARENT_ID))
{
Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString());
}
else if (Sql.IsEmptyGuid(gID))
{
Response.Redirect("default.aspx");
}
else
{
Response.Redirect("view.aspx?ID=" + gID.ToString());
}
}
}
/// <summary>
/// DataTable of all users
/// </summary>
/// <returns>Datatable with columns NAME, ID, CREATEDDATE</returns>
public DataTable Users()
{
string collection = UsersCollectionName;
using (DbConnection conn = Factory.CreateConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
if (!SchemaCollectionExists(conn, collection))
{
return(CreateDataTable(collection));
}
return(conn.GetSchema(collection));
}
}
/// <summary>
/// Determines the correct provider to use and sets up the connection and command
/// objects for use in other methods
/// </summary>
/// <param name="connectString">The full connection string to the database</param>
/// <param name="providerlist">The enum value of providers from dbutilities.Providers</param>
public void CreateDBObjects(string connectString, Providers providerList)
{
//CreateDBObjects(connectString, providerList, null);
switch (providerList)
{
case Providers.SqlServer:
_factory = SqlClientFactory.Instance;
break;
case Providers.Oracle:
_factory = OracleClientFactory.Instance;
break;
case Providers.OleDB:
_factory = OleDbFactory.Instance;
break;
case Providers.ODBC:
_factory = OdbcFactory.Instance;
break;
}
_connection = _factory.CreateConnection();
_command = _factory.CreateCommand();
_connection.ConnectionString = connectString;
_command.Connection = connection;
}
