/// <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 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);
}
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 string UploadAttachment()
{
var result = "";
StringBuilder strbuilder = new StringBuilder();
try
{
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
CurrentInfo _objCurr = new CurrentInfo();
var objAzureUpload = new DataControl.Repository.AzureBlobUpload();
var objPathProv = new DataControl.Impl.FileSystemProvider();
string accKey = objPathProv.GetConfigValue("UPLOADEDFILEBLOBACCKEY");
HttpFileCollectionBase files = Request.Files;
if (files.Count > 0)
{
for (var i = 0; i < files.Count; i++)
{
HttpPostedFileBase fileUpload = files[i];
String fileNametimeStamp = DateTime.Now.ToString("ddMMyyyyHHmmssfff");
fileNametimeStamp = fileNametimeStamp + '_' + fileUpload.FileName;
strbuilder.Append(objAzureUpload.PutAzureBlobStorage(fileUpload.InputStream, fileNametimeStamp, accKey, _objCurr.GetCompanyCode()));
strbuilder.Append("~");
}
}
}
catch (Exception ex)
{
throw ex;
}
result = strbuilder.ToString();
return(result);
}
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());
}
//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);
}
}
/// <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);
}
}
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 GetAllNoticesReadPopup(string mesgCode)
{
DataControl.CurrentInfo objCur = new DataControl.CurrentInfo();
string companyCode = objCur.GetCompanyCode();
string userCode = objCur.GetUserCode();
try
{
StringBuilder sbTableContent = new StringBuilder();
DataControl.HiDoctor_ActivityFactoryClasses.BL_NoticeBoard _objBlActivity = new DataControl.HiDoctor_ActivityFactoryClasses.BL_NoticeBoard();
DataControl.CurrentInfo _objCurrentInfo = new DataControl.CurrentInfo();
IEnumerable <MVCModels.NoticeBoardContentModel> lstdata = null;
lstdata = _objBlActivity.GetNoticeBoardPopup(companyCode, mesgCode, userCode);
sbTableContent.Append("</div>");
sbTableContent.Append("<table id='NoticeBoardpopup' class='table table-striped' style='width:100%;'>");
DataControl.Repository.AzureBlobUpload objAzureUpload = new DataControl.Repository.AzureBlobUpload();
DataControl.Abstraction.IFileProvider objPathProv = new DataControl.Impl.FileSystemProvider();
string accKey = objPathProv.GetConfigValue("NBFILES");
// string blobURL = objAzureUpload.AzureblockDownload(postedFile.FileName, accKey, containerName);
foreach (var item in lstdata)
{
sbTableContent.Append("<thead class='active'>");
sbTableContent.Append("<tr><td>NoticeBoardInfo</td><td></td></tr>");
sbTableContent.Append("</thead>");
sbTableContent.Append("<tr><td>Author</td>");
sbTableContent.Append("<td>" + item.Employee_Name + "(" + item.Author + ")</td></tr>");
sbTableContent.Append("<tr><td>Title</td>");
sbTableContent.Append("<td>" + item.Title + "</td></tr>");
sbTableContent.Append("<tr><td>Message</td>");
sbTableContent.Append("<td>" + item.Message + "</td></tr>");
sbTableContent.Append("<tr><td>Hyperlink</td>");
sbTableContent.Append("<td class='td-a'><a style='text-decoration:underline;cursor:pointer;' target='_blank' href='" + item.Hyperlink + "'>" + item.Hyperlink + "</a></td></tr>");
sbTableContent.Append("<tr><td>Attachments</td>");
string blobURL = accKey + Session["Comp_Code"].ToString().ToLower() + "/" + HttpUtility.UrlEncode(item.FilePath).ToString();
if (item.FilePath != "")
{
sbTableContent.Append("<td <div id='dvURL' class='div-alert'>Click on link to download : <a href=" + blobURL + " target='_blank' >" + item.FilePath + "</a></div></td></tr>");
}
else
{
sbTableContent.Append("<td></td></tr>");
}
}
return(sbTableContent.ToString());
}
catch (Exception ex)
{
Dictionary <string, string> dicContext = new Dictionary <string, string>();
dicContext.Add("Filter:CompanyCode", companyCode);
dicContext.Add("Filter:UserCode", userCode);
DataControl.Impl.ExceptionHandler.WriteLog(ex, dicContext);
throw new Exception("Sorry an error occurred. Please try again later");
throw ex;
}
}
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);
}
}
//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());
}
/// <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);
}
}
/// <summary>
/// get the selected message details
/// </summary>
/// <param name="msgCode"></param>
/// <returns>returns the selected message details in the form of json </returns>
public string GetSelectedMsgDetails(string msgCode, string TargetAddress)
{
DataControl.CurrentInfo objCurInfo = new DataControl.CurrentInfo();
DataControl.BLMessaging objMsg = new DataControl.BLMessaging();
DataControl.JSONConverter objJson = new DataControl.JSONConverter();
DataControl.Repository.AzureBlobUpload objAzureUpload = new DataControl.Repository.AzureBlobUpload();
DataControl.Abstraction.IFileProvider objPathProv = new DataControl.Impl.FileSystemProvider();
string accKey = objPathProv.GetConfigValue("NBFILES");
string blobURL = accKey + objCurInfo.GetCompanyCode().ToLower();
return(objJson.Serialize(objMsg.GetSelectedMsgDetails(objCurInfo.GetCompanyCode(), msgCode, TargetAddress)) + "~" + blobURL);
}
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 ConvertHtmlToPdf()
{
DataControl.CurrentInfo objCurInfo = new CurrentInfo();
DataControl.Repository.AzureBlobUpload objAzureUpload = new DataControl.Repository.AzureBlobUpload();
DataControl.Abstraction.IFileProvider objPathProv = new DataControl.Impl.FileSystemProvider();
string accKey = objPathProv.GetConfigValue("NBBLOBACCKEY");
string apiKey = "a06abb07-d574-4a8c-b223-845635e0cb16";
string value = "";
value += "<div class='col - xs - 12 clearfix' id='formtitle>< p >< b > Dear < span id = 'formUsername' ></ span >,</ b ></ p >";
value += "< p > Greetings of the day,</ p > < br />< p style ='text-indent:40px' > As per clause 7 of the agreed Work Norms, you are expected to make the following minimum call average:</ p >";
value += "< p style ='text-indent:100px' >< b > 1.Doctor Call Average - < span id = formExpcalAvg ></ span ></ b ></ p >< p style = 'text-indent:40px' >";
value += "However, on review of your call average, we are shocked to note that your call average is only<b> < span id = 'formCurcalAvg' ></ span ></ b > for the month of < b >< span id = 'formmnth'>";
value += "</ span ></ b >, < b >< span id = 'formyear' ></ span ></ b > which is much below the expectation.< br /> We also understand that your Regional Manager has already sent communication to you in this regard but has not received any assurance from you that you would improve upon the above.This is being viewed very seriously.You are cautioned to maintain doctor call average of < b >";
value += "< span id = 'testexpavg' ></ span ></ b > month after month, which will also help you in improving our business in your territory.We are sure you will take note of the above very seriously and ensure compliance.</ p >";
value += "< p style = 'position: relative;top: 9px;margin-bottom: 25px;' >< b > Regards,</ b ></ p >< p >< span id = 'ModRepusername' ></ span ></ p >";
value += "</ div > "; // a direct HTML string
string containerName = objCurInfo.GetCompanyCode().ToLower();
using (var client = new System.Net.WebClient())
{
// Build the conversion options
System.Collections.Specialized.NameValueCollection options = new System.Collections.Specialized.NameValueCollection();
options.Add("apikey", apiKey);
options.Add("value", value);
string HtmlToPDF = System.Configuration.ConfigurationManager.AppSettings["AlertHtmlToPDF"].ToString();
// Call the API convert to a PDF
System.IO.MemoryStream ms = new System.IO.MemoryStream(client.UploadValues(HtmlToPDF, options));
// Make the file a downloadable attachment - comment this out to show it directly inside
var fileName = objCurInfo.GetUserName() + DateTime.Now.ToString("ddMMyyyyHHmmssfff") + ".pdf";
string blobURL = objAzureUpload.PutAzureBlobStorage(ms, fileName, accKey, containerName);
// Return the file as a PDF
return(fileName);
}
}
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 ZipFileuploadResult(HttpPostedFileBase fileUpload, FormCollection collection)
{
string documentTypecode = string.Empty;
string month = string.Empty;
string year = string.Empty;
string documentName = string.Empty;
string documentCategory = string.Empty;
if (!string.IsNullOrEmpty(collection["txtdoctype"].ToString()))
{
documentTypecode = collection["txtdoctype"].ToString();
}
if (!string.IsNullOrEmpty(collection["txtdocMonth"].ToString()))
{
month = collection["txtdocMonth"].ToString();
}
if (!string.IsNullOrEmpty(collection["txtdocYear"].ToString()))
{
year = collection["txtdocYear"].ToString();
}
if (!string.IsNullOrEmpty(collection["txtdocName"].ToString()))
{
documentName = collection["txtdocName"].ToString();
}
if (!string.IsNullOrEmpty(collection["txtdocName"].ToString()))
{
documentCategory = collection["txtCatType"].ToString();
}
List <MVCModels.MyDocumentFileUploads> lstFiles = new List <MyDocumentFileUploads>();
MyDocumentFileUploads _objFileupload = new MyDocumentFileUploads();
DataControl.Repository.AzureBlobUpload objAzureUpload = new DataControl.Repository.AzureBlobUpload();
WebClient wc = new WebClient();
int rowsAffected = 0;
string result = string.Empty;
string companyCode = _objcurrentInfo.GetCompanyCode();
// string companyCode = "HCT00000011";
string fileName = fileUpload.FileName;
string accountKey = "DefaultEndpointsProtocol=https;AccountName=uploadedfile;AccountKey=gUccvu0LoMEfdlYhbuE+TMP7QxDe8SF7TGZ8c35CJcBY4vJto9OCNOSbxuNPR5CTKgCvwxu1STD4c+IBtGfNcA==";
string blobURL = objAzureUpload.AzureBlopforChunkUpload(fileUpload.InputStream, fileName, companyCode, accountKey);
if (!string.IsNullOrEmpty(blobURL))
{
_objFileupload.Company_Code = companyCode;
_objFileupload.Doc_Type_Id = documentTypecode;
_objFileupload.Doc_Month = month;
_objFileupload.Doc_Year = year;
_objFileupload.Doc_Name = documentName;
_objFileupload.Uploaded_File_Url = blobURL;
_objFileupload.Uploaded_File_name = fileName;
_objFileupload.Uploaded_Date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff");
_objFileupload.Status = 1;
_objFileupload.Processing_Status = 0;
_objFileupload.File_Extracting_status = fileExtractingStatus;
_objFileupload.Uploaded_By = _objcurrentInfo.GetUserCode();
_objFileupload.Doc_Upload_Category = documentCategory;
lstFiles.Add(_objFileupload);
if (lstFiles.Count > 0)
{
rowsAffected = _objBlMaster.InsertChunkFiles(lstFiles);
}
if (rowsAffected > 0)
{
result = "SUCCESS";
}
}
return(result);
}
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());
}
/// <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);
}
}
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);
}
//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 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 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);
}
}
///// <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 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());
}
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);
}
}
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);
}
