public void GetHolidayExcelDownload(FormCollection coll)
{
string blobUrl = string.Empty, error = string.Empty;
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
string holidayDate = string.Empty;
if (!string.IsNullOrEmpty(coll["hdnSearchDate"]))
{
holidayDate = Convert.ToString(coll["hdnSearchDate"]);
}
try
{
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
DataControl.CurrentInfo _objCurr = new DataControl.CurrentInfo();
string content = GetSearchHolidayDetails(holidayDate);
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = _objCurr.GetUserName();
string subDomin = _objCurr.GetSubDomain();
string fileName = "HolidayMaster" + "_" + subDomin + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(content.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicObj = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex: ex, dic: dicObj);
}
}
public void DownloadSFCExcelFormat(FormCollection col)
{
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
_objCurrentInfo = new CurrentInfo();
string regioncode = col["hdnRegionCode"].ToString();
string sfcStatus = col["SFCStatus"].ToString();
int archived = Convert.ToInt32(col["rdoShowArchived"].ToString());
int totalPageNo = 0;
string error = "";
List <SFCRegionModel> lstRegionModel = GetSFCRegions(regioncode, 1, true, ref totalPageNo, "", "", "", sfcStatus, archived).ToList();
string SFCGrid = SFCExcelTable(lstRegionModel);
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 = "SFCMASTER" + "_" + compCode + "_" + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(SFCGrid, accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
/// <summary>
/// DownLoad The ActivityMasterDetails into Excel
/// </summary>
public void PutActivityMasterIntoExcel()
{
string error = string.Empty;
StringBuilder sb = new StringBuilder();
try
{
string companyCode = _objcurrentInfo.GetCompanyCode();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
BLMaster Master = new BLMaster();
DataSet dsActivityMaster = new DataSet();
dsActivityMaster = Master.GetLeaveTypeDetails(companyCode);
//dsc
sb.Append("<table WIDTH='75%' id='tblsummary' class='table table-striped'>");
sb.Append("<thead class='active'>");
sb.Append("<tr style='background-color:#428bca;'>");
sb.Append("<td>Activity Name</td>");
sb.Append("<td>Start Date</td>");
sb.Append("<td>End Date</td>");
sb.Append("<td>Status</td>");
sb.Append("</tr>");
sb.Append("<tbody>");
//
if (dsActivityMaster != null && dsActivityMaster.Tables[0] != null && dsActivityMaster.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dsActivityMaster.Tables[0].Rows.Count; i++)
{
sb.Append("<td id='Activity_Name" + i + "'style='text-align:left;'>" + dsActivityMaster.Tables[0].Rows[i]["Activity_Name"] + "</td>");
sb.Append("<td id='Start_Date" + i + " 'style='text-align:left;'>" + dsActivityMaster.Tables[0].Rows[i]["Start_Date"] + "</td>");
sb.Append("<td id='End_Date" + i + " ' style='text-align:left;'>" + dsActivityMaster.Tables[0].Rows[i]["End_Date"] + "</td>");
sb.Append("<td id='Row_Status" + i + " 'style='text-align:left;'>" + dsActivityMaster.Tables[0].Rows[i]["Row_Status"] + "</td></tr>");
}
}
else
{
sb.Append("<tr><td>No records To Display</td></tr>");
}
sb.Append("</tbody>");
sb.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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "ActivityMaster" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(sb.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
public string GetPrivilegeAccessLogReport(string fromDate, string toDate, string userTypeCodes, string isExcel)
{
StringBuilder strContent = new StringBuilder();
string blobUrl = string.Empty;
strContent.Append("<table><tr><td colspan='7' class='tblhead'>Privilege Log Report - Log Period " + fromDate.Split('-')[2]
+ "/" + fromDate.Split('-')[1] + "/" + fromDate.Split('-')[0] + " to " + toDate.Split('-')[2]
+ "/" + toDate.Split('-')[1] + "/" + toDate.Split('-')[0] + "</td></tr></table>");
strContent.Append("<table class='table table-striped'><thead><tr><th>S.No</th><th>Date</th><th>Privilege Name </th><th>Privilege Value Name</th>");
strContent.Append("<th>User Type Name</th><th>Record Status</th><th>Request from</th><th>Request Date</th><th>Request Reason</th>");
strContent.Append("<th>Support User Name</th><th>Updated By</th></tr></thead><tbody>");
try
{
DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
DataControl.BLMaster objMaster = new DataControl.BLMaster();
List <MVCModels.HiDoctor_Master.UserTypePrivilegeMappingModel> lstPrivilege =
new List <MVCModels.HiDoctor_Master.UserTypePrivilegeMappingModel>(objMaster.GetPrivilegeLogReport(objCurInfo.GetCompanyCode(),
fromDate, toDate, userTypeCodes));
int i = 0;
if (lstPrivilege.Count > 0)
{
foreach (var dr in lstPrivilege)
{
i++;
strContent.Append("<tr><td>" + i + "</td>");
strContent.Append("<td>" + dr.Mapping_Updated_Date + "</td>");
strContent.Append("<td>" + dr.Privilege_Name + "</td>");
strContent.Append("<td>" + dr.Privilege_Value_Name + "</td>");
strContent.Append("<td>" + dr.User_Type_Name + "</td>");
strContent.Append("<td>" + dr.Record_Status + "</td>");
strContent.Append("<td>" + dr.Request_From + "</td>");
strContent.Append("<td>" + dr.Request_Date + "</td>");
strContent.Append("<td>" + dr.Request_Reason + "</td>");
strContent.Append("<td>" + dr.Support_User_Name + "</td>");
strContent.Append("<td>" + dr.Mapping_Updated_By + "</td></tr>");
}
}
strContent.Append("</tbody>");
strContent.Append("</table>");
if ("Y" == isExcel)
{
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = objCurInfo.GetUserName();
string compCode = objCurInfo.GetCompanyCode();
string fileName = "PRIVILEGELOG_" + "_" + compCode + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(strContent.ToString(), accKey, fileName, "bulkdatasvc");
strContent.Clear();
strContent.Append(blobUrl);
}
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
return(strContent.ToString());
}
/// <summary>
/// DownLoad The statusMasterDetails into Excel
/// </summary>
/// <returns></returns>
public void PutStatusMasterIntoExcel()
{
string companycode = _objcurrentInfo.GetCompanyCode();
string error = string.Empty;
try
{
List <StatusMasterModel> lstStatusMaster = (List <StatusMasterModel>)GetStatusMasterDetails();
StringBuilder sb = new StringBuilder();
BLMaster objMaster = new BLMaster();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
sb.Append("<table WIDTH='40%' id='tblsummary' class='table table-striped'>");
sb.Append("<thead class='active'>");
sb.Append("<tr style='background-color:#428bca;'>");
sb.Append("<td>Status Name</td>");
sb.Append("<td>Display Name</td>");
sb.Append("<td>Status</td>");
sb.Append("</tr>");
sb.Append("</thead>");
sb.Append("<tbody>");
if (lstStatusMaster != null && lstStatusMaster.Count > 0)
{
int i = 0;
foreach (var lstStatus in lstStatusMaster)
{
i++;
sb.Append("<td id='Status_Name" + i + "'>" + lstStatus.Status_Name + "</td>");
sb.Append("<td id='Display_Name" + i + "'>" + lstStatus.Display_Name + "</td>");
sb.Append("<td id='Record_Status" + i + "'>" + lstStatus.Record_Status + "</td></tr>");
}
}
else
{
sb.Append("<span>No Records TO Display</span>");
}
sb.Append("</body>");
sb.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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "Status Master" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(sb.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
//For excell
public void PutRequestCategoryMasterIntoExcel()
{
string blobUrl = string.Empty;
string error = string.Empty;
StringBuilder sbTableContent = new StringBuilder();
DataControl.CurrentInfo _objCurrentInfo = new DataControl.CurrentInfo();
string companyCode = _objCurrentInfo.GetCompanyCode();
StringBuilder sb = new StringBuilder();
DataControl.BLMaster _objBlmaster = new DataControl.BLMaster();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
try
{
DataControl.JSONConverter json = new DataControl.JSONConverter();
List <RequestCategoryMasterModel> lstRequestMap = new List <RequestCategoryMasterModel>();
DataControl.BLMaster _objMapping = new DataControl.BLMaster();
lstRequestMap = (List <RequestCategoryMasterModel>)_objMapping.GetRequestCategoryDetail(companyCode);
if (lstRequestMap != null && lstRequestMap.Count > 0)
{
sbTableContent.Append("<table id='tblrequest' class='table table-striped' >");
sbTableContent.Append("<thead class='active'>");
sbTableContent.Append("<tr style='background-color: #428bca'>");
sbTableContent.Append("<td>Request Category Name</td>");
sbTableContent.Append("<td>Status</td>");
sbTableContent.Append("</tr>");
sbTableContent.Append("</thead>");
sbTableContent.Append("<tbody>");
foreach (var item in lstRequestMap)
{
sbTableContent.Append("<tr><td>" + item.Request_Category_Name + "</td>");
sbTableContent.Append("<td>" + item.Request_Category_Status + "</td></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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "RequestCategoryMaster " + " - " + subdomainName + " - " + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(sbTableContent.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();;
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
public void PutPayslipmetadataintoExcel()
{
string error = string.Empty;
StringBuilder sb = new StringBuilder();
try
{
DataControl.BLUser objBlUser = new BLUser();
DataControl.CurrentInfo _objCurInfo = new DataControl.CurrentInfo();
string companyCode = _objCurInfo.GetCompanyCode();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
List <MVCModels.HiDoctor_Master.PaySlipMetaDataModel> lstdata = new List <MVCModels.HiDoctor_Master.PaySlipMetaDataModel>();
lstdata = objBlUser.GetpaysliprefsheetDetail(companyCode).ToList();
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
sb.Append("<table WIDTH='40%' id='tblpaysummary' class='table table-striped'>");
sb.Append("<thead class='active'>");
sb.Append("<tr style='background-color:#428bca;'>");
sb.Append("<td>User Type Name</td>");
sb.Append("<td>Column No</td>");
sb.Append("<td>Column Name</td>");
sb.Append("<td>DataTypeName</td>");
sb.Append("</tr>");
sb.Append("</thead>");
sb.Append("<tbody>");
if (lstdata.Count > 0)
{
foreach (var lstPayMetadata in lstdata)
{
sb.Append("<tr><td>" + lstPayMetadata.User_Type_Name + "</td>");
sb.Append("<td>" + lstPayMetadata.Column_No + "</td>");
sb.Append("<td>" + lstPayMetadata.Column_Name + "</td>");
sb.Append("<td>" + lstPayMetadata.DataTypeAliasName + "</td></tr>");
}
}
string userName = _objCurInfo.GetUserName();
string subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "PaySlipMetaData" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(sb.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
//public string GetDisabledUsers()
//{
// StringBuilder strUsers = new StringBuilder();
// DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
// DataControl.BLUser objUser = new DataControl.BLUser();
// IEnumerable<MVCModels.HiDoctor_Master.UserModel> lstUser = null;
// lstUser = objUser.GetDisabledUsers(objCurInfo.GetCompanyCode());
// strUsers.Append("<ul>");
// if (lstUser != null)
// {
// foreach (var user in lstUser)
// {
// strUsers.Append("<li onclick='fnGetUser(\"" + user.User_Code + "\")'>" + user.User_Name + "</li>");
// }
// }
// strUsers.Append("</ul>");
// return strUsers.ToString();
//}
//public string GetResignedUserLeaveTakenReport()
//{
// StringBuilder strTblContent = new StringBuilder();
// DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
// DataControl.BLUser objUser = new DataControl.BLUser();
// DataControl.BLLeave objLeave = new DataControl.BLLeave();
// return strTblContent.ToString();
//}
#region get user menu access log report
public string GetUserMenuAccessLogReport(string fromDate, string toDate, string userTypeCodes, string isExcel)
{
StringBuilder strContent = new StringBuilder();
string blobUrl = string.Empty;
strContent.Append("<table><tr><td colspan='7' class='tblhead'>Menu Access Log Report - Log Period " + fromDate.Split('-')[2]
+ "/" + fromDate.Split('-')[1] + "/" + fromDate.Split('-')[0] + " to " + toDate.Split('-')[2]
+ "/" + toDate.Split('-')[1] + "/" + toDate.Split('-')[0] + "</td></tr></table>");
strContent.Append("<table class='table table-striped'><thead><tr><th>S.No</th><th>Date</th><th>User Type Name </th><th>Menu Name</th>");
strContent.Append("<th>Menu URL</th><th>Access</th><th>Changed By</th></tr></thead><tbody>");
try
{
DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
DataControl.BLMaster objMaster = new DataControl.BLMaster();
List <MVCModels.UserTypeMenuAccessModel> lstUserMenu =
new List <UserTypeMenuAccessModel>(objMaster.GetUserTypeMenuAccessLog(objCurInfo.GetCompanyCode(), fromDate, toDate, userTypeCodes));
int i = 0;
if (lstUserMenu.Count > 0)
{
foreach (var dr in lstUserMenu)
{
i++;
strContent.Append("<tr><td>" + i + "</td>");
strContent.Append("<td>" + dr.Updated_Date + "</td>");
strContent.Append("<td>" + dr.User_Type_Name + "</td>");
strContent.Append("<td>" + dr.Menu_Text + "</td>");
strContent.Append("<td>" + dr.Menu_URL + "</td>");
strContent.Append("<td>" + dr.Access + "</td>");
strContent.Append("<td>" + dr.Updated_By + "</td></tr>");
}
}
strContent.Append("</tbody>");
strContent.Append("</table>");
if ("Y" == isExcel)
{
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = objCurInfo.GetUserName();
string compCode = objCurInfo.GetCompanyCode();
string fileName = "MENUACCESSLOG_" + "_" + compCode + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(strContent.ToString(), accKey, fileName, "bulkdatasvc");
strContent.Clear();
strContent.Append(blobUrl);
}
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
return(strContent.ToString());
}
public void PutProjectActivityIntoAzure(FormCollection coll)
{
try
{
string error = string.Empty;
DataControl.BLMaster _objBlMaster = new DataControl.BLMaster();
DataControl.CurrentInfo _objCurInfo = new DataControl.CurrentInfo();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
StringBuilder tblBuilder = new StringBuilder();
string companyCode = _objCurInfo.GetCompanyCode();
int rowNum = 0;
DataSet dsProjActMapp = _objBlMaster.GetSalesActivityMapping(companyCode);
if (dsProjActMapp != null && dsProjActMapp.Tables.Count > 0 && dsProjActMapp.Tables[0].Rows.Count > 0)
{
tblBuilder.Append("<table id='tblSFC' class='table table-striped' cellpadding='0' cellspacing='0'>");
tblBuilder.Append("<thead><tr><th>Project Name</th><th>Activity Name</th><th>Start Date</th><th>End Date</th><th>Status</th></tr></thead>");
tblBuilder.Append("<tbody>");
foreach (DataRow dr in dsProjActMapp.Tables[0].Rows)
{
rowNum++;
tblBuilder.Append("<tr>");
tblBuilder.Append("<td>" + dr["Project_Name"].ToString() + "<input type='hidden' id='hdnProjCode_" + rowNum + "' value='" + dr["Project_Code"].ToString() + "' /></td>");
tblBuilder.Append("<td>" + dr["Activity_Name"].ToString() + "<input type='hidden' id='hdnActivityCode_" + rowNum + "' value='" + dr["Activity_Code"].ToString() + "' /></td>");
tblBuilder.Append("<td id='tdStartDate_" + rowNum + "'>" + dr["StartDate"].ToString() + "</td>");
tblBuilder.Append("<td id='tdEndDate_" + rowNum + "'>" + dr["EndDate"].ToString() + "</td>");
tblBuilder.Append("<td id='tdStatus_" + rowNum + "'>" + dr["Status"].ToString() + "</td>");
tblBuilder.Append("</tr>");
}
tblBuilder.Append("</tbody>");
tblBuilder.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 = _objCurInfo.GetUserName();
string subdomainName = HttpContext.Request.Url.DnsSafeHost.ToString();
string fileName = "ProjectActivityMapping" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(tblBuilder.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicObj = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicObj);
}
}
/// <summary>
/// Method is Used to DownLoad the Subregion master in Excel
/// </summary>
public void PutSubRegionMasterIntoExcel()
{
string companycode = _objcurrentInfo.GetCompanyCode();
string error = string.Empty;
try
{
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
StringBuilder strTbl = new StringBuilder();
BLMaster _objBlMaster = new BLMaster();
List <SubRegionMasterModel> lstSubRegionMaster = _objBlMaster.GetSubRegionMaster(companycode).ToList();
if (lstSubRegionMaster != null && lstSubRegionMaster.Count() > 0)
{
strTbl.Append("<div class='table-responsive'>");
strTbl.Append("<table class='table table-striped' id='tblSubRegion' cellspacing='0' cellpadding='0'>");
strTbl.Append("<thead>");
strTbl.Append("<tr>");
strTbl.Append("<th>Sub Region Name</th>");
strTbl.Append("<th>Under Region</th>");
strTbl.Append("<th>Status</th></tr>");
strTbl.Append("</thead>");
strTbl.Append("<tbody>");
int rowCount = 0;
foreach (var subRegion in lstSubRegionMaster)
{
strTbl.Append("<tr id='" + rowCount + "'>");
strTbl.Append("<td>" + subRegion.SubRegion_Name + "</td>");
strTbl.Append("<td>" + subRegion.UnderRegion + " </td>");
strTbl.Append("<td>" + subRegion.SubRegion_Status + " </td>");
strTbl.Append("<tr>");
}
strTbl.Append("</tbody>");
strTbl.Append("</table></div>");
}
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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "SubRegion Master" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(strTbl.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
public string GenerateYearWiseHolidayExcel(string regionCodes, string year, string SearchKey, int pageNumber, bool excelDownload)
{
int count = 0;
int totalPageCount = 0;
DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
DataControl.BLRegion objRegion = new DataControl.BLRegion();
IEnumerable <MVCModels.HiDoctor_Master.HolidayModel> lstHoliday = null;
lstHoliday = objRegion.GetMappedHolidayDetails(objCurInfo.GetCompanyCode(), regionCodes, year, SearchKey, pageNumber, excelDownload, PAGESIZE,
ref totalPageCount);
StringBuilder strContent = new StringBuilder();
StringBuilder strExcelContent = new StringBuilder();
strExcelContent.Append("<table class='table table-striped'><thead><tr><td>S.No</td><td>Date</td><td>Holiday Name</td>");
strExcelContent.Append("<td>Region Name</td><td>Region Type</td><td>Reporting Region</td><td>Reporting Region Type</td></tr></thead>");
if (lstHoliday != null)
{
int i = 0;
foreach (var dr in lstHoliday)
{
i++;
strExcelContent.Append("<tr><td>" + i + "</td>");
strExcelContent.Append("<td><span id='lblHolidayDate_" + i + "'>" + dr.Holiday_Date + "</span></td>");
strExcelContent.Append("<td><span id='lblHolidayName_" + i + "'>" + dr.Holiday_Name + "</span></td>");
strExcelContent.Append("<td>" + dr.Region_Name + "<input type='hidden' id='hdnRegionCode_" + i + "' value='" + dr.Region_Code + "'/></td>");
strExcelContent.Append("<td>" + dr.Region_Type_Name + "</td>");
strExcelContent.Append("<td>" + dr.Reporting_Region_Name + "</td>");
strExcelContent.Append("<td>" + dr.Reporting_Region_Type_Name + "</td>");
strExcelContent.Append("</tr>");
}
}
strExcelContent.Append("</tbody></table>");
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = objCurInfo.GetUserName();
string subDomin = objCurInfo.GetSubDomain();
string fileName = "HolidayMaster" + "_" + subDomin + "_" + userName + ".xls";
string blobUrl = string.Empty;
blobUrl = objAzureBlob.AzureBlobUploadText(strExcelContent.ToString(), accKey, fileName, "bulkdatasvc");
return(blobUrl);
}
public void OnAsyncReportProcessCompletion(string CompanyCode, string CurrentUserCode, string TransNumber, string strHTMLContent, string ConnectionString, string ReportName)
{
//DataSet dsEmail = new DataSet();
//string strEmailContent = string.Empty;
//string strEmailID = string.Empty;
//string strUserName = string.Empty;
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string fileName = TransNumber + "_" + DateTime.UtcNow.ToLongDateString() + ".report";
string blobUrl = objAzureBlob.AzureBlobUploadText(strHTMLContent, accKey, fileName, "bulkdatasvc");
//BLUser objBLuser = new BLUser();
try
{
_objDALreportRegion.UpdateRptTransactionQueue(CompanyCode, TransNumber, "Completed", "", "", blobUrl, ConnectionString, CurrentUserCode);
//dsEmail = objBLuser.checkEmailid(CompanyCode, CurrentUserCode, ConnectionString);
//if (dsEmail.Tables[0].Rows.Count > 0)
//{
// strUserName = dsEmail.Tables[0].Rows[0]["User_Name"].ToString();
// strEmailID = dsEmail.Tables[0].Rows[0]["Email_Id"].ToString();
// strEmailContent = FrameEmailContent(strUserName, ReportName, TransNumber);
//}
//_objBL.SendMail(strEmailID, "HiDOCTOR Report - Generated Successfully", strEmailContent);
}
finally
{
//dsEmail.Dispose();
//objBLuser = null;
//_objBL = null;
//objBLuser = null;
}
}
public string GetEmployeeAuditReport(string fromDate, string toDate, string isExcel)
{
StringBuilder strContent = new StringBuilder();
string blobUrl = string.Empty;
strContent.Append("<table><tr><td colspan='7' class='tblhead'>Employee Audit Report - Period " + fromDate.Split('-')[2]
+ "/" + fromDate.Split('-')[1] + "/" + fromDate.Split('-')[0] + " to " + toDate.Split('-')[2]
+ "/" + toDate.Split('-')[1] + "/" + toDate.Split('-')[0] + "</td></tr></table>");
strContent.Append("<table class='table table-striped' id='tblEmployee'><thead><tr><th>S.No</th><th>Date</th><th>Employee Name </th><th>Employee Number</th>");
strContent.Append("<th>Gender</th><th>Date of birth</th><th>Address</th><th>Phone</th><th>Mobile</th>");
strContent.Append("<th>Employee Status</th><th>Email Id</th><th>Date of Joining</th><th>EDN Proof</th><th>Salary Proof</th>");
strContent.Append("<th>Resume Given</th><th>Resignation Submitted</th><th>Appointed</th><th>Bank Account Number1</th><th>Bank Account Number2</th>");
strContent.Append("<th>PF Number</th><th>PAN Number</th><th>Effective From</th><th>Effective To</th><th>Confirmation Date</th>");
strContent.Append("<th>Employee Entity Type</th><th>Created By</th><th>Created DateTime</th>");
strContent.Append("<th>Updated By</th><th>Updated DateTime</th></tr></thead><tbody>");
try
{
DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
DataControl.BLUser objUser = new DataControl.BLUser();
List <MVCModels.HiDoctor_Master.EmployeeModel> lstEmp =
new List <MVCModels.HiDoctor_Master.EmployeeModel>(objUser.GetEmployeeAuditReport(objCurInfo.GetCompanyCode(), fromDate, toDate));
int i = 0;
if (lstEmp.Count > 0)
{
foreach (var dr in lstEmp)
{
i++;
strContent.Append("<tr><td>" + i + "</td>");
strContent.Append("<td>" + Convert.ToString(dr.Updated_DateTime).Substring(0, 10) + "</td>");
strContent.Append("<td>" + dr.Employee_Name + "</td>");
strContent.Append("<td>" + dr.Employee_Number + "</td>");
strContent.Append("<td>" + dr.Gender + "</td>");
if (dr.Date_Of_Birth == "01/01/1900")
{
strContent.Append("<td></td>");
}
else
{
strContent.Append("<td>" + dr.Date_Of_Birth + "</td>");
}
//strContent.Append("<td>" + dr.Date_Of_Birth + "</td>");
strContent.Append("<td>" + dr.Address + "</td>");
strContent.Append("<td>" + dr.Phone + "</td>");
strContent.Append("<td>" + dr.Mobile + "</td>");
strContent.Append("<td>" + dr.Employee_Status + "</td>");
strContent.Append("<td>" + dr.Email_Id + "</td>");
if (dr.Date_of_Joining == "01/01/1900")
{
strContent.Append("<td></td>");
}
else
{
strContent.Append("<td>" + dr.Date_of_Joining + "</td>");
}
strContent.Append("<td>" + ((dr.EDN_Proof == "0") ? "No" : "Yes") + "</td>");
strContent.Append("<td>" + ((dr.Salary_Proof == "0") ? "No" : "Yes") + "</td>");
strContent.Append("<td>" + ((dr.Resume_Given == "0") ? "No" : "Yes") + "</td>");
strContent.Append("<td>" + ((dr.Resignation_Submitted == "0") ? "No" : "Yes") + "</td>");
strContent.Append("<td>" + ((dr.Appointed == "0") ? "No" : "Yes") + "</td>");
strContent.Append("<td>" + dr.SCB_Account_Number + "</td>");
strContent.Append("<td>" + dr.ICICI_Account_Number + "</td>");
strContent.Append("<td>" + dr.PF_Number + "</td>");
strContent.Append("<td>" + dr.PAN_Number + "</td>");
if (dr.Effective_From == "01/01/1900")
{
strContent.Append("<td></td>");
}
else
{
strContent.Append("<td>" + dr.Effective_From + "</td>");
}
if (dr.Effective_To == "01/01/1900")
{
strContent.Append("<td></td>");
}
else
{
strContent.Append("<td>" + dr.Effective_To + "</td>");
}
if (dr.Confirmation_Date == "01/01/1900")
{
strContent.Append("<td></td>");
}
else
{
strContent.Append("<td>" + dr.Confirmation_Date + "</td>");
}
strContent.Append("<td>" + dr.Employee_Entity_Type + "</td>");
strContent.Append("<td>" + dr.Created_By + "</td>");
if (dr.Created_DateTime == "01/01/1900")
{
strContent.Append("<td></td>");
}
else
{
strContent.Append("<td>" + dr.Created_DateTime + "</td>");
}
strContent.Append("<td>" + dr.Updated_By + "</td>");
if (dr.Updated_DateTime == "01/01/1900")
{
strContent.Append("<td></td>");
}
else
{
strContent.Append("<td>" + dr.Updated_DateTime + "</td>");
}
strContent.Append("</tr>");
}
}
else
{
strContent.Append("<tr><td colspan='29' style='text-align:center;'>No records found</td></tr>");
}
strContent.Append("</tbody>");
strContent.Append("</table>");
if ("Y" == isExcel)
{
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = objCurInfo.GetUserName();
string compCode = objCurInfo.GetCompanyCode();
string fileName = "EMPLOYEEAUDITREPORT_" + "_" + compCode + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(strContent.ToString(), accKey, fileName, "bulkdatasvc");
strContent.Clear();
strContent.Append(blobUrl);
}
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
return(strContent.ToString());
}
///// <summary>
///// Get TP Master report for alumini users
///// </summary>
///// <param name="userCode"></param>
///// <param name="date"></param>
///// <returns>return the table content</returns>
public string GetTPMasterReportForAluminiUsers(string userCode, string month, string year, string isExcel, string monthName)
{
StringBuilder strContent = new StringBuilder();
DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
DataControl.BL_Report objReport = new DataControl.BL_Report();
DataControl.BLUser objUser = new DataControl.BLUser();
#region tp header
strContent.Append(objUser.GetReportHeaderTableString(objCurInfo.GetCompanyCode(), userCode, monthName + "-" + year, "", "", ""));
strContent.Append("<table class='table table-striped' cellpadding=0 cellspacing=0 id ='tblTPReport'>");
strContent.Append("<thead>");
strContent.Append("<tr id='tblTr'>");
strContent.Append("<th>User Name</th>");
strContent.Append("<th>TP Date</th>");
strContent.Append("<th>Call Objective</th>");
strContent.Append("<th>Activity Name</th>");
strContent.Append("<th>Category</th>");
strContent.Append("<th>CP Name</th>");
strContent.Append("<th>Meeting Point </th>");
strContent.Append("<th>Meeting Time </th>");
strContent.Append("<th>Work Place</th>");
strContent.Append("<th>SFC</th>");
strContent.Append("<th>Accomp Name</th>");
strContent.Append("<th>No oF Planned Doctors</th>");
strContent.Append("<th>Entered Date</th>");
strContent.Append("<th>Entered By</th>");
strContent.Append("<th>Remarks</th>");
strContent.Append("<th>Status</th>");
strContent.Append("<th>Approved/Unapproved Date</th>");
strContent.Append("<th>Approve/Unapprove Reason</th>");
strContent.Append("</tr>");
strContent.Append("</thead>");
strContent.Append("<tbody>");
#endregion tp header
try
{
DataSet ds = new DataSet();
ds = objReport.GetTPMasterReportForAlumniUsers(objCurInfo.GetCompanyCode(), month, year, userCode);
if (ds.Tables.Count > 0)
{
int noOfDays = System.DateTime.DaysInMonth(Convert.ToInt32(year), Convert.ToInt32(month));
DateTime startDate = Convert.ToDateTime(year + "-" + month + "-01");
DateTime endDate = Convert.ToDateTime(year + "-" + month + "-" + noOfDays);
DateTime dtStartDate = new DateTime();
DateTime dtEndDate = new DateTime();
dtStartDate = Convert.ToDateTime(startDate);
dtEndDate = Convert.ToDateTime(endDate);
DateTime dtDate = dtStartDate;
TimeSpan ts;
ts = dtEndDate - dtStartDate;
for (var j = 0; j <= ts.Days; j++)
{
if (j != 0)
{
dtDate = dtDate.AddDays(Convert.ToDouble(1));
}
DataRow[] dr = ds.Tables[0].AsEnumerable().Where(a => Convert.ToInt32(a["Day"]) == dtDate.Day &&
Convert.ToInt32(a["Month"]) == dtDate.Month && Convert.ToInt32(a["Year"]) == dtDate.Year).ToArray();
if (dr.Length > 0)
{
strContent.Append("<tr>");
strContent.Append("<td>" + Convert.ToString(dr[0]["User_Name"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["TP_Date"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Activity_Code"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Project_Code"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Category"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["CP_name"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Meeting_point"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Meeting_Time"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Work_Area"]) + "</td>");
#region TP SFC details
// var sfcDet = jsonPath(jsData, "$.Tables[4].Rows[?(@.TP_Id=='" + details[0].TP_Id + "')]");
DataRow[] drSFC = ds.Tables[2].AsEnumerable().Where(z => Convert.ToString(z["TP_Id"]) == Convert.ToString(dr[0]["TP_Id"])).ToArray();
StringBuilder strSFC = new StringBuilder();
if (drSFC.Length > 0)
{
for (int s = 0; s < drSFC.Length; s++)
{
if (!string.IsNullOrEmpty(Convert.ToString(drSFC[s]["From_Place"])))
{
strSFC.Append("" + (s + 1) + "/ " + Convert.ToString(drSFC[s]["From_Place"]) + " to "
+ Convert.ToString(drSFC[s]["To_Place"]) + "<br />");
}
}
}
strContent.Append("<td>" + strSFC.ToString() + "</td>");
#endregion TP SFC details
StringBuilder strAccName = new StringBuilder();
if (!string.IsNullOrEmpty(Convert.ToString(dr[0]["Accomp_Name"])))
{
strAccName.Append("" + Convert.ToString(dr[0]["Accomp_Name"]) + " <br />");
}
if (!string.IsNullOrEmpty(Convert.ToString(dr[0]["Accompanist2_Name"])))
{
strAccName.Append("" + Convert.ToString(dr[0]["Accompanist2_Name"]) + "<br />");
}
if (!string.IsNullOrEmpty(Convert.ToString(dr[0]["Accompanist3_Name"])))
{
strAccName.Append("" + Convert.ToString(dr[0]["Accompanist3_Name"]) + "<br />");
}
if (!string.IsNullOrEmpty(Convert.ToString(dr[0]["Accompanist4_Name"])))
{
strAccName.Append("" + Convert.ToString(dr[0]["Accompanist4_Name"]) + "<br /> ");
}
strContent.Append("<td>" + strAccName.ToString() + "</td>");
DataRow[] drDoctors = ds.Tables[1].AsEnumerable().Where(c => Convert.ToString(c["TP_Date"]) ==
Convert.ToString(dr[0]["TP_Date"])).ToArray();
if (drDoctors.Length > 0)
{
// content += "<td>" + dJsonData.length + "</td>";
strContent.Append("<td align='center' ><span onclick='fnTPDetails(\"" + userCode + "_"
+ dr[0]["TP_Date"] + "\")' style='text-decoration:underline;cursor:pointer'>" + drDoctors.Length + "</span></td>");
}
else
{
strContent.Append("<td></td>");
}
strContent.Append("<td>" + Convert.ToString(dr[0]["Entered_Date"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Entered_by"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Remarks"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Status"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Approved_Date"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(dr[0]["Unapprove_Reason"]) + "</td>");
strContent.Append("</tr>");
}
else
{
#region holiday details
DataRow[] drHoliday = ds.Tables[3].AsEnumerable().Where(a => Convert.ToInt32(a["Day"]) == dtDate.Day &&
Convert.ToInt32(a["Month"]) == dtDate.Month && Convert.ToInt32(a["Year"]) == dtDate.Year).ToArray();
// var details = jsonPath(jsData, "$.Tables[5].Rows[?(@.Day=='" + day + "' & @.Month=='" + month + "' & @.Year=='" + year + "')]");
if (drHoliday.Length > 0)
{
if (Convert.ToString(drHoliday[0]["Type"]) == "S")
{
strContent.Append("<tr>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'>" + Convert.ToString(drHoliday[0]["Date"]) + "</td>");
strContent.Append("<td style='background: #efefef;'> - Sunday</td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("</tr>");
}
else
{
strContent.Append("<tr>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'>" + Convert.ToString(drHoliday[0]["Date"]) + "</td>");
strContent.Append("<td style='background: #efefef;'> - Holiday - </td>");
strContent.Append("<td style='background: #efefef;'>" + Convert.ToString(drHoliday[0]["Holiday"]) + "</td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("<td style='background: #efefef;'></td>");
strContent.Append("</tr>");
}
}
#endregion holiday details
}
}
}
}
catch (Exception ex)
{
}
strContent.Append("</tbody>");
strContent.Append("</table>");
string blobUrl = string.Empty;
if ("Y" == isExcel)
{
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = objCurInfo.GetUserName();
string compCode = objCurInfo.GetCompanyCode();
string fileName = "TPMASTERALUMINI_" + "_" + compCode + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(strContent.ToString(), accKey, fileName, "bulkdatasvc");
strContent.Clear();
strContent.Append(blobUrl);
}
return(strContent.ToString());
}
public string GetShareAllocationReport(string months, string year, string isExcel, string regionCodes, string Mode, string SelectedMonth)
{
StringBuilder strContent = new StringBuilder();
CurrentInfo objCurInfo = new CurrentInfo();
strContent.Append("<table class='table table-striped'><thead><tr><td rowspan='2'>User Name</td>");
strContent.Append("<td rowspan='2'>Employee Name</td><td rowspan='2'>Employee No</td>");
strContent.Append("<td rowspan='2'>Region Name</td><td rowspan='2'>Reporting Region Name</td>");
strContent.Append("<td colspan='3'>Secondary Sales</td><td colspan='2'>Primary Sales</td>");
strContent.Append("<td colspan='2'>Target</td></tr>");
strContent.Append("<tr><td>Current Month</td><td>Variation</td><td>Previous Month</td>");
strContent.Append("<td>Variation</td><td>Current Month</td>");
strContent.Append("<td>Variation</td><td>Current Month</td></tr></thead><tbody>");
try
{
DataControl.HiDoctor_ReportsFactoryClasses.DAL_ReportRegion objRegion = new DataControl.HiDoctor_ReportsFactoryClasses.DAL_ReportRegion();
string previousMonth = string.Empty;
string previousYear = string.Empty;
if (Mode == "M")
{
previousMonth = Convert.ToString(Convert.ToDateTime(year + "-" + months + "-01").AddMonths(-1).Month) + ",";
previousYear = Convert.ToString(Convert.ToDateTime(year + "-" + months + "-01").AddMonths(-1).Year);
}
else
{
DateTime previousdate = Convert.ToDateTime(SelectedMonth.Split('-')[0] + "-"
+ SelectedMonth.Split('-')[1].Split('_')[0] + "-01");
previousMonth = Convert.ToString(previousdate.AddMonths(-3).Month) + "," + Convert.ToString(previousdate.AddMonths(-2).Month) + "," +
Convert.ToString(previousdate.AddMonths(-1).Month + ",");
previousYear = Convert.ToString(previousdate.Year);
}
List <MVCModels.HiDoctor_Master.StockistShare> lstPreviousMonthShare = new List <MVCModels.HiDoctor_Master.StockistShare>();
lstPreviousMonthShare = (List <MVCModels.HiDoctor_Master.StockistShare>)objRegion.GetStockistShareAllocation(objCurInfo.GetCompanyCode(),
previousMonth, previousYear, regionCodes);
List <MVCModels.HiDoctor_Master.StockistShare> lstCurrentMonthShare = new List <MVCModels.HiDoctor_Master.StockistShare>();
lstCurrentMonthShare = (List <MVCModels.HiDoctor_Master.StockistShare>)objRegion.GetStockistShareAllocation(objCurInfo.GetCompanyCode(),
months, year, regionCodes);
var lstDisUsers = lstPreviousMonthShare.Concat(lstCurrentMonthShare).AsEnumerable().Select(a => a.User_Code).Distinct();
foreach (string userCode in lstDisUsers)
{
strContent.Append("<tr>");
var userDetails = lstPreviousMonthShare.AsEnumerable().Where(z => z.User_Code == Convert.ToString(userCode)).ToList();
if (userDetails.Count == 0)
{
userDetails = lstCurrentMonthShare.AsEnumerable().Where(z => z.User_Code == Convert.ToString(userCode)).ToList();
}
strContent.Append("<td>" + userDetails[0].User_Name + "</td>");
strContent.Append("<td>" + userDetails[0].Employee_Name + "</td>");
strContent.Append("<td>" + userDetails[0].Employee_Number + "</td>");
strContent.Append("<td>" + userDetails[0].Region_Name + "</td>");
strContent.Append("<td>" + userDetails[0].Reporting_Region_Name + "</td>");
double currentSecShare = 0;
double previousSecShare = 0;
double curPriShare = 0;
double curTargetShare = 0;
#region secondary sales
var lstCurrentMonth = lstCurrentMonthShare.AsEnumerable().Where(c => c.User_Code == Convert.ToString(userCode)).ToList();
if (lstCurrentMonth.Count > 0)
{
currentSecShare = Convert.ToDouble(lstCurrentMonth[0].SS_Value);
curPriShare = Convert.ToDouble(lstCurrentMonth[0].PS_Value);
curTargetShare = Convert.ToDouble(lstCurrentMonth[0].Target_Value);
}
var lstPreviousMonth = lstPreviousMonthShare.AsEnumerable().Where(c => c.User_Code == Convert.ToString(userCode)).ToList();
if (lstPreviousMonth.Count > 0)
{
previousSecShare = Convert.ToDouble(lstPreviousMonth[0].SS_Value);
}
double secVariation = 0;
strContent.Append("<td>" + currentSecShare + "</td>");
strContent.Append("<td>");
if (previousSecShare > 0)
{
secVariation = ((currentSecShare - previousSecShare) / previousSecShare) * 100;
if (secVariation > 0)
{
if (secVariation > 100)
{
strContent.Append("<div class='dvPositive' style='width:100% !important'>" + Math.Round(secVariation, 0) + "</div>");
}
else
{
strContent.Append("<div class='dvPositive' style='width:" + Math.Round(secVariation, 0)
+ "% !important'>" + Math.Round(secVariation, 0) + "</div>");
}
}
else
{
strContent.Append("<div class='dvNegative' style='width:" + Math.Round((secVariation * -1), 0)
+ "% !important'>" + Math.Round(secVariation, 0) + "</div>");
}
}
else
{
strContent.Append("<div>NA</div>");
}
strContent.Append("</td>");
strContent.Append("<td>" + previousSecShare + "</td>");
#endregion secondary sales
#region primary sales
double priVariation = 0;
strContent.Append("<td>");
if (curPriShare > 0)
{
priVariation = ((currentSecShare - curPriShare) / curPriShare) * 100;
if (priVariation > 0)
{
if (priVariation > 100)
{
strContent.Append("<div class='dvPositive' style='width:100% !important'>" + Math.Round(priVariation, 0) + "</div>");
}
else
{
strContent.Append("<div class='dvPositive' style='width:" + Math.Round(priVariation, 0)
+ "% !important'>" + Math.Round(priVariation, 0) + "</div>");
}
}
else
{
strContent.Append("<div class='dvNegative' style='width:" + Math.Round(priVariation, 0)
+ "% !important'>" + Math.Round(priVariation, 0) + "</div>");
}
}
else
{
strContent.Append("<div>NA</div>");
}
strContent.Append("</td>");
strContent.Append("<td>" + curPriShare + "</td>");
#endregion primary sales
#region target
double tarVariation = 0;
strContent.Append("<td>");
if (curTargetShare > 0)
{
tarVariation = ((currentSecShare - curTargetShare) / curTargetShare) * 100;
if (tarVariation > 0)
{
if (tarVariation > 100)
{
strContent.Append("<div class='dvPositive' style='width:100% !important'>" + Math.Round(tarVariation, 0) + "</div>");
}
else
{
strContent.Append("<div class='dvPositive' style='width:" + Math.Round(tarVariation, 0)
+ "% !important'>" + Math.Round(tarVariation, 0) + "</div>");
}
}
else
{
strContent.Append("<div class='dvNegative' style='width:" + Math.Round(tarVariation, 0)
+ "% !important'>" + Math.Round(tarVariation, 0) + "</div>");
}
}
else
{
strContent.Append("<div>NA</div>");
}
strContent.Append("</td>");
strContent.Append("<td>" + curTargetShare + "</td>");
#endregion target
strContent.Append("</tr>");
}
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
// return "";
}
strContent.Append("</tbody></table>");
string blobUrl = string.Empty;
if ("Y" == isExcel)
{
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = objCurInfo.GetUserName();
string compCode = objCurInfo.GetCompanyCode();
string fileName = "SHAREALLOCATIONREPORT_" + "_" + compCode + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(strContent.ToString(), accKey, fileName, "bulkdatasvc");
strContent.Clear();
strContent.Append(blobUrl);
}
return(strContent.ToString());
}
public string GetCPDetails(string regionCode)
{
string blobUrl = string.Empty;
DataControl.BLCampaignPlanner _objBlCP = new DataControl.BLCampaignPlanner();
DataControl.CurrentInfo _objCurInfo = new DataControl.CurrentInfo();
StringBuilder strContent = new StringBuilder();
DataSet ds = new DataSet();
try
{
ds = _objBlCP.GetCPDetails(_objCurInfo.GetCompanyCode(), regionCode);
strContent.Append("<table class='table table-striped'>");
strContent.Append("<thead><tr><td>Edit</td><td>CP Name</td><td>Status</td> <td>Category Name</td>");
strContent.Append("<td>Work Area</td><td>Place From</td> <td>Place To</td><td>SFC Category</td>");
strContent.Append("<td >Distance</td><td>Fare</td><td >Travel Mode</td>");
strContent.Append("<td>Doctor Name</td>");//<td>MDL/SVL No</td>");
strContent.Append("<td>Unapprove Reason</td></tr></thead><tbody>");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string cpCode = Convert.ToString(ds.Tables[0].Rows[i]["CP_Code"]);
DataRow[] drHop = ds.Tables[1].AsEnumerable().Where(a => Convert.ToString(a["CP_Code"]) == cpCode).ToArray();
DataRow[] drDoctors = ds.Tables[2].AsEnumerable().Where(a => Convert.ToString(a["CP_Code"]) == cpCode).ToArray();
#region cp details
//StringBuilder strWorkArea = new StringBuilder();
StringBuilder strFromPlace = new StringBuilder();
StringBuilder strToPlace = new StringBuilder();
StringBuilder strSFCCategory = new StringBuilder();
StringBuilder strDistance = new StringBuilder();
StringBuilder strTravelMode = new StringBuilder();
StringBuilder strFare = new StringBuilder();
// strWorkArea.Append("<table>");
strFromPlace.Append("<table>");
strToPlace.Append("<table>");
strSFCCategory.Append("<table>");
strDistance.Append("<table>");
strTravelMode.Append("<table>");
strFare.Append("<table>");
if (drHop.Length > 0)
{
for (int j = 0; j < drHop.Length; j++)
{
//strWorkArea.Append("<tr><td>" + drHop[j]["Work_Place"] + "</td></tr>");
if (drHop[j]["Route_Way"].ToString() == "D")
{
strFromPlace.Append("<tr><td>" + drHop[j]["From_Place"] + "</td></tr>");
strToPlace.Append("<tr><td>" + drHop[j]["To_Place"] + "</td></tr>");
strSFCCategory.Append("<tr><td>" + drHop[j]["SFC_Category_Name"] + "</td></tr>");
}
else
{
strFromPlace.Append("<tr><td>" + drHop[j]["To_Place"] + "</td></tr>");
strToPlace.Append("<tr><td>" + drHop[j]["From_Place"] + "</td></tr>");
strSFCCategory.Append("<tr><td>" + drHop[j]["SFC_Category_Name"] + "</td></tr>");
}
strDistance.Append("<tr><td>" + drHop[j]["Distance"] + "</td></tr>");
strTravelMode.Append("<tr><td>" + drHop[j]["Travel_Mode"] + "</td></tr>");
strFare.Append("<tr><td>" + drHop[j]["Amount"] + "</td></tr>");
}
}
else
{
// strWorkArea.Append("<tr><td>" + ds.Tables[0].Rows[i]["Work_Area"] + "</td></tr>");
if (ds.Tables[0].Rows[i]["Route_Way"].ToString() == "D")
{
strFromPlace.Append("<tr><td>" + ds.Tables[0].Rows[i]["Place_From"] + "</td></tr>");
strToPlace.Append("<tr><td>" + ds.Tables[0].Rows[i]["Place_To"] + "</td></tr>");
strSFCCategory.Append("<tr><td>" + ds.Tables[0].Rows[i]["SFC_Category_Name"] + "</td></tr>");
}
else
{
strFromPlace.Append("<tr><td>" + ds.Tables[0].Rows[i]["Place_To"] + "</td></tr>");
strToPlace.Append("<tr><td>" + ds.Tables[0].Rows[i]["Place_From"] + "</td></tr>");
strSFCCategory.Append("<tr><td>" + ds.Tables[0].Rows[i]["SFC_Category_Name"] + "</td></tr>");
}
strDistance.Append("<tr><td>" + ds.Tables[0].Rows[i]["Distance"] + "</td></tr>");
strTravelMode.Append("<tr><td>" + ds.Tables[0].Rows[i]["Travel_Mode"] + "</td></tr>");
strFare.Append("<tr><td>" + ds.Tables[0].Rows[i]["Fare_Amount"] + "</td></tr>");
}
//strWorkArea.Append("</table>");
strFromPlace.Append("</table>");
strToPlace.Append("</table>");
strSFCCategory.Append("</table>");
strDistance.Append("</table>");
strTravelMode.Append("</table>");
strFare.Append("</table>");
#endregion cp details
#region cp doctor details
StringBuilder strDoctorName = new StringBuilder();
if (drDoctors.Length > 0)
{
foreach (DataRow drr in drDoctors)
{
if (drr["Qualification"] != "")
{
strDoctorName.Append(drr["Customer_Name"] + "_" + drr["MDL_Number"] + "_" + drr["Speciality_Name"] + "_" + drr["Qualification"] + "</br>");
}
else
{
strDoctorName.Append(drr["Customer_Name"] + "_" + drr["MDL_Number"] + "_" + drr["Speciality_Name"] + "</br>");
}
}
}
#endregion cp doctor details
if (Convert.ToString(ds.Tables[0].Rows[i]["Status"]).ToUpper() == "APPROVED")
{
strContent.Append("<tr><td></td>");
}
else
{
strContent.Append("<tr><td><a onclick='fnEditCPDetails(\"" + cpCode + "\");'>Edit</a></td>");
}
strContent.Append("<td>" + Convert.ToString(ds.Tables[0].Rows[i]["CP_Name"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(ds.Tables[0].Rows[i]["Status"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(ds.Tables[0].Rows[i]["Expense_Entity_Name"]) + "</td>");
// strContent.Append("<td>" + Convert.ToString(strWorkArea) + "</td>");
strContent.Append("<td>" + Convert.ToString(ds.Tables[0].Rows[i]["Work_Area"]) + "</td>");
strContent.Append("<td>" + Convert.ToString(strFromPlace) + "</td>");
strContent.Append("<td>" + Convert.ToString(strToPlace) + "</td>");
strContent.Append("<td>" + Convert.ToString(strSFCCategory) + "</td>");
strContent.Append("<td>" + Convert.ToString(strDistance) + "</td>");
strContent.Append("<td>" + Convert.ToString(strFare) + "</td>");
strContent.Append("<td>" + Convert.ToString(strTravelMode) + "</td>");
strContent.Append("<td>" + Convert.ToString(strDoctorName) + "</td>");
strContent.Append("<td>" + Convert.ToString(ds.Tables[0].Rows[i]["Unapprove_Reason"]) + "</td></tr>");
}
}
}
strContent.Append("</tbody></table>");
MasterController objMaster = new MasterController();
string compCode = _objCurInfo.GetCompanyCode();
DataSet dsUser = null;
string userName = string.Empty;
dsUser = new DataSet();
dsUser = objMaster.GetRegionWiseUser(compCode, regionCode);
if (dsUser != null && dsUser.Tables[0].Rows.Count > 0)
{
userName = dsUser.Tables[0].Rows[0]["User_Name"].ToString();
}
else
{
userName = _objCurInfo.GetUserName();
}
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string fileName = "CampaignPlanner_" + "_" + compCode + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(strContent.ToString(), accKey, fileName, "bulkdatasvc");
}
catch (Exception ex)
{
Dictionary <string, string> dicObj = new Dictionary <string, string>();
dicObj.Add("regionCode", regionCode);
dicObj.Add("strContent", strContent.ToString());
dicObj.Add("blobUrl", blobUrl);
DataControl.Impl.ExceptionHandler.WriteLog(ex: ex, dic: dicObj);
}
return(strContent.ToString() + "$" + _objJson.Serialize(ds) + "^" + blobUrl);
}
public string GetExpenseAnalysisReportNG(string userCode, string startDate, string endDate)
{
StringBuilder strTblContent = new StringBuilder();
StringBuilder strMainTblContent = new StringBuilder();
DataControl.CurrentInfo _objCurInfo = new DataControl.CurrentInfo();
DataControl.BLExpense _objBLExpense = new DataControl.BLExpense();
DataSet ds = new DataSet();
int intTblCount = 0;
string strTableId = string.Empty;
string strImgId = string.Empty;
double dblEligAmount = 0.0;
double dblEnteredAmt = 0.0;
double dblTotalExpense = 0.0;
string eligAmount = "";
StringBuilder strUserInfo = new StringBuilder();
ds = _objBLExpense.GetExpenseAnalysisReportNG(_objCurInfo.GetCompanyCode(), userCode, startDate, endDate);
if (ds.Tables.Count > 0)
{
//User Info
if (ds.Tables[3].Rows.Count > 0)
{
strUserInfo.Append("<table id='tblUserInfo' cellspacing=0 cellpadding=0 width='100%'>");
strUserInfo.Append("<tr><td colspan=4><div id='dvReportPeriod'>Expense Analysis Report of " +
ds.Tables[3].Rows[0]["User_Name"].ToString() + " for the period ");
strUserInfo.Append("" + startDate.Split('-')[2] + "-" + startDate.Split('-')[1] + "-" + startDate.Split('-')[0] + " to ");
strUserInfo.Append("" + endDate.Split('-')[2] + "-" + endDate.Split('-')[1] + "-" + endDate.Split('-')[0] + " </div></td></tr>");
strUserInfo.Append("<tr><td colspan=4 align='right'><div style='width:100%;float:left;'><img style='display: inline;' id='imgClient' ></div></td></tr>");
strUserInfo.Append("<tr><td>Company Name </td><td> : " + ds.Tables[3].Rows[0]["Company_Name"].ToString() + "</td>");
strUserInfo.Append("<td>Region Name </td><td> : " + ds.Tables[3].Rows[0]["Region_Name"].ToString() + "</td></tr>");
strUserInfo.Append("<tr><td>Employee Name </td><td > : " + ds.Tables[3].Rows[0]["Employee_Name"].ToString() + "(" +
ds.Tables[3].Rows[0]["Employee_Number"].ToString() + ")</td>");
if (string.IsNullOrEmpty(ds.Tables[3].Rows[0]["Date_of_Joining"].ToString()))
{
strUserInfo.Append("<td> Date of Joining </td><td > : Not Available</td></tr>");
}
else
{
strUserInfo.Append("<td> Date of Joining </td><td > : " + ds.Tables[3].Rows[0]["Date_of_Joining"].ToString() + "</td></tr>");
}
strUserInfo.Append("<tr><td>User Name </td><td> : " + ds.Tables[3].Rows[0]["User_Name"].ToString() + "</td>");
strUserInfo.Append("<td>Designation </td><td > : " + ds.Tables[3].Rows[0]["User_Type_Name"].ToString() + "</td></tr>");
StringBuilder strDivision = new StringBuilder();
if (ds.Tables.Count > 4)
{
for (var j = 0; j < ds.Tables[5].Rows.Count; j++)
{
strDivision.Append(ds.Tables[5].Rows[j]["Division_Name"].ToString() + ",");
}
}
if (string.IsNullOrEmpty(strDivision.ToString()))
{
strUserInfo.Append("<tr><td>Division Name </td><td > : Not Available</td>");
}
else
{
strUserInfo.Append("<tr><td>Division Name </td><td > : " + strDivision.ToString().TrimEnd(',') + "</td>");
}
if (string.IsNullOrEmpty(ds.Tables[3].Rows[0]["ICICI_Account_Number"].ToString()))
{
strUserInfo.Append("<td>Bank Account No </td><td> : Not Available</td></tr>");
}
else
{
strUserInfo.Append("<td>Bank Account No </td><td> : " + ds.Tables[3].Rows[0]["ICICI_Account_Number"].ToString() + "</td></tr>");
}
if (string.IsNullOrEmpty(ds.Tables[3].Rows[0]["Under_User_Name"].ToString()))
{
strUserInfo.Append("<tr><td>Reporting Manager Name </td><td> : Not Available (");
}
else
{
strUserInfo.Append("<tr><td>Reporting Manager Name </td><td> : " + ds.Tables[3].Rows[0]["Under_User_Name"].ToString() + "(");
}
if (string.IsNullOrEmpty(ds.Tables[3].Rows[0]["Reporting_Employee_Number"].ToString()))
{
strUserInfo.Append("Not Available</td>");
}
else
{
strUserInfo.Append(ds.Tables[3].Rows[0]["Reporting_Employee_Number"].ToString() + ")</td>");
}
if (string.IsNullOrEmpty(ds.Tables[3].Rows[0]["Mobile"].ToString()))
{
strUserInfo.Append("<td>Emp Mobile No</td><td > :Not Available</td></tr></table></br>");
}
else
{
strUserInfo.Append("<td>Emp Mobile No</td><td > : " + ds.Tables[3].Rows[0]["Mobile"].ToString() + "</td></tr></table></br>");
}
}
int includeExpenseChart = 0;
if (ds.Tables[0].Rows.Count > 0)
{
includeExpenseChart = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
if (includeExpenseChart == 0)
{
DataTable dtExpenseMode = new DataTable();
if (ds.Tables[1].Rows.Count > 0)
{
dtExpenseMode = ds.Tables[1].DefaultView.ToTable(true, "Expense_Mode");
if (dtExpenseMode.Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[1].Rows)
{
if (!string.IsNullOrEmpty(dr["Expense_Amount"].ToString().Trim()))
{
dblTotalExpense += Convert.ToDouble(dr["Expense_Amount"].ToString().Trim());
}
}
strMainTblContent.Append("<table border='0' cellspacing='0' cellpadding='0' style='width:100%;'>");
strMainTblContent.Append("<tr><td><div id='dvNetTotal'>");
strMainTblContent.Append("TOTAL EXPENSE AMOUNT IS " + dblTotalExpense.ToString("N2") + "</div></br></td></tr>");
strMainTblContent.Append("<tr><td align='right' valign='top' width='100%'><table id='tbl_" +
intTblCount.ToString() + "' border='0' cellspacing='0' cellpadding='0' width='100%'>");
foreach (DataRow dr in dtExpenseMode.Rows)
{
DataRow[] rowFilter;
double dblExpence = 0.0;
strTblContent.Clear();
strTblContent.Append("<table id='" + strTableId + "' cellspacing='0' cellpadding='0' class='data display datatable clsExpense'>");
// strTblContent.Append("<tr><td>" + dr["Expense_Mode"].ToString().Trim().ToUpper() + " Expense Details : </td></tr>");
strTblContent.Append("<tr><td class='tdHeader'>DCR Date</td><td class='tdHeader'>DCR Type</td><td class='tdHeader'>DCR Status</td>");
strTblContent.Append("<td class='tdHeader'>Category</td><td class='tdHeader'>Place Worked</td><td class='tdHeader'>Place From</td>");
strTblContent.Append("<td class='tdHeader'>Place To</td><td class='tdHeader' style='width:4% !important;'>Travel Mode</td>");
strTblContent.Append("<td class='tdHeader' style='width:4% !important;'>Distance</td>");
strTblContent.Append("<td class='tdHeader'>Expense Type</td><td class='tdHeader' style='width:4% !important;'>Expense Amount</td>");
strTblContent.Append("<td class='tdHeader'>Remarks</td></tr>");
rowFilter = ds.Tables[1].AsEnumerable().Where(d => d["Expense_Mode"].ToString().Trim().ToUpper() ==
dr["Expense_Mode"].ToString().Trim().ToUpper()).ToArray();
for (int k = 0; k < rowFilter.Length; k++)
{
DataRow[] rowfilterHop;
eligAmount = rowFilter[k]["Expense_Amount"].ToString().Trim();
dblEligAmount = 0.0;
dblEnteredAmt = 0.0;
rowfilterHop = ds.Tables[2].AsEnumerable().Where(z => z["DCR_Actual_Date"].ToString() == rowFilter[k]["DCR_Date"].ToString() && z["DCR_HOP_Flag"].ToString() == rowFilter[k]["Flag"].ToString()).ToArray();
if (rowfilterHop.Length > 0 && rowFilter[k]["Category"].ToString().Trim().ToUpper() != "HQ")
{
strTblContent.Append("<tr>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["DCR_Date"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["DCR_Flag"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["Status"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["Category"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["Place_Worked"].ToString() + "</td>");
for (int index = 0; index < rowfilterHop.Length; index++)
{
string fromPlace = string.Empty;
string toPlace = string.Empty;
if (rowfilterHop[index]["Route_Way"].ToString().Trim().ToUpper() == "R")
{
fromPlace = rowfilterHop[index]["Place_To"].ToString().Trim();
toPlace = rowfilterHop[index]["From_Place"].ToString().Trim();
}
else
{
fromPlace = rowfilterHop[index]["From_Place"].ToString().Trim();
toPlace = rowfilterHop[index]["Place_To"].ToString().Trim();
}
// END: SFC Changes, Added by Michael.
strTblContent.Append("<td>" + fromPlace + "</td>");
strTblContent.Append("<td>" + toPlace + "</td>");
strTblContent.Append("<td>" + rowfilterHop[index].ItemArray[4] + "</td>");
if (index == 0)
{
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>" + rowFilter[k]["Distance"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>" +
rowFilter[k]["Expense_Type_Name"].ToString() + "</td>");
if (!string.IsNullOrEmpty(rowFilter[k]["Eligibility_Amount"].ToString()))
{
if (rowFilter[k]["Eligibility_Amount"].ToString() != "0.00")
{
double eliAmt = double.Parse(rowFilter[k]["Eligibility_Amount"].ToString());
if (eliAmt < double.Parse(eligAmount))
{
strTblContent.Append("<td style='color:red;text-align:right;' rowspan='" +
rowfilterHop.Length.ToString() + "'>" + eligAmount + "(" + eliAmt + ")</td>");
}
else
{
strTblContent.Append("<td style='text-align:right;' rowspan='" + rowfilterHop.Length.ToString() + "'>" +
eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;' rowspan='" + rowfilterHop.Length.ToString() + "'>" + eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;' rowspan='" + rowfilterHop.Length.ToString() + "'>" + eligAmount + "</td>");
}
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>" +
rowFilter[k]["Expense_Remarks"].ToString() + "</td>");
strTblContent.Append("</tr >");
}
else
{
strTblContent.Append("</tr>");
}
}
dblExpence += Convert.ToDouble(rowFilter[k]["Expense_Amount"].ToString());
}
else
{
strTblContent.Append("<tr>");
strTblContent.Append("<td>" + rowFilter[k]["DCR_Date"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["DCR_Flag"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Status"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Category"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Place_Worked"].ToString() + "</td>");
string fromPlace = string.Empty;
string toPlace = string.Empty;
if (rowFilter[k]["Route_Way"].ToString().Trim().ToUpper() == "R")
{
fromPlace = rowFilter[k]["Place_To"].ToString().Trim();
toPlace = rowFilter[k]["From_Place"].ToString().Trim();
}
else
{
fromPlace = rowFilter[k]["From_Place"].ToString().Trim();
toPlace = rowFilter[k]["Place_To"].ToString().Trim();
}
strTblContent.Append("<td>" + fromPlace + "</td>");
strTblContent.Append("<td>" + toPlace + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Travel_Mode"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Distance"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Expense_Type_Name"].ToString() + "</td>");
if (!string.IsNullOrEmpty(rowFilter[k]["Eligibility_Amount"].ToString()))
{
if (rowFilter[k]["Eligibility_Amount"].ToString() != "0.00")
{
double eliAmt = double.Parse(rowFilter[k]["Eligibility_Amount"].ToString());
if (eliAmt < double.Parse(eligAmount))
{
strTblContent.Append("<td style='color:red;text-align:right;'>" + eligAmount + "(" + eliAmt + ")</td>");
}
else
{
strTblContent.Append("<td style='text-align:right;'>" + eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;'>" + eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;'>" + eligAmount + "</td>");
}
strTblContent.Append("<td>" + rowFilter[k]["Expense_Remarks"].ToString() + "</td>");
strTblContent.Append("</tr>");
dblExpence += Convert.ToDouble(rowFilter[k]["Expense_Amount"].ToString());
}
}
strTblContent.Append("<tr class='Font'><td colspan=11 style='text-align:right;font-weight:bold'>Grand Total of " + dr["Expense_Mode"].ToString() + " expense:</td>");
strTblContent.Append("<td style='text-align:left;font-weight:bold'>Rs." + dblExpence.ToString("N2") + "/-</td></tr>");
strTblContent.Append("</table></br>");
strMainTblContent.Append("<tr><td style='font-weight:bold;text-align:left;'>");
strMainTblContent.Append("TOTAL " + dr["Expense_Mode"].ToString().ToUpper() + " EXPENSE : Rs." + dblExpence.ToString("N2"));
strMainTblContent.Append("</td></tr>");
strMainTblContent.Append("<tr><td style='font-weight:bold;text-align:left;'>");
strMainTblContent.Append(dr["Expense_Mode"].ToString().ToUpper() + " EXPENSE DETAILS : </td></tr>");
strMainTblContent.Append("<tr><td align='left' valign='top'>");
strMainTblContent.Append(strTblContent.ToString() + "</td></tr>");
}
strMainTblContent.Append("</table></br>");
}
}
else
{
strMainTblContent.Append("<div style='width:100%'>No expense details found</div>");
}
}
else
{
DataTable dtExpenseMode = new DataTable();
DataTable dtDisExpense = new DataTable();
DataSet dsFinalExpense = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("DCR_Date", typeof(String));
dt.Columns.Add("Expense_Mode", typeof(String));
dt.Columns.Add("Place_Worked", typeof(String));
dt.Columns.Add("Category", typeof(String));
dt.Columns.Add("From_Place", typeof(String));
dt.Columns.Add("Place_To", typeof(String));
dt.Columns.Add("Travel_Mode", typeof(String));
dt.Columns.Add("Distance", typeof(String));
dt.Columns.Add("Expense_Type_Code", typeof(String));
dt.Columns.Add("Expense_Type_Name", typeof(String));
dt.Columns.Add("Expense_Amount", typeof(String));
dt.Columns.Add("DCR_Flag", typeof(String));
dt.Columns.Add("Flag", typeof(String));
dt.Columns.Add("Region_Code", typeof(String));
dt.Columns.Add("Region_Classification_Code", typeof(String));
dt.Columns.Add("Expense_Entity_Code", typeof(String));
dt.Columns.Add("Eligibility_Amount", typeof(String));
dt.Columns.Add("Status", typeof(String));
dt.Columns.Add("Expense_Remarks", typeof(String));
dt.Columns.Add("Route_Way", typeof(String));
dt.Columns.Add("DCR_Actual_Date", typeof(String));
dt.Columns.Add("DCR_Code", typeof(String));
dsFinalExpense.Tables.Add(dt);
dsFinalExpense.AcceptChanges();
if (ds.Tables[1].Rows.Count > 0)
{
ArrayList alExpenceMode = new ArrayList();
alExpenceMode.Add("Daily");
alExpenceMode.Add("Monthly");
alExpenceMode.Add("Weekly");
alExpenceMode.Add("Fortnightly");
alExpenceMode.Add("Yearly");
//Get the distinct expense records
dtDisExpense = ds.Tables[1].DefaultView.ToTable(true, "DCR_Date", "Expense_Entity_Code", "DCR_Flag", "Expense_Type_Code");
if (dtDisExpense.Rows.Count > 0)
{
foreach (DataRow dr in dtDisExpense.Rows)
{
DataRow[] drDisExp;
drDisExp = ds.Tables[1].AsEnumerable().Where(a => a["DCR_Date"].ToString() == dr["DCR_Date"].ToString() &&
a["Expense_Entity_Code"].ToString() == dr["Expense_Entity_Code"].ToString() &&
a["DCR_Flag"].ToString() == dr["DCR_Flag"].ToString() &&
a["Expense_Type_Code"].ToString() == dr["Expense_Type_Code"].ToString()).ToArray();
bool blFlag = false;
foreach (DataRow drPar in drDisExp)
{
int count = ds.Tables[4].Rows.Count;
int loopCount = 0;
while (count > loopCount)
{
string parentRegionCode = ds.Tables[4].Rows[Convert.ToInt32(count - loopCount) - 1]["Region_Code"].ToString();
if (parentRegionCode == drPar["Region_Code"].ToString())
{
dsFinalExpense.Tables[0].ImportRow(drPar);
blFlag = true;
break;
}
loopCount = loopCount + 1;
}
if (blFlag)
{
break;
}
}
}
}
// double dblTotalExpense=0.00;
foreach (DataRow dr in dsFinalExpense.Tables[0].Rows)
{
if (!string.IsNullOrEmpty(dr["Expense_Amount"].ToString().Trim()))
{
dblTotalExpense += Convert.ToDouble(dr["Expense_Amount"].ToString().Trim());
}
}
strMainTblContent.Append("<table border='0' cellspacing='1' cellpadding='1' width='100%'>");
strMainTblContent.Append("<tr><td><div id='dvNetTotal'>");
strMainTblContent.Append("TOTAL EXPENSE AMOUNT IS " + dblTotalExpense.ToString("N2") + "</div></td></tr>");
strMainTblContent.Append("<tr><td align='right' valign='top' width='100%'><table id='tbl_" + intTblCount.ToString() + "' border='0' cellspacing='0' cellpadding='0' width='100%'>");
DataRow[] rowFilter;
DataRow[] rowfilterHop;
for (int j = 0; j < alExpenceMode.Count; j++)
{
strTblContent.Clear();
intTblCount++;
strTableId = "tbl_" + intTblCount.ToString();
strImgId = "img" + intTblCount.ToString();
rowFilter = dsFinalExpense.Tables[0].AsEnumerable().Where(d => d["Expense_Mode"].ToString().Trim().ToUpper() ==
alExpenceMode[j].ToString().Trim().ToUpper()).ToArray();
double dblExpence = 0.0;
if (rowFilter.Length > 0)
{
strTblContent.Append("<table width='100%' id='" + strTableId + "' cellspacing='0' cellpadding='0' class='data display datatable clsExpense'>");
// strTblContent.Append("<tr><td>" + alExpenceMode[j].ToString().ToUpper() + " EXPENSE DETAILS : </td></tr>");
strTblContent.Append("<tr><td class='tdHeader'>DCR Date</td><td class='tdHeader'>DCR Type</td><td class='tdHeader'>DCR Status</td>");
strTblContent.Append("<td class='tdHeader'>Category</td><td class='tdHeader'>Place Worked</td><td class='tdHeader'>Place From</td>");
strTblContent.Append("<td class='tdHeader'>Place To</td><td class='tdHeader' style='width:4% !important;'>Travel Mode</td>");
strTblContent.Append("<td class='tdHeader' style='width:4% !important;'>Distance</td>");
strTblContent.Append("<td class='tdHeader'>Expense Type</td><td class='tdHeader' style='width:4% !important;'>Expense Amount</td>");
strTblContent.Append("<td class='tdHeader'>Remarks</td></tr>");
for (int k = 0; k < rowFilter.Length; k++)
{
eligAmount = rowFilter[k]["Expense_Amount"].ToString().Trim();
dblEligAmount = 0.0;
dblEnteredAmt = 0.0;
rowfilterHop = ds.Tables[2].AsEnumerable().Where(z => z["DCR_Actual_Date"].ToString() == rowFilter[k]["DCR_Date"].ToString()).ToArray();
if (rowfilterHop.Length > 0 && rowFilter[k]["Category"].ToString().Trim().ToUpper() != "HQ")
{
strTblContent.Append("<tr>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["DCR_Date"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["DCR_Flag"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["Status"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["Category"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>");
strTblContent.Append(rowFilter[k]["Place_Worked"].ToString() + "</td>");
for (int index = 0; index < rowfilterHop.Length; index++)
{
string fromPlace = string.Empty;
string toPlace = string.Empty;
if (rowfilterHop[index]["Route_Way"].ToString().Trim().ToUpper() == "R")
{
fromPlace = rowfilterHop[index]["Place_To"].ToString().Trim();
toPlace = rowfilterHop[index]["From_Place"].ToString().Trim();
}
else
{
fromPlace = rowfilterHop[index]["From_Place"].ToString().Trim();
toPlace = rowfilterHop[index]["Place_To"].ToString().Trim();
}
// END: SFC Changes, Added by Michael.
strTblContent.Append("<td>" + fromPlace + "</td>");
strTblContent.Append("<td>" + toPlace + "</td>");
strTblContent.Append("<td>" + rowfilterHop[index].ItemArray[4] + "</td>");
if (index == 0)
{
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>" + rowFilter[k]["Distance"].ToString() + "</td>");
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>" + rowFilter[k]["Expense_Type_Name"].ToString() + "</td>");
if (!string.IsNullOrEmpty(rowFilter[k]["Eligibility_Amount"].ToString()))
{
if (rowFilter[k]["Eligibility_Amount"].ToString() != "0.00")
{
double eliAmt = double.Parse(rowFilter[k]["Eligibility_Amount"].ToString());
if (eliAmt < double.Parse(eligAmount))
{
strTblContent.Append("<td style='color:red;text-align:right;' rowspan='" +
rowfilterHop.Length.ToString() + "'>" + eligAmount + "(" + eliAmt + ")</td>");
}
else
{
strTblContent.Append("<td style='text-align:right;' rowspan='" + rowfilterHop.Length.ToString() + "'>" +
eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;' rowspan='" + rowfilterHop.Length.ToString() + "'>" + eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;' rowspan='" + rowfilterHop.Length.ToString() + "'>" + eligAmount + "</td>");
}
strTblContent.Append("<td rowspan='" + rowfilterHop.Length.ToString() + "'>" +
rowFilter[k]["Expense_Remarks"].ToString() + "</td>");
strTblContent.Append("</tr >");
}
else
{
strTblContent.Append("</tr>");
}
}
dblExpence += Convert.ToDouble(rowFilter[k]["Expense_Amount"].ToString());
}
else
{
strTblContent.Append("<tr>");
strTblContent.Append("<td>" + rowFilter[k]["DCR_Date"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["DCR_Flag"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Status"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Category"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Place_Worked"].ToString() + "</td>");
string fromPlace = string.Empty;
string toPlace = string.Empty;
if (rowFilter[k]["Route_Way"].ToString().Trim().ToUpper() == "R")
{
fromPlace = rowFilter[k]["Place_To"].ToString().Trim();
toPlace = rowFilter[k]["From_Place"].ToString().Trim();
}
else
{
fromPlace = rowFilter[k]["From_Place"].ToString().Trim();
toPlace = rowFilter[k]["Place_To"].ToString().Trim();
}
strTblContent.Append("<td>" + fromPlace + "</td>");
strTblContent.Append("<td>" + toPlace + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Travel_Mode"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Distance"].ToString() + "</td>");
strTblContent.Append("<td>" + rowFilter[k]["Expense_Type_Name"].ToString() + "</td>");
if (!string.IsNullOrEmpty(rowFilter[k]["Eligibility_Amount"].ToString()))
{
if (rowFilter[k]["Eligibility_Amount"].ToString() != "0.00")
{
double eliAmt = double.Parse(rowFilter[k]["Eligibility_Amount"].ToString());
if (eliAmt < double.Parse(eligAmount))
{
strTblContent.Append("<td style='color:red;text-align:right;'>" + eligAmount + "(" + eliAmt + ")</td>");
}
else
{
strTblContent.Append("<td style='text-align:right;'>" + eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;'>" + eligAmount + "</td>");
}
}
else
{
strTblContent.Append("<td style='text-align:right;'>" + eligAmount + "</td>");
}
strTblContent.Append("<td>" + rowFilter[k]["Expense_Remarks"].ToString() + "</td>");
strTblContent.Append("</tr >");
dblExpence += Convert.ToDouble(rowFilter[k]["Expense_Amount"].ToString());
}
}
strTblContent.Append("<tr class='Font'><td colspan=11 style='text-align:right !important;font-weight:bold;'>Grand Total of " +
alExpenceMode[j].ToString() + " expense:</td><td style='text-align:left;font-weight:bold'>Rs." + dblExpence.ToString("N2") + "/-</td></tr>");
strTblContent.Append("</table><br>");
strMainTblContent.Append("<tr><td style='font-weight:bold;'>");
strMainTblContent.Append("Total " + alExpenceMode[j].ToString().ToUpper() + " EXPENSE : Rs." + dblExpence.ToString("N2"));
strMainTblContent.Append("</td></tr>");
strMainTblContent.Append("<tr><td style='font-weight:bold;'>");
strMainTblContent.Append(alExpenceMode[j].ToString().ToUpper() + " EXPENSE DETAILS : </td></tr>");
strMainTblContent.Append("<tr><td align='left'>");
strMainTblContent.Append(strTblContent.ToString() + "</td></tr>");
}
}
strMainTblContent.Append("</td></tr></table>");
}
else
{
strMainTblContent.Append("<div style='width:100%'>No expense details found</div>");
}
}
}
}
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = _objCurInfo.GetUserName();
string compCode = _objCurInfo.GetCompanyCode();
string fileName = "EXPENSEANALYSIS_" + "_" + compCode + "_" + userName + ".xls";
string blobUrl = string.Empty;
blobUrl = objAzureBlob.AzureBlobUploadText(strUserInfo.ToString() + strMainTblContent.ToString(), accKey, fileName, "bulkdatasvc");
return(strUserInfo.ToString() + strMainTblContent.ToString() + "$" + blobUrl);
}
public string GetDoctorVisitAnalysisSpecialityWiseReportFreqPopUp(FormCollection coll)
{
string companyCode = _objcurrentInfo.GetCompanyCode();
string userCode = coll[COLL_USER_CODE];
int month = Convert.ToInt32(coll[COLL_MONTH]);
int year = Convert.ToInt32(coll[COLL_YEAR]);
string mode = coll[Coll_MODE].ToString();
string regionCode = coll[COLL_REGION_CODE].ToString();
string specialityCode = coll["specialityCode"].ToString();
string categoryCode = coll["categoryCode"].ToString();
string blobUrl = "";
StringBuilder sbTblContent = new StringBuilder();
StringBuilder sbHeader = new StringBuilder();
DataSet ds = new DataSet();
ds = _objBL.GetDoctorVisitAnalysisSpecialityWiseReportPopUp(companyCode, userCode, regionCode, month, year, specialityCode, categoryCode, mode);
if (ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
//user details
sbTblContent.Append("<table class='headerTableUser' cellspacing='0' cellpadding='0'>");
sbTblContent.Append("<tr>");
sbTblContent.Append("<td style='font-weight:bold;'>User Name</td><td>" + ds.Tables[0].Rows[0]["User_Name"] + "</td>");
sbTblContent.Append("<td style='font-weight:bold;'>Division Name</td><td>" + ds.Tables[0].Rows[0]["Division_Name"] + "</td>");
sbTblContent.Append("<td style='font-weight:bold;'>Manager Name</td><td>" + ds.Tables[0].Rows[0]["Manager1"] + "</td>");
sbTblContent.Append("</tr>");
sbTblContent.Append("<tr>");
sbTblContent.Append("<td style='font-weight:bold;'>Employee Name</td><td>" + ds.Tables[0].Rows[0]["Employee_Name"] + "</td>");
sbTblContent.Append("<td style='font-weight:bold;'>Region Name</td><td>" + ds.Tables[0].Rows[0]["Region_Name"] + "</td>");
sbTblContent.Append("<td style='font-weight:bold;'>Reporting HQ</td><td>" + ds.Tables[0].Rows[0]["Manager1_Region"] + "</td>");
sbTblContent.Append("</tr>");
sbTblContent.Append("<tr>");
sbTblContent.Append("<td style='font-weight:bold;'>Employee No</td><td>" + ds.Tables[0].Rows[0]["Employee_Number"] + "</td>");
sbTblContent.Append("<td style='font-weight:bold;'>Date Of Joining</td><td>" + ds.Tables[0].Rows[0]["Date_of_Joining"] + "</td>");
sbTblContent.Append("<td ></td><td></td>");
sbTblContent.Append("</tr>");
sbTblContent.Append("</table><br />");
// Detail Table
sbTblContent.Append("<table id='tblFreqCount' class='data display dataTable box' cellspacing='0' cellpadding='0' width='100%'>");
sbTblContent.Append("<thead><tr>");
sbTblContent.Append("<th>S.No</th>");
sbTblContent.Append("<th>Doctor Name</th>");
sbTblContent.Append("<th>MDL No</th>");
sbTblContent.Append("<th>Category</th>");
sbTblContent.Append("<th>Speciality</th>");
sbTblContent.Append("<th>Hospital Name</th>");
sbTblContent.Append("<th>No of Visits</th>");
sbTblContent.Append("<th>Doctor covered frequency</th>");
sbTblContent.Append("</tr></thead>");
sbTblContent.Append("<tbody>");
int cnt = 1;
int standaredVisitCont = Convert.ToInt32(ds.Tables[1].Rows[0]["Standard_Visits_Count"]);
DataRow[] drAbv, drRight, drBelow;
drAbv = ds.Tables[1].AsEnumerable().Where(a => Convert.ToInt32(a["Total_Visits"]) > standaredVisitCont).ToArray();
drRight = ds.Tables[1].AsEnumerable().Where(a => Convert.ToInt32(a["Total_Visits"]) == standaredVisitCont).ToArray();
drBelow = ds.Tables[1].AsEnumerable().Where(a => Convert.ToInt32(a["Total_Visits"]) < standaredVisitCont).ToArray();
foreach (DataRow dr in drAbv)
{
sbTblContent.Append("<tr>");
sbTblContent.Append("<td>" + cnt.ToString() + "</td>");
sbTblContent.Append("<td>" + dr["Doctor_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["MDL_Number"] + "</td>");
sbTblContent.Append("<td>" + dr["Category_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["Speciality_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["Hospital_Name"] + "</td>");
sbTblContent.Append("<td style='text-align:right;'>" + dr["Total_Visits"] + "</td>");
sbTblContent.Append("<td>Doctor covered above Frequency</td>");
sbTblContent.Append("</tr>");
cnt++;
}
foreach (DataRow dr in drRight)
{
sbTblContent.Append("<tr>");
sbTblContent.Append("<td>" + cnt.ToString() + "</td>");
sbTblContent.Append("<td>" + dr["Doctor_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["MDL_Number"] + "</td>");
sbTblContent.Append("<td>" + dr["Category_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["Speciality_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["Hospital_Name"] + "</td>");
sbTblContent.Append("<td style='text-align:right;'>" + dr["Total_Visits"] + "</td>");
sbTblContent.Append("<td>Doctor covered right Frequency</td>");
sbTblContent.Append("</tr>");
cnt++;
}
foreach (DataRow dr in drBelow)
{
sbTblContent.Append("<tr>");
sbTblContent.Append("<td>" + cnt.ToString() + "</td>");
sbTblContent.Append("<td>" + dr["Doctor_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["MDL_Number"] + "</td>");
sbTblContent.Append("<td>" + dr["Category_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["Speciality_Name"] + "</td>");
sbTblContent.Append("<td>" + dr["Hospital_Name"] + "</td>");
sbTblContent.Append("<td style='text-align:right;'>" + dr["Total_Visits"] + "</td>");
sbTblContent.Append("<td>Doctor covered below Frequency</td>");
sbTblContent.Append("</tr>");
cnt++;
}
sbTblContent.Append("</tbody></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 = "DrVisitSpecialityWiseFreqAnalysisPopUp_" + "_" + compCode + "_" + userName + ".xls";
// header for print and excel
sbHeader.Append("<div style='width:100%;float:left;font-weight:bold;font-size:15px;'>Frequency Achieved Doctors Visit</div>");
blobUrl = objAzureBlob.AzureBlobUploadText(sbHeader.ToString() + sbTblContent.ToString(), accKey, fileName, "bulkdatasvc");
}
else
{
sbTblContent.Append("<div style='width:100%;float:left;'>“No information available for this Category");
sbTblContent.Append("</div>");
}
return(sbTblContent.ToString() + "$" + blobUrl + "$" + sbHeader.ToString());
}
public string GetBrandAnalysisReport(string userCode, string month, string year, string dcrStatus)
{
StringBuilder strTblContent = new StringBuilder();
DataControl.CurrentInfo _objCurInfo = new DataControl.CurrentInfo();
DataControl.BAL_DoctorVisitAnalysis _objBLDoctor = new DataControl.BAL_DoctorVisitAnalysis();
DataSet ds = new DataSet();
StringBuilder strUserInfo = new StringBuilder();
ds = _objBLDoctor.GetBrandAnalysisReport(_objCurInfo.GetCompanyCode(), userCode, month, year, dcrStatus);
if (ds.Tables.Count > 0)
{
//User Info
if (ds.Tables[6].Rows.Count > 0)
{
strUserInfo.Append("<table id='tblUserInfo' cellspacing=0 cellpadding=0 width='100%'>");
strUserInfo.Append("<tr><td colspan=4><div id='dvReportPeriod'>Brand Analysis Report * of " +
ds.Tables[6].Rows[0]["User_Name"].ToString() + " for the month of ");
strUserInfo.Append("" + CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(Convert.ToInt32(month)).ToString() + "-" + year + " </div></td></tr>");
strUserInfo.Append("<tr><td colspan=4> * This report contains ");
string status = string.Empty;
string[] arStatus;
arStatus = dcrStatus.Split('^');
string[] strArray = new string[4];
strArray[0] = "Unapproved";
strArray[1] = "Applied";
strArray[2] = "Approved";
strArray[3] = "Drafted";
StringBuilder strStatus = new StringBuilder();
for (int z = 0; z < arStatus.Length - 1; z++)
{
strStatus.Append(strArray[Convert.ToInt32(arStatus[z])].ToString() + ",");
}
strUserInfo.Append(strStatus.ToString().TrimEnd(',') + " records </td></tr>");
strUserInfo.Append("<tr><td>User Name </td><td> : " + ds.Tables[6].Rows[0]["User_Name"].ToString() + " | " + ds.Tables[6].Rows[0]["Region_Name"].ToString() + "</td>");
if (string.IsNullOrEmpty(ds.Tables[6].Rows[0]["Reporting_Manager_Name"].ToString().Trim()))
{
strUserInfo.Append("<td>Reporting Manager Name </td><td> : Not Available</td></tr>");
}
else
{
strUserInfo.Append("<td>Reporting Manager Name </td><td> : " + ds.Tables[6].Rows[0]["Reporting_Manager_Name"].ToString() + "</td></tr>");
}
strUserInfo.Append("<tr><td>Employee Name </td><td > : " + ds.Tables[6].Rows[0]["Employee_Name"].ToString() + "</td>");
if (string.IsNullOrEmpty(ds.Tables[6].Rows[0]["Reporting_Employee_Name"].ToString().Trim()))
{
strUserInfo.Append("<td>Reporting manager Employee Name </td><td > : Not Available</td></tr>");
}
else
{
strUserInfo.Append("<td>Reporting manager Employee Name </td><td > : " + ds.Tables[6].Rows[0]["Reporting_Employee_Name"].ToString() + "</td></tr>");
}
strUserInfo.Append("<tr><td>Employee Number </td><td > : " + ds.Tables[6].Rows[0]["Employee_Number"].ToString() + "</td>");
if (string.IsNullOrEmpty(ds.Tables[6].Rows[0]["Reporting_Employee_Number"].ToString().Trim()))
{
strUserInfo.Append("<td>Reporting manager Employee Number </td><td > : Not Available</td></tr>");
}
else
{
strUserInfo.Append("<td>Reporting manager Employee Number </td><td > : " + ds.Tables[6].Rows[0]["Reporting_Employee_Number"].ToString() + "</td></tr>");
}
strUserInfo.Append("<tr><td>Designation </td><td > : " + ds.Tables[6].Rows[0]["User_Type_Name"].ToString() + "</td>");
strUserInfo.Append("<td>Reporting Manager Designation </td><td > : " + ds.Tables[6].Rows[0]["Reporting_User_Type_Name"].ToString() + "</td></tr>");
strUserInfo.Append("<tr><td>Region Name </td><td> : " + ds.Tables[6].Rows[0]["Region_Name"].ToString() + "</td></tr>");
strUserInfo.Append("<td>Reporting Region Name </td><td> : " + ds.Tables[6].Rows[0]["Reporting_Region_Name"].ToString() + "</td>");
strUserInfo.Append("</tr>");
strUserInfo.Append("</table></br>");
}
strTblContent.Append("<table id='tblBrand' class='data display datatable'><thead><tr><th rowspan='2' style='width:3%;'>S.No</th>");
strTblContent.Append("<th rowspan='2' style='width:5%;'>Doctor Name</th>");
strTblContent.Append("<th rowspan='2' style='width:2%;'>MDL No</th><th rowspan='2' style='width:5%;'>Category</th>");
strTblContent.Append("<th rowspan='2' style='width:5%;'>Speciality</th><th rowspan='2' style='width:5%;'>Doctor status</th>");
strTblContent.Append("<th rowspan='2' style='width:5%;'>Market Place</th>");
StringBuilder strTemp = new StringBuilder();
StringBuilder strCP = new StringBuilder();
StringBuilder strWP = new StringBuilder();
foreach (DataRow dr in ds.Tables[5].Rows)
{
strTblContent.Append("<th colspan='3' align='center' style='width:10%;'>" + dr["Brand_Name"].ToString() + "</th>");
strTemp.Append("<th>Detailed Unit Count</th><th>Samples Unit Count</th><th>Input Unit Count</th>");
}
strTblContent.Append("</tr><tr>" + strTemp.ToString() + "</tr></thead>");
strTblContent.Append("<tbody>");
int i = 0;
foreach (DataRow drDoc in ds.Tables[0].Rows)
{
i++;
strTblContent.Append("<tr><td>" + i.ToString() + "</td>");
strTblContent.Append("<td>" + drDoc["Doctor_Name"].ToString() + "</td>");
strTblContent.Append("<td>" + drDoc["MDL_Number"].ToString() + "</td>");
strTblContent.Append("<td>" + drDoc["Category_Name"].ToString() + "</td>");
strTblContent.Append("<td>" + drDoc["Speciality_Name"].ToString() + "</td>");
strTblContent.Append("<td>" + drDoc["Customer_Status"].ToString() + "</td>");
// Local AreadrDoc["Local_Area"].ToString()
if (!string.IsNullOrEmpty(drDoc["Local_Area"].ToString()))
{
strTblContent.Append("<td>" + drDoc["Local_Area"].ToString() + "</td>");
}
else
{
strTblContent.Append("<td align='center'>-</td>");
}
// Brand Details
foreach (DataRow drBrand in ds.Tables[5].Rows)
{
double isdetailedCount = 0;
double samplesCount = 0;
double inputCount = 0;
DataRow[] drIs = ds.Tables[2].AsEnumerable().Where(b => b["Brand_Code"].ToString().ToUpper() == drBrand["Brand_Code"].ToString().ToUpper() &&
b["Doctor_Code"].ToString().ToUpper() == drDoc["Doctor_Code"].ToString().ToUpper()).ToArray();
isdetailedCount = Convert.ToDouble(drIs.Sum(a => a.Field <Int32>("Is_Detailed")));
DataRow[] drSamples = ds.Tables[3].AsEnumerable().Where(b => b["Brand_Code"].ToString().ToUpper() == drBrand["Brand_Code"].ToString().ToUpper() &&
b["Doctor_Code"].ToString().ToUpper() == drDoc["Doctor_Code"].ToString().ToUpper()).ToArray();
samplesCount = Convert.ToDouble(drSamples.Sum(a => a.Field <Int32>("Quantity_Provided")));
DataRow[] drInput = ds.Tables[4].AsEnumerable().Where(b => b["Brand_Code"].ToString().ToUpper() == drBrand["Brand_Code"].ToString().ToUpper() &&
b["Doctor_Code"].ToString().ToUpper() == drDoc["Doctor_Code"].ToString().ToUpper()).ToArray();
inputCount = Convert.ToDouble(drInput.Sum(a => a.Field <Int32>("Quantity_Provided")));
strTblContent.Append("<td>" + isdetailedCount + "</td>");
strTblContent.Append("<td>" + samplesCount + "</td>");
strTblContent.Append("<td>" + inputCount + "</td>");
}
strTblContent.Append("</tr>");
}
strTblContent.Append("</tbody></table>");
}
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = _objCurInfo.GetUserName();
string compCode = _objCurInfo.GetCompanyCode();
string fileName = "BRANDANALYSIS_" + "_" + compCode + "_" + userName + ".xls";
string blobUrl = string.Empty;
blobUrl = objAzureBlob.AzureBlobUploadText(strUserInfo.ToString() + strTblContent.ToString(), accKey, fileName, "bulkdatasvc");
return(strUserInfo.ToString() + strTblContent.ToString() + "$" + blobUrl);
}
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);
}
public string GetDoctorDeviationReportDetails(string startDate, string endDate, string statusName, string userCode, string userName,
string dcrStatus, string reportFormat)
{
StringBuilder strTblContent = new StringBuilder();
string blobUrl = string.Empty;
try
{
DataControl.CurrentInfo objCurInfo = new CurrentInfo();
DataControl.BLUser objUser = new BLUser();
DataControl.BAL_DoctorVisitAnalysis objDoctorVisit = new BAL_DoctorVisitAnalysis();
IEnumerable <MVCModels.HiDoctor_Master.UserModel> lstUser = null;
IEnumerable <MVCModels.DCRHeaderModel> lstHeader = null;
IEnumerable <MVCModels.DCRDoctorVisitModel> lstDoctors = null;
strTblContent.Append(objUser.GetReportHeaderTableString(objCurInfo.GetCompanyCode(), userCode, startDate, endDate, statusName, ""));
lstHeader = objDoctorVisit.GetDCRHeaderByStatusAndDate(objCurInfo.GetCompanyCode(), dcrStatus, userCode, startDate, endDate);
StringBuilder strAcc = new StringBuilder();
ArrayList al = new ArrayList();
if (lstHeader != null)
{
#region get dcr header info
foreach (var dr in lstHeader)
{
if (!string.IsNullOrEmpty(Convert.ToString(dr.Acc1_Name)))
{
if (!al.Contains(Convert.ToString(dr.Acc1_Name)))
{
strAcc.Append(Convert.ToString(dr.Acc1_Name).Trim() + "^");
al.Add(Convert.ToString(dr.Acc1_Name).Trim());
}
}
if (!string.IsNullOrEmpty(Convert.ToString(dr.Acc2_Name)))
{
if (!al.Contains(Convert.ToString(dr.Acc2_Name)))
{
strAcc.Append(Convert.ToString(dr.Acc2_Name) + "^");
al.Add(Convert.ToString(dr.Acc2_Name).Trim());
}
}
if (!string.IsNullOrEmpty(Convert.ToString(dr.Acc3_Name)))
{
if (!al.Contains(Convert.ToString(dr.Acc3_Name)))
{
strAcc.Append(Convert.ToString(dr.Acc3_Name) + "^");
al.Add(Convert.ToString(dr.Acc3_Name).Trim());
}
}
if (!string.IsNullOrEmpty(Convert.ToString(dr.Acc4_Name)))
{
if (!al.Contains(Convert.ToString(dr.Acc4_Name)))
{
strAcc.Append(Convert.ToString(dr.Acc4_Name) + "^");
al.Add(Convert.ToString(dr.Acc4_Name).Trim());
}
}
}
strAcc.Append(userName.Split(',')[0] + "^");
#endregion dcr header info
lstDoctors = objDoctorVisit.GetDCRDoctorsByUserName(objCurInfo.GetCompanyCode(), dcrStatus, strAcc.ToString(), startDate, endDate);
strTblContent.Append("<table class='table table-striped'>");
strTblContent.Append("<thead><tr><th>Date</th><th>" + userName + "</th>");
foreach (string accName in al)
{
strTblContent.Append("<th>Doctors met by " + accName + "</th>");
}
strTblContent.Append("<th>Doctor Missed</th>");
strTblContent.Append("<th>Doctor Deviation</th></thead><tbody>");
if (lstDoctors != null)
{
foreach (var dr in lstHeader)
{
StringBuilder strDoctorContent = new StringBuilder();
strTblContent.Append("<tr><td>" + dr.DCR_Date + "</td>");
var disDcr = lstDoctors.AsEnumerable().Where(a => a.DCR_Actual_Date == dr.DCR_Date &&
a.User_Name.ToUpper() == userName.Split(',')[0].ToString().ToUpper()).ToList();
ArrayList alManagerDocs = new ArrayList();
ArrayList alChildDocs = new ArrayList();
if (disDcr.Count > 0)
{
strDoctorContent.Append("<table>");
foreach (var doc in disDcr)
{
strDoctorContent.Append("<tr><td>" + doc.Doctor_Name + "</td></tr>");
alManagerDocs.Add(doc.Doctor_Name);
}
strDoctorContent.Append("</table>");
strTblContent.Append("<td>" + strDoctorContent.ToString() + "</td>");
foreach (string accName in al)
{
StringBuilder strAccDocs = new StringBuilder();
if (accName == Convert.ToString(dr.Acc1_Name).Trim() || accName == Convert.ToString(dr.Acc2_Name).Trim() ||
accName == Convert.ToString(dr.Acc3_Name).Trim() || accName == Convert.ToString(dr.Acc4_Name).Trim())
{
var lstAcc = lstDoctors.AsEnumerable().Where(b => b.DCR_Actual_Date == dr.DCR_Date && b.User_Name == accName).ToList();
if (lstAcc.Count > 0)
{
strAccDocs.Append("<table>");
foreach (var accDoc in lstAcc)
{
strAccDocs.Append("<tr><td>" + accDoc.Doctor_Name + "</td></tr>");
alChildDocs.Add(accDoc.Doctor_Name);
}
strAccDocs.Append("</table>");
}
}
strTblContent.Append("<td>" + strAccDocs.ToString() + "</td>");
}
strDoctorContent.Clear();
#region doctor missed count
if (alChildDocs.Count > 0)
{
strDoctorContent.Append("<table>");
foreach (string childDoctor in alChildDocs)
{
if (!alManagerDocs.Contains(childDoctor))
{
strDoctorContent.Append("<tr><td>" + childDoctor.Trim() + "</td></tr>");
}
}
strDoctorContent.Append("</table>");
}
strTblContent.Append("<td>" + strDoctorContent.ToString() + "</td>");
#endregion doctor missed count
#region doctor deviation
strDoctorContent.Clear();
int count = 0;
if (alManagerDocs.Count > 0)
{
strDoctorContent.Append("<table>");
foreach (string managerDoctor in alManagerDocs)
{
if (!alChildDocs.Contains(managerDoctor))
{
count++;
strDoctorContent.Append("<tr><td>" + managerDoctor.Trim() + "</td></tr>");
}
}
strDoctorContent.Append("</table>");
if (count < 0)
{
strDoctorContent.Clear();
}
}
strTblContent.Append("<td>" + strDoctorContent + "</td>");
#endregion doctor deviation
}
strTblContent.Append("</tr>");
}
}
}
if (reportFormat == "2")
{
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.CurrentInfo _objCurr = new CurrentInfo();
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string compCode = _objCurr.GetCompanyCode();
string fileName = "DoctorDeviationReport_" + "_" + compCode + "_" + _objCurr.GetUserName() + ".xls";
string error = string.Empty;
//string strExcelContent = GetDoctorDeviationReportDetails(startDate,endDate,statusName,userCode,userName,dcrStatus);
blobUrl = objAzureBlob.AzureBlobUploadText(strTblContent.ToString(), accKey, fileName, "bulkdatasvc");
}
}
catch (Exception ex)
{
}
if (reportFormat == "1")
{
return(strTblContent.ToString());
}
else
{
return(blobUrl);
}
}
/// <summary>
/// Method is used to download the ProductpriceMaster into Excel
/// </summary>
/// <returns></returns>
public void PutProductPricemasterIntoExcel()
{
string companycode = _objcurrentInfo.GetCompanyCode();
string error = string.Empty;
try
{
List <ProductPriceMasterModel> lstproductpriceMastermodel = (List <ProductPriceMasterModel>)GetProductPriceDetails();
StringBuilder sb = new StringBuilder();
BLMaster objMaster = new BLMaster();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
sb.Append("<table WIDTH='70%' id='tblsummary' class='table table-striped'>");
sb.Append("<thead class='active'>");
sb.Append("<tr style='background-color:#428bca;'>");
sb.Append("<td>Edit</td>");
sb.Append("<td>Change Status</td>");
sb.Append("<td>Region Type</td>");
sb.Append("<td>Region</td>");
sb.Append("<td>Product</td>");
sb.Append("<td>Price</td>");
sb.Append("<td>Effective From</td>");
sb.Append("<td>Effective To</td>");
sb.Append("<td>Status</td>");
sb.Append("</tr>");
sb.Append("</thead>");
sb.Append("<tbody>");
if (lstproductpriceMastermodel != null && lstproductpriceMastermodel.Count > 0)
{
int i = 0;
foreach (var lstproductprice in lstproductpriceMastermodel)
{
i++;
sb.Append("<tr><td class='td-a'><a id='E" + i + "' onclick='fnEdit(this)'>Edit</a></td>");
sb.Append("<td class='td-a'><a id='C" + i + "' onclick='fnchangeStatus(this)'>Change Status</a></td>");
sb.Append("<td style='display:none' id='Price_Code" + i + "'>" + lstproductprice.Price_Code + "</td>");
sb.Append("<td id='Region_Type_Name" + i + "'>" + lstproductprice.Region_Type_Name + "</td>");
sb.Append("<td style='display:none' id='Region_Type_Code" + i + "'>" + lstproductprice.Region_Type_Code + "</td>");
sb.Append("<td id='Region_Name" + i + "'>" + lstproductprice.Region_Name + "</td>");
sb.Append("<td style='display:none' id='Region_Code" + i + "'>" + lstproductprice.Region_Code + "</td>");
sb.Append("<td id='Product_Name" + i + "'>" + lstproductprice.Product_Name + "</td>");
sb.Append("<td style='display:none' id='Product_Code" + i + "'>" + lstproductprice.Product_Code + "</td>");
sb.Append("<td id='Price" + i + "'>" + lstproductprice.Price + "</td>");
sb.Append("<td id='Effective_From" + i + "'>" + lstproductprice.Effective_From + "</td>");
sb.Append("<td id='Effective_To" + i + "'>" + lstproductprice.Effective_To + "</td>");
sb.Append("<td id='Price_Status" + i + "'>" + lstproductprice.Price_Status + "</td>");
}
}
else
{
sb.Append("<span>No Records TO Display</span>");
}
sb.Append("</body>");
sb.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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "ProductpriceMaster" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(sb.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
public string GetDoctorVisitAnalysisSpecialityWiseReport(FormCollection coll)
{
string companyCode = _objcurrentInfo.GetCompanyCode();
string userCode = coll[COLL_USER_CODE];
int month = Convert.ToInt32(coll[COLL_MONTH]);
int year = Convert.ToInt32(coll[COLL_YEAR]);
string mode = coll[Coll_MODE].ToString();
string reportName = coll[Coll_REPORT_NAME].ToString();
string blobUrl = "";
reportName = ((reportName == "") ? "Doctor Visit Analysis Speciality Wise Report" : reportName);
StringBuilder sbTblContent = new StringBuilder();
StringBuilder sbCatExpected = new StringBuilder();
StringBuilder sbCatMetAsperVisit = new StringBuilder();
StringBuilder sbCatFreq = new StringBuilder();
StringBuilder sbCatVisitPer = new StringBuilder();
DataSet ds = new DataSet();
ds = _objBL.GetDoctorVisitAnalysisSpecialityWiseReport(companyCode, userCode, month, year, mode);
sbTblContent.Append("<div style='width:100%;float:left;text-align:left;font-size:18px;margin-bottom:10px;font-weight:bold;'>" + reportName + " for the month of " + coll["monthName"].ToString() + " " + year.ToString() + "</div>");
sbTblContent.Append("<div style='width:100%;float:left;text-align:left;'>* System shows the current user hierarchy and NOT the historical user hierarchy</div>");
if (ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0)
{
sbTblContent.Append("<div style='width:100%;float:left;'>");
sbTblContent.Append("<table width='100%' id='tblDrVisitAnalysisSpecialityWise' class='data display dataTable box' cellspacing='0' cellpadding='0'>");
sbTblContent.Append("<thead><tr>");
sbTblContent.Append("<th>User Name</th>");
sbTblContent.Append("<th>User Type</th>");
sbTblContent.Append("<th>Territory Name</th>");
sbTblContent.Append("<th>Employee Name</th>");
sbTblContent.Append("<th>Employee No</th>");
sbTblContent.Append("<th>Division Name</th>");
sbTblContent.Append("<th>Line 1 Reporting manager</th>");
sbTblContent.Append("<th>Line 1 Reporting HQ</th>");
sbTblContent.Append("<th>Line 2 Reporting manager</th>");
sbTblContent.Append("<th>Line 2 Reporting HQ</th>");
sbTblContent.Append("<th>Line 3 Reporting manager</th>");
sbTblContent.Append("<th>Line 3 Reporting HQ</th>");
sbTblContent.Append("<th>Speciality Name</th>");
sbTblContent.Append("<th>Total Listed drs count</th>");
if (ds.Tables.Count > 2 && ds.Tables[2].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[2].Rows)
{
sbTblContent.Append("<th>" + dr["Category_Name"] + " drs count</th>");
sbCatExpected.Append("<th>" + dr["Category_Name"] + " Drs Expected Visits </th>");
sbCatMetAsperVisit.Append("<th>" + dr["Category_Name"] + " Drs Met as per visit count</th>");
sbCatFreq.Append("<th>Freq. achieved " + dr["Category_Name"] + " Drs Visits</th>");
sbCatVisitPer.Append("<th>" + dr["Category_Name"] + " Visits %</th>");
}
}
sbTblContent.Append("<th>Total Expected Dr Visits count</th>");
sbTblContent.Append(sbCatExpected.ToString());
sbTblContent.Append(sbCatMetAsperVisit.ToString());
sbTblContent.Append(sbCatFreq.ToString());
sbTblContent.Append("<th>Total Freq. achieved Visits</th>");
sbTblContent.Append(sbCatVisitPer.ToString());
sbTblContent.Append("<th>Total Visits %</th>");
sbTblContent.Append("</tr></thead>");
sbTblContent.Append("<tbody>");
foreach (DataRow dr in ds.Tables[1].Rows)
{
DataRow[] drUserDetails, dsCatDetails, dsCatMetPerVisit;
int totExpected = 0, totFreq = 0;
double totVisit = 0.0;
drUserDetails = ds.Tables[0].AsEnumerable().Where(a => a["Region_Code"].ToString() == dr["Region_Code"].ToString()).ToArray();
sbTblContent.Append("<tr>");
sbTblContent.Append("<td>" + drUserDetails[0]["User_Name"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["User_Type_Name"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Region_Name"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Employee_Name"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Employee_Number"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Division_Name"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Manager1"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Manager1_Region"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Manager2"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Manager2_Region"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Manager3"] + "</td>");
sbTblContent.Append("<td>" + drUserDetails[0]["Manager3_Region"] + "</td>");
sbTblContent.Append("<td>" + dr["Speciality_Name"] + "</td>");
if (Convert.ToInt32(dr["Total_Doctors_Count"]) > 0)
{
sbTblContent.Append("<td style='text-align:right;' class='td-a' onclick='fnOpenDoctorVisitAnalysisSpecialityWiseDrCountPopUp(\"" + drUserDetails[0]["User_Code"] + "_" + dr["Region_Code"] + "_" + dr["Speciality_Code"] + "_ _T" + "\")'>" + dr["Total_Doctors_Count"] + "</td>");
}
else
{
sbTblContent.Append("<td style='text-align:right;'>0</td>");
}
sbCatExpected.Clear();
sbCatMetAsperVisit.Clear();
sbCatFreq.Clear();
sbCatVisitPer.Clear();
foreach (DataRow drCat in ds.Tables[2].Rows)
{
dsCatDetails = ds.Tables[3].Select("Region_Code='" + dr["Region_Code"] + "' AND Speciality_Code='" + dr["Speciality_Code"] + "' AND Category_Code='" + drCat["Category_Code"] + "'");
dsCatMetPerVisit = ds.Tables[4].Select("Region_Code='" + dr["Region_Code"] + "' AND Speciality_Code='" + dr["Speciality_Code"] + "' AND Category_Code='" + drCat["Category_Code"] + "'");
if (dsCatDetails.Length > 0)
{
// dr count
if (Convert.ToInt32(dsCatDetails[0]["Doctors_Count"]) > 0)
{
sbTblContent.Append("<td style='text-align:right;' class='td-a' onclick='fnOpenDoctorVisitAnalysisSpecialityWiseDrCountPopUp(\"" + drUserDetails[0]["User_Code"] + "_" + dr["Region_Code"] + "_" + dr["Speciality_Code"] + "_" + drCat["Category_Code"] + "_N" + "\")'>" + dsCatDetails[0]["Doctors_Count"] + "</td>");
}
else
{
sbTblContent.Append("<td style='text-align:right;' >0</td>");
}
// Expected Visits Count = speciality wise category 1 doctor count of that region (User region. I.e. Point No 3) x Standard visit count of that category
sbCatExpected.Append("<td style='text-align:right;'>" + dsCatDetails[0]["Expected_Visits_Count"] + "</td>");
totExpected = totExpected + Convert.ToInt32(dsCatDetails[0]["Expected_Visits_Count"]);
//Freq Acheived
if (Convert.ToInt32(dsCatDetails[0]["Freq_Achieved"]) > 0)
{
sbCatFreq.Append("<td style='text-align:right;' class='td-a' onclick='fnOpenDoctorVisitAnalysisSpecialityWiseFreqPopUp(\"" + drUserDetails[0]["User_Code"] + "_" + dr["Region_Code"] + "_" + dr["Speciality_Code"] + "_" + drCat["Category_Code"] + "_N" + "\")'>" + dsCatDetails[0]["Freq_Achieved"] + "</td>");
totFreq = totFreq + Convert.ToInt32(dsCatDetails[0]["Freq_Achieved"]);
}
else
{
sbCatFreq.Append("<td style='text-align:right;' >0</td>");
}
// Visits % = (Freq Achieved / DoctorCount) x 100
double percent = 0.0;
if (Convert.ToInt32(dsCatDetails[0]["Expected_Visits_Count"]) != 0)
{
percent = (Convert.ToDouble(dsCatDetails[0]["Freq_Achieved"]) / Convert.ToDouble(dsCatDetails[0]["Expected_Visits_Count"])) * 100;
}
sbCatVisitPer.Append("<td style='text-align:right;'>" + percent.ToString("N2") + "</td>");
//totVisit += Convert.ToDouble(percent.ToString("N2"));
}
else
{
sbTblContent.Append("<td style='text-align:right;'>0</td>");
// Expected Visits Count = speciality wise category 1 doctor count of that region (User region. I.e. Point No 3) x Standard visit count of that category
sbCatExpected.Append("<td style='text-align:right;'>0</td>");
sbCatFreq.Append("<td style='text-align:right;'>0</td>");
sbCatVisitPer.Append("<td style='text-align:right;'>0</td>");
}
// Met As per Visit Count =Count of speciality wise category 1 doctors who are met by the user equal to or greater than the standard visit count of that category
if (dsCatMetPerVisit.Length > 0)
{
sbCatMetAsperVisit.Append("<td style='text-align:right;'>" + dsCatMetPerVisit[0]["Doctors_Met_As_Per_Visit_Count"] + "</td>");
}
else
{
sbCatMetAsperVisit.Append("<td style='text-align:right;'>0</td>");
}
}
sbTblContent.Append("<td style='text-align:right;'>" + totExpected.ToString() + "</td>");
sbTblContent.Append(sbCatExpected.ToString());
sbTblContent.Append(sbCatMetAsperVisit.ToString());
sbTblContent.Append(sbCatFreq.ToString());
sbTblContent.Append("<td style='text-align:right;'>" + totFreq.ToString() + "</td>");
sbTblContent.Append(sbCatVisitPer.ToString());
if (totExpected != 0)
{
totVisit = (Convert.ToDouble(totFreq) / Convert.ToDouble(totExpected)) * 100;
sbTblContent.Append("<td style='text-align:right;'>" + totVisit.ToString("N2") + "</td>");
}
else
{
sbTblContent.Append("<td style='text-align:right;'>0.0</td>");
}
sbTblContent.Append("</tr>");
}
sbTblContent.Append("</tbody></table>");
sbTblContent.Append("</div>");
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 = "DrVisitSpecialityWiseReport_" + "_" + compCode + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(sbTblContent.ToString(), accKey, fileName, "bulkdatasvc");
}
else
{
sbTblContent.Append("<div style='width:100%;float:left;'>“No information available for this user");
sbTblContent.Append("</div>");
}
return(sbTblContent.ToString() + "$" + blobUrl);
}
public void PutDCRRequestScreenintoExcel()
{
string companycode = _objcurrentInfo.GetCompanyCode();
string userCode = _objcurrentInfo.GetUserCode();
string error = string.Empty;
try
{
List <DCRRestrictionModel> lstDCRRequestMaster = new List <DCRRestrictionModel>();
StringBuilder sb = new StringBuilder();
BLMaster objMaster = new BLMaster();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
lstDCRRequestMaster = objMaster.GetRequestScreenforDcrRestrict(companycode, userCode).ToList();
sb.Append("<table WIDTH='70%' id='tblsummary' class='table table-striped'>");
sb.Append("<thead class='active'>");
sb.Append("<tr style='background-color:#428bca;'>");
sb.Append("<td>Region Category</td>");
sb.Append("<td>Period</td>");
sb.Append("<td>Admin Remarks</td>");
sb.Append("<td>User Remarks</td>");
sb.Append("<td>Status</td>");
sb.Append("</tr>");
sb.Append("</thead>");
sb.Append("<tbody>");
if (lstDCRRequestMaster != null && lstDCRRequestMaster.Count > 0)
{
int i = 0;
foreach (var lstRequest in lstDCRRequestMaster)
{
i++;
sb.Append("<td id='Request_Category_Name_" + lstRequest.Request_Id + "'>");
sb.Append(lstRequest.Request_Category_Name);
sb.Append("</td>");
sb.Append("<td id='Request_period_" + lstRequest.Request_Id + "'>");
sb.Append(lstRequest.Date_From);
sb.Append(" to ");
sb.Append(lstRequest.Date_To);
sb.Append("</td>");
sb.Append("<td>" + lstRequest.Admin_Remarks + "</td>");
sb.Append("<td>" + lstRequest.Requesting_User_Remarks + "</td>");
sb.Append("<td id='Record_Status_" + lstRequest.Request_Id + "'>" + lstRequest.Request_Status + "</td></tr>");
}
}
else
{
sb.Append("No Records TO Display.");
}
sb.Append("</body>");
sb.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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "DCR_Request_Screen" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(sb.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
//For excell
public void PutRegionTypeMasterIntoExcel()
{
string blobUrl = string.Empty;
string error = string.Empty;
StringBuilder sbTableContent = new StringBuilder();
DataControl.CurrentInfo _objCurrentInfo = new DataControl.CurrentInfo();
StringBuilder sb = new StringBuilder();
DataControl.BLMaster _objBlmaster = new DataControl.BLMaster();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
try
{
DataSet ds = new DataSet();
ds = _objBlmaster.GetRegiontypeDetailsAll(_objCurrentInfo.GetCompanyCode());
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
sbTableContent.Append("<table id='tblRegionsummary' class='table table-striped' >");
sbTableContent.Append("<thead class='active'>");
sbTableContent.Append("<tr style='background-color: #428bca'>");
sbTableContent.Append("<td>Region Type Name</td>");
sbTableContent.Append("<td>Under Region</td>");
sbTableContent.Append("<td>Status</td>");
sbTableContent.Append("</tr>");
sbTableContent.Append("</thead>");
sbTableContent.Append("<tbody>");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sbTableContent.Append("<tr>");
sbTableContent.Append("<td>" + ds.Tables[0].Rows[i]["Region_Type_Name"] + "</td>");
sbTableContent.Append("<td>" + ds.Tables[0].Rows[i]["UnderRegion"] + "</td>");
sbTableContent.Append("<td>" + ds.Tables[0].Rows[i]["Status"] + "</td></tr>");
}
}
else
{
sbTableContent.Append("<tr><td>No records To Display</td></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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "RegionTypeMaster " + " - " + subdomainName + " - " + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(sbTableContent.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();;
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
public void GetDistanceFareChartExcel()
{
try
{
List <DistanceFareChartModel> lstDFC = new List <DistanceFareChartModel>();
BLMaster _objMast = new BLMaster();
StringBuilder sbTableContentExcel = new StringBuilder();
string blobUrl = string.Empty, error = string.Empty;
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
lstDFC = _objMast.GetDistanceFareChart(_objCurr.GetCompanyCode());
var lstDistUserType = lstDFC.Select(x => new { x.User_Type_Code, x.User_Type_Name }).Distinct().ToList();
if (lstDFC != null && lstDFC.Count > 0)
{ //excel
sbTableContentExcel.Append("<div style='font-size:14px;width:100%;font-weight:bold;float:left' align='left'>Distance Fare Chart</div>");
sbTableContentExcel.Append("<table class='table table-striped' id='tblDFC' cellspacing='0' cellpadding='0' border='1'>");
sbTableContentExcel.Append("<thead>");
sbTableContentExcel.Append("<tr>");
sbTableContentExcel.Append("<th>From Km</th>");
sbTableContentExcel.Append("<th>To Km</th>");
sbTableContentExcel.Append("<th>Fare Amount</th>");
sbTableContentExcel.Append("<th>User Type</th>");
sbTableContentExcel.Append("<th>Entity</th>");
sbTableContentExcel.Append("<th>Date From</th>");
sbTableContentExcel.Append("<th>Date To</th>");
sbTableContentExcel.Append("<th>Is Amount Fixed</th>");
sbTableContentExcel.Append("<th>Status</th>");
sbTableContentExcel.Append("</tr>");
sbTableContentExcel.Append("</thead>");
sbTableContentExcel.Append("<tbody>"); //
foreach (var userType in lstDistUserType)
{
var lstDist = lstDFC.AsEnumerable().Where(x => x.User_Type_Code == userType.User_Type_Code);
if (lstDist != null && lstDist.Count() > 0)
{
//excel
sbTableContentExcel.Append("<tr><th class='collapseHeader' colspan='9'>" + userType.User_Type_Name + "</th></tr>");
//
foreach (var item in lstDist)
{
// excel
sbTableContentExcel.Append("<tr >");
sbTableContentExcel.Append("<td >" + item.From_Km.ToString() + "</td>");
sbTableContentExcel.Append("<td >" + item.To_Km.ToString() + "</td>");
sbTableContentExcel.Append("<td >" + item.Fare_Amount.ToString() + "</td>");
sbTableContentExcel.Append("<td >" + item.User_Type_Name.ToString() + "</td>");
sbTableContentExcel.Append("<td >" + item.Entity_Name.ToString() + "</td>");
sbTableContentExcel.Append("<td >" + item.Date_From.ToString() + "</td>");
sbTableContentExcel.Append("<td >" + item.Date_To.ToString() + "</td>");
sbTableContentExcel.Append("<td >" + ((item.Is_Amount_Fixed == "1") ? "Yes" : "No") + "</td>");
sbTableContentExcel.Append("<td >" + ((item.Status == 1) ? "Enabled" : "Disabled") + "</td>");
sbTableContentExcel.Append("</tr>");
//
}
}
}
//excel
sbTableContentExcel.Append("</tbody></table>");
//
}
DataControl.Abstraction.IConfigProvider iConfigPro = new DataControl.Impl.ConfigProvider();
DataControl.Repository.AzureBlobUpload objAzureBlob = new DataControl.Repository.AzureBlobUpload();
string accKey = iConfigPro.GetConfigValue("SWAASBLOBACCKEY");
string userName = _objCurr.GetUserName();
string subDomin = _objCurr.GetSubDomain();
string fileName = "DistanceFareChart_" + "_" + subDomin + "_" + userName + ".xls";
blobUrl = objAzureBlob.AzureBlobUploadText(sbTableContentExcel.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicObj = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex: ex, dic: dicObj);
}
}
/// <summary>
/// Method is used to download the Doctorcategory into Excel
/// </summary>
/// <returns></returns>
public void PutDoctorcategoryIntoExcel()
{
string companycode = _objcurrentInfo.GetCompanyCode();
string error = string.Empty;
try
{
BLMaster _objmaster = new BLMaster();
List <MVCModels.HiDoctor_Master.DoctorModel> lstdoctcategory = (List <MVCModels.HiDoctor_Master.DoctorModel>)GetdoctorcategoryDetails();
DataControl.Repository.FileDownload objFileDownload = new DataControl.Repository.FileDownload();
DataControl.Abstraction.IFileProvider objProvider = new DataControl.Impl.FileSystemProvider();
//dsc
StringBuilder sb = new StringBuilder();
sb.Append("<table WIDTH='100%' id='tblsummary' class='table table-striped' >");
sb.Append("<thead class='active'>");
sb.Append("<tr style='background-color:#428bca;'>");
sb.Append("<td>Doctor Category Name</td>");
sb.Append("<td>Visit Count</td>");
sb.Append("<td>Doctor Count</td>");
sb.Append("<td>Division Name</td>");
sb.Append("<td>Effective From</td>");
sb.Append("<td>Effective To</td>");
sb.Append("<td>Status</td>");
sb.Append("</tr>");
sb.Append("</thead>");
sb.Append("<tbody>");
//
if (lstdoctcategory != null && lstdoctcategory.Count > 0)
{
int i = 0;
foreach (var lstdoc in lstdoctcategory)
{
i++;
sb.Append("<tr><td id='Category_Name" + i + "' style='text-align:left;'>" + lstdoc.Category_Name + "</td>");
sb.Append("<td id='Visit_Count" + i + "' style='text-align:left;'>" + lstdoc.Visit_Count + "</td>");
sb.Append("<td id='Doctor_Count" + i + "' style='text-align:left;' >" + lstdoc.Doctor_Count + "</td>");
sb.Append("<td id='Division_Name" + i + "' style='text-align:left;' >" + lstdoc.Division_Name + "</td>");
sb.Append("<td id='Effective_From" + i + "' style='text-align:left;' >" + lstdoc.Effective_From + "</td>");
sb.Append("<td id='Effective_To" + i + "' style='text-align:left;' >" + lstdoc.Effective_To + "</td>");
sb.Append("<td id='status" + i + "' style='text-align:left;'>" + lstdoc.Status + "</td></tr>");
}
}
sb.Append("</tbody>");
sb.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 subdomainName = System.Web.HttpContext.Current.Request.Url.DnsSafeHost;;
string fileName = "DoctorCategoryMaster" + " - " + subdomainName + " - " + userName + ".xls";
string blobUrl = objAzureBlob.AzureBlobUploadText(sb.ToString(), accKey, fileName, "bulkdatasvc");
objFileDownload.DownloadFile(blobUrl, fileName, out error);
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
}
}
