protected void btnReset_Click(object sender, EventArgs e)
{
SearchReportCommon itmsearch = new SearchReportCommon();
itmsearch.months = "'" + gmonth + "'";
itmsearch.divs = "'eva'";
itmsearch.yr = src_year.SelectedValue;
FetchReportdata(itmsearch, 0);
populatedropdowns();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
populatedropdowns();
SearchReportCommon itmsearch = new SearchReportCommon();
itmsearch.months = "'" + gmonth + "'";
itmsearch.divs = "'eva'";
FetchReportdata(itmsearch, 1000);
}
}
private void FetchReportdata(SearchReportCommon searchdoctorvisit, int rows)
{
DataSet ds = (new RptCallAverageDL()).GetSampleReportDataDynamic(searchdoctorvisit, rows);
//manage headers as per division
//String disthead = "";
//if (searchdoctorvisit.divs.ToLower().Contains("eva") || searchdoctorvisit.divs.ToLower().Contains("concord"))
// disthead = "MFSO";
//if (searchdoctorvisit.divs.ToLower().Contains("phoenix"))
//{
// if (disthead != "")
// disthead = disthead + "_SO";
// else
// disthead = "SO";
//}
//if (searchdoctorvisit.divs.ToLower().Contains("mad"))
//{
// if (disthead != "")
// disthead = disthead + "_MSR";
// else
// disthead = "MSR";
//}
//if (disthead != "")
// disthead = disthead + "_HQ";
//else
// disthead = "MFSO_HQ";
//ds.Tables[0].Columns["District"].ColumnName = disthead;
//ds.Tables[0].Columns["AsmHQ"].ColumnName = "ASM_DSO_HQ";
ds.Tables[0].Columns.Remove("rptcallaverageID");
ds.Tables[0].Columns.Remove("ASMHQ");
ds.Tables[0].Columns.Remove("MFSOHQ");
ds.Tables[0].Columns.Remove("SMHQ");
//foreach(DataColumn col in ds.Tables[0].Columns)
//{
// col.ColumnName = col.ColumnName.ToUpper();
//}
rpt_sample.ReportTitle = "Call Average Report";
rpt_sample.ReportName = "CallAverageReport";
rpt_sample.DataBind(ds.Tables[0].Copy());
rpt_sample.Visible = true;
}
private void FetchReportdata(SearchReportCommon searchdoctorvisit, int rows)
{
DataSet ds = (new RptAgeingTransactionDL()).GetSampleReportDataDynamic(searchdoctorvisit, rows);
// ds.Tables[0].Columns.Remove("rptageingtransactionID");
ds.Tables[0].Columns.Remove("MANAGER");
//foreach(DataColumn col in ds.Tables[0].Columns)
//{
// col.ColumnName = col.ColumnName.ToUpper();
//}
rpt_sample.ReportTitle = "Ageing Transaction Report";
rpt_sample.ReportName = "AgeingTransactionReport";
rpt_sample.DataBind(ds.Tables[0].Copy());
rpt_sample.Visible = true;
}
public DataSet GetSampleReportDataDynamic(SearchReportCommon itmsearch, int rows)
{
Common.OpenConnection();
String sql = @"select [MONTH],[YEAR],[DIVISION],[STATUS],FORMAT(CONVERT(date, [DATE]),'dd-MMM-yyyy') as 'DATE',[ZSMHQ],[RSMHQ],[EMPNAME],[DESIGNATION],[MANAGER],
[AREA],[AREATYPE],[CLIENTCODE],[CLIENTNAME],[CATEGORY],[SPECIALITY],[QUALIFICATION],[PRODUCTNAME],[PRODUCTSTATUS],[ASMVISIT],[RSMVISIT]
From rpt_ageingtransaction";
if (rows > 0)
{
sql = @"select top " + rows + " [MONTH],[YEAR],[DIVISION],[STATUS],FORMAT(CONVERT(date, [DATE]),'dd-MMM-yyyy') as 'DATE',[ZSMHQ],[RSMHQ],[EMPNAME],[DESIGNATION],[MANAGER]," +
"[AREA],[AREATYPE],[CLIENTCODE],[CLIENTNAME],[CATEGORY],[SPECIALITY],[QUALIFICATION],[PRODUCTNAME],[PRODUCTSTATUS],[ASMVISIT],[RSMVISIT]" +
" From rpt_ageingtransaction";
}
string whsql = "";
string strconcat = "";
if (itmsearch.months != null && itmsearch.months != "")
{
whsql = whsql + strconcat + " month in (" + itmsearch.months + ")";
strconcat = " and ";
}
if (itmsearch.yr != null && itmsearch.yr != "")
{
whsql = whsql + strconcat + " year ='" + itmsearch.yr + "'";
strconcat = " and ";
}
if (itmsearch.divs != null && itmsearch.divs != "" && itmsearch.divs != "select")
{
whsql = whsql + strconcat + " division in(" + itmsearch.divs + ")";
strconcat = " and ";
}
if (itmsearch.zsms != null && itmsearch.zsms != "" && itmsearch.zsms != "select")
{
whsql = whsql + strconcat + " ZSMHQ in(" + itmsearch.zsms + ")";
strconcat = " and ";
}
if (itmsearch.rsms != null && itmsearch.rsms != "" && itmsearch.rsms != "select")
{
whsql = whsql + strconcat + " RSMHQ in(" + itmsearch.rsms + ")";
strconcat = " and ";
}
if (itmsearch.hqs != null && itmsearch.hqs != "" && itmsearch.hqs != "select")
{
whsql = whsql + strconcat + " AREA in (" + itmsearch.areas + ")";
strconcat = " and ";
}
if (itmsearch.emps != null && itmsearch.emps != "" && itmsearch.emps != "select")
{
whsql = whsql + strconcat + " empname in (" + itmsearch.emps + ")";
strconcat = " and ";
}
if (whsql != null && whsql != "")
{
sql = sql + " where " + whsql;
}
SqlCommand cmd = new SqlCommand(sql, Common.conn);
DataSet dsCustomers = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
sda.Fill(dsCustomers);
}
Common.CloseConnection();
return(dsCustomers);
}
public DataSet GetDocChemMapReportDataDynamic(SearchReportCommon itmsearch, int rows)
{
Common.OpenConnection();
String sql = "select * from rpt_doctorchemistmapping";
if (rows > 0)
{
sql = "select top " + rows + " * from rpt_doctorchemistmapping";
}
string whsql = "";
string strconcat = "";
if (itmsearch.months != null && itmsearch.months != "")
{
whsql = whsql + strconcat + " month in (" + itmsearch.months + ")";
strconcat = " and ";
}
if (itmsearch.yr != null && itmsearch.yr != "")
{
whsql = whsql + strconcat + " year ='" + itmsearch.yr + "'";
strconcat = " and ";
}
if (itmsearch.divs != null && itmsearch.divs != "" && itmsearch.divs != "select")
{
whsql = whsql + strconcat + " division in(" + itmsearch.divs + ")";
strconcat = " and ";
}
if (itmsearch.areas != null && itmsearch.areas != "" && itmsearch.areas != "select")
{
whsql = whsql + strconcat + " Area in (" + itmsearch.areas + ")";
strconcat = " and ";
}
if (itmsearch.emps != null && itmsearch.emps != "" && itmsearch.emps != "select")
{
whsql = whsql + strconcat + " employee in (" + itmsearch.emps + ")";
strconcat = " and ";
}
//if (itmsearch.emps != null && itmsearch.emps != "" && itmsearch.emps != "select")
//{
// whsql = whsql + strconcat + " EmpName in (" + itmsearch.emps + ")";
// strconcat = " and ";
//}
//if (itmsearch.desigs != null && itmsearch.desigs != "" && itmsearch.desigs != "select")
//{
// whsql = whsql + strconcat + " Designation in (" + itmsearch.desigs + ")";
// strconcat = " and ";
//}
//if(itmsearch.asms!=null && itmsearch.asms!="" && itmsearch.asms != "select")
//{
// whsql = whsql + strconcat + " AsmHQ in (" + itmsearch.asms + ")";
// strconcat = " and ";
//}
//if (itmsearch.msrs != null && itmsearch.msrs != "" && itmsearch.msrs != "select")
//{
// whsql = whsql + strconcat + " MFSOHQ in (" + itmsearch.msrs + ")";
// strconcat = " and ";
//}
if (whsql != null && whsql != "")
{
sql = sql + " where " + whsql;
}
SqlCommand cmd = new SqlCommand(sql, Common.conn);
DataSet dsCustomers = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
sda.Fill(dsCustomers);
}
Common.CloseConnection();
return(dsCustomers);
}
public DataSet GetSampleReportDataDynamic(SearchReportCommon itmsearch, int rows)
{
Common.OpenConnection();
String sql = "select * from rpt_callaverage";
if (rows > 0)
{
sql = "select top " + rows + " * from rpt_callaverage";
}
string whsql = "";
string strconcat = "";
if (itmsearch.months != null && itmsearch.months != "")
{
whsql = whsql + strconcat + " month in (" + itmsearch.months + ")";
strconcat = " and ";
}
if (itmsearch.yr != null && itmsearch.yr != "")
{
whsql = whsql + strconcat + " year ='" + itmsearch.yr + "'";
strconcat = " and ";
}
if (itmsearch.divs != null && itmsearch.divs != "" && itmsearch.divs != "select")
{
whsql = whsql + strconcat + " division in(" + itmsearch.divs + ")";
strconcat = " and ";
}
if (itmsearch.zsms != null && itmsearch.zsms != "" && itmsearch.zsms != "select")
{
whsql = whsql + strconcat + " ZSMHQ in(" + itmsearch.zsms + ")";
strconcat = " and ";
}
if (itmsearch.rsms != null && itmsearch.rsms != "" && itmsearch.rsms != "select")
{
whsql = whsql + strconcat + " RSMHQ in(" + itmsearch.rsms + ")";
strconcat = " and ";
}
if (itmsearch.hqs != null && itmsearch.hqs != "" && itmsearch.hqs != "select")
{
whsql = whsql + strconcat + " HQ in (" + itmsearch.hqs + ")";
strconcat = " and ";
}
if (itmsearch.emps != null && itmsearch.emps != "" && itmsearch.emps != "select")
{
whsql = whsql + strconcat + " empname in (" + itmsearch.emps + ")";
strconcat = " and ";
}
if (whsql != null && whsql != "")
{
sql = sql + " where " + whsql;
}
SqlCommand cmd = new SqlCommand(sql, Common.conn);
DataSet dsCustomers = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
sda.Fill(dsCustomers);
}
Common.CloseConnection();
return(dsCustomers);
}
protected void btnsearch_Click(object sender, EventArgs e)
{
String separator = "";
String monthsearch = "";
String divsearch = "";
string districtsearch = "";
string empsearch = "";
//string zsmsearch = "";
//string rsmsearch = "";
foreach (ListItem itm in src_month4m.Items)
{
if (itm.Selected)
{
monthsearch = monthsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
separator = "";
foreach (ListItem itm in src_div.Items)
{
if (itm.Selected)
{
divsearch = divsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
separator = "";
foreach (ListItem itm in src_district.Items)
{
if (itm.Selected)
{
districtsearch = districtsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
separator = "";
foreach (ListItem itm in src_employee.Items)
{
if (itm.Selected)
{
empsearch = empsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
////zsm
//separator = "";
//foreach (ListItem itm in src_zsm.Items)
//{
// if (itm.Selected)
// {
// zsmsearch = zsmsearch + separator + "'" + itm.Value + "'";
// separator = ",";
// }
//}
////rsm
//separator = "";
//foreach (ListItem itm in src_rsm.Items)
//{
// if (itm.Selected)
// {
// rsmsearch = rsmsearch + separator + "'" + itm.Value + "'";
// separator = ",";
// }
//}
SearchReportCommon itmsearch = new SearchReportCommon();
itmsearch.months = monthsearch;
itmsearch.divs = divsearch;
itmsearch.dists = districtsearch;
itmsearch.emps = empsearch;
itmsearch.yr = src_year.SelectedValue;
FetchReportdata(itmsearch, 0);
String curyear = DateTime.Now.Year.ToString();
StaticData itmstat = (new RptMonthlyAttendanceDL()).GetDropdownData(monthsearch, curyear);
src_district.DataSource = itmstat.lstdistrict;
src_district.DataBind();
src_employee.DataSource = itmstat.lstemployee;
src_employee.DataBind();
}
protected void btnsearch_Click(object sender, EventArgs e)
{
String separator = "";
String monthsearch = "";
String divsearch = "";
string areasearch = "";
string empsearch = "";
string zsmsearch = "";
string rsmsearch = "";
foreach (ListItem itm in src_month4m.Items)
{
if (itm.Selected)
{
monthsearch = monthsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
separator = "";
foreach (ListItem itm in src_div.Items)
{
if (itm.Selected)
{
divsearch = divsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
separator = "";
foreach (ListItem itm in src_area.Items)
{
if (itm.Selected)
{
areasearch = areasearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
separator = "";
foreach (ListItem itm in src_employee.Items)
{
if (itm.Selected)
{
empsearch = empsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
//zsm
separator = "";
foreach (ListItem itm in src_zsm.Items)
{
if (itm.Selected)
{
zsmsearch = zsmsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
//rsm
separator = "";
foreach (ListItem itm in src_rsm.Items)
{
if (itm.Selected)
{
rsmsearch = rsmsearch + separator + "'" + itm.Value + "'";
separator = ",";
}
}
SearchReportCommon itmsearch = new SearchReportCommon();
itmsearch.months = monthsearch;
itmsearch.divs = divsearch;
itmsearch.areas = areasearch;
itmsearch.emps = empsearch;
itmsearch.zsms = zsmsearch;
itmsearch.rsms = rsmsearch;
itmsearch.yr = src_year.SelectedValue;
FetchReportdata(itmsearch, 0);
String curyear = DateTime.Now.Year.ToString();
StaticData itmstat = (new RptTourPlanDL()).GetDropdownData(monthsearch, curyear); //curyear);
src_zsm.DataSource = itmstat.lstzsm;
src_zsm.DataBind();
src_rsm.DataSource = itmstat.lstrsm;
src_rsm.DataBind();
src_area.DataSource = itmstat.lstarea;
src_area.DataBind();
src_employee.DataSource = itmstat.lstemployee;
src_employee.DataBind();
}
