public JsonResult GetRegionTypes() { DAL_Reports_CategoryWiseDrVisitAnalysis objDAL = new DAL_Reports_CategoryWiseDrVisitAnalysis(); DataControl.CurrentInfo _objcurrentInfo = new DataControl.CurrentInfo(); string companyCode = _objcurrentInfo.GetCompanyCode(); dsRegionType = objDAL.GetRegionTypes(companyCode); DataRow[] rowFilter; rowFilter = dsRegionType.Tables[0].Select("Region_Type_Code = Under_Region_Type"); if (rowFilter.Length > 0) { foreach (DataRow dr in rowFilter) { regionTypeLevelNo = 1; dr["Level_No"] = regionTypeLevelNo.ToString(); dsRegionType.Tables[0].AcceptChanges(); } foreach (DataRow dr in rowFilter) { GenerateRegionTypeLevel(dr["Region_Type_Code"].ToString()); } } DataSet ds = new DataSet(); string regionCode = string.Empty; string regionTypeCode = string.Empty; regionCode = _objcurrentInfo.GetRegionCode(); regionTypeCode = objDAL.GetRegionTypeCode(companyCode, regionCode); ds = dsRegionType.Clone(); rowFilter = dsRegionType.Tables[0].Select("Region_Type_Code = '" + regionTypeCode + "'"); if (rowFilter.Length > 0) { regionTypeLevelNo = Convert.ToInt16(rowFilter[0]["Level_No"].ToString()); rowFilter = dsRegionType.Tables[0].Select("Level_No >= '" + regionTypeLevelNo + "'"); foreach (DataRow dr in rowFilter) { ds.Tables[0].ImportRow(dr); ds.Tables[0].AcceptChanges(); } } DataControl.JSONConverter json = new DataControl.JSONConverter(); return(Json(json.Serialize(ds), JsonRequestBehavior.AllowGet)); }
public string ShowCategoryWiseDrVisitAnalysis(FormCollection collection) { DAL_Reports_CategoryWiseDrVisitAnalysis objDAL = new DAL_Reports_CategoryWiseDrVisitAnalysis(); DataControl.CurrentInfo _objcurrentInfo = new DataControl.CurrentInfo(); string blobUrl = string.Empty; string companyCode = _objcurrentInfo.GetCompanyCode(); string selectedRegionCode = _objcurrentInfo.GetRegionCode(); string month = collection["Month"].ToString(); string year = collection["Year"].ToString(); string groupByRegionTypeCode = collection["GroupByRegionTypeCode"].ToString(); string aggregateRegionTypeCode = collection["AggregateRegionTypeCode"].ToString(); string dcrStatus = collection["DCRStatus"].ToString(); string monthName = collection["MonthName"].ToString(); string groupByRegionTypeName = collection["GroupByRegionTypeName"].ToString(); string aggregateRegionTypeName = collection["AggregateRegionTypeName"].ToString(); string dcrText = string.Empty; DataSet dsUserInfo = null; DataSet dsDoctorCategory = null; //DataSet dsDoctorMaster = null; //DataSet dsDoctorVisit = null; //DataSet dsFieldDays = null; DataSet dsLastDcrDate = null; DataSet dsRvSv = null; StringBuilder sbTableContent = new StringBuilder(); string currentRegionCode = string.Empty; string regionUserCount = string.Empty; string categoryCode = string.Empty; string userCodes = string.Empty; int standardVisitCount = 0; double doctorMasterCount = 0; double doctorVisitCount = 0; double visitAvg = 0; int loopCount = 0; int totalRegionCnt = 0; int totalUserCnt = 0; int totalNoOfFieldDays = 0; DataRow[] rowFilter; DataRow filteredRow; if (string.IsNullOrEmpty(month)) { month = "0"; } if (string.IsNullOrEmpty(year)) { year = "0"; } dsUserInfo = objDAL.GetChildRegions(companyCode, selectedRegionCode, groupByRegionTypeCode); dsDoctorCategory = objDAL.GetDoctorCategories(companyCode); dsRvSv = objDAL.GetRVSVDetails(companyCode, aggregateRegionTypeCode, selectedRegionCode, groupByRegionTypeCode, int.Parse(month), int.Parse(year), dcrStatus); if (dsUserInfo != null && dsUserInfo.Tables.Count > 0) { foreach (string status in dcrStatus.Split('^')) { if (status == "0") { dcrText += "Unapproved,"; } else if (status == "1") { dcrText = "Applied,"; } else if (status == "2") { dcrText = "Approved,"; } } dcrText = dcrText.Substring(0, dcrText.Length - 1); sbTableContent.Append("<div>RVSV Analysis for the month of " + monthName + " grouped by " + groupByRegionTypeName + " aggregated by " + aggregateRegionTypeName + " (Considered " + dcrText + " DCRs only)</div>"); sbTableContent.Append("<table cellspacing='0' cellpadding='0' id='tblTpVsActualDocVisits' width='100%'>"); // Binding header information - Start sbTableContent.Append("<thead>"); sbTableContent.Append("<tr>"); sbTableContent.Append("<th>User Name</th>"); sbTableContent.Append("<th>Employee Name</th>"); sbTableContent.Append("<th>Region Name</th>"); // sbTableContent.Append("<th>Reporting Region Name</th>"); sbTableContent.Append("<th>Line 1 Manager Name</th>"); sbTableContent.Append("<th>Line 1 Manager User Type</th>"); sbTableContent.Append("<th>Line 1 Manager Region Name</th>"); sbTableContent.Append("<th>Line 1 Manager Region type</th>"); sbTableContent.Append("<th>Line 2 Manager Name</th>"); sbTableContent.Append("<th>Line 2 Manager User Type</th>"); sbTableContent.Append("<th>Line 2 Manager Region Name</th>"); sbTableContent.Append("<th>Line 2 Manager Region type</th>"); sbTableContent.Append("<th>Line 3 Manager Name</th>"); sbTableContent.Append("<th>Line 3 Manager User Type</th>"); sbTableContent.Append("<th>Line 3 Manager Region Name</th>"); sbTableContent.Append("<th>Line 3 Manager Region type</th>"); sbTableContent.Append("<th>Region Count</th>"); sbTableContent.Append("<th>User Count</th>"); sbTableContent.Append("<th>DCR Last Entered Date</th>"); sbTableContent.Append("<th>No. of Field Work Days</th>"); int categoryCount = 0; if (dsDoctorCategory != null && dsDoctorCategory.Tables.Count > 0) { foreach (DataRow drDocCategory in dsDoctorCategory.Tables[0].Rows) { standardVisitCount = 0; if (!string.IsNullOrEmpty(drDocCategory["Visit_Count"].ToString())) { standardVisitCount = Convert.ToInt16(drDocCategory["Visit_Count"].ToString()); } categoryCount += 1 + standardVisitCount; sbTableContent.Append("<th>" + drDocCategory["Category_Name"].ToString() + " Drs in the list </th>"); for (int i = 1; i <= standardVisitCount; i++) { sbTableContent.Append("<th>" + drDocCategory["Category_Name"].ToString() + " Drs met at least " + i.ToString() + " time(s) </th>"); sbTableContent.Append("<th>" + drDocCategory["Category_Name"].ToString() + " Drs met at least " + i.ToString() + " time(s) % </th>"); } } } int[] columnWiseTotal = new int[categoryCount]; sbTableContent.Append("</tr>"); sbTableContent.Append("</thead>"); sbTableContent.Append("<tbody>"); foreach (DataRow drGroupbyRegion in dsUserInfo.Tables[0].Rows) { userCodes += drGroupbyRegion["User_Code"].ToString() + "^"; } if (string.IsNullOrEmpty(userCodes)) { userCodes = "^"; } dsLastDcrDate = objDAL.GetLastDcrDate(companyCode, userCodes, month, year, dcrStatus); foreach (DataRow drGroupbyRegion in dsUserInfo.Tables[0].Rows) { sbTableContent.Append("<tr>"); if (!string.IsNullOrEmpty(drGroupbyRegion["User_Name"].ToString())) { sbTableContent.Append("<td>" + drGroupbyRegion["User_Name"].ToString() + "</td>"); } else { sbTableContent.Append("<td>VACANT</td>"); } if (!string.IsNullOrEmpty(drGroupbyRegion["Employee_Name"].ToString())) { sbTableContent.Append("<td>" + drGroupbyRegion["Employee_Name"].ToString() + "</td>"); } else { sbTableContent.Append("<td>VACANT</td>"); } sbTableContent.Append("<td>" + drGroupbyRegion["Region_Name"].ToString() + "</td>"); //sbTableContent.Append("<td>" + drGroupbyRegion["Reporting_Region_Name"].ToString() + "</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line1_Manager_User_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line1_Manager_User_Type_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["line1_Manager_Region_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line1_Manager_Region_Type_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line2_Manager_User_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line2_Manager_User_Type_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["line2_Manager_Region_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line2_Manager_Region_Type_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line3_Manager_User_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line3_Manager_User_Type_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["line3_Manager_Region_Name"].ToString()); sbTableContent.Append("</td>"); sbTableContent.Append("<td align='left' width='15%'>"); sbTableContent.Append(drGroupbyRegion["Line3_Manager_Region_Type_Name"].ToString()); sbTableContent.Append("</td>"); currentRegionCode = drGroupbyRegion["Region_Code"].ToString(); // Region Count & User Count //regionUserCount = objDAL.GetRegionUserCount(companyCode, currentRegionCode, aggregateRegionTypeCode); filteredRow = dsRvSv.Tables[0].AsEnumerable().Where(a => a["Region_Code"].ToString().Equals(currentRegionCode)).FirstOrDefault(); if (filteredRow != null) { if (filteredRow.ItemArray.Length > 0) { totalRegionCnt += int.Parse(filteredRow["RegionCount"].ToString()); totalUserCnt += int.Parse(filteredRow["UserCount"].ToString()); sbTableContent.Append("<td>" + filteredRow["RegionCount"] + "</td>"); sbTableContent.Append("<td>" + filteredRow["UserCount"] + "</td>"); } else { sbTableContent.Append("<td>0</td>"); sbTableContent.Append("<td>0</td>"); } } // Last DCR submitted date filteredRow = dsLastDcrDate.Tables[0].AsEnumerable().Where(a => a["User_Code"].ToString().Equals(drGroupbyRegion["User_Code"].ToString())).FirstOrDefault(); if (filteredRow != null) { sbTableContent.Append("<td>" + filteredRow["DCR_Actual_Date"].ToString() + "</td>"); } else { sbTableContent.Append("<td>N/A</td>"); } // No. of field days filteredRow = dsRvSv.Tables[1].AsEnumerable().Where(a => a["Region_Code"].ToString().Equals(currentRegionCode)).FirstOrDefault(); if (filteredRow != null) { if (filteredRow.ItemArray.Length > 0) { sbTableContent.Append("<td>" + filteredRow["FieldCount"] + "</td>"); if (!string.IsNullOrEmpty(filteredRow["FieldCount"].ToString())) { totalNoOfFieldDays += int.Parse(filteredRow["FieldCount"].ToString()); } } else { sbTableContent.Append("<td>0</td>"); } } loopCount = 0; foreach (DataRow drDoctorCategory in dsDoctorCategory.Tables[0].Rows) { standardVisitCount = 0; if (!string.IsNullOrEmpty(drDoctorCategory["Visit_Count"].ToString())) { standardVisitCount = Convert.ToInt16(drDoctorCategory["Visit_Count"].ToString()); } categoryCode = drDoctorCategory["Category_Code"].ToString(); // Category Drs in the list doctorMasterCount = 0; DataRow[] drfilteredRow; drfilteredRow = dsRvSv.Tables[3].AsEnumerable().Where(c => c["Category_Code"].ToString() == categoryCode && c["Parent_Region"].ToString() == currentRegionCode).ToArray(); if (drfilteredRow.Length > 0) { doctorMasterCount = Convert.ToDouble(drfilteredRow[0]["Doctor_Count"].ToString()); } sbTableContent.Append("<td>" + doctorMasterCount + "</td>"); columnWiseTotal[loopCount] += Convert.ToInt16(doctorMasterCount); loopCount++; for (int i = 1; i <= standardVisitCount; i++) { // Category Drs met at least [i] time(s) doctorVisitCount = 0; var query = from row in dsRvSv.Tables[2].AsEnumerable() where row.Field <string>("Category_Code") == categoryCode && row.Field <string>("Parent_Region") == currentRegionCode && row.Field <int>("Visit_Count") >= i select row; if (query != null) { doctorVisitCount = query.AsDataView().Count; } columnWiseTotal[loopCount] += Convert.ToInt16(doctorVisitCount); loopCount++; sbTableContent.Append("<td>" + doctorVisitCount + "</td>"); // Category Drs met at least [i] time(s) % if (doctorMasterCount > 0 && doctorVisitCount > 0) { visitAvg = (doctorVisitCount / doctorMasterCount) * 100; sbTableContent.Append("<td>" + visitAvg.ToString("N2") + "</td>"); } else { sbTableContent.Append("<td>0</td>"); } } } sbTableContent.Append("</tr>"); } sbTableContent.Append("<tr class='totaltr'>"); sbTableContent.Append("<td colspan='4'>Total</td>"); sbTableContent.Append("<td>" + totalRegionCnt.ToString() + "</td>"); sbTableContent.Append("<td>" + totalUserCnt.ToString() + "</td>"); sbTableContent.Append("<td></td>"); sbTableContent.Append("<td>" + totalNoOfFieldDays.ToString() + "</td>"); loopCount = 0; foreach (DataRow drDoctorCategory in dsDoctorCategory.Tables[0].Rows) { standardVisitCount = 0; if (!string.IsNullOrEmpty(drDoctorCategory["Visit_Count"].ToString())) { standardVisitCount = Convert.ToInt16(drDoctorCategory["Visit_Count"].ToString()); } sbTableContent.Append("<td>" + columnWiseTotal[loopCount].ToString() + "</td>"); loopCount++; for (int i = 1; i <= standardVisitCount; i++) { sbTableContent.Append("<td>" + columnWiseTotal[loopCount].ToString() + "</td>"); sbTableContent.Append("<td></td>"); loopCount++; } } sbTableContent.Append("</tr>"); sbTableContent.Append("</tbody>"); sbTableContent.Append("</table>"); DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider(); DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload(); string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY"); string userName = _objcurrentInfo.GetUserName(); string compCode = _objcurrentInfo.GetCompanyCode(); string fileName = "CATEGORY_VISIT_ANALYSIS_" + "_" + compCode + "_" + userName + ".xls"; blobUrl = objAzureBlob.AzureBlobUploadText(sbTableContent.ToString(), accKey, fileName, "bulkdatasvc"); } return(sbTableContent.ToString() + "$" + blobUrl); }