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[] rsDirectDocumentPrint(String ReportSQL, string UserName, int DocumentId = 0, string ReportFileType = ReportFileTypeConstants.PDF) { string ReportName = ""; List <ReportParameter> Params = new List <ReportParameter>(); ReportParameter Param = new ReportParameter(); Param.Name = "Id"; Param.Values.Add(DocumentId.ToString()); Params.Add(Param); 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()) { ReportUIDValues rid = new ReportUIDValues(); rid.UID = uid; rid.Type = "Id"; rid.Value = Params.Where(m => m.Name == "Id").FirstOrDefault().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); }
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); }
public static string CreateFilesFromSQLReporting(String ReportName, string FileName, string FilterParameter, string UserNameValue, string Parameters, string ParameterValue) { List <ReportParameter> Params = new List <ReportParameter>(); string[] ParameterList = Parameters.Split(','); string[] ParameterValueList = ParameterValue.Split(','); int Length = 0; foreach (string item in ParameterList) { ReportParameter Parameter = new ReportParameter(); Parameter.Name = ParameterList[Length]; Parameter.Values.Add(ParameterValueList[Length]); Params.Add(Parameter); Length = Length + 1; } ReportParameter UserName = new ReportParameter(); UserName.Name = "PrintedBy"; UserName.Values.Add(UserNameValue); Params.Add(UserName); ReportParameter ConString = new ReportParameter(); ConString.Name = "DatabaseConnectionString"; ConString.Values.Add(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString.ToString()); Params.Add(ConString); ReportGenerateService c = new ReportGenerateService(); string mimetype = ""; string ReportFileName = ""; ReportFileName = FileName; try { string Filename = GenerateDocument(c.ReportGenerateCustom(out mimetype, ReportFileTypeConstants.PDF, "Satyam.Tripathi", Params, ReportName), ReportFileName + "_" + DateTime.Now.ToString("dd-MMM-yyyy-HH_mm_ss"), mimetype); return(Filename); } catch (Exception ex) { using (StreamWriter writer = new StreamWriter(@"c:\temp\Cachecallback.txt", true)) { writer.WriteLine("Excp:" + ex.Message + "EX2:" + ex.InnerException.Message + "EX3:" + ex.InnerException.InnerException.Message + "EX4:" + ex.InnerException.InnerException.InnerException.Message); writer.Close(); } return(ex.Message); } }