public DataSet GetAgeingReportDataDynamic(SearchAgeing itmsearch, int rows)
{
RptAgeingList lstAgeing = new RptAgeingList();
Common.OpenConnection();
String sql = "select * from rpt_ageing";
if (rows > 0)
{
sql = "select top " + rows + " * from rpt_ageing";
}
string whsql = "";
string strconcat = "";
if (itmsearch.div != null && itmsearch.div != "" && itmsearch.div != "select")
{
whsql = whsql + strconcat + " division like '%" + itmsearch.div + "%'";
strconcat = " and ";
}
if (itmsearch.rsm != null && itmsearch.rsm != "" && itmsearch.rsm != "select")
{
whsql = whsql + strconcat + " RSMHQ like '%" + itmsearch.rsm + "%'";
strconcat = " and ";
}
if (itmsearch.asm != null && itmsearch.asm != "" && itmsearch.asm != "select")
{
whsql = whsql + strconcat + " ASMHQ like '%" + itmsearch.asm + "%'";
strconcat = " and ";
}
if (itmsearch.msr != null && itmsearch.msr != "" && itmsearch.msr != "select")
{
whsql = whsql + strconcat + " MSRHQ like '%" + itmsearch.msr + "%'";
strconcat = " and ";
}
if (itmsearch.area != null && itmsearch.area != "" && itmsearch.area != "select")
{
whsql = whsql + strconcat + " Area like '%" + itmsearch.area + "%'";
strconcat = " and ";
}
if (itmsearch.doctor != null && itmsearch.doctor != "" && itmsearch.doctor != "select")
{
whsql = whsql + strconcat + " DoctorName like '%" + itmsearch.doctor + "%'";
strconcat = " and ";
}
//date range not required
//if (itmsearch.intFrom > 0 && itmsearch.intTo > 0)
//{
// string months = "";
// string separator = "";
// int count = itmsearch.intFrom;
// while (count <= itmsearch.intTo)
// {
// months = months + separator + "'" + Common.monthlist.ElementAt(count) + "'";
// separator = ",";
// count++;
// }
// whsql = whsql + strconcat + " month in (" + months + ")";
//}
if (itmsearch.intFrom > 0)
{
string months = "";
string separator = "";
int count = itmsearch.intFrom;
months = "'" + Common.monthlist.ElementAt(count) + "'";
whsql = whsql + strconcat + " month = " + months;
}
if (whsql != null && whsql != "")
{
sql = sql + " where " + whsql;
}
SqlCommand cmd = new SqlCommand(sql, Common.conn);
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//DataTable dt = new DataTable();
//da.Fill(dt);
//return dt;
//MendineMasterdummyDataSet dsCustomers = new MendineMasterdummyDataSet();
DataSet dsCustomers = new DataSet();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
//sda.Fill(dsCustomers, "rpt_ageing");
sda.Fill(dsCustomers);
}
return(dsCustomers);
//using (SqlDataReader rdr = cmd.ExecuteReader())
//{
// while (rdr.Read())
// {
// RptAgeing itmaging = new RptAgeing();
// itmaging.ZsmHQ = Common.GetString(rdr["ZsmHQ"]);
// itmaging.Area = Common.GetString(rdr["Area"]);
// itmaging.AreaType = Common.GetString(rdr["AreaType"]);
// itmaging.DoctorName = Common.GetString(rdr["DoctorName"]);
// lstAgeing.Add(itmaging);
// }
//}
}
public DataSet GetDoctorVisitReportDataDynamic(SearchDoctorVisit itmsearch, int rows)
{
RptAgeingList lstAgeing = new RptAgeingList();
Common.OpenConnection();
String sql = "select * from rpt_doctorvisit";
if (rows > 0)
{
sql = "select top " + rows + " * from rpt_doctorvisit";
}
string whsql = "";
string strconcat = "";
if (itmsearch.months != null && itmsearch.months != "")
{
whsql = whsql + strconcat + " month in (" + itmsearch.months + ")";
strconcat = " and ";
}
if (itmsearch.divs != null && itmsearch.divs != "" && itmsearch.divs != "select")
{
whsql = whsql + strconcat + " division in(" + itmsearch.divs + ")";
strconcat = " and ";
}
if (itmsearch.rsms != null && itmsearch.rsms != "" && itmsearch.rsms != "select")
{
whsql = whsql + strconcat + " RSMHQ in (" + itmsearch.rsms + ")";
strconcat = " and ";
}
if (itmsearch.dists != null && itmsearch.dists != "" && itmsearch.dists != "select")
{
whsql = whsql + strconcat + " Area in (" + itmsearch.dists + ")";
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, "rpt_ageing");
sda.Fill(dsCustomers);
}
Common.CloseConnection();
return(dsCustomers);
}