public ActionResult ProcedureExcute(FormCollection form, string ReportFileType)
{
DataTable ReportData = new DataTable();
Dictionary <string, string> ReportFilters = new Dictionary <string, string>();
StringBuilder queryString = new StringBuilder();
string ReportHeaderId = (form["ReportHeaderId"].ToString());
ReportHeader header = new ReportHeaderService(_unitOfWork).GetReportHeader(Convert.ToInt32(ReportHeaderId));
List <ReportLine> lines = _ReportLineService.GetReportLineList(header.ReportHeaderId).ToList();
ApplicationDbContext Db = new ApplicationDbContext();
queryString.Append(db.strSchemaName + "." + header.SqlProc);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(queryString.ToString(), sqlConnection);
foreach (var item in lines)
{
if (item.SqlParameter != "" && item.SqlParameter != null)
{
if (item.SqlParameter == "@LoginSite" || item.SqlParameter == "@LoginDivision")
{
if (item.SqlParameter == "@LoginSite")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginSiteId]);
}
else if (item.SqlParameter == "@LoginDivision")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginDivisionId]);
}
}
else if (item.FieldName == "Site" && form[item.FieldName].ToString() == "")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginSiteId]);
}
else if (item.FieldName == "Division" && form[item.FieldName].ToString() == "")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginDivisionId]);
}
else
{
if (form[item.FieldName].ToString() != "")
{
if (item.DataType == "Date")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (form[item.FieldName].ToString() != "" ? String.Format("{0:MMMM dd yyyy}", form[item.FieldName].ToString()) : "Null"));
}
else
{
cmd.Parameters.AddWithValue(item.SqlParameter, (form[item.FieldName].ToString() != "" ? form[item.FieldName].ToString() : "Null"));
}
}
}
}
}
cmd.CommandTimeout = 200;
SqlDataAdapter sqlDataAapter = new SqlDataAdapter(cmd);
sqlDataAapter.SelectCommand.CommandType = CommandType.StoredProcedure;
dsRep.EnforceConstraints = false;
sqlDataAapter.Fill(ReportData);
}
var Paralist = new List <string>();
foreach (var item in lines)
{
if (item.SqlParameter == "@LoginSite" || item.SqlParameter == "@LoginDivision")
{
}
else
{
if (item.SqlParameter != "" && item.SqlParameter != null && form[item.FieldName].ToString() != "")
{
if (item.DataType == "Date")
{
if (!string.IsNullOrEmpty(form[item.FieldName].ToString()))
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName].ToString());
}
}
else if (item.DataType == "Single Select")
{
if (!string.IsNullOrEmpty(item.ListItem))
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName].ToString());
}
else if (!string.IsNullOrEmpty(form[item.FieldName].ToString()))
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName + "Names"].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName + "Names"].ToString());
}
}
else if (item.DataType == "Multi Select")
{
if (form[item.FieldName].ToString() != "")
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName + "Names"].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName + "Names"].ToString());
}
}
else
{
if (form[item.FieldName].ToString() != "")
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName].ToString());
}
}
}
}
}
XElement s = new XElement(CustomStringOp.CleanCode(header.ReportName));
XElement Name = new XElement("Filters");
foreach (var Rec in ReportFilters)
{
Name.Add(new XElement(CustomStringOp.CleanCode(Rec.Key), Rec.Value));
}
s.Add(Name);
LogActivity.LogActivityDetail(LogVm.Map(new ActiivtyLogViewModel
{
DocTypeId = new DocumentTypeService(_unitOfWork).FindByName(TransactionDoctypeConstants.Report).DocumentTypeId,
DocId = header.ReportHeaderId,
Narration = header.ReportName,
ActivityType = (int)ActivityTypeContants.Report,
xEModifications = s,
}));
if (ReportData.Rows.Count > 0)
{
if (ReportData.Rows[0][0].ToString() == "Success")
{
return(View("Success"));
}
else
{
ViewBag.Error = ReportData.Rows[0][0].ToString();
return(View("Error"));
}
}
else
{
ViewBag.Message = "No Record to Print.";
return(View("Close"));
}
}
public ActionResult ReportPrint(FormCollection form, string ReportFileType)
{
var SubReportDataList = new List <DataTable>();
var SubReportNameList = new List <string>();
DataTable ReportData = new DataTable();
Dictionary <string, string> ReportFilters = new Dictionary <string, string>();
StringBuilder queryString = new StringBuilder();
string ReportHeaderId = (form["ReportHeaderId"].ToString());
ReportHeader header = _ReportHeaderService.GetReportHeader(Convert.ToInt32(ReportHeaderId));
List <ReportLineViewModel> lines = _ReportLineService.GetReportLineList(header.ReportHeaderId).ToList();
if (string.IsNullOrEmpty(header.ReportSQL))
{
List <string> SubReportProcList = new List <string>();
queryString.Append(header.SqlProc);
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(queryString.ToString(), sqlConnection);
foreach (var item in lines)
{
if (item.SqlParameter != "" && item.SqlParameter != null)
{
if (item.SqlParameter == "@LoginSite" || item.SqlParameter == "@LoginDivision")
{
if (item.SqlParameter == "@LoginSite")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginSiteId]);
}
//cmd.Parameters.AddWithValue(item.SqlParameter, 17);
else if (item.SqlParameter == "@LoginDivision")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginDivisionId]);
}
}
else if (item.FieldName == "Site" && form[item.FieldName].ToString() == "")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginSiteId]);
}
else if (item.FieldName == "Division" && form[item.FieldName].ToString() == "")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (int)System.Web.HttpContext.Current.Session[SessionNameConstants.LoginDivisionId]);
}
else
{
if (form[item.FieldName].ToString() != "")
{
if (item.DataType == "Date")
{
cmd.Parameters.AddWithValue(item.SqlParameter, (form[item.FieldName].ToString() != "" ? String.Format("{0:MMMM dd yyyy}", form[item.FieldName].ToString()) : "Null"));
}
else
{
cmd.Parameters.AddWithValue(item.SqlParameter, (form[item.FieldName].ToString() != "" ? form[item.FieldName].ToString() : "Null"));
}
}
}
}
}
cmd.CommandTimeout = 200;
SqlDataAdapter sqlDataAapter = new SqlDataAdapter(cmd);
sqlDataAapter.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDataAapter.Fill(ReportData);
if (ReportData.Rows.Count > 0)
{
if (ReportData.Columns.Contains("SubReportProcList"))
{
SubReportProcList.Add(ReportData.Rows[0]["SubReportProcList"].ToString());
}
DataTable SubRepData = new DataTable();
String SubReportProc;
if (SubReportProcList != null)
{
if (SubReportProcList.Count > 0)
{
SubRepData = ReportData.Copy();
SqlConnection Con = new SqlConnection(connectionString);
while (SubRepData.Rows.Count > 0 && SubRepData.Columns.Contains("SubReportProcList"))
{
SubReportProc = SubRepData.Rows[0]["SubReportProcList"].ToString();
if (SubReportProc != "")
{
String query = "Web." + SubReportProc;
SqlDataAdapter sqlDataAapter1 = new SqlDataAdapter(query.ToString(), Con);
sqlDataAapter1.SelectCommand.CommandTimeout = 200;
SubRepData.Reset();
sqlDataAapter1.Fill(SubRepData);
DataTable SubDataTable = new DataTable();
SubDataTable = SubRepData.Copy();
string SubRepName = "";
if (SubDataTable.Rows.Count > 0)
{
SubReportDataList.Add(SubDataTable);
SubRepName = (string)SubDataTable.Rows[0]["ReportName"];
SubReportNameList.Add(SubRepName);
}
SubDataTable.Dispose();
}
else
{
//SubRepData = null;
break;
}
}
}
}
}
}
if (ReportData.Rows.Count > 0)
{
var Paralist = new List <string>();
foreach (var item in lines)
{
if (item.SqlParameter == "@LoginSite" || item.SqlParameter == "@LoginDivision")
{
}
else
{
if (item.SqlParameter != "" && item.SqlParameter != null && form[item.FieldName].ToString() != "")
{
if (item.DataType == "Date")
{
if (!string.IsNullOrEmpty(form[item.FieldName].ToString()))
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName].ToString());
}
}
else if (item.DataType == "Single Select")
{
if (!string.IsNullOrEmpty(item.ListItem))
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName].ToString());
}
else if (!string.IsNullOrEmpty(form[item.FieldName].ToString()))
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName + "Names"].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName + "Names"].ToString());
}
}
//else if (item.DataType == "Constant Value")
//{
// //if (!string.IsNullOrEmpty(form[item.FieldName].ToString())) { Paralist.Add(item.DisplayName + " : " + form[item.FieldName + "Names"].ToString()); }
//}
else if (item.DataType == "Multi Select")
{
if (form[item.FieldName].ToString() != "")
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName + "Names"].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName + "Names"].ToString());
}
}
else
{
if (form[item.FieldName].ToString() != "")
{
Paralist.Add(item.DisplayName + " : " + form[item.FieldName].ToString()); ReportFilters.Add(item.DisplayName, form[item.FieldName].ToString());
}
}
}
}
}
string mimtype;
ReportGenerateService c = new ReportGenerateService();
byte[] BAR;
//BAR = c.ReportGenerate(ReportData, out mimtype, ReportFileType, Paralist, SubReportDataList);
BAR = c.ReportGenerate(ReportData, out mimtype, ReportFileType, Paralist, SubReportDataList, null, SubReportNameList, User.Identity.Name);
XElement s = new XElement(CustomStringOp.CleanCode(header.ReportName));
XElement Name = new XElement("Filters");
foreach (var Rec in ReportFilters)
{
Name.Add(new XElement(CustomStringOp.CleanCode(Rec.Key), Rec.Value));
}
s.Add(Name);
_logger.LogActivityDetail(LogVm.Map(new ActiivtyLogViewModel
{
DocTypeId = _documentTypeService.Find(TransactionDoctypeConstants.Report).DocumentTypeId,
DocId = header.ReportHeaderId,
ActivityType = (int)ActivityTypeContants.Report,
xEModifications = s,
}));
if (BAR.Length == 1)
{
ViewBag.Message = "Report Name is not define.";
return(View("Close"));
}
else if (BAR.Length == 2)
{
ViewBag.Message = "Report Title is not define.";
return(View("Close"));
}
else
{
//if (mimtype != "application/pdf")
if (mimtype == "application/vnd.ms-excel")
{
return(File(BAR, mimtype, header.ReportName + ".xls"));
}
else
{
return(File(BAR, mimtype));
}
}
}
else
{
ViewBag.Message = "No Record to Print.";
return(View("Close"));
}
}
else
{
List <ReportParameter> Params = new List <ReportParameter>();
string ReportName = "";
foreach (var item in lines)
{
if (item.SqlParameter != "" && item.SqlParameter != null)
{
if (item.SqlParameter == "@LoginSite" || item.SqlParameter == "@LoginDivision")
{
if (item.SqlParameter == "@LoginSite")
{
ReportParameter Param = new ReportParameter();
Param.Name = CustomStringOp.CleanCode(item.SqlParameter);
Param.Values.Add(Convert.ToString(System.Web.HttpContext.Current.Session[SessionNameConstants.LoginSiteId]));
Params.Add(Param);
}
else if (item.SqlParameter == "@LoginDivision")
{
ReportParameter Param = new ReportParameter();
Param.Name = CustomStringOp.CleanCode(item.SqlParameter);
Param.Values.Add(Convert.ToString(System.Web.HttpContext.Current.Session[SessionNameConstants.LoginDivisionId]));
Params.Add(Param);
}
}
else if (item.FieldName == "Site" && form[item.FieldName].ToString() == "")
{
ReportParameter Param = new ReportParameter();
Param.Name = CustomStringOp.CleanCode(item.SqlParameter);
Param.Values.Add(Convert.ToString(System.Web.HttpContext.Current.Session[SessionNameConstants.LoginSiteId]));
Params.Add(Param);
}
else if (item.FieldName == "Division" && form[item.FieldName].ToString() == "")
{
ReportParameter Param = new ReportParameter();
Param.Name = CustomStringOp.CleanCode(item.SqlParameter);
Param.Values.Add(Convert.ToString(System.Web.HttpContext.Current.Session[SessionNameConstants.LoginDivisionId]));
Params.Add(Param);
}
else
{
if (form[item.FieldName].ToString() != "")
{
if (item.DataType == "Date")
{
ReportParameter Param = new ReportParameter();
Param.Name = CustomStringOp.CleanCode(item.SqlParameter);
Param.Values.Add((form[item.FieldName].ToString() != "" ? String.Format("{0:MMMM dd yyyy}", form[item.FieldName].ToString()) : "Null"));
Params.Add(Param);
}
else
{
ReportParameter Param = new ReportParameter();
Param.Name = CustomStringOp.CleanCode(item.SqlParameter);
Param.Values.Add((form[item.FieldName].ToString() != "" ? form[item.FieldName].ToString() : "Null"));
Params.Add(Param);
}
}
}
}
}
int i = 0;
foreach (var item in lines)
{
if (item.SqlParameter == "@LoginSite" || item.SqlParameter == "@LoginDivision")
{
}
else
{
if (item.SqlParameter != "" && item.SqlParameter != null && form[item.FieldName].ToString() != "")
{
if (item.DataType == "Date")
{
if (!string.IsNullOrEmpty(form[item.FieldName].ToString()))
{
ReportParameter Param = new ReportParameter();
Param.Name = "FilterStr" + ++i;
Param.Values.Add(item.DisplayName + " : " + form[item.FieldName].ToString());
Params.Add(Param);
ReportFilters.Add(item.DisplayName, form[item.FieldName].ToString());
}
}
else if (item.DataType == "Single Select")
{
if (!string.IsNullOrEmpty(item.ListItem) && !string.IsNullOrEmpty(form[item.FieldName]))
{
ReportParameter Param = new ReportParameter();
Param.Name = "FilterStr" + ++i;
Param.Values.Add(item.DisplayName + " : " + form[item.FieldName].ToString());
Params.Add(Param);
}
else if (!string.IsNullOrEmpty(form[item.FieldName].ToString()) && !string.IsNullOrEmpty(form[item.FieldName + "Names"]))
{
ReportParameter Param = new ReportParameter();
Param.Name = "FilterStr" + ++i;
Param.Values.Add(item.DisplayName + " : " + form[item.FieldName + "Names"].ToString());
Params.Add(Param);
ReportFilters.Add(item.DisplayName, form[item.FieldName + "Names"].ToString());
}
}
else if (item.DataType == "Multi Select")
{
if (form[item.FieldName].ToString() != "" && !string.IsNullOrEmpty(form[item.FieldName + "Names"]))
{
ReportParameter Param = new ReportParameter();
Param.Name = "FilterStr" + ++i;
Param.Values.Add(item.DisplayName + " : " + form[item.FieldName + "Names"].ToString());
Params.Add(Param);
ReportFilters.Add(item.DisplayName, form[item.FieldName + "Names"].ToString());
}
}
else
{
if (form[item.FieldName].ToString() != "" && !string.IsNullOrEmpty(form[item.FieldName]))
{
ReportParameter Param = new ReportParameter();
Param.Name = "FilterStr" + ++i;
Param.Values.Add(item.DisplayName + " : " + form[item.FieldName].ToString());
Params.Add(Param);
ReportFilters.Add(item.DisplayName, form[item.FieldName].ToString());
}
}
}
}
}
var uid = Guid.NewGuid();
_reportUidValuesService.InsertRange(Params, uid);
ReportName = _ReportHeaderService.GetReportNameFromProcedure(header.ReportSQL.Replace("REPORTUID", uid.ToString()));
_reportUidValuesService.DeleteRange(uid);
ReportParameter UserName = new ReportParameter();
UserName.Name = "PrintedBy";
UserName.Values.Add(User.Identity.Name);
Params.Add(UserName);
ReportParameter ConString = new ReportParameter();
ConString.Name = "DatabaseConnectionString";
ConString.Values.Add(connectionString);
//Data Source=192.168.2.17;Initial Catalog=RUG;Integrated Security=false; User Id=sa; pwd=
Params.Add(ConString);
string mimtype;
ReportGenerateService c = new ReportGenerateService();
byte[] BAR;
BAR = c.ReportGenerateCustom(out mimtype, ReportFileType, User.Identity.Name, Params, ReportName);
XElement s = new XElement(CustomStringOp.CleanCode(header.ReportName));
XElement Name = new XElement("Filters");
foreach (var Rec in ReportFilters)
{
Name.Add(new XElement(CustomStringOp.CleanCode(Rec.Key), Rec.Value));
}
s.Add(Name);
_logger.LogActivityDetail(LogVm.Map(new ActiivtyLogViewModel
{
DocTypeId = _documentTypeService.Find(TransactionDoctypeConstants.Report).DocumentTypeId,
DocId = header.ReportHeaderId,
ActivityType = (int)ActivityTypeContants.Report,
}));
if (BAR.Length == 1)
{
ViewBag.Message = "Report Name is not define.";
return(View("Close"));
}
else if (BAR.Length == 2)
{
ViewBag.Message = "Report Title is not define.";
return(View("Close"));
}
else
{
if (mimtype == "application/vnd.ms-excel")
{
return(File(BAR, mimtype, header.ReportName + ".xls"));
}
else
{
return(File(BAR, mimtype));
}
}
}
}
public byte[] rsDirectDocumentPrintDisplay(String ReportSQL, string Route, string UserName, int DocumentId = 0, string ReportFileType = ReportFileTypeConstants.PDF)
{
ApplicationDbContext DB = new ApplicationDbContext();
string ReportName = "";
List <ReportParameter> Params = new List <ReportParameter>();
var Settings = DB.StockInHandSetting.Where(m => m.ProductTypeId == DocumentId && m.UserName == UserName && m.TableName == Route).FirstOrDefault();
if (Settings == null)
{
Settings = DB.StockInHandSetting.Where(m => m.ProductTypeId == DocumentId && m.TableName == Route).FirstOrDefault();
}
ReportParameter Param = new ReportParameter();
Param.Name = CustomStringOp.CleanCode("@GroupOn");
Param.Values.Add(Settings.GroupOn);
Params.Add(Param);
ReportParameter Param1 = new ReportParameter();
Param1.Name = CustomStringOp.CleanCode("@Site");
Param1.Values.Add(Settings.SiteIds);
Params.Add(Param1);
ReportParameter Param2 = new ReportParameter();
Param2.Name = CustomStringOp.CleanCode("@FromDate");
Param2.Values.Add((Settings.FromDate.ToString() != "" ? String.Format("{0:MMMM dd yyyy}", Settings.FromDate.ToString()) : "Null").ToString());
Params.Add(Param2);
ReportParameter Param3 = new ReportParameter();
Param3.Name = CustomStringOp.CleanCode("@ToDate");
Param3.Values.Add((Settings.ToDate.ToString() != "" ? String.Format("{0:MMMM dd yyyy}", Settings.ToDate.ToString()) : "Null").ToString());
Params.Add(Param3);
ReportParameter Param4 = new ReportParameter();
Param4.Name = CustomStringOp.CleanCode("@ProductType");
Param4.Values.Add(DocumentId.ToString());
Params.Add(Param4);
ReportParameter Param5 = new ReportParameter();
Param5.Name = CustomStringOp.CleanCode("@ShowBalance");
Param5.Values.Add(Settings.ShowBalance.ToString());
Params.Add(Param5);
ReportParameter Param6 = new ReportParameter();
Param6.Name = CustomStringOp.CleanCode("@ShowOpening");
Param6.Values.Add((Settings.ShowOpening == true ? 1 : 0).ToString());
Params.Add(Param6);
ReportParameter Param7 = new ReportParameter();
Param7.Name = CustomStringOp.CleanCode("@TableName");
Param7.Values.Add(Settings.TableName);
Params.Add(Param7);
ReportParameter rpUserName = new ReportParameter();
rpUserName.Name = "PrintedBy";
rpUserName.Values.Add(UserName);
Params.Add(rpUserName);
ReportParameter ConString = new ReportParameter();
ConString.Name = "DatabaseConnectionString";
ConString.Values.Add((string)System.Web.HttpContext.Current.Session["DefaultConnectionString"]);
//Data Source=192.168.2.17;Initial Catalog=RUG;Integrated Security=false; User Id=sa; pwd=
Params.Add(ConString);
var uid = Guid.NewGuid();
using (ApplicationDbContext context = new ApplicationDbContext())
{
foreach (var item in Params)
{
ReportUIDValues rid = new ReportUIDValues();
rid.UID = uid;
rid.Type = item.Name;
rid.Value = item.Values[0];
rid.ObjectState = Model.ObjectState.Added;
context.ReportUIDValues.Add(rid);
}
context.SaveChanges();
}
using (ApplicationDbContext context = new ApplicationDbContext())
{
ReportName = context.Database.SqlQuery <string>(ReportSQL.Replace("REPORTUID", uid.ToString())).FirstOrDefault();
var Items = context.ReportUIDValues.Where(m => m.UID == uid).ToList();
foreach (var item in Items)
{
item.ObjectState = Model.ObjectState.Deleted;
context.ReportUIDValues.Remove(item);
}
context.SaveChanges();
}
string mimtype;
ReportGenerateService c = new ReportGenerateService();
byte[] BAR;
BAR = c.ReportGenerateCustom(out mimtype, ReportFileType, UserName, Params, ReportName);
return(BAR);
//if (mimtype == "application/vnd.ms-excel")
// return File(BAR, mimtype, ReportName + ".xls");
//else
// return File(BAR, mimtype);
}
