/// <summary>
/// Creates a new memariService and sets the Database context
/// </summary>
public memariServiceBase(DatabaseConnection connection)
: base()
{
UseConnectionAndTransaction(connection);
m_memariAdapter = new memariDataAdapter(connection);
}
public void PingTest_InputDBConnectionString_IsDBConnectionCorrect()
{
Service x = new Service();
AgentPingResponse resp = x.Ping();
DatabaseConnection db = new DatabaseConnection(AppSettings.GetString("AgentTemplateDbConnectionString"));
Assert.AreEqual(resp.Database, db["Database"]);
}
/// <summary>
/// Creates a new noeService and sets the Database context
/// </summary>
public noeServiceBase(DatabaseConnection connection)
: base()
{
UseConnectionAndTransaction(connection);
m_noeAdapter = new noeDataAdapter(connection);
}
private static void PerformPing()
{
string connectionString = AppSettings.GetString("AgentTemplateDbConnectionString");
int commandTimeoutSeconds = AppSettings.GetInt32("AgentTemplateDbCommandTimeoutSeconds", 30);
DatabaseConnection dbc = new DatabaseConnection(connectionString, commandTimeoutSeconds);
AssemblyName exe = Assembly.GetExecutingAssembly().GetName();
Console.WriteLine(exe.Name + ", Version=" + exe.Version);
Console.WriteLine("Database Connection String: " + dbc.ConnectionString);
Console.WriteLine("Ping:");
try {
using (DatabaseRequest db = new DatabaseRequest(dbc, "PingGetDatabaseInfo")) {
using (SqlDataReader reader = db.ExecuteSingleReader()) {
if (reader.Read()) {
Console.WriteLine(" Timestamp: " + reader.GetDateTime(0).ToString());
Console.WriteLine(" Server Instance: " + reader.GetString(1));
Console.WriteLine(" Version: " + reader.GetString(2));
Console.WriteLine(" Edition: " + reader.GetString(3));
Console.WriteLine(" Database: " + reader.GetString(4));
Console.WriteLine(" User: "******" <no data returned>");
}
}
}
} catch (Exception ex) {
Console.WriteLine("Ping failed." + Environment.NewLine + ex.ToString());
}
}
public static Permissions createPermissions(User user, DatabaseConnection db)
{
PreparedStatement preStmtUser = db.Prepare("SELECT action.name, content_id, allow FROM user_account_can_do_action, action WHERE action.id = user_account_can_do_action.action_id AND user_account_id = "+user.id);
PreparedStatement preStmtGroup = db.Prepare("SELECT action.name, content_id, allow FROM action, user_group_can_do_action, (SELECT user_group_id FROM user_account_in_user_group WHERE user_account_id = " + user.id + ") userGroups WHERE action.id = user_group_can_do_action.action_id AND user_group_can_do_action.user_group_id = userGroups.user_group_id");
List<RestService.Entities.Action> actions = new List<RestService.Entities.Action>();
Console.WriteLine(preStmtUser.GetCmd().CommandText);
SqlDataReader reader = db.Query(new Dictionary<string,string>(),preStmtUser);
while (reader.Read())
{
int contentId = int.Parse(reader.GetString(reader.GetOrdinal("content_id")));
string actionName = reader.GetString(reader.GetOrdinal("name"));
bool allowed = reader.GetBoolean(reader.GetOrdinal("allow"));
actions.Add(new Entities.Action(contentId,actionName,null, true));
}
reader = db.Query(new Dictionary<string,string>(), preStmtGroup);
while (reader.Read())
{
int contentId = reader.GetInt32(reader.GetOrdinal("content_id"));
string actionName = reader.GetString(reader.GetOrdinal("name"));
bool allowed = reader.GetBoolean(reader.GetOrdinal("allow"));
actions.Add(new Entities.Action(contentId, actionName, null, true));
}
return new Permissions(actions.ToArray(), user);
}
/// <summary>
/// Creates a new sazehService and sets the Database context
/// </summary>
public sazehServiceBase(DatabaseConnection connection)
: base()
{
UseConnectionAndTransaction(connection);
m_sazehAdapter = new sazehDataAdapter(connection);
}
/// <summary>
/// Creates a new divar_dakli_glbService and sets the Database context
/// </summary>
public divar_dakli_glbServiceBase(DatabaseConnection connection)
: base()
{
UseConnectionAndTransaction(connection);
m_divar_dakli_glbAdapter = new divar_dakli_glbDataAdapter(connection);
}
static DsDbConnConfig()
{
DatabaseAccess dbAccess = new DatabaseAccess(DatabaseConnection.Default);
using (DataTable dt = dbAccess.ExecuteDataTable("SELECT * FROM dsk_db_cfg")) {
if (dt != null) {
foreach (DataRow dr in dt.Rows) {
string name = dr["DB_NAME"].ToString();
string ip = dr["DB_IP"].ToString();
string database = dr["SID"].ToString();
int port = Convert.ToInt32(dr["DB_PORT"]);
string userId = dr["USR_NAME"].ToString();
string pwd = dr["USR_PWD"].ToString();
DatabaseType typ;
switch (dr["DB_TYP"].ToString()) {
case "1":
typ = DatabaseType.Oracle;
break;
case "2":
typ = DatabaseType.SQLServer;
break;
case "3":
typ = DatabaseType.MySql;
break;
default:
typ = DatabaseType.Oracle;
break;
}
DatabaseConnection conn = new DatabaseConnection(ip, port, database, userId, pwd, typ);
_dic[name] = conn;
}
}
}
}
public PictureForm(string str,byte typ_prierezu)
{
InitializeComponent();
dat_conn = DatabaseConnection.getInstance();
this.combo_text = str;
this.typ_prierezu = typ_prierezu;
this.loadPictureOfCrossSection(typ_prierezu);
}
/// <summary>
/// Creates a new instance of the ProcedureObject class
/// </summary>
public ProcedureObject()
{
m_DataObjectFactory = this.CreateDataObjectFactory();
DbConnection = new DatabaseConnection(m_DataObjectFactory.CreateConnection());
Command = m_DataObjectFactory.CreateCommand(DbConnection, System.Data.CommandType.StoredProcedure);
DataAdapter = m_DataObjectFactory.CreateDataAdapter();
DataAdapter.SelectCommand = Command;
}
//Constructor
public EN1999_1_1Form()
{
InitializeComponent();
dat_conn = DatabaseConnection.getInstance();
this.loadCombo1(); //loading data to combobox1
dtform = new DataTableForm(1); //for inicializing of object default value is 1
}
public void AsynchronousTest3()
{
// testBase class receives the connection call back after the asynch connection occurs
_mongoServerConnection.ConnectAsyncDelegate(_mongoServerConnection_Connected);
_serverConnectionAutoResetEvent.WaitOne();
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_1_NAME);
MongoCollection<Entry> collection = _mongoDatabaseConnection.GetCollection<Entry>(MONGO_COLLECTION_1_NAME);
}
static void GameProcessor(DatabaseConnection database)
{
List<LocationInfo> _locations;
#region Get initial data from database
_locations = database.GetLocations().ToList();
foreach (LocationInfo location in _locations)
{
database.UpdateStockInfo(location);
Console.WriteLine(location.Name);
foreach (StockInfo stock in location.Stocks)
{
Console.WriteLine(" - {0}:\t{1}\t{2}", Enum.GetName(typeof(ResourceEnum), stock.ResourceType), stock.Quantity, stock.UnitPrice);
}
}
#endregion //Get initial data from database
bool _exitThread = false;
DateTime _startTime;
DateTime _financialYear = DateTime.Now;
while (!_exitThread)
{
_startTime = DateTime.Now;
// The main thread for looping around processing game logic
// production happens on every cycle
database.ProductionCycle();
// financial "year" occurs every minute
if (_financialYear < _startTime)
{
// process the next financial cycle
database.UpdatePrices();
// and set the earliest the next financial year can occur
_financialYear = _startTime.AddMinutes(1);
}
Console.Write('.');
database.UpdateTransporters();
// sleep for the rest of this second (assuming there is some remaining)
TimeSpan timeRemaining = _startTime.AddSeconds(1).Subtract(DateTime.Now);// new TimeSpan(0, 0, 1);//1000 - ((DateTime.Now.Ticks / 1000L) - _startTime); // e-7 s
if (timeRemaining.Milliseconds > 0)
{
Thread.Sleep(timeRemaining.Milliseconds);
}
}
}
public void ConnectionStateTest()
{
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_1_NAME);
Assert.AreEqual(MongoServerState.Connected, _mongoDatabaseConnection.State);
Assert.IsNull(_mongoDatabaseConnection.Db);
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_1_NAME);
_mongoDatabaseConnection.Connect();
Assert.AreEqual(MongoServerState.Connected, _mongoDatabaseConnection.State);
Assert.IsNotNull(_mongoDatabaseConnection.Db);
}
public void Should_be_able_to_begin_and_rollback_a_transaction()
{
var dataSource = DefaultDataSource();
using (
var connection = new DatabaseConnection(dataSource,
DbConnectionFactory.Default().CreateConnection(dataSource),
new Mock<IDbCommandFactory>().Object,
new Mock<IDatabaseConnectionCache>().Object))
{
connection.BeginTransaction();
}
}
public void Should_be_able_to_call_commit_without_a_transaction()
{
var dataSource = DefaultDataSource();
using (
var connection = new DatabaseConnection(dataSource,
DbConnectionFactory.Default().CreateConnection(dataSource),
new Mock<IDbCommandFactory>().Object,
new Mock<IDatabaseConnectionCache>().Object))
{
connection.CommitTransaction();
}
}
public void Should_be_able_to_call_dispose_more_than_once()
{
var dataSource = DefaultDataSource();
using (
var connection = new DatabaseConnection(dataSource,
DbConnectionFactory.Default().CreateConnection(dataSource),
new Mock<IDbCommandFactory>().Object,
new Mock<IDatabaseConnectionCache>().Object))
{
connection.Dispose();
connection.Dispose();
}
}
public void DbTest()
{
Assert.IsNotNull(_mongoDatabaseConnection.Db, "Database is null");
Assert.AreEqual(MONGO_DATABASE_1_NAME, _mongoDatabaseConnection.Db.Name);
// assign a bad server connection and verify Db is null
_mongoServerConnection = new ServerConnection(MONGO_CONNECTION_STRING);
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_1_NAME);
Assert.IsNull(_mongoDatabaseConnection.Db, "Database is null when not connected");
_mongoDatabaseConnection.Connect();
Assert.IsNotNull(_mongoDatabaseConnection.Db, "Database is not null");
}
public Server(string uriPrefix, DatabaseConnection connection)
{
if (string.IsNullOrEmpty(uriPrefix)) throw new ArgumentNullException("uriPrefix");
if (connection == null) throw new ArgumentNullException("connection");
System.Threading.ThreadPool.SetMaxThreads(5, 1000);
System.Threading.ThreadPool.SetMinThreads(5, 5);
_listener = new HttpListener();
_listener.Prefixes.Add(uriPrefix);
_connection = connection;
_sessions = new SessionList();
}
public void DropAllDatabasesTest()
{
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_1_NAME);
_mongoDatabaseConnection.Connect();
AddMongoEntry(collectionName: MONGO_COLLECTION_1_NAME);// uses MONGO_DATABASE_1_NAME
AddMongoEntry(collectionName: MONGO_COLLECTION_1_NAME);
AddMongoEntry(collectionName: MONGO_COLLECTION_2_NAME);
AddMongoEntry(collectionName: MONGO_COLLECTION_2_NAME);
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_2_NAME);
_mongoDatabaseConnection.Connect();
_reader = new Reader(_mongoDatabaseConnection);
_writer = new Writer(_mongoDatabaseConnection);// need to reinitialize writer when we change the DatabaseConnection
AddMongoEntry(collectionName: MONGO_COLLECTION_1_NAME);// uses MONGO_DATABASE_2_NAME
AddMongoEntry(collectionName: MONGO_COLLECTION_1_NAME);
AddMongoEntry(collectionName: MONGO_COLLECTION_2_NAME);
AddMongoEntry(collectionName: MONGO_COLLECTION_2_NAME);
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_3_NAME);
_mongoDatabaseConnection.Connect();
_reader = new Reader(_mongoDatabaseConnection);
_writer = new Writer(_mongoDatabaseConnection);// need to reinitialize writer when we change the DatabaseConnection
AddMongoEntry(collectionName: MONGO_COLLECTION_1_NAME);// uses MONGO_DATABASE_3_NAME
AddMongoEntry(collectionName: MONGO_COLLECTION_1_NAME);
AddMongoEntry(collectionName: MONGO_COLLECTION_2_NAME);
AddMongoEntry(collectionName: MONGO_COLLECTION_2_NAME);
List<string> results = _mongoServerConnection.GetDbNamesForConnection();
Assert.IsTrue(results.Contains(MONGO_DATABASE_1_NAME));
Assert.IsTrue(results.Contains(MONGO_DATABASE_2_NAME));
Assert.IsTrue(results.Contains(MONGO_DATABASE_3_NAME));
List<CommandResult> commandResults = _mongoServerConnection.DropAllDatabases();
foreach (CommandResult commandResult in commandResults)
{
Assert.IsNotNull(commandResult);
Assert.IsTrue(commandResult.Ok);
Assert.IsNull(commandResult.ErrorMessage);
}
results = _mongoServerConnection.GetDbNamesForConnection();
Assert.IsFalse(results.Contains(MONGO_DATABASE_1_NAME));
Assert.IsFalse(results.Contains(MONGO_DATABASE_2_NAME));
Assert.IsFalse(results.Contains(MONGO_DATABASE_3_NAME));
}
private static string GetUserPassword(string email)
{
DatabaseConnection dbConnect = new DatabaseConnection("SMU");
string query = "SELECT password FROM user_account WHERE email=/'" + email + "/'";
PreparedStatement prepStat = dbConnect.Prepare(query);
SqlDataReader data = dbConnect.Query(null, prepStat);
string userPassword = data.GetString(0);
data.Close();
dbConnect.CloseConnection();
return userPassword;
}
public DataTableForm(byte typ_prierezu)
{
InitializeComponent();
dat_conn = DatabaseConnection.getInstance();
this.typ_prierezu = typ_prierezu;
//najprv sa naloaduju data z databazy
//obnovi sa tak zoznam zozChLInfos zo suboru checklist.dat
this.LoadData();
this.nacitajCheckedListBox();
selectedAllItems1 = false;
selectedAllItems2 = false;
selectedAllItems3 = false;
selectedAllItems4 = false;
}
public System.Data.DataSet DataSet()
{
System.Data.SqlClient.SqlConnection con = new DatabaseConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Database"].ConnectionString).GetConnection;
System.Data.DataSet dat_set;
con.Open();
da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);
dat_set = new System.Data.DataSet();
da_1.Fill(dat_set, "Table_Data_1");
con.Close();
return dat_set;
}
public void AsynchronousTest1()
{
_mongoServerConnection = new ServerConnection(MONGO_CONNECTION_STRING);
Assert.AreEqual(MongoServerState.Disconnected, _mongoServerConnection.State);
_mongoDatabaseConnection = new DatabaseConnection(_mongoServerConnection, MONGO_DATABASE_1_NAME);
Assert.AreEqual(_mongoDatabaseConnection.State, MongoServerState.Disconnected);
_mongoDatabaseConnection.ConnectAsyncDelegate(_mongoDatabaseConnection_Connected);
Assert.AreEqual(MongoServerState.Disconnected, _mongoDatabaseConnection.State);
_databaseConnectionAutoResetEvent.WaitOne();// wait for the async operation to complete so that we can compare the connection state
Assert.AreEqual(_mongoDatabaseConnection.State, MongoServerState.Connected);/**/
Assert.AreEqual(_databaseConnectionResult, ConnectionResult.Success);/**/
Assert.IsNotNull(_serverConnectionReturnMessage);
Assert.IsNotNull(_databaseConnectionReturnMessage);
}
public static void insertMediaFile(DatabaseConnection db, Stream file, int id, Permissions per)
{
PreparedStatement stmt1 = db.Prepare("SELECT * FROM media WHERE id = " + id.ToString());
SqlDataReader reader = db.Query(null, stmt1);
Media mediaMeta = null;
while (reader.Read())
{
int mediaId = reader.GetInt32(reader.GetOrdinal("id"));
int mediaCategory = reader.GetInt32(reader.GetOrdinal("media_category_id"));
int user = reader.GetInt32(reader.GetOrdinal("user_account_id"));
string mediaFileLocation = reader.GetString(reader.GetOrdinal("file_location"));
string title = reader.GetString(reader.GetOrdinal("title"));
string description = reader.GetString(reader.GetOrdinal("description"));
int mediaLength = reader.GetInt32(reader.GetOrdinal("minutes"));
string format = reader.GetString(reader.GetOrdinal("format"));
mediaMeta = new Media(mediaId, mediaCategory, user, mediaFileLocation, title, description, mediaLength, format);
}
reader.Close();
string fileLocation = @"C:\RentItServices\Rentit26\MediaFiles\" + mediaMeta.id.ToString();
System.IO.Directory.CreateDirectory(fileLocation);
string fileDir = fileLocation + @"\" + mediaMeta.title + "." + mediaMeta.format;
FileStream writer = new FileStream(fileDir, FileMode.Create, FileAccess.Write);
byte[] bytes = new Byte[4096];
int bytesRead = 0;
while ((bytesRead = file.Read(bytes, 0, bytes.Length)) != 0)
{
writer.Write(bytes, 0, bytesRead);
}
file.Close();
writer.Close();
string fileStream = "http://rentit.itu.dk/RentIt26/MediaFiles/" + mediaMeta.id.ToString() + "/" + mediaMeta.title + "." + mediaMeta.format;
PreparedStatement stmt2 = db.Prepare("UPDATE media SET file_location = '"+fileStream+"' WHERE id = "+mediaMeta.id.ToString());
db.Command(null, stmt2);
}
public bool OpenConnection (string pincode, bool onlyCheckPincode = false)
{
try{
string database = FilesystemHelper.DatabaseFile;
var connection = new DatabaseConnection (database, pincode, _synclock);
if (connection != null) {
if (!onlyCheckPincode) {
_connection = connection;
_pincode = pincode;
}
return true;
}
return false;
}
catch(Exception ex){
Console.WriteLine (ex.Message);
return false;
}
}
public static void Main()
{
DatabaseConnection _connection = new DatabaseConnection();
_connection.Connect();
Thread gameThread = new Thread(
delegate()
{
GameProcessor(_connection);
}
);
gameThread.IsBackground = false;
gameThread.Start();
#if DEBUG
Server server = new Server(@"http://*****:*****@"http://192.168.0.105:54321/", _connection);
#endif
new System.Threading.Thread(server.Start).Start();
}
private static string GetSecretKey(string clientKey)
{
DatabaseConnection dbConnect = new DatabaseConnection("SMU");
string query = "SELECT * FROM secret_key WHERE clientKey=/'" + clientKey + "/'";
PreparedStatement prepStat = dbConnect.Prepare(query);
SqlDataReader data = dbConnect.Query(null, prepStat);
string secretKey;
if (data.Read())
{ secretKey = data.GetString(1); }
else
{
data.Close();
dbConnect.CloseConnection();
throw new Exception("No such clientKey exists");
}
data.Close();
dbConnect.CloseConnection();
return secretKey;
}
public MainForm()
{
InitializeComponent();
////////////////////////////////////////////////////////////////////////////////////////////
// Open File Data and Program Database
////////////////////////////////////////////////////////////////////////////////////////////
dat_conn = DatabaseConnection.getInstance();
/////////////////////////////////////////////////////////////////////////////////////////////
// MainTree and Table Tabpage
/////////////////////////////////////////////////////////////////////////////////////////////
//docking
_manager = new DockingManager(this, VisualStyle.IDE);
// Create Content which contains a RichTextBox
c = _manager.Contents.Add(new TreeForm(), "tree menu");
_manager.AddContentWithState(c, State.DockLeft);
////////////////////////////////////////////////////////////////////////////////////////////
// Main WorkSpace - Show main working window - display model graphics
////////////////////////////////////////////////////////////////////////////////////////////
// ShowModelGraphicWindow();
}
private void updateBtn_Click(object sender, EventArgs e)
{
DBOperations.query = "UPDATE attendance_manager.rooms SET `name` = @name WHERE `rooms`.`id` = @id; ";
DBOperations.cmd = new MySqlCommand(DBOperations.query, DatabaseConnection.getConnection());
DBOperations.cmd.Parameters.Clear();
DBOperations.cmd.Parameters.AddWithValue("id", this.id);
DBOperations.cmd.Parameters.AddWithValue("name", roomNameTextBox.Text.Trim());
DBOperations.Execute(DBOperations.cmd);
roomDGV.DataSource = DBOperations.Execute(DBOperations.cmd = new MySqlCommand(DBOperations.query = "SELECT * FROM attendance_manager.rooms", DatabaseConnection.getConnection()));
Reset();
}
private int MakeGanttBar(ref DigiGantt.CoordsMaps rects, ref DigiGantt dg, int j, ref ArrayList arsections, ref ArrayList arsections2, ref ArrayList arsections3, ref ArrayList arsections4, ref ArrayList arsections5, DataRow dr, int padLeft, DataSet sections)
{
string avquery = @"SELECT PROGRESS,WEIGHT FROM PROJECT_TIMING RIGHT OUTER JOIN PROJECT_SECTION_MEMBERS ON (PROJECT_TIMING.IDRIF=PROJECT_SECTION_MEMBERS.ID AND PROJECT_TIMING.RIFTYPE=1)
WHERE PROJECT_SECTION_MEMBERS.IDSECTIONRIF=" + dr["ID"].ToString();
ProjectCalculator pc = new ProjectCalculator();
int average = pc.GetSectionProgress(DatabaseConnection.CreateDataset(avquery).Tables[0]);
arsections.Add("{S" + padLeft.ToString().PadLeft(2, '0') + "}" + dr["TITLE"].ToString());
DateTime plannedstart = UC.LTZ.ToLocalTime((DateTime)dr["PLANNEDSTARTDATE"]);
object variationstart = DatabaseConnection.SqlScalartoObj(String.Format(@"select top 1 newstartdate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=0)
inner join PROJECT_SECTION on (PROJECT_TIMING.idrif = PROJECT_SECTION.id)
where PROJECT_SECTION.ID={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=0
order by newstartdate desc", dr["ID"].ToString()));
DateTime DrawStart = plannedstart;
if (variationstart != null && variationstart != System.DBNull.Value)
{
arsections2.Add(UC.LTZ.ToLocalTime(Convert.ToDateTime(variationstart)).ToString(@"dd/MM/yy"));
DrawStart = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationstart));
}
else
{
arsections2.Add(plannedstart.ToString(@"dd/MM/yy"));
}
DateTime plannedend = UC.LTZ.ToLocalTime((DateTime)dr["PLANNEDENDDATE"]);
object variationend = DatabaseConnection.SqlScalartoObj(String.Format(@"select top 1 newplanneddate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=1)
inner join PROJECT_SECTION_MEMBERS on PROJECT_TIMING.idrif=PROJECT_SECTION_MEMBERS.id
inner join PROJECT_SECTION on (PROJECT_SECTION_MEMBERS.IDSectionRif = PROJECT_SECTION.id)
where PROJECT_SECTION.ID={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=1
order by newplanneddate desc", dr["ID"].ToString()));
object variationend2 = DatabaseConnection.SqlScalartoObj(String.Format(@"select top 1 newplanneddate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=0)
inner join PROJECT_SECTION on (PROJECT_TIMING.idrif = PROJECT_SECTION.id)
where PROJECT_SECTION.ID={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=0
order by newplanneddate desc", dr["ID"].ToString()));
DateTime DrawEnd = plannedend;
if (variationend != null && variationend != System.DBNull.Value)
{
if ((variationend2 != null && variationend2 != System.DBNull.Value) && ((DateTime)variationend2) > ((DateTime)variationend))
{
arsections3.Add(UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend2)).ToString(@"dd/MM/yy"));
DrawEnd = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend2));
}
else
{
arsections3.Add(UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend)).ToString(@"dd/MM/yy"));
DrawEnd = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend));
}
}
else
if ((variationend2 != null && variationend2 != System.DBNull.Value))
{
arsections3.Add(UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend2)).ToString(@"dd/MM/yy"));
DrawEnd = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend2));
}
else
{
arsections3.Add(plannedend.ToString(@"dd/MM/yy"));
}
arsections4.Add(dr["PLANNEDMINUTEDURATION"].ToString());
arsections5.Add(dr["OWNERNAME"].ToString());
connectors.Add(dr["ID"].ToString() + '|' + j.ToString());
if (dr["COLOR"] != System.DBNull.Value)
{
rects.Add(dg.DrawSection(DrawStart, DrawEnd, Color.FromName(dr["COLOR"].ToString()), j++, average, Convert.ToInt32(dr["ID"])));
}
else
{
rects.Add(dg.DrawSection(DrawStart, DrawEnd, Color.Gold, j++, average, Convert.ToInt32(dr["ID"])));
}
if (DrawEnd != plannedend || DrawStart != plannedstart)
{
rects.Add(dg.DrawGhost(plannedstart, plannedend, (j - 1), Color.FromName(dr["COLOR"].ToString()), 0));
}
if (DrawEnd != plannedend && (plannedend > DrawStart && plannedend < DrawEnd))
{
rects.Add(dg.DrawExpected(plannedend, Color.LightGreen, (j - 1), 0, "Variazione fine"));
}
if (DrawStart != plannedstart && (plannedstart > DrawStart && plannedstart < DrawEnd))
{
rects.Add(dg.DrawExpected(plannedstart.AddDays(-1), Color.LightSkyBlue, (j - 1), 0, "Variazione inizio"));
}
DataSet dsevents = DatabaseConnection.CreateDataset("SELECT * FROM PROJECT_EVENTS WHERE SECTIONID=" + dr["ID"].ToString());
if (dsevents.Tables[0].Rows.Count > 0)
{
foreach (DataRow drevent in dsevents.Tables[0].Rows)
{
arsections.Add("{E" + padLeft.ToString().PadLeft(2, '0') + "}" + drevent["DESCRIPTION"].ToString());
arsections2.Add((UC.LTZ.ToLocalTime((DateTime)drevent["EVENTDATE"])).ToString(@"dd/MM/yy"));
arsections3.Add(string.Empty);
arsections4.Add(string.Empty);
arsections5.Add(string.Empty);
rects.Add(dg.DrawPoint(UC.LTZ.ToLocalTime((DateTime)drevent["EVENTDATE"]), Color.Orange, j++, Convert.ToInt32(dr["ID"]), drevent["DESCRIPTION"].ToString()));
}
}
SubSection(ref rects, ref dg, ref j, ref arsections, ref arsections2, ref arsections3, ref arsections4, ref arsections5, sections, ++padLeft, dr["ID"].ToString());
return(j);
}
public Task <string> GenerateCreatePackageDdlAsync(DatabaseConnection databaseConnection, string database, string schema, string packageName)
{
return(Task.Run(() => GenerateCreatePackageDdl(databaseConnection, database, schema, packageName)));
}
private void Repqbtable_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
switch (e.Item.ItemType)
{
case ListItemType.Item:
case ListItemType.AlternatingItem:
string[] ftype = ((Literal)e.Item.FindControl("FieldType")).Text.Split('|');
StringBuilder p = new StringBuilder();
string paramsStr = (Request.Form["Param_" + ftype[1]] != null) ? Request.Form["Param_" + ftype[1]] : DataBinder.Eval((DataRowView)e.Item.DataItem, "parameter").ToString();
Trace.Warn("ftype", ftype[2]);
switch (ftype[2])
{
case "0":
p.AppendFormat("<input type=\"text\" name=\"Param_{0}\" class=\"BoxDesign\" value=\"" + paramsStr + "\">", ftype[1]);
break;
case "1":
case "2":
DataSet dsDrop = DatabaseConnection.CreateDataset("SELECT * FROM QB_DROPDOWNPARAMS WHERE IDRIF=" + int.Parse(ftype[1]));
StringBuilder SBdrop = new StringBuilder();
StringBuilder SBdropparams = new StringBuilder();
SBdrop.AppendFormat("SELECT {0} AS KEYFIELD,{1} AS TEXTFIELD ", dsDrop.Tables[0].Rows[0]["ValueField"].ToString(), dsDrop.Tables[0].Rows[0]["TextField"].ToString());
SBdrop.AppendFormat("FROM {0} ", dsDrop.Tables[0].Rows[0]["RefTable"].ToString());
if (dsDrop.Tables[0].Rows[0]["LangField"].ToString().Length > 0)
{
SBdropparams.AppendFormat(" {0}='{1}' ", dsDrop.Tables[0].Rows[0]["LangField"].ToString(), UC.Culture.Substring(0, 2));
}
Trace.Warn("culture", (UC.CultureSpecific.Length > 0) ? UC.CultureSpecific : UC.Culture.Substring(0, 2));
if (dsDrop.Tables[0].Rows[0]["P1"].ToString().Length > 0)
{
if (dsDrop.Tables[0].Rows[0]["P1"].ToString().Length == 1)
{
SBdropparams.Append(" ");
}
else
{
SBdropparams.Append(" ");
}
}
if (dsDrop.Tables[0].Rows[0]["P2"].ToString().Length > 0)
{
SBdropparams.AppendFormat(" AND ({0}=0 ", dsDrop.Tables[0].Rows[0]["P2"].ToString());
SBdropparams.AppendFormat("OR ({0}=1 AND {1}={2}))", dsDrop.Tables[0].Rows[0]["P2"].ToString(), dsDrop.Tables[0].Rows[0]["P3"].ToString(), UC.UserId);
}
if (dsDrop.Tables[0].Rows[0]["P5"].ToString().Length > 0)
{
SBdropparams.AppendFormat(" AND ({0}) ", dsDrop.Tables[0].Rows[0]["P5"].ToString());
}
if (SBdropparams.ToString().Length > 0)
{
if (SBdropparams.ToString().Substring(0, 4) == " AND")
{
SBdrop.Append("WHERE " + SBdropparams.ToString().Substring(4, SBdropparams.ToString().Length - 4));
}
else
{
SBdrop.Append("WHERE " + SBdropparams.ToString());
}
}
dsDrop.Clear();
dsDrop = DatabaseConnection.CreateDataset(SBdrop.ToString());
p.AppendFormat("<select name=\"Param_{0}\" class=\"BoxDesign\">", ftype[1]);
foreach (DataRow d in dsDrop.Tables[0].Rows)
{
p.AppendFormat("<option value=\"{0}\" {2}>{1}</option>", d["keyfield"].ToString(), d["textfield"].ToString(), (paramsStr == d["keyfield"].ToString()) ? "selected" : "");
}
p.Append("</select>");
break;
case "3":
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param_{0}\" readOnly name=\"Param_{0}\" class=\"BoxDesign\" value=\"" + paramsStr + "\">", ftype[1]);
p.Append("</td><td width=\"30\">");
p.AppendFormat(" <img src=\"/i/SmallCalendar.gif\" border=\"0\" style=\"cursor:pointer\" onclick=\"FrameCreateBox('/Common/PopUpDate.aspx?textbox=Param_{0}',event,195,195)\">", ftype[1]);
p.Append("</td></tr></table>");
break;
case "4":
p.AppendFormat("<input type=\"checkbox\" name=\"Param_{0}\" value=\"true\" {1}>", ftype[1], (paramsStr == "true") ? "checked" : "");
break;
case "5":
string paramt = String.Empty;
if (Request.Form["Paramt_" + ftype[1]] != null)
{
paramt = Request.Form["Paramt_" + ftype[1]];
}
else
{
paramt = DatabaseConnection.SqlScalar("SELECT (NAME+' '+SURNAME) AS USERNAME FROM ACCOUNT WHERE UID=" + paramsStr);
}
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param_{0}\" name=\"Param_{0}\" style=\"display:none\" value=\"" + paramsStr + "\"><input type=\"text\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" class=\"BoxDesign\" value=\"" + paramt + "\">", ftype[1]);
p.Append("</td><td width=\"30\">");
p.AppendFormat(" <img src=\"/i/user.gif\" border=\"0\" style=\"cursor:pointer\" onclick=\"FrameCreateBox('/Common/PopAccount.aspx?render=no&textbox=Paramt_{0}&textbox2=Param_{0}',event)\">", ftype[1]);
p.Append("</td></tr></table>");
break;
case "8":
string range1 = String.Empty;
string range2 = String.Empty;
if (Request.Form["Param_" + ftype[1]] != null)
{
range1 = Request.Form["Param_" + ftype[1]];
range2 = Request.Form["Param2_" + ftype[1]];
}
else
{
if (paramsStr.IndexOf("|") > 0)
{
range1 = paramsStr.Split('|')[0];
range2 = paramsStr.Split('|')[1];
}
}
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param_{0}\" readOnly name=\"Param_{0}\" class=\"BoxDesign\" value=\"" + range1 + "\">", ftype[1]);
p.Append("</td><td width=\"30\">");
p.AppendFormat(" <img src=\"/i/SmallCalendar.gif\" border=\"0\" style=\"cursor:pointer\" onclick=\"FrameCreateBox('/Common/PopUpDate.aspx?textbox=Param_{0}',event,195,195)\">", ftype[1]);
p.Append("</td></tr>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param2_{0}\" readOnly name=\"Param2_{0}\" class=\"BoxDesign\" value=\"" + range2 + "\">", ftype[1]);
p.Append("</td><td width=\"30\">");
p.AppendFormat(" <img src=\"/i/SmallCalendar.gif\" border=\"0\" style=\"cursor:pointer\" onclick=\"FrameCreateBox('/Common/PopUpDate.aspx?textbox=Param2_{0}',event,195,195)\">", ftype[1]);
p.Append("</td></tr></table>");
break;
case "9": // pop aziende
paramt = String.Empty;
if (Request.Form["Paramt_" + ftype[1]] != null)
{
paramt = Request.Form["Paramt_" + ftype[1]];
}
else
{
paramt = DatabaseConnection.SqlScalar("SELECT COMPANYNAME FROM BASE_COMPANIES WHERE ID=" + paramsStr);
}
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param_{0}\" name=\"Param_{0}\" style=\"display:none\" value=\"" + paramsStr + "\"><input type=\"text\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" class=\"BoxDesign\" value=\"" + paramt + "\">", ftype[1]);
p.Append("</td><td width=\"30\">");
p.AppendFormat(" <img src=\"/i/user.gif\" border=\"0\" style=\"cursor:pointer\" onclick=\"FrameCreateBox('/Common/PopCompany.aspx?render=no&textbox=Paramt_{0}&textbox2=Param_{0}',event,500,400)\">", ftype[1]);
p.Append("</td></tr></table>");
break;
case "10": // pop contatti
paramt = String.Empty;
if (Request.Form["Paramt_" + ftype[1]] != null)
{
paramt = Request.Form["Paramt_" + ftype[1]];
}
else
{
paramt = DatabaseConnection.SqlScalar("SELECT (ISNULL(SURNAME,'')+' '+ISNULL(NAME,'')) AS CONTACT FROM BASE_CONTACTS WHERE ID=" + paramsStr);
}
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param_{0}\" name=\"Param_{0}\" style=\"display:none\" value=\"" + paramsStr + "\"><input type=\"text\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" class=\"BoxDesign\" value=\"" + paramt + "\">", ftype[1]);
p.Append("</td><td width=\"30\">");
p.AppendFormat(" <img src=\"/i/user.gif\" border=\"0\" style=\"cursor:pointer\" onclick=\"FrameCreateBox('/Common/popcontacts.aspx?render=no&textbox=Paramt_{0}&textboxID=Param_{0}',event,400,300)\">", ftype[1]);
p.Append("</td></tr></table>");
break;
case "11": // pop opportunit
paramt = String.Empty;
if (Request.Form["Paramt_" + ftype[1]] != null)
{
paramt = Request.Form["Paramt_" + ftype[1]];
}
else
{
paramt = DatabaseConnection.SqlScalar("SELECT TITLE FROM CRM_OPPORTUNITY WHERE ID=" + paramsStr);
}
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param_{0}\" name=\"Param_{0}\" style=\"display:none\" value=\"" + paramsStr + "\"><input type=\"text\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" class=\"BoxDesign\" value=\"" + paramt + "\">", ftype[1]);
p.Append("</td><td width=\"30\">");
p.AppendFormat(" <img src=\"/i/user.gif\" border=\"0\" style=\"cursor:pointer\" onclick=\"FrameCreateBox('/Common/PopOpportunity.aspx?render=no&textbox=Paramt_{0}&textboxID=Param_{0}',event,400,300)\">", ftype[1]);
p.Append("</td></tr></table>");
break;
case "12":
paramt = String.Empty;
if (Request.Form["Paramt_" + ftype[1]] != null)
{
paramt = Request.Form["Paramt_" + ftype[1]];
}
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr><td>");
p.AppendFormat("<input type=\"text\" id=\"Param_{0}\" name=\"Param_{0}\" class=\"BoxDesign\" value=\"" + Request.Form["Param_" + ftype[1]] + "\">", ftype[1]);
p.Append("</td><td width=\"50%\" class=\"normal\">");
p.AppendFormat("<input type=\"radio\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" value=\"0\" {1}><", ftype[1], (paramt == "0") ? "checked" : "");
p.AppendFormat("<input type=\"radio\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" value=\"1\" {1}>>", ftype[1], (paramt == "1") ? "checked" : "");
p.Append("</td></tr></table>");
break;
case "13":
DataTable dtDrop = DatabaseConnection.CreateDataset("SELECT * FROM QB_FIXEDDROPDOWNPARAMS WHERE IDRIF=" + ftype[1]).Tables[0];
p.AppendFormat("<select name=\"Param_{0}\" class=\"BoxDesign\">", ftype[1]);
foreach (DataRow d in dtDrop.Rows)
{
p.AppendFormat("<option value=\"{0}\">{1}</option>", d["dropvalue"].ToString(), Root.rm.GetString("QBTxt" + d["rmvalue"].ToString()));
}
p.Append("</select>");
break;
case "14": // radio attivit fatte/da fare/annullate
paramt = String.Empty;
if (Request.Form["Paramt_" + ftype[1]] != null)
{
paramt = Request.Form["Paramt_" + ftype[1]];
}
if ((paramt != null && paramt.Length == 0))
{
paramt = "1";
}
p.Append("<table width=\"100%\" cellspacing=0 cellpadding=0>");
p.Append("<tr>");
p.Append("<td width=\"50%\" class=\"normal\">");
p.AppendFormat("<input type=\"radio\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" value=\"1\" {1}>{2}", ftype[1], (paramt == "1") ? "checked" : "", Root.rm.GetString("Acttxt71"));
p.AppendFormat("<input type=\"radio\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" value=\"0\" {1}>{2}", ftype[1], (paramt == "0") ? "checked" : "", Root.rm.GetString("Acttxt72"));
p.AppendFormat("<input type=\"radio\" id=\"Paramt_{0}\" name=\"Paramt_{0}\" value=\"2\" {1}>{2}", ftype[1], (paramt == "2") ? "checked" : "", Root.rm.GetString("Acttxt103"));
p.Append("</td></tr></table>");
break;
case "-4": // dropdown da free fields
string[] dsDropFree = DatabaseConnection.SqlScalar("SELECT ITEMS FROM ADDEDFIELDS WHERE ID=" + DatabaseConnection.FilterInjection(ftype[1])).Split('|');
p.AppendFormat("<select name=\"Param_-{0}\" class=\"BoxDesign\">", ftype[1]);
foreach (string d in dsDropFree)
{
if (d.Length > 0)
{
p.AppendFormat("<option value=\"{0}\" {1}>{0}</option>", d, (paramsStr == d) ? "selected" : "");
}
}
p.Append("</select>");
break;
case "-1": // Text da free fields
case "-2":
p.AppendFormat("<input type=\"text\" name=\"Param_{0}\" class=\"BoxDesign\" value=\"" + paramsStr + "\">", ftype[1]);
break;
}
((Label)e.Item.FindControl("LblParameter")).Text = p.ToString();
break;
}
}
public Task <string> GenerateCreateViewDdlAsync(DatabaseConnection databaseConnection, string database, string schema,
string tableName)
{
return(Task.Run(() => GenerateCreateViewDdl(databaseConnection, database, schema, tableName)));
//return Task.Run(() => GenerateCreateTableDdl(databaseConnection, database, schema, tableName));
}
private bool FillDataTable(int id)
{
DataTable dt = CreateNewDataTable();
DataSet mainDs = DatabaseConnection.CreateDataset("SELECT * FROM QB_CUSTOMERQUERY WHERE ID=" + id);
if (mainDs.Tables[0].Rows.Count > 0)
{
DataRow mainDr = mainDs.Tables[0].Rows[0];
QBQueryName.Text = mainDr["Title"].ToString();
QBQueryDescription.Text = mainDr["Description"].ToString();
QBQueryCategory.SelectedIndex = -1;
foreach (ListItem li in QBQueryCategory.Items)
{
if (li.Value == mainDr["Category"].ToString())
{
li.Selected = true;
break;
}
}
string queryField = "SELECT QB_CUSTOMERQUERYFIELDS.*, QB_ALL_FIELDS.RMVALUE AS RMVALUE, " +
"QB_ALL_FIELDS.FIELD AS FIELD,QB_ALL_FIELDS.FIELDTYPE AS FIELDTYPE " +
"FROM QB_CUSTOMERQUERYFIELDS " +
"INNER JOIN QB_ALL_FIELDS ON QB_CUSTOMERQUERYFIELDS.IDFIELD = QB_ALL_FIELDS.ID " +
"WHERE IDQUERY=" + id;
DataTable fieldDt = DatabaseConnection.CreateDataset(queryField).Tables[0];
foreach (DataRow d in fieldDt.Rows)
{
if ((int)d["IDTable"] > 0)
{
DataRow dr = dt.NewRow();
string param = String.Empty;
dr["trid"] = d["Options"].ToString().Split(',')[0];
dr["Label"] = Root.rm.GetString("QBTxt" + d["rmValue"].ToString());
dr["Options"] = d["Options"].ToString();
param = DatabaseConnection.SqlScalar("SELECT FIXEDVALUE FROM QB_CUSTOMERQUERYPARAMFIELDS WHERE IDQUERY=" + id + " AND IDFIELD=" + d["idfield"].ToString());
dr["parameter"] = param;
dr["order"] = d["Options"].ToString().Split(',')[0];
dr["fieldtype"] = d["IDTable"].ToString() + "|" + d["IDField"].ToString() + "|" + d["fieldType"].ToString();
dr["fieldlabel"] = d["ColumnName"].ToString();
dt.Rows.Add(dr);
}
else
{
DataRow freef = DatabaseConnection.CreateDataset("SELECT * FROM ADDEDFIELDS WHERE ID=" + d["IDField"]).Tables[0].Rows[0];
DataRow dr = dt.NewRow();
string param = String.Empty;
dr["trid"] = d["Options"].ToString().Split(',')[0];
dr["Label"] = freef["name"].ToString();
dr["Options"] = d["Options"].ToString();
param = DatabaseConnection.SqlScalar("SELECT FIXEDVALUE FROM QB_CUSTOMERQUERYPARAMFIELDS WHERE IDQUERY=" + id + " AND IDFIELD=" + d["idfield"].ToString());
dr["parameter"] = param;
dr["order"] = d["Options"].ToString().Split(',')[0];
dr["fieldtype"] = d["IDTable"].ToString() + "|" + d["IDField"].ToString() + "|-" + freef["Type"].ToString();
dr["fieldlabel"] = freef["name"].ToString();
dt.Rows.Add(dr);
}
}
Session["qbtable"] = dt;
StartScript(dt);
return(true);
}
else
{
return(false);
}
}
public abstract string GenerateCreateIndexDdl(DatabaseConnection databaseConnection, string database, string indexSchema, string indexName, object indexId);
public static Task <IPrivateMessage> GetPrivateMessageAsync(uint id)
{
return(DatabaseConnection.NewAsyncConnection((dbConnection) => dbConnection.ReadDataAsync($"SELECT COUNT(p.id) OVER() AS results, p.id, p.to_user_id, p.from_user_id, COALESCE(u.id, 0) AS from_user_id, COALESCE(u.username, 'Unknown') AS from_username, COALESCE(u.name_color, -14855017) AS from_name_color, p.type, p.sent_time, t.title, t.message, b.block_id, b.title AS block_title, l.level_id, l.title AS level_title FROM base.pms p LEFT JOIN base.users u ON u.id = p.from_user_id LEFT JOIN base.pms_text t ON t.id = p.id LEFT JOIN base.transfers_block b ON p.id = b.id LEFT JOIN base.transfers_level l ON p.id = l.id WHERE p.id = {id} LIMIT 1").ContinueWith(PrivateMessageManager.ParseSqlPm)));
}
public static Task <(uint Results, IReadOnlyList <IPrivateMessage> PMs)> GetUserPMsAsync(uint userId, uint start = 0, uint count = uint.MaxValue)
{
return(DatabaseConnection.NewAsyncConnection((dbConnection) => dbConnection.ReadDataAsync($"SELECT COUNT(p.id) OVER() AS results, p.id, p.to_user_id, p.from_user_id, COALESCE(u.id, 0) AS from_user_id, COALESCE(u.username, 'Unknown') AS from_username, COALESCE(u.name_color, -14855017) AS from_name_color, p.type, p.sent_time, t.title, t.message, b.block_id, b.title AS block_title, l.level_id, l.title AS level_title FROM base.pms p LEFT JOIN base.users u ON u.id = p.from_user_id LEFT JOIN base.pms_text t ON t.id = p.id LEFT JOIN base.transfers_block b ON p.id = b.id LEFT JOIN base.transfers_level l ON p.id = l.id WHERE p.to_user_id = {userId} ORDER BY p.sent_time DESC OFFSET {start} LIMIT {count}").ContinueWith(PrivateMessageManager.ParseSqlMultiplePMs)));
}
public Regimen ObtenerRegimenAllInclusiveModerado()
{
return(new Regimen(Convert.ToInt32(DatabaseConnection.GetInstance().
ExecuteProcedureScalar("OBTENER_REGIMEN_ALL_INCLUSIVE_MODERADO"))));
}
/// <summary>
/// Admin login method
/// </summary>
/// <param name="adminLoginShowModel"></param>
/// <returns></returns>
public LoginResponseModel AdminLogin(LoginShowModel adminLoginShowModel)
{
try
{
var password = PasswordEncrypt.Encryptdata(adminLoginShowModel.Password);
DatabaseConnection databaseConnection = new DatabaseConnection(this.configuration);
SqlConnection sqlConnection = databaseConnection.GetConnection();
SqlCommand sqlCommand = databaseConnection.GetCommand("AdminLogin", sqlConnection);
sqlCommand.Parameters.AddWithValue("@Email", adminLoginShowModel.Email);
sqlCommand.Parameters.AddWithValue("@Password", password);
sqlConnection.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
var userData = new RegisterModel();
while (sqlDataReader.Read())
{
userData = new RegisterModel();
userData.Id = (int)sqlDataReader["Id"];
userData.FirstName = sqlDataReader["FirstName"].ToString();
userData.LastName = sqlDataReader["LastName"].ToString();
userData.Email = sqlDataReader["Email"].ToString();
userData.Password = sqlDataReader["Password"].ToString();
userData.IsActive = Convert.ToBoolean(sqlDataReader["IsActive"]);
userData.UserRole = sqlDataReader["UserRole"].ToString();
userData.CreatedDate = Convert.ToDateTime(sqlDataReader["CreatedDate"]);
userData.ModifiedDate = Convert.ToDateTime(sqlDataReader["ModifiedDate"]);
}
if (userData.Email != null)
{
/* if (password.Equals(userData.Password))
* {
* ////Here generate encrypted key and result store in security key
* var securityKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(configuration["Token:token"]));
*
* //// here using securitykey and algorithm(security) the credentials is generate(SigningCredentials present in Token)
* var creadintials = new SigningCredentials(securityKey, SecurityAlgorithms.HmacSha256);
* var claims = new[] {
* new Claim ("Id", userData.Id.ToString()),
* new Claim("Email", userData.Email.ToString()),
* new Claim("UserRole", userData.UserRole.ToString())
* };
*
* var token = new JwtSecurityToken("Security token", "https://Test.com",
* claims,
* DateTime.UtcNow,
* expires: DateTime.Now.AddDays(1),
* signingCredentials: creadintials);
* var returnToken = new JwtSecurityTokenHandler().WriteToken(token);*/
var responseShow = new LoginResponseModel()
{
Id = userData.Id,
FirstName = userData.FirstName,
LastName = userData.LastName,
Email = userData.Email,
IsActive = userData.IsActive,
UserRole = userData.UserRole,
CreatedDate = userData.CreatedDate,
ModifiedDate = userData.ModifiedDate,
};
return(responseShow);
}
else
{
return(null);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
public Task <string> GenerateDropIndexDdlAsync(DatabaseConnection databaseConnection, string database, string schema,
string tableName)
{
return(Task.Run(() => GenerateDropIndexDdl(databaseConnection, database, schema, tableName)));
}
public Task <string> GenerateCreateIndexDdlAsync(DatabaseConnection databaseConnection, string database, string schema,
string tableName, object indexId)
{
return(Task.Run(() => GenerateCreateIndexDdl(databaseConnection, database, schema, tableName, indexId)));
}
private void insertBtn_Click(object sender, EventArgs e)
{
DBOperations.query = "INSERT INTO attendance_manager.rooms (`name`) VALUES (@name);";
DBOperations.cmd = new MySqlCommand(DBOperations.query, DatabaseConnection.getConnection());
DBOperations.cmd.Parameters.Clear();
DBOperations.cmd.Parameters.AddWithValue("name", roomNameTextBox.Text.Trim());
DBOperations.Execute(DBOperations.cmd);
roomDGV.DataSource = DBOperations.Execute(DBOperations.cmd = new MySqlCommand(DBOperations.query = "SELECT * FROM attendance_manager.rooms", DatabaseConnection.getConnection()));
Reset();
}
private void deleteBtn_Click(object sender, EventArgs e)
{
DBOperations.query = "DELETE FROM attendance_manager.rooms WHERE `rooms`.`id` = @id ";
DBOperations.cmd = new MySqlCommand(DBOperations.query, DatabaseConnection.getConnection());
DBOperations.cmd.Parameters.Clear();
DBOperations.cmd.Parameters.AddWithValue("id", this.id);
DBOperations.Execute(DBOperations.cmd);
roomDGV.DataSource = DBOperations.Execute(DBOperations.cmd = new MySqlCommand(DBOperations.query = "SELECT * FROM attendance_manager.rooms", DatabaseConnection.getConnection()));
Reset();
}
public virtual string GenerateDropViewDdl(DatabaseConnection databaseConnection, string database, string schema,
string tableName)
{
return("DROP VIEW " + GetFullyQualifiedName(database, schema, tableName) +
databaseConnection.DatabaseServer.SqlTerminators.First());
}
public abstract string GenerateCreateTableDdl(DatabaseConnection databaseConnection, string database, string schema, string tableName);
public abstract string GenerateCreateViewFullDdl(DatabaseConnection databaseConnection, string database, string schema, string viewName);
private void DashPortAddress(string type)
{
string companyLead = RadioButtonList1.SelectedValue;
Trace.Warn("companylead", companyLead);
StringBuilder dashQuery = new StringBuilder();
ArrayList ar = new ArrayList();
ArrayList arlegend = new ArrayList();
DataTable dt;
if (companyLead == "0" || companyLead == "2")
{
string opp = getOpportunity();
if (opp.Length > 0)
{
opp = " AND (" + opp + ")";
}
dashQuery.AppendFormat("SELECT DISTINCT {0},COUNT({0}) AS NTIMES FROM DASH1_COMPANYINDUSTRY_VIEW WHERE {1} GROUP BY {0}", type, opp);
dt = DatabaseConnection.CreateDataset(dashQuery.ToString()).Tables[0];
foreach (DataRow dr in dt.Rows)
{
Trace.Warn("C " + dr[type].ToString().ToUpper(), dr["ntimes"].ToString());
AddressPercent newsales = new AddressPercent();
newsales.address = dr[type].ToString().ToUpper();
newsales.ntimes = Convert.ToInt32(dr["ntimes"]);
ar.Add(newsales);
}
}
if (companyLead == "0" || companyLead == "1")
{
dashQuery.Length = 0;
string opp = getOpportunity();
if (opp.Length > 0)
{
opp = " AND (" + opp + ")";
}
dashQuery.AppendFormat("SELECT DISTINCT {0},COUNT({0}) AS NTIMES FROM DASH1_LEADINDUSTRY_VIEW WHERE {1} GROUP BY {0}", type, opp);
dt = DatabaseConnection.CreateDataset(dashQuery.ToString()).Tables[0];
foreach (DataRow dr in dt.Rows)
{
Trace.Warn("L " + dr[type].ToString().ToUpper(), dr["ntimes"].ToString());
bool exists = false;
for (int i = 0; i < ar.Count; i++)
{
AddressPercent temp = (AddressPercent)ar[i];
if (temp.address == dr[type].ToString().ToUpper())
{
temp.ntimes += Convert.ToInt32(dr["ntimes"]);
ar[i] = temp;
exists = true;
break;
}
}
if (!exists)
{
AddressPercent newsales = new AddressPercent();
newsales.address = dr[type].ToString().ToUpper();
newsales.ntimes = Convert.ToInt32(dr["ntimes"]);
ar.Add(newsales);
}
}
}
int total = 0;
double percTotal = 0;
string res = String.Empty;
for (int i = 0; i < ar.Count; i++)
{
AddressPercent temp = (AddressPercent)ar[i];
total += temp.ntimes;
}
for (int i = 0; i < ar.Count; i++)
{
AddressPercent temp = (AddressPercent)ar[i];
string nationName = (temp.address.Length == 0) ?Root.rm.GetString("Das1txt14") : temp.address;
double percent = Math.Round(Convert.ToDouble(temp.ntimes) * 100 / Convert.ToDouble(total), 3);
percTotal += percent;
Legenda leg = new Legenda();
leg.title = nationName;
leg.value = temp.ntimes;
leg.percent = percent;
arlegend.Add(leg);
res += nationName + "|" + temp.ntimes + "|";
}
string title = String.Empty;
switch (type)
{
case "Nation":
title = Root.rm.GetString("Das1txt19");
break;
case "Province":
title = Root.rm.GetString("Das1txt20");
break;
case "City":
title = Root.rm.GetString("Das1txt21");
break;
}
try
{
piechart(title, res.Substring(0, res.Length - 1), arlegend);
}
catch
{
graphTitle.Text = Root.rm.GetString("Das1txt38");
}
}
private void SaveCustomerQuery(DataTable dt)
{
string oldGroup = String.Empty;
if (QBQueryID.Text != "-1")
{
int queryId = int.Parse(QBQueryID.Text);
oldGroup = DatabaseConnection.SqlScalar("SELECT GROUPS FROM QB_CUSTOMERQUERY WHERE ID=" + queryId);
DatabaseConnection.DoCommand("DELETE FROM QB_CUSTOMERQUERY WHERE ID=" + queryId);
}
object newId;
long NewCategory = 0;
if (HiddenNewCategory.Text.Length > 0)
{
using (DigiDapter dgcat = new DigiDapter())
{
dgcat.Add("Description", HiddenNewCategory.Text);
object NewCatID = dgcat.Execute("QB_CATEGORIES", "ID=-1", DigiDapter.Identities.Identity);
NewCategory = Convert.ToInt64(NewCatID);
this.FillQBQueryCategory();
}
}
else
{
try
{
NewCategory = Convert.ToInt64(QBQueryCategory.Value);
}
catch
{
NewCategory = 0;
}
}
using (DigiDapter dg = new DigiDapter())
{
dg.Add("TITLE", QBQueryName.Text);
dg.Add("DESCRIPTION", QBQueryDescription.Text);
dg.Add("CATEGORY", NewCategory);
dg.Add("CREATEDBYID", UC.UserId);
if (oldGroup.Length > 0)
{
dg.Add("GROUPS", oldGroup);
}
else
{
dg.Add("GROUPS", "|" + UC.UserGroupId + "|");
}
string grby = string.Empty;
foreach (DataRow dr in dt.Rows)
{
if (dr["Options"].ToString().Split(',')[2] == "1")
{
grby += dr["fieldtype"].ToString();
}
}
dg.Add("GROUPBY", grby);
newId = dg.Execute("QB_CUSTOMERQUERY", "ID=-1", DigiDapter.Identities.Identity);
}
ArrayList tt = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
if (dr["Options"].ToString().Split(',')[3] == "1")
{
tt.Add(dr["fieldtype"].ToString().Split('|')[0]);
break;
}
}
foreach (DataRow dr in dt.Rows)
{
bool exists = false;
string[] t = dr["fieldtype"].ToString().Split('|');
foreach (string x in tt)
{
if (x == t[0])
{
exists = true;
break;
}
}
if (!exists)
{
tt.Add(t[0]);
}
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO QB_CUSTOMERQUERYTABLES (IDQUERY,IDTABLE,MAINTABLE) VALUES ({0},{1},{2});", Convert.ToInt64(newId.ToString()), Convert.ToInt32(tt[0]), 1);
for (int i = 1; i < tt.Count; i++)
{
sb.AppendFormat("INSERT INTO QB_CUSTOMERQUERYTABLES (IDQUERY,IDTABLE) VALUES ({0},{1});", Convert.ToInt64(newId.ToString()), Convert.ToInt32(tt[i]));
}
DatabaseConnection.DoCommand(sb.ToString());
DataRow[] drsort = dt.Select("", "trid");
for (int i = 0; i < drsort.Length; i++)
{
sb = new StringBuilder();
string[] t = drsort[i]["fieldtype"].ToString().Split('|');
sb.Append("INSERT INTO QB_CUSTOMERQUERYFIELDS (IDQUERY,IDTABLE,IDFIELD,COLUMNNAME,FIELDVISIBLE,OPTIONS) VALUES ");
sb.AppendFormat("({0},", Convert.ToInt64(newId.ToString()));
sb.AppendFormat("{0},", Convert.ToInt32(t[0]));
sb.AppendFormat("{0},", Convert.ToInt32(t[1]));
sb.AppendFormat("'{0}',", drsort[i]["fieldlabel"].ToString());
sb.AppendFormat("{0},", (drsort[i]["Options"].ToString().Split(',')[1] == "1") ? 1 : 0);
sb.AppendFormat("'{0}')", drsort[i]["Options"].ToString());
Trace.Warn("sb", sb.ToString());
DatabaseConnection.DoCommand(sb.ToString());
}
for (int i = 0; i < drsort.Length; i++)
{
string[] t = drsort[i]["fieldtype"].ToString().Split('|');
if (drsort[i]["Options"].ToString().Split(',')[4] == "1")
{
sb = new StringBuilder();
sb.Append("INSERT INTO QB_CUSTOMERQUERYPARAMFIELDS (IDQUERY,IDTABLE,IDFIELD,FIXEDVALUE) VALUES ");
sb.AppendFormat("({0},", Convert.ToInt64(newId.ToString()));
sb.AppendFormat("{0},", Convert.ToInt32(t[0]));
sb.AppendFormat("{0},", Convert.ToInt32(t[1]));
if (Request.Form["Param_" + t[1]] != null && Request.Form["Param_" + t[1]].Length > 0)
{
switch (t[2])
{
case "8":
sb.AppendFormat("'{0}')", Request.Form["Param_" + t[1]] + "|" + Request.Form["Param2_" + t[1]]);
break;
case "12":
sb.AppendFormat("'{0}')", Request.Form["Param_" + t[1]] + "|" + Request.Form["Paramt_" + t[1]]);
break;
default:
sb.AppendFormat("'{0}')", Request.Form["Param_" + t[1]]);
break;
}
}
else
{
sb.Append("'')");
}
Trace.Warn("sb", sb.ToString());
DatabaseConnection.DoCommand(sb.ToString());
}
}
QBSearchStep1.Visible = false;
Repqbtable.Visible = false;
QBSaveInfo.Text = String.Format(Root.rm.GetString("QBUtxt18"), QBQueryName.Text);
QBSaveInfo.Visible = true;
SaveReport.Visible = false;
Session["ReportToOpen"] = newId.ToString();
ClientScript.RegisterStartupScript(this.GetType(), "OpenScript", "<script>parent.location='/CRM/QBDefault.aspx?m=55&dgb=1&si=42'</script>");
}
private void DashPortIndustry()
{
string companyLead = RadioButtonList1.SelectedValue;
StringBuilder dashQuery = new StringBuilder();
ArrayList ar = new ArrayList();
ArrayList arlegend = new ArrayList();
string finalQuery = String.Empty;
DataTable dt;
if (companyLead == "0" || companyLead == "2")
{
dashQuery.AppendFormat("SELECT * FROM DASH1_COMPANYINDUSTRY_VIEW");
string opp = getOpportunity();
if (opp.Length > 0)
{
opp = " AND (" + opp + ")";
}
finalQuery = dashQuery.ToString() + opp + " ORDER BY COMPANYTYPEID";
dt = DatabaseConnection.CreateDataset(finalQuery).Tables[0];
foreach (DataRow dr in dt.Rows)
{
bool exists = false;
for (int i = 0; i < ar.Count; i++)
{
IndustryPercent temp = (IndustryPercent)ar[i];
if (temp.industryid == Convert.ToInt32(dr["CompanyTypeID"]))
{
temp.ntimes = temp.ntimes + 1;
ar[i] = temp;
exists = true;
break;
}
}
if (!exists)
{
IndustryPercent newindustry = new IndustryPercent();
newindustry.industryid = Convert.ToInt32(dr["CompanyTypeID"]);
newindustry.industryname = dr["CompanyIndustry"].ToString();
newindustry.ntimes = 1;
ar.Add(newindustry);
}
}
}
if (companyLead == "0" || companyLead == "1")
{
dashQuery.Length = 0;
dashQuery.AppendFormat("SELECT * FROM DASH1_LEADINDUSTRY_VIEW");
string opp = getOpportunity();
if (opp.Length > 0)
{
opp = " AND (" + opp + ")";
}
finalQuery = dashQuery.ToString() + opp + " ORDER BY INDUSTRY";
dt = DatabaseConnection.CreateDataset(finalQuery).Tables[0];
foreach (DataRow dr in dt.Rows)
{
bool exists = false;
for (int i = 0; i < ar.Count; i++)
{
IndustryPercent temp = (IndustryPercent)ar[i];
if (temp.industryid == Convert.ToInt32(dr["Industry"]))
{
temp.ntimes = temp.ntimes + 1;
ar[i] = temp;
exists = true;
break;
}
}
if (!exists)
{
IndustryPercent newindustry = new IndustryPercent();
newindustry.industryid = Convert.ToInt32(dr["Industry"]);
newindustry.industryname = dr["LeadIndustry"].ToString();
newindustry.ntimes = 1;
ar.Add(newindustry);
}
}
}
int total = 0;
double percTotal = 0;
string res = String.Empty;
for (int i = 0; i < ar.Count; i++)
{
IndustryPercent temp = (IndustryPercent)ar[i];
total += temp.ntimes;
}
for (int i = 0; i < ar.Count; i++)
{
IndustryPercent temp = (IndustryPercent)ar[i];
string indName = (temp.industryid == 0) ?Root.rm.GetString("Das1txt14") : temp.industryname;
double percent = Math.Round(Convert.ToDouble(temp.ntimes) * 100 / Convert.ToDouble(total), 0);
percTotal += percent;
Legenda leg = new Legenda();
leg.title = indName;
leg.value = temp.ntimes;
leg.percent = percent;
arlegend.Add(leg);
res += indName + "|" + temp.ntimes + "|";
}
try
{
piechart(Root.rm.GetString("Das1txt17"), res.Substring(0, res.Length - 1), arlegend);
}
catch
{
graphTitle.Text = Root.rm.GetString("Das1txt38");
}
}
private void FillDatatable()
{
DataTable dt = new DataTable();
if (Session["qbtable"] != null)
{
dt = (DataTable)Session["qbtable"];
foreach (RepeaterItem it in Repqbtable.Items)
{
if (it.ItemType == ListItemType.Item || it.ItemType == ListItemType.AlternatingItem)
{
DataRow[] dreorder = dt.Select("trid = " + ((Literal)it.FindControl("LabelID")).Text);
dreorder[0]["Options"] = Request.Form["opt" + ((Literal)it.FindControl("LabelID")).Text];
dreorder[0]["fieldlabel"] = Request.Form["afl" + ((Literal)it.FindControl("LabelID")).Text];
}
}
foreach (DataRow dr in dt.Rows)
{
dr["trid"] = dr["Options"].ToString().Split(',')[0];
}
}
else
{
dt = CreateNewDataTable();
}
if (Request.Params["AddNewRow"] != null)
{
string addNewRowParam = DatabaseConnection.FilterInjection(Request.Params["AddNewRow"]);
bool exists = false;
QBSaveInfo.Visible = false;
foreach (DataRow dr_esiste in dt.Rows)
{
if (dr_esiste["fieldtype"].ToString() == addNewRowParam)
{
exists = true;
break;
}
}
if (!exists)
{
string[] newfield = addNewRowParam.Split('|');
if (newfield[0].Substring(0, 1) != "-")
{
Trace.Warn("fieldquery>>>", "SELECT ID,FIELDTYPE,FIELD,RMVALUE,FLAGPARAM FROM QB_ALL_FIELDS WHERE ID=" + newfield[1]);
DataRow dtt = DatabaseConnection.CreateDataset("SELECT ID,FIELDTYPE,FIELD,RMVALUE,FLAGPARAM FROM QB_ALL_FIELDS WHERE ID=" + newfield[1]).Tables[0].Rows[0];
DataRow[] dfilter = dt.Select("", "TRID DESC");
int trid = 1;
if (dfilter.Length > 0)
{
trid = Convert.ToInt32(dfilter[0]["trid"]) + 1;
}
DataRow d = dt.NewRow();
d[0] = d[4] = trid;
d[1] = Root.rm.GetString("QBTxt" + dtt["rmVAlue"].ToString());
d[2] = trid.ToString() + ",1,0,0,0";
d[3] = String.Empty;
d[5] = addNewRowParam;
dt.Rows.Add(d);
}
else
{
int trid = 1;
DataRow[] dfilter = dt.Select("", "trid DESC");
if (dfilter.Length > 0)
{
trid = Convert.ToInt32(dfilter[0]["trid"]) + 1;
}
DataRow dtt;
dtt = DatabaseConnection.CreateDataset("SELECT * FROM ADDEDFIELDS WHERE TABLENAME=" + newfield[0].Substring(1, newfield[0].Length - 1) + " AND ID=" + newfield[1]).Tables[0].Rows[0];
DataRow d = dt.NewRow();
d[0] = d[4] = trid;
d[1] = dtt["Name"].ToString();
d[2] = trid.ToString() + ",1,0,0,0";
d[3] = String.Empty;
d[5] = addNewRowParam;
dt.Rows.Add(d);
}
}
}
ArrayList tt = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
string[] t = dr["fieldtype"].ToString().Split('|');
if (t[2] == "7")
{
string[] aggfields = DatabaseConnection.SqlScalar("SELECT AGGREGATEFIELDS FROM QB_ALL_FIELDS WHERE ID=" + t[1]).Split(',');
string aggQuery = String.Empty;
foreach (string af in aggfields)
{
aggQuery += "ID=" + af + " OR ";
}
DataTable aggDt = DatabaseConnection.CreateDataset("SELECT ID,FIELDTYPE,FIELD,RMVALUE,FLAGPARAM,TABLEID FROM QB_ALL_FIELDS WHERE " + aggQuery.Substring(0, aggQuery.Length - 3)).Tables[0];
bool first = true;
foreach (DataRow d in aggDt.Rows)
{
foreach (DataRow drag in dt.Rows)
{
string[] op = drag["Options"].ToString().Split(',');
if (drag["fieldtype"].ToString().Split('|')[1] == d[0].ToString())
{
drag["Options"] = op[0] + "," + op[1] + ",1," + op[3] + "," + op[4];
}
else if (first)
{
drag["Options"] = op[0] + "," + op[1] + ",2," + op[3] + "," + op[4];
}
}
first = false;
}
}
bool exists = false;
string[] tableid = dr["fieldtype"].ToString().Split('|');
foreach (string x in tt)
{
if (x == tableid[0])
{
exists = true;
break;
}
}
if (!exists)
{
tt.Add(t[0]);
}
}
if (tt.Count > 1)
{
bool first = true;
foreach (DataRow drag in dt.Rows)
{
string[] op = drag["Options"].ToString().Split(',');
if (first)
{
drag["Options"] = op[0] + "," + op[1] + "," + op[2] + ",1," + op[4];
}
else
{
drag["Options"] = op[0] + "," + op[1] + "," + op[2] + ",0," + op[4];
}
first = false;
}
}
FillRepeater(dt);
StartScript(dt);
}
public virtual string GenerateDropIndexDdl(DatabaseConnection databaseConnection, string database, string schema,
string indexName)
{
return("DROP INDEX " + GetFullyQualifiedName(database, schema, indexName) +
databaseConnection.DatabaseServer.SqlTerminators.First());
}
public static Task <uint> SendTextPrivateMessageAsync(uint receiverUserId, uint senderUserId, string title, string message)
{
return(DatabaseConnection.NewAsyncConnection((dbConnection) => dbConnection.ReadDataAsync($"WITH pm AS (INSERT INTO base.pms(to_user_id, from_user_id, type) VALUES({receiverUserId}, {senderUserId}, 'text') RETURNING id) INSERT INTO base.pms_text(id, title, message) SELECT id, {title}, {message} FROM pm RETURNING id").ContinueWith(PrivateMessageManager.ParseSqlSendTextPm)));
}
ValueTask IDbSynchronizationStrategy <object> .ReleaseAsync(DatabaseConnection connection, string resourceName, object lockCookie) => ExecuteReleaseCommandAsync(connection, resourceName, isTry: false);
public static Task ReportPrivateMessageAsync(uint receiverUserId, uint pmId)
{
return(DatabaseConnection.NewAsyncConnection((dbConnection) => dbConnection.ExecuteNonQueryAsync($"INSERT INTO base.pms_reported(id) SELECT id FROM base.pms WHERE id = {pmId} AND to_user_id = {receiverUserId} ON CONFLICT DO NOTHING")));
}
public CertificatesDB(DatabaseConnection db) : base(db)
{
}
public void MakeGantt()
{
UC = (UserConfig)HttpContext.Current.Session["userconfig"];
DataSet prjInfo = DatabaseConnection.CreateDataset(@"SELECT PROJECT.TITLE, ACCOUNT.NAME + ' ' + ACCOUNT.SURNAME AS OWNERNAME
FROM PROJECT INNER JOIN ACCOUNT ON PROJECT.OWNER = ACCOUNT.UID
WHERE ID=" + prjID);
DataSet sections = DatabaseConnection.CreateDataset(String.Format(@"SELECT PROJECT_SECTION.ID,PROJECT_SECTION.TITLE,PROJECT_SECTION.DESCRIPTION,PROJECT_SECTION.PARENT,PROJECT_TIMING.PROGRESS,PROJECT_TIMING.PLANNEDSTARTDATE, PROJECT_TIMING.PLANNEDENDDATE, PROJECT_SECTION.MEMBERID,
ACCOUNT.NAME+' '+ACCOUNT.SURNAME AS OWNERNAME, PROJECT_TIMING.PLANNEDMINUTEDURATION, PROJECT_SECTION.COLOR
FROM PROJECT_SECTION LEFT OUTER JOIN PROJECT_TIMING ON (PROJECT_SECTION.ID=PROJECT_TIMING.IDRIF AND PROJECT_TIMING.RIFTYPE=0)
INNER JOIN ACCOUNT ON PROJECT_SECTION.MEMBERID=ACCOUNT.UID
WHERE PROJECT_SECTION.PARENT IS NULL AND PROJECT_SECTION.IDRIF={0}
ORDER BY PROJECT_SECTION.SECTIONORDER;
SELECT PROJECT_SECTION.ID,PROJECT_SECTION.TITLE,PROJECT_SECTION.DESCRIPTION,PROJECT_SECTION.PARENT,PROJECT_TIMING.PROGRESS,PROJECT_TIMING.PLANNEDSTARTDATE, PROJECT_TIMING.PLANNEDENDDATE, PROJECT_SECTION.MEMBERID,
ACCOUNT.NAME+' '+ACCOUNT.SURNAME AS OWNERNAME, PROJECT_TIMING.PLANNEDMINUTEDURATION, PROJECT_SECTION.COLOR
FROM PROJECT_SECTION LEFT OUTER JOIN PROJECT_TIMING ON (PROJECT_SECTION.ID=PROJECT_TIMING.IDRIF AND PROJECT_TIMING.RIFTYPE=0)
INNER JOIN ACCOUNT ON PROJECT_SECTION.MEMBERID=ACCOUNT.UID
WHERE PROJECT_SECTION.PARENT IS NOT NULL AND PROJECT_SECTION.IDRIF={0} ORDER BY PROJECT_SECTION.SECTIONORDER;", prjID));
if (sections.Tables[0].Rows.Count > 0)
{
DigiGantt.CoordsMaps rects = new DigiGantt.CoordsMaps();
DigiGantt dg = new DigiGantt();
DataRow startdates = DatabaseConnection.CreateDataset(string.Format(@"SELECT TOP 1 PLANNEDSTARTDATE,
(select top 1 newstartdate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=1)
inner join PROJECT_SECTION_MEMBERS on PROJECT_TIMING.idrif=PROJECT_SECTION_MEMBERS.id
inner join PROJECT_SECTION on (PROJECT_SECTION_MEMBERS.IDSectionRif = PROJECT_SECTION.id)
where PROJECT_SECTION.IDRIF={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=1
order by newstartdate asc) as variation,
(select top 1 newstartdate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=0)
inner join PROJECT_SECTION on (PROJECT_Timing.idrif = PROJECT_SECTION.id)
where PROJECT_SECTION.IDRIF={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=0
order by newstartdate asc) as globalvariation
FROM PROJECT_SECTION
INNER JOIN PROJECT_TIMING ON (PROJECT_SECTION.ID=PROJECT_TIMING.IDRIF AND RIFTYPE=0)
WHERE PROJECT_SECTION.IDRIF={0} AND PLANNEDSTARTDATE IS NOT NULL ORDER BY PLANNEDSTARTDATE ASC;", prjID)).Tables[0].Rows[0];
DateTime firststartDate = UC.LTZ.ToLocalTime((DateTime)startdates[0]);
DateTime startDate;
if (startdates[1] != System.DBNull.Value)
{
if (startdates[2] != System.DBNull.Value && ((DateTime)startdates[1]) < ((DateTime)startdates[2]))
{
startDate = UC.LTZ.ToLocalTime((DateTime)startdates[1]);
}
else
{
startDate = UC.LTZ.ToLocalTime((DateTime)startdates[2]);
}
}
else
if (startdates[2] != System.DBNull.Value && firststartDate < ((DateTime)startdates[2]))
{
startDate = firststartDate;
}
else if (startdates[2] != System.DBNull.Value)
{
startDate = UC.LTZ.ToLocalTime((DateTime)startdates[2]);
}
else
{
startDate = firststartDate;
}
DataRow enddates = DatabaseConnection.CreateDataset(string.Format(@"SELECT top 1 PLANNEDENDDATE,
(select top 1 newplanneddate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=1)
inner join PROJECT_SECTION_MEMBERS on PROJECT_TIMING.idrif=PROJECT_SECTION_MEMBERS.id
inner join PROJECT_SECTION on (PROJECT_SECTION_MEMBERS.IDSectionRif = PROJECT_SECTION.id)
where PROJECT_SECTION.IDRIF={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=1
order by newplanneddate desc) as variation,
(select top 1 newplanneddate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=0)
inner join PROJECT_SECTION on (PROJECT_Timing.idrif = PROJECT_SECTION.id)
where PROJECT_SECTION.IDRIF={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=0
order by newplanneddate desc) as globalvariation
FROM PROJECT_SECTION
JOIN PROJECT_TIMING ON (PROJECT_SECTION.ID=PROJECT_TIMING.IDRIF AND RIFTYPE=0)
WHERE PROJECT_SECTION.IDRIF={0} AND PLANNEDSTARTDATE IS NOT NULL ORDER BY PLANNEDENDDATE DESC;", prjID)).Tables[0].Rows[0];
DateTime firstendDate = UC.LTZ.ToLocalTime((DateTime)enddates[0]);
DateTime endDate = firstendDate;
if (enddates[1] != System.DBNull.Value)
{
if (enddates[2] != System.DBNull.Value && ((DateTime)enddates[1]) > ((DateTime)enddates[2]))
{
endDate = UC.LTZ.ToLocalTime((DateTime)enddates[1]);
}
else if (enddates[2] != System.DBNull.Value)
{
endDate = UC.LTZ.ToLocalTime((DateTime)enddates[2]);
}
else if (((DateTime)enddates[1]) > firstendDate)
{
endDate = UC.LTZ.ToLocalTime((DateTime)enddates[1]);
}
}
else
if (enddates[2] != System.DBNull.Value && firstendDate > ((DateTime)enddates[2]))
{
endDate = firstendDate;
}
else if (enddates[2] != System.DBNull.Value)
{
endDate = UC.LTZ.ToLocalTime((DateTime)enddates[2]);
}
System.Drawing.Image img = dg.DrawSchema(out rects, 2000, startDate.AddDays(-2), endDate.AddDays(2), DigiGantt.RowsFlags.NoYears);
rects.Add(dg.DrawProject(startDate, endDate, 1));
if (firstendDate != endDate)
{
rects.Add(dg.DrawExpected(firstendDate, Color.LightGreen, 1, 0, "Variazione data"));
}
int j = 2;
ArrayList arsections = new ArrayList();
ArrayList arsections2 = new ArrayList();
ArrayList arsections3 = new ArrayList();
ArrayList arsections4 = new ArrayList();
ArrayList arsections5 = new ArrayList();
arsections.Add("Descrizione");
arsections2.Add("Inizio");
arsections3.Add("Fine");
arsections4.Add("Durata");
arsections5.Add("Responsabile");
arsections.Add("{P00}" + prjInfo.Tables[0].Rows[0][0].ToString());
arsections2.Add(startDate.ToString(@"dd/MM/yy"));
arsections3.Add(endDate.ToString(@"dd/MM/yy"));
arsections4.Add(DatabaseConnection.SqlScalar(string.Format(@"SELECT SUM(PLANNEDMINUTEDURATION)
FROM PROJECT_SECTION
INNER JOIN PROJECT_TIMING ON (PROJECT_TIMING.IDRIF=PROJECT_SECTION.ID AND PROJECT_TIMING.RIFTYPE=0)
WHERE PROJECT_SECTION.IDRIF={0}", prjID)));
arsections5.Add(prjInfo.Tables[0].Rows[0][1].ToString());
foreach (DataRow dr in sections.Tables[0].Rows)
{
j = MakeGanttBar(ref rects, ref dg, j, ref arsections, ref arsections2, ref arsections3, ref arsections4, ref arsections5, dr, 0, sections);
}
DataTable dtrelations = DatabaseConnection.CreateDataset("SELECT * FROM PROJECT_RELATIONS WHERE PROJECTID=" + prjID + " ORDER BY ID").Tables[0];
foreach (DataRow drrelations in dtrelations.Rows)
{
int r1 = 0;
int r2 = 0;
foreach (string i in connectors)
{
string[] s = i.Split('|');
if (s[0] == drrelations["FIRSTRIFID"].ToString())
{
r1 = int.Parse(s[1]);
break;
}
}
foreach (string i in connectors)
{
string[] s = i.Split('|');
if (s[0] == drrelations["SECONDRIFID"].ToString())
{
r2 = int.Parse(s[1]);
break;
}
}
DateTime d1 = new DateTime();
if (drrelations["RELATIONTYPE"].ToString() == "0")
{
d1 = UC.LTZ.ToLocalTime((DateTime)DatabaseConnection.SqlScalartoObj("SELECT PLANNEDSTARTDATE FROM PROJECT_TIMING WHERE RIFTYPE=0 AND IDRIF=" + drrelations["FIRSTRIFID"].ToString()));
}
else
{
d1 = UC.LTZ.ToLocalTime((DateTime)DatabaseConnection.SqlScalartoObj("SELECT PLANNEDENDDATE FROM PROJECT_TIMING WHERE RIFTYPE=0 AND IDRIF=" + drrelations["FIRSTRIFID"].ToString()));
object variationend = DatabaseConnection.SqlScalartoObj(String.Format(@"select top 1 newplanneddate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=1)
inner join PROJECT_SECTION_MEMBERS on PROJECT_TIMING.idrif=PROJECT_SECTION_MEMBERS.id
inner join PROJECT_SECTION on (PROJECT_SECTION_MEMBERS.IDSectionRif = PROJECT_SECTION.id)
where PROJECT_SECTION.ID={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=1
order by newplanneddate desc", drrelations["FIRSTRIFID"].ToString()));
object variationend2 = DatabaseConnection.SqlScalartoObj(String.Format(@"select top 1 newplanneddate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=0)
inner join PROJECT_SECTION on (PROJECT_TIMING.idrif = PROJECT_SECTION.id)
where PROJECT_SECTION.ID={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=0
order by newplanneddate desc", drrelations["FIRSTRIFID"].ToString()));
if (variationend != null && variationend != System.DBNull.Value)
{
if ((variationend2 != null && variationend2 != System.DBNull.Value) && ((DateTime)variationend2) > ((DateTime)variationend))
{
d1 = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend2));
}
else
{
d1 = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend));
}
}
else
if (variationend2 != null && variationend2 != System.DBNull.Value)
{
d1 = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationend2));
}
}
DateTime d2 = UC.LTZ.ToLocalTime((DateTime)DatabaseConnection.SqlScalartoObj("SELECT PLANNEDSTARTDATE FROM PROJECT_TIMING WHERE RIFTYPE=0 AND IDRIF=" + drrelations["SECONDRIFID"].ToString()));
object variationstart = DatabaseConnection.SqlScalartoObj(String.Format(@"select top 1 newstartdate from PROJECT_TIMING_VARIATION
inner join PROJECT_TIMING on (PROJECT_TIMING_VARIATION.idtiming=PROJECT_TIMING.ID AND PROJECT_TIMING.RIFTYPE=0)
inner join PROJECT_SECTION on (PROJECT_TIMING.idrif = PROJECT_SECTION.id)
where PROJECT_SECTION.ID={0} AND PROJECT_TIMING_VARIATION.RIFTYPE=0
order by newstartdate desc", drrelations["SECONDRIFID"].ToString()));
if (variationstart != null && variationstart != System.DBNull.Value)
{
d2 = UC.LTZ.ToLocalTime(Convert.ToDateTime(variationstart));
}
dg.DrawConnector(d1, r1, d2, r2);
}
img = dg.CloseGanttChart(img, j);
string[][] mdArr = new string[5][] { ((string[])arsections.ToArray(typeof(System.String))), ((string[])arsections2.ToArray(typeof(System.String))), ((string[])arsections3.ToArray(typeof(System.String))), ((string[])arsections4.ToArray(typeof(System.String))), ((string[])arsections5.ToArray(typeof(System.String))) };
System.Drawing.Image img2 = dg.DrawLegend(ref rects, mdArr, 0);
if (NoRender)
{
this.Img1 = img;
this.Img2 = img2;
}
else
{
Cache.Add("Projectimg1_" + prjID, dg.ConvertImageToByteArray(img, System.Drawing.Imaging.ImageFormat.Png), null, DateTime.Now.AddSeconds(30), TimeSpan.Zero, CacheItemPriority.NotRemovable, null);
Cache.Add("Projectimg2_" + prjID, dg.ConvertImageToByteArray(img2, System.Drawing.Imaging.ImageFormat.Png), null, DateTime.Now.AddSeconds(30), TimeSpan.Zero, CacheItemPriority.NotRemovable, null);
litGantt.Text = dg.ImageMapRender("/project/GanttImg.aspx?prjID=" + prjID, rects);
}
}
else
{
litGantt.Text = "<span style=\"color:red\"> Nessuna sezione in questo progetto.</span>";
}
}
public abstract string GenerateCreatePackageDdl(DatabaseConnection databaseConnection, string database, string schema, string packageName);
public SqliteEmployeeCrud(DatabaseConnection connection)
{
this.DBConnection = connection;
}
public void Disconnect()
{
Utils.ThrowException(mConnection == null ? new InvalidOperationException() : null);
mConnection.Disconnect();
mConnection = null;
}
