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_DeleteOlapReport" , CommandType.StoredProcedure , parameters , null);
}
public override void LoadHeader()
{
FI.Common.DataAccess.ICustomMdxReportsDA dacObj = DataAccessFactory.Instance.GetCustomMdxReportsDA();
FI.Common.Data.FIDataTable dataTable = dacObj.ReadReportHeader(this.Owner.ID, this.ID);
this._parentReportId = (decimal)dataTable.Rows[0]["parent_report_id"];
this._name = (string)dataTable.Rows[0]["name"];
this._description = (string)dataTable.Rows[0]["description"];
this._isSelected = (bool)dataTable.Rows[0]["is_selected"];
this._sharing = (Report.SharingEnum)((byte)dataTable.Rows[0]["sharing_status"]);
this._maxSubscriberSharing = (Report.SharingEnum)((byte)dataTable.Rows[0]["max_subscriber_sharing_status"]);
}
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);
// might throw exception
DataBase.Instance.ExecuteCommand("dbo.sproc_DeleteOlapReport", CommandType.StoredProcedure, parameters, null);
DeleteReportCache(ReportId);
}
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_DeleteSharedOlapReport" , CommandType.StoredProcedure , parameters , null);
if(parameters[2].Value!=System.DBNull.Value)
MaxSubscriberSharingStatus=(short)parameters[2].Value;
}
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_DeleteSharedOlapReport", CommandType.StoredProcedure, parameters, null);
if (parameters[2].Value != System.DBNull.Value)
{
MaxSubscriberSharingStatus = (short)parameters[2].Value;
}
}
public void SaveState(decimal ReportId, byte MaxStateCount, string ReportXml, ref byte UndoCount)
{
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("@MaxStateCount", MaxStateCount);
parameters[2] = new SqlParameter("@ReportXml", ReportXml);
parameters[3] = new SqlParameter("@UndoCount", UndoCount);
parameters[3].Direction = ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_SaveOlapReportState", CommandType.StoredProcedure, parameters, null);
if (parameters[3].Value != System.DBNull.Value)
{
UndoCount = (byte)parameters[3].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_InsertSharedOlapReport" , CommandType.StoredProcedure , parameters , null);
if(parameters[3].Value!=System.DBNull.Value)
reportId=(decimal)parameters[3].Value;
return reportId;
}
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_InsertSharedOlapReport", CommandType.StoredProcedure, parameters, null);
if (parameters[3].Value != System.DBNull.Value)
{
reportId = (decimal)parameters[3].Value;
}
return(reportId);
}
public string ExportToCSV()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
FI.Common.Data.FIDataTable notDelivered = this.GetReportPage(ReportPageTypeEnum.NotDelivered, 0, 50000, this.FilterExpression, "");
FI.Common.Data.FIDataTable neverDelivered = this.GetReportPage(ReportPageTypeEnum.NeverDelivered, 0, 50000, this.FilterExpression, "");
// not delivered
if (notDelivered != null)
{
sb.Append("\r\nNo Transactions Within ");
sb.Append(this.Days);
sb.Append(" Days:\r\n");
// -- headers
sb.Append("Salesman\t");
sb.Append("Central Chain\t");
sb.Append("Chain\t");
sb.Append("Store Name\t");
sb.Append("Store City\t");
sb.Append("Store Postal Code\t");
sb.Append("Store Address\t");
sb.Append("Last Transaction\t");
sb.Append("\r\n");
// -- headers
foreach (System.Data.DataRow row in notDelivered.Rows)
{
sb.Append(row["SALMNAME"] + "\t");
sb.Append(row["CCHNAME"] + "\t");
sb.Append(row["CHNNAME"] + "\t");
sb.Append(row["COMNAME"] + "\t");
sb.Append(row["COMCITY"] + "\t");
sb.Append(row["COMPCODE"] + "\t");
sb.Append(row["COMADDR"] + "\t");
sb.Append(row["DELDATE"] + "\t");
sb.Append("\r\n");
}
}
// -- never delivered
if (neverDelivered != null)
{
sb.Append("\r\nNo Transactions Found\r\n");
// -- headers
sb.Append("Salesman\t");
sb.Append("Central Chain\t");
sb.Append("Chain\t");
sb.Append("Store Name\t");
sb.Append("Store City\t");
sb.Append("Store Postal Code\t");
sb.Append("Store Address\t");
sb.Append("Last Transaction\t");
sb.Append("\r\n");
// -- headers
foreach (System.Data.DataRow row in neverDelivered.Rows)
{
sb.Append(row["SALMNAME"] + "\t");
sb.Append(row["CCHNAME"] + "\t");
sb.Append(row["CHNNAME"] + "\t");
sb.Append(row["COMNAME"] + "\t");
sb.Append(row["COMCITY"] + "\t");
sb.Append(row["COMPCODE"] + "\t");
sb.Append(row["COMADDR"] + "\t");
sb.Append(row["DELDATE"] + "\t");
sb.Append("\r\n");
}
}
return(sb.ToString());
}
public string ExportToHTML()
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
FI.Common.Data.FIDataTable notDelivered = this.GetReportPage(ReportPageTypeEnum.NotDelivered, 0, 50000, this.FilterExpression, "");
FI.Common.Data.FIDataTable neverDelivered = this.GetReportPage(ReportPageTypeEnum.NeverDelivered, 0, 50000, this.FilterExpression, "");
// style
sb.Append(@"
<html><head><meta charset='utf-8'></meta>
<style>
.tbl1_H {
text-align:left; PADDING-RIGHT: 2pt; border: solid 1px #aaaaaa; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; COLOR: black; font-face: tahoma ; ; padding-top: 1px ; padding-bottom: 1px; BACKGROUND-COLOR: #e0e0e0;
}
.tbl1_H1 {
text-align:left; PADDING-RIGHT: 2pt; border: solid 1px #aaaaaa; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; COLOR: black; FONT-WEIGHT: bold; font-face: tahoma ; ; padding-top: 1px ; padding-bottom: 1px; BACKGROUND-COLOR: #e0e0e0;
}
.tbl1_H2 {
text-align:center; PADDING-RIGHT: 2pt; border: solid 1px #aaaaaa; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; COLOR: black; font-face: tahoma ; padding-top: 1px ; padding-bottom: 1px; BACKGROUND-COLOR: #e0e0e0;
}
.tbl1_H3 {
text-align:center; PADDING-RIGHT: 2pt; border: solid 1px #aaaaaa; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; COLOR: black; FONT-WEIGHT: bold; font-face: tahoma ; ; padding-top: 1px ; padding-bottom: 1px; BACKGROUND-COLOR: #e0e0e0;
}
.tbl1_HC {
background-color:#FFFFC0; FONT-SIZE: 8pt; font-face: tahoma;PADDING-LEFT: 2pt;PADDING-RIGHT: 2pt;
}
.tbl1_T {
border-collapse:collapse;
}
.tbl1_C {
text-align:right; PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; BACKGROUND-COLOR: white; font-face: tahoma; padding-top: 1px ; padding-bottom: 1px; border: solid 1px #888888;
}
.tbl1_C1 {
text-align:left; PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; BACKGROUND-COLOR: white; font-face: tahoma; padding-top: 1px ; padding-bottom: 1px; border: solid 1px #888888;
}
.tbl1_C2 {
text-align:right; PADDING-RIGHT: 2pt; PADDING-LEFT: 6pt; FONT-SIZE: 8pt; BACKGROUND-COLOR: white; font-face: tahoma; padding-top: 1px ; padding-bottom: 1px; border: solid 1px #888888;
}
</style>
</head>
<body>
" );
// ----- HEADER ------
sb.Append(@"
<table cellspacing=0 cellpadding=3 width=100% class=capt><tr><td align=right style='BORDER-WIDTH:0px;background-color:red'>
<font face=ArialBlack color=white size=1><b><i>© FieldForce Solutions </b></i></font>
</tr></td>
<tr><td style='BACKGROUND:#e0e0e0; BORDER-WIDTH:0px;'>
<B><font color=red size=-1>" + this.Description + @"</font></B><font color=black size=-2><i> (description)</i></font>
<br>
<B><font color=4682b4 size=-2>" + this.Name + @"</font></B><font color=black size=-2><i> (name)</i></font>
<BR>
<B><font color=4682b4 size=-2>" + this.Owner.Name + @"</font></B><font color=black size=-2><i> (owner)</i></font>
<BR>
<B><font color=4682b4 size=-2>" + System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToShortTimeString() + @"</font></B><font color=black size=-2><i> (distributed)</i></font>
</td></tr>
</table>
<hr>
" );
// not delivered
if (notDelivered != null)
{
sb.Append("<br><div class=capt><font color=red><b>No Transactions Within ");
sb.Append(this.Days);
sb.Append(" Days:</b></font></div><br>");
sb.Append("<table>");
// -- headers
sb.Append("<tr>");
sb.Append("<td class=tbl1_H>Salesman</td>");
sb.Append("<td class=tbl1_H>Central Chain</td>");
sb.Append("<td class=tbl1_H>Chain</td>");
sb.Append("<td class=tbl1_H>Store Name</td>");
sb.Append("<td class=tbl1_H>Store City</td>");
sb.Append("<td class=tbl1_H>Store Postal Code</td>");
sb.Append("<td class=tbl1_H>Store Address</td>");
sb.Append("<td class=tbl1_H>Last Transaction</td>");
sb.Append("</tr>");
// -- headers
foreach (System.Data.DataRow row in notDelivered.Rows)
{
sb.Append("<tr>");
sb.Append("<td class=tbl1_C>" + row["SALMNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["CCHNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["CHNNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMCITY"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMPCODE"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMADDR"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["DELDATE"] + "</td>");
sb.Append("</tr>");
}
sb.Append("</table>");
}
// -- never delivered
if (neverDelivered != null)
{
sb.Append("<br><div class=capt><font color=red><b>No Transactions Found");
sb.Append("</b></font></div><br>");
sb.Append("<table>");
// -- headers
sb.Append("<tr>");
sb.Append("<td class=tbl1_H>Salesman</td>");
sb.Append("<td class=tbl1_H>Central Chain</td>");
sb.Append("<td class=tbl1_H>Chain</td>");
sb.Append("<td class=tbl1_H>Store Name</td>");
sb.Append("<td class=tbl1_H>Store City</td>");
sb.Append("<td class=tbl1_H>Store Postal Code</td>");
sb.Append("<td class=tbl1_H>Store Address</td>");
sb.Append("<td class=tbl1_H>Last Transaction</td>");
sb.Append("</tr>");
// -- headers
foreach (System.Data.DataRow row in neverDelivered.Rows)
{
sb.Append("<tr>");
sb.Append("<td class=tbl1_C>" + row["SALMNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["CCHNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["CHNNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMNAME"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMCITY"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMPCODE"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["COMADDR"] + "</td>");
sb.Append("<td class=tbl1_C>" + row["DELDATE"] + "</td>");
sb.Append("</tr>");
}
sb.Append("</table>");
}
sb.Append("</body></html>");
return(sb.ToString());
}
public void SaveState(decimal ReportId, byte MaxStateCount, string ReportXml , ref byte UndoCount)
{
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("@MaxStateCount" , MaxStateCount);
parameters[2]=new SqlParameter("@ReportXml" , ReportXml);
parameters[3]=new SqlParameter("@UndoCount" , UndoCount);
parameters[3].Direction=ParameterDirection.Output;
DataBase.Instance.ExecuteCommand("dbo.sproc_SaveOlapReportState" , CommandType.StoredProcedure , parameters , null);
if(parameters[3].Value!=System.DBNull.Value)
UndoCount=(byte)parameters[3].Value;
}
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 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_LoadOlapReportHeaders" , CommandType.StoredProcedure , parameters , data);
return data;
}
public string LoadState(decimal ReportId, short StateCode , 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_LoadOlapReportState" , 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;
return (string)data.Rows[0][0];
}