public void EnqueueDistributions(System.DateTime Date)
{
FI.Common.Data.FIDataTable table=new FI.Common.Data.FIDataTable();
FI.Common.DataAccess.IDistributionsDA dacObj=DataAccessFactory.Instance.GetDistributionsDA();
FI.Common.Data.FIDataTable distrTable=dacObj.ReadDistributions(_owner.ID);
if(distrTable==null || distrTable.Rows.Count==0)
return;
for(int i=0;i<distrTable.Rows.Count;i++)
{
Distribution distr=_owner.DistributionSystem.GetDistribution((decimal)distrTable.Rows[i]["DistributionId"] , true);
if(distr.IsScheduledFor(Date))
{
try
{
dacObj.EnqueueDistribution(distr.ID, "");
}
catch
{
// do nothing , exception is logged
}
}
}
}
private FI.Common.Data.FIDataTable GetFormattedMembers()
{
FI.Common.Data.FIDataTable ret = new FI.Common.Data.FIDataTable();
ret.Columns.Add("uniquename", typeof(string));
ret.Columns.Add("hierarchy", typeof(string));
ret.Columns.Add("default_name", typeof(string));
ret.Columns.Add("name", typeof(string));
ret.Columns.Add("format", typeof(string));
// data members
foreach (Hierarchy hier in _report.Schema.Hierarchies)
{
foreach (CalculatedMember cmem in hier.CalculatedMembers)
{
// skip sets
if (cmem is Set)
{
continue;
}
// skip default formatted members
if (!(cmem is MemberWrapper) && cmem.Name == cmem.GetDefaultName() && cmem.Format == CalculatedMember.FormatEnum.Default)
{
continue;
}
ret.Rows.Add(new object[] { cmem.UniqueName, cmem.Hierarchy.UniqueName, cmem.GetDefaultName(), cmem.Name, cmem.Format.ToString() });
}
}
return(ret);
}
private void ConvertEnums(ref FI.Common.Data.FIDataTable dt)
{
if (dt == null)
{
return;
}
System.Data.DataColumn col = dt.Columns["Format"];
if (col == null)
{
return;
}
// set data
System.Data.DataColumn newCol = dt.Columns.Add("_" + col.ColumnName, typeof(string));
foreach (System.Data.DataRow dr in dt.Rows)
{
if (dr[col] != DBNull.Value)
{
dr[newCol] = Enum.GetName(typeof(Report.ExportFormat), dr[col]);
}
}
// replace cols
dt.Columns.Remove(col);
newCol.ColumnName = col.ColumnName;
}
public void LoadState(decimal ReportId, short StateCode, ref string Mdx, ref string Xsl, ref byte UndoCount, ref byte RedoCount)
{
FI.Common.Data.FIDataTable data = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[4];
parameters[0] = new SqlParameter("@ReportId", ReportId);
parameters[1] = new SqlParameter("@StateCode", StateCode);
parameters[2] = new SqlParameter("@UndoCount", UndoCount);
parameters[2].Direction = ParameterDirection.Output;
parameters[3] = new SqlParameter("@RedoCount", RedoCount);
parameters[3].Direction = ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadMdxReportState", CommandType.StoredProcedure, parameters, data);
if (parameters[2].Value != System.DBNull.Value)
{
UndoCount = (byte)parameters[2].Value;
}
if (parameters[3].Value != System.DBNull.Value)
{
RedoCount = (byte)parameters[3].Value;
}
Mdx = (string)data.Rows[0]["mdx"];
Xsl = (string)data.Rows[0]["xsl"];
}
public User(decimal ID, bool AsProxy)
{
if (AsProxy == false)
{
FI.DataAccess.Users dacObj = DataAccessFactory.Instance.GetUsersDA();
FI.Common.Data.FIDataTable table = dacObj.ReadUser(ID);
if (table == null || table.Rows.Count == 0)
{
throw new Exception("Cannot authenticate by id:" + ID.ToString());
}
LoadData(table);
this._isProxy = false;
}
this._id = ID;
if (this.IsNew == false)
{
_contactSystem = new ContactSystem(this);
_reportSystem = new ReportSystem(this);
_distributionSystem = new DistributionSystem(this); // after Contacts and Reports
}
}
public void EnqueueDistributions(System.DateTime Date)
{
FI.Common.Data.FIDataTable table = new FI.Common.Data.FIDataTable();
FI.DataAccess.Distributions dacObj = DataAccessFactory.Instance.GetDistributionsDA();
FI.Common.Data.FIDataTable distrTable = dacObj.ReadDistributions(_owner.ID);
if (distrTable == null || distrTable.Rows.Count == 0)
{
return;
}
for (int i = 0; i < distrTable.Rows.Count; i++)
{
Distribution distr = _owner.DistributionSystem.GetDistribution((decimal)distrTable.Rows[i]["DistributionId"], true);
if (distr.IsScheduledFor(Date))
{
try
{
dacObj.EnqueueDistribution(distr.ID, "");
}
catch
{
// do nothing , exception is logged
}
}
}
}
public FI.Common.Data.FIDataTable GetQueryProcessorInfo()
{
FI.Common.Data.FIDataTable ret=new FI.Common.Data.FIDataTable();
ret.Columns.Add("Server", typeof(string));
ret.Columns.Add("Database", typeof(string));
ret.Columns.Add("State", typeof(string));
ret.Columns.Add("AllocatedSpan", typeof(string));
ret.Columns.Add("TaskId", typeof(string));
ret.Columns.Add("TaskTag", typeof(string));
lock(this)
{
for(int i=0;i<_pool.Count;i++)
{
QueryProcessor proc=(QueryProcessor)_pool[i];
ret.Rows.Add(new object[]{
proc.Server, proc.Database, proc.State.ToString(),
proc.AllocatedSpan.ToString(),
proc.TaskId, proc.TaskTag});
}
}
return ret;
}
private void LoadReportPanel()
{
// load table
_reportProxy.LoadHeader();
FI.Common.Data.FIDataTable rptTable = new FI.Common.Data.FIDataTable();
rptTable.Columns.Add("name", typeof(string));
rptTable.Columns.Add("description", typeof(string));
rptTable.Rows.Add(new object[] { _reportProxy.Name, _reportProxy.Description });
//loading grid control
_rptGr = (FI.UI.Web.Controls.FIDataTableGrid)Page.LoadControl("Controls/FIDataTableGrid.ascx");
_rptGr.ID = "RptGrid";
_rptGr.InMemory = true;
_rptGr.DataSource = rptTable;
_rptGr.ColumnNameArray = new string[] { "name", "description" };
_rptGr.ColumnCaptionArray = new string[] { "Report Name", "Description" };
_rptGr.ColumnWidthArray = new int[] { 200, 400 };
_rptGr.EnableSort = false;
_rptGr.EnableFilter = false;
_rptGr.EnableCheckBoxes = false;
_rptGr.EnablePages = false;
ReportPanel.Controls.Add(_rptGr);
//load values into text fields
string name = ((string)rptTable.Rows[0]["name"]).Trim();
name = (name.Length > 42?name.Substring(0, 42):name);
txtName.Text = "Copy of " + name;
txtDescription.Text = ((string)rptTable.Rows[0]["description"]).Trim();
}
public FI.Common.Data.FIDataTable GetReportHeaders(System.Type ReportType)
{
FI.Common.Data.FIDataTable table = null;
if (ReportType == typeof(OlapReport))
{
FI.Common.DataAccess.IOlapReportsDA dacObj = DataAccessFactory.Instance.GetOlapReportsDA();
table = dacObj.ReadReportHeaders(_owner.ID);
}
else if (ReportType == typeof(StorecheckReport))
{
FI.Common.DataAccess.IStorecheckReportsDA dacObj = DataAccessFactory.Instance.GetStorecheckReportsDA();
table = dacObj.ReadReportHeaders(_owner.ID);
}
else if (ReportType == typeof(CustomSqlReport))
{
FI.Common.DataAccess.ICustomSqlReportsDA dacObj = DataAccessFactory.Instance.GetCustomSqlReportsDA();
table = dacObj.ReadReportHeaders(_owner.ID);
}
else if (ReportType == typeof(CustomMdxReport))
{
FI.Common.DataAccess.ICustomMdxReportsDA dacObj = DataAccessFactory.Instance.GetCustomMdxReportsDA();
table = dacObj.ReadReportHeaders(_owner.ID);
}
else
{
throw new NotSupportedException();
}
return(table);
}
public void LoadState(decimal ReportId, short StateCode,
ref string ProductsXml, ref byte ProductsLogic, ref short Days, ref string FilterXml,
ref bool InSelOnly, ref bool InBSelOnly, ref byte DataSource,
ref byte UndoCount, ref byte RedoCount)
{
FI.Common.Data.FIDataTable data = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[4];
parameters[0] = new SqlParameter("@ReportId", ReportId);
parameters[1] = new SqlParameter("@StateCode", StateCode);
parameters[2] = new SqlParameter("@UndoCount", UndoCount);
parameters[2].Direction = ParameterDirection.Output;
parameters[3] = new SqlParameter("@RedoCount", RedoCount);
parameters[3].Direction = ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadStorecheckReportState", CommandType.StoredProcedure, parameters, data);
if (parameters[2].Value != System.DBNull.Value)
{
UndoCount = (byte)parameters[2].Value;
}
if (parameters[3].Value != System.DBNull.Value)
{
RedoCount = (byte)parameters[3].Value;
}
ProductsXml = (string)data.Rows[0]["products_xml"];
ProductsLogic = (byte)data.Rows[0]["products_logic"];
Days = (short)data.Rows[0]["days"];
FilterXml = (string)data.Rows[0]["filter_xml"];
InSelOnly = (bool)data.Rows[0]["insel"];
InBSelOnly = (bool)data.Rows[0]["inbsel"];
DataSource = (byte)(data.Rows[0]["datasource"] == null || data.Rows[0]["datasource"] == DBNull.Value ? 0 : data.Rows[0]["datasource"]);
}
public FI.Common.Data.FIDataTable ReadCompanies()
{
FI.Common.Data.FIDataTable dataTable = new FI.Common.Data.FIDataTable();
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadCompanies", CommandType.StoredProcedure, null, dataTable);
return(dataTable);
}
public FI.Common.Data.FIDataTable GetUsersWithChildReports(Report ParentReport)
{
FI.Common.Data.FIDataTable table = null;
if (ParentReport.GetType() == typeof(OlapReport))
{
FI.Common.DataAccess.IOlapReportsDA dacObj = DataAccessFactory.Instance.GetOlapReportsDA();
table = dacObj.ReadUsersWithChildReports(ParentReport.ID, this.GetReportTypeCode(ParentReport.GetType()));
}
else if (ParentReport.GetType() == typeof(StorecheckReport))
{
FI.Common.DataAccess.IStorecheckReportsDA dacObj = DataAccessFactory.Instance.GetStorecheckReportsDA();
table = dacObj.ReadUsersWithChildReports(ParentReport.ID, this.GetReportTypeCode(ParentReport.GetType()));
}
else if (ParentReport.GetType() == typeof(CustomSqlReport))
{
FI.Common.DataAccess.ICustomSqlReportsDA dacObj = DataAccessFactory.Instance.GetCustomSqlReportsDA();
table = dacObj.ReadUsersWithChildReports(ParentReport.ID, this.GetReportTypeCode(ParentReport.GetType()));
}
else if (ParentReport.GetType() == typeof(CustomMdxReport))
{
FI.Common.DataAccess.ICustomMdxReportsDA dacObj = DataAccessFactory.Instance.GetCustomMdxReportsDA();
table = dacObj.ReadUsersWithChildReports(ParentReport.ID, this.GetReportTypeCode(ParentReport.GetType()));
}
else
{
throw new NotSupportedException();
}
return(table);
}
public void SaveState(decimal ReportId, byte MaxStateCount,
string ProductsXml, byte ProductsLogic, short Days, string FilterXml,
bool InSelOnly, bool InBSelOnly, byte DataSource,
ref byte UndoCount)
{
FI.Common.Data.FIDataTable data = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[10];
parameters[0] = new SqlParameter("@ReportId", ReportId);
parameters[1] = new SqlParameter("@MaxStateCount", MaxStateCount);
parameters[2] = new SqlParameter("@ProductsXml", ProductsXml);
parameters[3] = new SqlParameter("@ProductsLogic", ProductsLogic);
parameters[4] = new SqlParameter("@Days", Days);
parameters[5] = new SqlParameter("@FilterXml", FilterXml);
parameters[6] = new SqlParameter("@InSelOnly", InSelOnly);
parameters[7] = new SqlParameter("@InBSelOnly", InBSelOnly);
parameters[8] = new SqlParameter("@DataSource", DataSource);
parameters[9] = new SqlParameter("@UndoCount", UndoCount);
parameters[9].Direction = ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_SaveStorecheckReportState", CommandType.StoredProcedure, parameters, null);
if (parameters[9].Value != System.DBNull.Value)
{
UndoCount = (byte)parameters[9].Value;
}
}
private void LoadData(FI.Common.Data.FIDataTable table)
{
System.Data.DataRow row = table.Rows[0];
if (table.Rows.Count == 0)
{
throw new Exception("Cannot load data");
}
this._logon = (string)row["Logon"];
this._password = (string)row["Password"];
this._passwordTimestamp = (DateTime)row["PasswordTimestamp"];
this._name = (string)row["Name"];
this._email = (string)row["Email"];
this._ipAddress = (string)row["ConnectionAddress"];
this._sessionId = (string)row["SessionId"];
this._companyId = (decimal)row["CompanyId"];
this._isLoggedIn = (bool)row["IsLoggedIn"];
this._companyNameShort = (string)row["CompanyNameShort"];
this._companyNameLong = (string)row["CompanyNameLong"];
this._isAdmin = (bool)row["IsAdmin"];
this._oltpDatabase = (string)row["OltpDatabase"];
this._oltpDatabase = (string)row["OltpDatabase"];
this.CssStyle = (byte)(row["CssStyle"] == DBNull.Value ? (byte)0 : row["CssStyle"]);
this._isAdminAudit = this._isAdmin;
}
public void DeleteSharedReports(Report ParentReport)
{
FI.Common.Data.FIDataTable dataTable = this.GetUsersWithChildReports(ParentReport);
if (dataTable == null || dataTable.Rows.Count == 0)
{
return;
}
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow row = dataTable.Rows[i];
decimal userId = (decimal)row["user_id"];
decimal reportId = (decimal)row["report_id"];
int reportType = (int)row["report_type"];
if (reportId == 0)
{
continue;
}
User user = new User(userId, true);
Report childReport = user.ReportSystem.GetReport(reportId, user.ReportSystem.GetReportType(reportType), false);
user.ReportSystem.DeleteSharedReport(ParentReport, childReport);
}
}
public FI.Common.Data.FIDataTable GetContactsPage(int CurrentPage, int RowCount, string FilterExpression, string SortExpression)
{
int StartIndex = (CurrentPage - 1) * RowCount;
FI.DataAccess.Contacts dacObj = DataAccessFactory.Instance.GetContactsDA();
FI.Common.Data.FIDataTable table = dacObj.ReadContactsPage(_owner.ID, StartIndex, RowCount, FilterExpression, SortExpression);
return(table);
}
public FI.Common.Data.FIDataTable ReadUsers(decimal CompanyId)
{
FI.Common.Data.FIDataTable dataTable = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("@CompanyId", CompanyId);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadUsers", CommandType.StoredProcedure, parameters, dataTable);
return(dataTable);
}
public FI.Common.Data.FIDataTable ReadReportResult(string Sql, string Database)
{
FI.Common.Data.FIDataTable data = new FI.Common.Data.FIDataTable();
string connString = FI.Common.AppConfig.DA_OltpConnectionString.Replace("@DATABASE", Database);
DataBase.Instance.ExecuteCommand(Sql, connString, CommandType.Text, null, data);
return(data);
}
public FI.Common.Data.FIDataTable ReadReportHeaders(decimal UserId)
{
FI.Common.Data.FIDataTable data = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("@UserId", UserId);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadStorecheckReportHeaders", CommandType.StoredProcedure, parameters, data);
return(data);
}
public FI.Common.Data.FIDataTable ReadDistributions(decimal UserID)
{
FI.Common.Data.FIDataTable dataTable = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter("@UserId", UserID);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadDistributionsByUserId", CommandType.StoredProcedure, parameters, dataTable);
return(dataTable);
}
public FI.Common.Data.FIDataTable GetDistributionLogPage(Report report, int CurrentPage, int RowCount, string FilterExpression, string SortExpression)
{
int StartIndex = (CurrentPage - 1) * RowCount;
FI.Common.DataAccess.IDistributionsDA dacObj = DataAccessFactory.Instance.GetDistributionsDA();
FI.Common.Data.FIDataTable table = null;
table = dacObj.ReadReportDistributionLog(_owner.ID, report.ID, report.GetTypeCode(), StartIndex, RowCount, FilterExpression, SortExpression);
return(table);
}
public void DeleteReport(decimal UserId, decimal ReportId, bool DenyShared)
{
FI.Common.Data.FIDataTable data = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[3];
parameters[0] = new SqlParameter("@UserId", UserId);
parameters[1] = new SqlParameter("@ReportId", ReportId);
parameters[2] = new SqlParameter("@DenyShared", DenyShared);
DataBase.Instance.ExecuteCommand("dbo.sproc_DeleteStorecheckReport", CommandType.StoredProcedure, parameters, null);
}
public FI.Common.Data.FIDataTable ReadUsersWithChildReports(decimal ParentReportId, int ParentReportType)
{
FI.Common.Data.FIDataTable data = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter("@ParentReportId", ParentReportId);
parameters[1] = new SqlParameter("@ParentReportType", ParentReportType);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadUsersWithChildReports", CommandType.StoredProcedure, parameters, data);
return(data);
}
public FI.Common.Data.FIDataTable GetDistributionsWithContactsPage(Report report, int CurrentPage, int RowCount, string FilterExpression, string SortExpression)
{
int StartIndex = (CurrentPage - 1) * RowCount;
FI.DataAccess.Distributions dacObj = DataAccessFactory.Instance.GetDistributionsDA();
FI.Common.Data.FIDataTable table = new FI.Common.Data.FIDataTable();
table = dacObj.ReadDistributionsWithContactsPage(_owner.ID, report.ID, report.GetTypeCode(), StartIndex, RowCount, FilterExpression, SortExpression);
ConvertEnums(ref table);
return(table);
}
public FI.Common.Data.FIDataTable GetDistributionQueuePage(int CurrentPage, int RowCount, string FilterExpression, string SortExpression)
{
int StartIndex = (CurrentPage - 1) * RowCount;
FI.DataAccess.Distributions dacObj = DataAccessFactory.Instance.GetDistributionsDA();
FI.Common.Data.FIDataTable table = null;
table = dacObj.ReadDistributionQueue(_owner.CompanyId, StartIndex, RowCount, FilterExpression, SortExpression);
return(table);
}
public FI.Common.Data.FIDataTable ReadContact(decimal UserID , decimal ContactID)
{
FI.Common.Data.FIDataTable dataTable=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[2];
parameters[0]=new SqlParameter("@UserId" , UserID);
parameters[1]=new SqlParameter("@ContactId" , ContactID);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadContact" , CommandType.StoredProcedure , parameters , dataTable);
return dataTable;
}
public void DeleteReport(decimal UserId , decimal ReportId, bool DenyShared)
{
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[3];
parameters[0]=new SqlParameter("@UserId" , UserId);
parameters[1]=new SqlParameter("@ReportId" , ReportId);
parameters[2]=new SqlParameter("@DenyShared" , DenyShared);
DataBase.Instance.ExecuteCommand("dbo.sproc_DeleteMdxReport" , CommandType.StoredProcedure , parameters , null);
}
public FI.Common.Data.FIDataTable ReadUser(string CompanyNameShort, string Logon, string Password)
{
FI.Common.Data.FIDataTable dataTable = new FI.Common.Data.FIDataTable();
SqlParameter[] parameters = new SqlParameter[3];
parameters[0] = new SqlParameter("@CompanyNameShort", CompanyNameShort);
parameters[1] = new SqlParameter("@Logon", Logon);
parameters[2] = new SqlParameter("@Password", Password);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadUserByAuthentication", CommandType.StoredProcedure, parameters, dataTable);
return(dataTable);
}
public void DeleteAll()
{
FI.Common.Data.FIDataTable table = GetContactsPage(1, 100000, "", "");
while (table != null && table.Rows.Count > 0)
{
foreach (System.Data.DataRow row in table.Rows)
{
Contact cnt = this.GetContact((decimal)row["id"], false);
this.DeleteContact(cnt);
}
table = GetContactsPage(1, 100000, "", "");
}
}
private FI.Common.Data.FIDataTable GetUsersWithChildReports()
{
FI.Common.Data.FIDataTable table = _user.ReportSystem.GetUsersWithChildReports(this._reportProxy);
table.Columns.Add("sharing_string", typeof(string));
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
int shSt = int.Parse(((byte)row["sharing_status"]).ToString());
Report.SharingEnum sh = (Report.SharingEnum)shSt;
row["sharing_string"] = sh.ToString();
}
return(table);
}
/*
* public FI.Common.Data.FIDataTable GetMasterDistributionQueuePage(int CurrentPage , int RowCount , string FilterExpression , string SortExpression)
* {
* int StartIndex=(CurrentPage-1)*RowCount;
*
* FI.DataAccess.Distributions dacObj=DataAccessFactory.Instance.GetDistributionsDA();
* FI.Common.Data.FIDataTable table=null;
*
* table=dacObj.ReadMasterDistributionQueue(StartIndex , RowCount , FilterExpression , SortExpression);
*
* return table;
* }
*/
public FI.Common.Data.FIDataTable GetDistributionInfo(bool checkOnly)
{
// get from DA
FI.DataAccess.Distributions dacObj = DataAccessFactory.Instance.GetDistributionsDA();
FI.Common.Data.FIDataTable ret = dacObj.GetDistributionInfo(false);
// resolve distributions not sent according to schedule
DataColumn checkStatusCol = ret.Columns["CheckStatus"];
DataColumn timeCol = ret.Columns["LastTimestamp"];
DataColumn statusCol = ret.Columns["LastStatus"];
DataColumn freqTypeCol = ret.Columns["ScheduleType"];
DataColumn freqValCol = ret.Columns["ScheduleValue"];
foreach (DataRow r in ret.Rows)
{
if ((string)r[checkStatusCol] != string.Empty)
{
continue; // already has check status
}
if ((string)r[timeCol] == string.Empty)
{
continue; // no time to iterate from
}
if ((string)r[statusCol] != "Ok")
{
continue; // last status is not ok
}
DateTime date = DateTime.Parse(r[timeCol].ToString());
date = date.AddDays(1);
DateTime now = DateTime.Now;
bool ok = true;
while (date <= now)
{
string reqType = r[freqTypeCol].ToString();
string reqVal = r[freqValCol].ToString();
if (Distribution.IsScheduledForDate(reqType, reqVal, date))
{
ok = false;
break;
}
date = date.AddDays(1);
}
if (!ok)
{
r[checkStatusCol] = "Schedule Failed";
}
}
return(ret);
}
private void LoadTabs()
{
int id = 0, id1 = 0;
id = this.CreateRootTabs(_tabView, _user.Name, _user.IsLoggedIn, true, PageBase.RootTabsEnum.Olap_Reports);
_tabView.AddTab(id, " List ", Request.ApplicationPath + "/ReportList.aspx?content=List&rpttype=" + _report.GetTypeCode().ToString(), false, false);
FI.Common.Data.FIDataTable rptTable = _user.ReportSystem.GetReportHeaders(_report.GetType());
foreach (System.Data.DataRow row in rptTable.Rows)
{
decimal rptId = decimal.Parse(row["id"].ToString());
bool rptSelected = (bool)row["is_selected"];
bool rptOpen = (_report != null && rptId == _report.ID?true:false);
string rptName = (string)row["name"];
FI.BusinessObjects.Report.SharingEnum rptSharingStatus = (FI.BusinessObjects.Report.SharingEnum) int.Parse(row["sharing_status"].ToString());
FI.BusinessObjects.Report.SharingEnum rptMaxSubscriberSharingStatus = (FI.BusinessObjects.Report.SharingEnum) int.Parse(row["max_subscriber_sharing_status"].ToString());
if (rptSelected)
{
int reportType = _report.GetTypeCode();
id1 = _tabView.AddTab(id, rptName, Request.ApplicationPath + "/ReportList.aspx?content=Load&action=Open&rptid=" + rptId + "&rpttype=" + reportType.ToString(), rptOpen, false);
if (rptSharingStatus == FI.BusinessObjects.Report.SharingEnum.SnapshotSubscriber)
{
_tabView.AddImage(id1, "images/share.gif");
}
else if (rptSharingStatus == FI.BusinessObjects.Report.SharingEnum.InheriteSubscriber)
{
_tabView.AddImage(id1, "images/share_change.gif");
}
else if (rptMaxSubscriberSharingStatus == FI.BusinessObjects.Report.SharingEnum.SnapshotSubscriber)
{
_tabView.AddImage(id1, "images/distr.gif");
}
else if (rptMaxSubscriberSharingStatus == FI.BusinessObjects.Report.SharingEnum.InheriteSubscriber)
{
_tabView.AddImage(id1, "images/distr_change.gif");
}
}
}
_tabView.AddTab(id1, " Table ", Request.ApplicationPath + "/OlapReport/Table.aspx", true, false);
_tabView.AddTab(id1, " Graph ", Request.ApplicationPath + "/OlapReport/Graph.aspx", false, false);
_tabView.AddTab(id1, " Design ", Request.ApplicationPath + "/OlapReport/Design.aspx", false, false);
_tabView.AddTab(id1, " Format ", Request.ApplicationPath + "/OlapReport/Format.aspx", false, false);
}
public void DeleteSharedReport(decimal ParentReportId, decimal ChildReportId, ref short MaxSubscriberSharingStatus)
{
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[3];
parameters[0]=new SqlParameter("@ParentReportId" , ParentReportId);
parameters[1]=new SqlParameter("@ChildReportId" , ChildReportId);
parameters[2]=new SqlParameter("@MaxSubscriberSharingStatus" , MaxSubscriberSharingStatus);
parameters[2].Direction=ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_DeleteSharedMdxReport" , CommandType.StoredProcedure , parameters , null);
if(parameters[2].Value!=System.DBNull.Value)
MaxSubscriberSharingStatus=(short)parameters[2].Value;
}
public decimal CreateSharedReport(decimal ParentReportId , decimal SubscriberUserId , int SubscriberSharingStatus)
{
decimal reportId=0;
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[4];
parameters[0]=new SqlParameter("@ParentReportId" , ParentReportId);
parameters[1]=new SqlParameter("@SubscriberUserId" , SubscriberUserId);
parameters[2]=new SqlParameter("@SubscriberSharingStatus" , SubscriberSharingStatus);
parameters[3]=new SqlParameter("@SubscriberReportId" , reportId);
parameters[3].Direction=ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_InsertSharedMdxReport" , CommandType.StoredProcedure , parameters , null);
if(parameters[3].Value!=System.DBNull.Value)
reportId=(decimal)parameters[3].Value;
return reportId;
}
private void LoadReportPanel()
{
// load table
_reportProxy.LoadHeader();
FI.Common.Data.FIDataTable rptTable=new FI.Common.Data.FIDataTable();
rptTable.Columns.Add("name" , typeof(string));
rptTable.Columns.Add("description" , typeof(string));
rptTable.Rows.Add(new object[] {_reportProxy.Name , _reportProxy.Description});
//loading grid control
_rptGr = (FI.UI.Web.Controls.FIDataTableGrid)Page.LoadControl("Controls/FIDataTableGrid.ascx");
_rptGr.ID="RptGrid";
_rptGr.InMemory=true;
_rptGr.DataSource=rptTable;
_rptGr.ColumnNameArray=new string[] {"name" , "description" };
_rptGr.ColumnCaptionArray=new string[] {"Report Name" , "Description"};
_rptGr.ColumnWidthArray=new int[] {200 , 400};
_rptGr.EnableSort=false;
_rptGr.EnableFilter=false;
_rptGr.EnableCheckBoxes=false;
_rptGr.EnablePages=false;
ReportPanel.Controls.Add(_rptGr);
}
private void LoadReportPanel()
{
// load table
_report.LoadHeader();
FI.Common.Data.FIDataTable rptTable=new FI.Common.Data.FIDataTable();
rptTable.Columns.Add("name" , typeof(string));
rptTable.Columns.Add("description" , typeof(string));
rptTable.Rows.Add(new object[] {_report.Name , _report.Description});
//loading grid control
_rptGr = (FI.UI.Web.Controls.FIDataTableGrid)Page.LoadControl("Controls/FIDataTableGrid.ascx");
_rptGr.ID="RptGrid";
_rptGr.InMemory=true;
_rptGr.DataSource=rptTable;
_rptGr.ColumnNameArray=new string[] {"name" , "description" };
_rptGr.ColumnCaptionArray=new string[] {"Report Name" , "Description"};
_rptGr.ColumnWidthArray=new int[] {200 , 400};
_rptGr.EnableSort=false;
_rptGr.EnableFilter=false;
_rptGr.EnableCheckBoxes=false;
_rptGr.EnablePages=false;
ReportPanel.Controls.Add(_rptGr);
// if shared report , saving is disabled
if(_report.SharingStatus==Report.SharingEnum.InheriteSubscriber || _report.SharingStatus==Report.SharingEnum.SnapshotSubscriber)
this.radioSave.Enabled=false;
//load values into text fields
string name=((string)rptTable.Rows[0]["name"]).Trim();
name=(name.Length>42?name.Substring(0,42):name);
txtName.Text="Copy of " + name;
txtDescription.Text=((string)rptTable.Rows[0]["description"]).Trim();
}
private FI.Common.Data.FIDataTable GetFormattedMembers()
{
FI.Common.Data.FIDataTable ret=new FI.Common.Data.FIDataTable();
ret.Columns.Add("uniquename", typeof(string));
ret.Columns.Add("hierarchy", typeof(string));
ret.Columns.Add("default_name", typeof(string));
ret.Columns.Add("name", typeof(string));
ret.Columns.Add("format", typeof(string));
// data members
foreach(Hierarchy hier in _report.Schema.Hierarchies)
foreach(CalculatedMember cmem in hier.CalculatedMembers)
{
// skip sets
if(cmem is Set)
continue;
// skip default formatted members
if(!(cmem is MemberWrapper) && cmem.Name==cmem.GetDefaultName() && cmem.Format==CalculatedMember.FormatEnum.Default)
continue;
ret.Rows.Add(new object[]{cmem.UniqueName, cmem.Hierarchy.UniqueName, cmem.GetDefaultName(), cmem.Name, cmem.Format.ToString() });
}
return ret;
}
public FI.Common.Data.FIDataTable ExecutePagedCommand(int StartIndex, int RecordCount, SqlParameter[] SelectColumns, string Sql, string FilterExpression , string SortExpression)
{
// ------------- construct select list
string selectList="";
foreach(SqlParameter column in SelectColumns)
selectList= selectList + column.ParameterName + ", ";
// remove last coma and space
selectList=selectList.Remove(selectList.Length-2,2);
// ------------- construct variables definitions
string varDecl="";
foreach(SqlParameter column in SelectColumns)
{
varDecl=varDecl + "\r\nDECLARE @" + column.ParameterName + " " + column.SqlDbType.ToString();
if(column.Size>0)
varDecl=varDecl + "(" + column.Size +")";
}
// ------------- construct variables list
string varList="";
foreach(SqlParameter column in SelectColumns)
varList= varList + "@" + column.ParameterName + ", ";
// remove last coma and space
varList=varList.Remove(varList.Length-2,2);
// ------------- construct cusor
string cursorSql=@"
DECLARE temp_cursor CURSOR STATIC FOR
SELECT " + selectList + @"
FROM
(" + Sql + ") TBL";
if(FilterExpression!=null && FilterExpression!="")
cursorSql=cursorSql + " WHERE (" + FilterExpression + ")";
if(SortExpression!=null && SortExpression!="")
cursorSql=cursorSql + " ORDER BY " + SortExpression ;
// ------------- construct temp table
string tableSql=@"
if object_id('tempdb..#tmp') is not null
drop table #tmp
create table #tmp
(
[_serno] int IDENTITY(1,1) PRIMARY KEY,";
foreach(SqlParameter column in SelectColumns)
{
tableSql= tableSql + column.ParameterName + " " + column.SqlDbType.ToString() ;
if(column.Size>0)
tableSql=tableSql + "(" + column.Size +"), ";
else
tableSql=tableSql + ", ";
}
// remove last coma and space
tableSql=tableSql.Remove(tableSql.Length-2,2);
tableSql=tableSql + @"
)
";
// ------------- construct sql
string sql=@"
SET NOCOUNT ON
" + varDecl + @"
" + tableSql + @"
" + cursorSql + @"
DECLARE @i int
DECLARE @StartIndex int
DECLARE @RecordCount int
DECLARE @TotalCount int
SET @i=0
SET @StartIndex=" + StartIndex + @"+1
SET @RecordCount=" + RecordCount + @"
OPEN temp_cursor
FETCH ABSOLUTE @StartIndex from temp_cursor INTO " + varList + @"
WHILE @@FETCH_STATUS=0 AND @i<@RecordCount
BEGIN
INSERT INTO #tmp(" + selectList + @")
VALUES(" + varList + @")
FETCH NEXT from temp_cursor INTO " + varList + @"
SET @i = @i + 1
END
-- WORKS FOR STATIC CURSORS ONLY !!
SET @TotalCount = @@CURSOR_ROWS
CLOSE temp_cursor
DEALLOCATE temp_cursor
SELECT
" + selectList + @"
FROM #tmp
order by [_serno]
SELECT @TotalCount as TotalCount
DROP TABLE #tmp
";
// get data
DataSet dataSet=new DataSet();
FI.Common.Data.FIDataTable resultTable=new FI.Common.Data.FIDataTable();
FI.Common.Data.FIDataTable countTable=new FI.Common.Data.FIDataTable();
dataSet.Tables.Add(resultTable);
dataSet.Tables.Add(countTable);
ExecuteCommand(sql , CommandType.Text , null, dataSet);
// second table must have total count
int totalCount=(int)dataSet.Tables[1].Rows[0][0];
//first table is result
FI.Common.Data.FIDataTable result=(FI.Common.Data.FIDataTable)dataSet.Tables[0];
result.TotalCount=totalCount;
return result;
}
public FI.Common.Data.FIDataTable ReadReportDistributionLog(decimal UserID, decimal ReportID , int ReportType , int StartIndex , int RecordCount , string FilterExpression , string SortExpression)
{
FI.Common.Data.FIDataTable dataTable=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[8];
parameters[0]=new SqlParameter("@UserId" , UserID);
parameters[1]=new SqlParameter("@ReportId" , ReportID);
parameters[2]=new SqlParameter("@ReportType" , ReportType);
parameters[3]=new SqlParameter("@StartIndex" , StartIndex);
parameters[4]=new SqlParameter("@RecordCount" , RecordCount);
parameters[5]=new SqlParameter("@FilterExpression" , FilterExpression );
parameters[6]=new SqlParameter("@SortExpression" , SortExpression);
parameters[7]=new SqlParameter("@TotalCount" , dataTable.TotalCount);
parameters[7].Direction=ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadReportDistributionLogPage" , CommandType.StoredProcedure , parameters , dataTable);
if(parameters[7].Value!=System.DBNull.Value)
dataTable.TotalCount=(int)parameters[7].Value;
return dataTable;
}
public FI.Common.Data.FIDataTable ReadDistributions(decimal UserID)
{
FI.Common.Data.FIDataTable dataTable=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[1];
parameters[0]=new SqlParameter("@UserId" , UserID);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadDistributionsByUserId" , CommandType.StoredProcedure , parameters , dataTable);
return dataTable;
}
public FI.Common.Data.FIDataTable GetDistributionInfo(bool checkOnly)
{
FI.Common.Data.FIDataTable ret=new FI.Common.Data.FIDataTable();
string sql=string.Format(@"
select * from
(
SELECT
d.Id,
d.freq_type as ScheduleType,
d.freq_value as ScheduleValue,
com.short_name as Domain,
usr.id as UserId,
usr.name as UserName,
cnt.[name] as Contact ,
cnt.[email] as ContactEmail ,
rpt.Id as ReportId,
rpt.rpt_type as ReportTypeCode,
case
when rpt.rpt_type=0 then 'Olap'
when rpt.rpt_type=1 then 'Storecheck'
when rpt.rpt_type=2 then 'CustomSql'
when rpt.rpt_type=3 then 'CustomMdx'
end as ReportType ,
rpt.name as ReportName ,
rpt.description as ReportDescr,
isnull((select top 1 status from tdistribution_log l1 where l1.distribution_id=d.id and status in ('Pending', 'Executing')),'') as IsQueued,
case
when isnull(rpt_ok_log.avg_duration,0)>7200 or isnull(rpt_ok_log.last_duration,0)>7200 -- avg or last ok more than 2 hours
then 'Duration'
when last_log.status='Canceled' and isnull(rpt_cancel_log.avg_duration,0)>7200
then 'Duration'
when last_log.status='Error'
then 'Last Error'
when last_log.status='Canceled'
then 'Last Canceled'
else ''
end as CheckStatus,
last_log.status as LastStatus,
convert(varchar(25), last_log.timestamp, 120) as LastTimestamp,
last_log.message as LastMessage,
rpt_ok_log.log_count as OkCount,
rpt_ok_log.min_duration as OkMinDuration,
rpt_ok_log.max_duration as OkMaxDuration,
rpt_ok_log.avg_duration as OkAvgDuration,
rpt_ok_log.last_duration as OkLastDuration,
rpt_cancel_log.log_count as CancelCount,
rpt_cancel_log.avg_duration as CancelAvgDuration,
rpt_error_log.log_count as ErrorCount
from
tdistribution d
INNER JOIN tcontacts cnt on d.contact_id=cnt.[id]
INNER JOIN
(
select [id] , 0 as rpt_type , name , description, user_id from v_olap_reports
union
select [id] , 1 as rpt_type , name , description, user_id from v_storecheck_reports
union
select [id] , 2 as rpt_type , name , description, user_id from v_sql_reports
union
select [id] , 3 as rpt_type ,name , description, user_id from v_mdx_reports
) rpt on d.rpt_id=rpt.[id] and d.rpt_type=rpt.rpt_type
INNER JOIN tusers usr on rpt.user_id=usr.[id]
INNER JOIN tcompany com on usr.company_id=com.[id]
left outer join
(select rpt_id, rpt_type,
count(*) as log_count, max(timestamp) as last_timestamp,
max(duration) as max_duration, min(duration) as min_duration, avg(duration) as avg_duration,
(select top 1 duration
from tdistribution_log l2 inner join tdistribution d2 on l2.distribution_id=d2.id
where l2.status='Ok' and l2.timestamp=max(l1.timestamp) and isnull(l2.isfromcache,0)=0
and d1.rpt_id=d2.rpt_id and d1.rpt_type=d2.rpt_type
) as last_duration
from tdistribution_log l1 inner join tdistribution d1 on l1.distribution_id=d1.id
where l1.timestamp>=DATEADD(mm, -3, GetDate()) and l1.status='Ok' and isnull(l1.isfromcache,0)=0
group by rpt_id, rpt_type
) rpt_ok_log on rpt_ok_log.rpt_id=d.rpt_id and rpt_ok_log.rpt_type=d.rpt_type
left outer join
(select rpt_id, rpt_type,
count(*) as log_count, avg(duration) as avg_duration
from tdistribution_log l1 inner join tdistribution d1 on l1.distribution_id=d1.id
where l1.timestamp>=DATEADD(mm, -3, GetDate()) and l1.status='Canceled'
group by rpt_id, rpt_type
) rpt_cancel_log on rpt_cancel_log.rpt_id=d.rpt_id and rpt_cancel_log.rpt_type=d.rpt_type
left outer join
(select rpt_id, rpt_type,
count(*) as log_count
from tdistribution_log l1 inner join tdistribution d1 on l1.distribution_id=d1.id
where l1.timestamp>=DATEADD(mm, -3, GetDate()) and l1.status='Error'
group by rpt_id, rpt_type
) rpt_error_log on rpt_error_log.rpt_id=d.rpt_id and rpt_error_log.rpt_type=d.rpt_type
left outer join
(
select l2.distribution_id, l2.timestamp, l2.message, l2.status from
(select distribution_id, max(timestamp) as last_timestamp from tdistribution_log where status in ('Ok', 'Canceled', 'Error') group by distribution_id) l1 inner join
tdistribution_log l2 on l1.distribution_id=l2.distribution_id and l1.last_timestamp=l2.timestamp
) last_log on last_log.distribution_id=d.id
) tbl
{0}", (checkOnly ? "where CheckStatus!=''" : ""));
DataBase.Instance.ExecuteCommand(sql , CommandType.Text , null , ret);
return ret;
}
public void LoadState(decimal ReportId, short StateCode , ref string Mdx , ref string Xsl, ref byte UndoCount , ref byte RedoCount)
{
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[4];
parameters[0]=new SqlParameter("@ReportId" , ReportId);
parameters[1]=new SqlParameter("@StateCode" , StateCode);
parameters[2]=new SqlParameter("@UndoCount" , UndoCount);
parameters[2].Direction=ParameterDirection.Output;
parameters[3]=new SqlParameter("@RedoCount" , RedoCount);
parameters[3].Direction=ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadMdxReportState" , CommandType.StoredProcedure , parameters , data);
if(parameters[2].Value!=System.DBNull.Value)
UndoCount=(byte)parameters[2].Value;
if(parameters[3].Value!=System.DBNull.Value)
RedoCount=(byte)parameters[3].Value;
Mdx=(string)data.Rows[0]["mdx"];
Xsl=(string)data.Rows[0]["xsl"];
}
public FI.Common.Data.FIDataTable ReadReportHeaders(decimal UserId)
{
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[1];
parameters[0]=new SqlParameter("@UserId" , UserId);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadMdxReportHeaders" , CommandType.StoredProcedure , parameters , data);
return data;
}
public FI.Common.Data.FIDataTable ReadUsersWithChildReports(decimal ParentReportId , int ParentReportType)
{
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[2];
parameters[0]=new SqlParameter("@ParentReportId" , ParentReportId);
parameters[1]=new SqlParameter("@ParentReportType" , ParentReportType);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadUsersWithChildReports" , CommandType.StoredProcedure , parameters , data);
return data;
}
public void SaveState(decimal ReportId, byte MaxStateCount, string Mdx, string Xsl , ref byte UndoCount)
{
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[5];
parameters[0]=new SqlParameter("@ReportId" , ReportId);
parameters[1]=new SqlParameter("@MaxStateCount" , MaxStateCount);
parameters[2]=new SqlParameter("@Mdx" , Mdx);
parameters[3]=new SqlParameter("@Xsl" , Xsl);
parameters[4]=new SqlParameter("@UndoCount" , UndoCount);
parameters[4].Direction=ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_SaveMdxReportState" , CommandType.StoredProcedure , parameters , null);
if(parameters[4].Value!=System.DBNull.Value)
UndoCount=(byte)parameters[4].Value;
}
public FI.Common.Data.FIDataTable ReadUser(string CompanyNameShort, string Logon, string Password)
{
FI.Common.Data.FIDataTable dataTable=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[3];
parameters[0]=new SqlParameter("@CompanyNameShort" , CompanyNameShort);
parameters[1]=new SqlParameter("@Logon" , Logon);
parameters[2]=new SqlParameter("@Password" , Password);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadUserByAuthentication" , CommandType.StoredProcedure , parameters , dataTable);
return dataTable;
}
public FI.Common.Data.FIDataTable GetDistributionsWithContactsPage(Report report, int CurrentPage , int RowCount , string FilterExpression , string SortExpression)
{
int StartIndex=(CurrentPage-1)*RowCount;
FI.Common.DataAccess.IDistributionsDA dacObj=DataAccessFactory.Instance.GetDistributionsDA();
FI.Common.Data.FIDataTable table=new FI.Common.Data.FIDataTable();
table=dacObj.ReadDistributionsWithContactsPage(_owner.ID, report.ID , report.GetTypeCode() , StartIndex , RowCount , FilterExpression , SortExpression);
ConvertEnums(ref table);
return table;
}
public FI.Common.Data.FIDataTable ReadUsers(decimal CompanyId)
{
FI.Common.Data.FIDataTable dataTable=new FI.Common.Data.FIDataTable();
SqlParameter[] parameters=new SqlParameter[1];
parameters[0]=new SqlParameter("@CompanyId" , CompanyId);
DataBase.Instance.ExecuteCommand("dbo.sproc_LoadUsers" , CommandType.StoredProcedure , parameters , dataTable);
return dataTable;
}
public FI.Common.Data.FIDataTable ReadReportResult(string Sql , string Database)
{
FI.Common.Data.FIDataTable data=new FI.Common.Data.FIDataTable();
string connString=FI.Common.AppConfig.DA_OltpConnectionString.Replace("@DATABASE" , Database);
DataBase.Instance.ExecuteCommand(Sql , connString , CommandType.Text , null , data);
return data;
}
