protected DataSet getAdministratorInfo(string strOrdey)
{
string sql_searchUser;
StandRepComToSQL sql = new StandRepComToSQL();
string sqlReport = "SELECT ViewName FROM ReportList where ID = " + HidReportId.Value;
DataSet ds_qreport = helper.GetDataSet(sqlReport);
string viewName = ds_qreport.Tables[0].Rows[0][0].ToString();
if (viewName.Equals("BookingsSales"))
{
sql_searchUser = sql.RepCompileSql(HidReportId.Value, getLoginRole(), "", "", strOrdey, sql.getSelectMeetingDate(Session["RSMID"].ToString()), Session["RSMID"].ToString());
}
else if (viewName.Equals("SalesBacklog"))
{
sql_searchUser = sql.RepCompileSqlBacklog(HidReportId.Value, getLoginRole(), strOrdey, strOrdey, Session["RSMID"].ToString());
}
else {
sql_searchUser = sql.RepCompileSql(HidReportId.Value, getLoginRole(), "", "", strOrdey, Session["RSMID"].ToString());
}
DataSet ds_query_Admin = helper.GetDataSet(sql_searchUser);
return ds_query_Admin;
}
protected DataSet getAdministratorInfo(string strOrdey)
{
string sql_searchUser;
StandRepComToSQL sql = new StandRepComToSQL();
string sqlReport = "SELECT ViewName FROM ReportList where ID = " + HidReportId.Value;
DataSet ds_qreport = helper.GetDataSet(sqlReport);
string viewName = ds_qreport.Tables[0].Rows[0][0].ToString();
if (viewName.Equals("BookingsSales"))
{
sql_searchUser = sql.RepCompileSql(HidReportId.Value, getLoginRole(), "", "", strOrdey, sql.getSelectMeetingDate(Session["ExecutiveID"].ToString()), Session["ExecutiveID"].ToString());
}
else if (viewName.Equals("SalesBacklog"))
{
sql_searchUser = sql.RepCompileSqlBacklog(HidReportId.Value, getLoginRole(), strOrdey, sql.getSelectMeetingDate(Session["ExecutiveID"].ToString()), Session["ExecutiveID"].ToString());
}
//by yyan ItemW149 20110916 add start
//else if (viewName.Equals("ProjectInformation"))
//{
// sql_searchUser = sql.RepCompileSqlA(HidReportId.Value, getLoginRole(), "", "", strOrdey, viewName);
//}
//by yyan ItemW149 20110916 add end
else {
sql_searchUser = sql.RepCompileSql(HidReportId.Value, getLoginRole(), "", "", strOrdey, Session["ExecutiveID"].ToString());
}
DataSet ds_query_Admin = helper.GetDataSet(sql_searchUser);
return ds_query_Admin;
}
protected DataSet getReportList()
{
string query_region = string.Format("select * from ReportList where FlagShare =0 order by ID desc",UserID);
DataSet dataSet = helper.GetDataSet(query_region);
//by yyan 20110518 item 53 add start
if (dataSet.Tables[0].Rows.Count > 0)
{
string strID = "";
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
if (dataSet.Tables[0].Rows[i]["ViewName"].ToString().Equals("BookingsSales"))
{
string id = dataSet.Tables[0].Rows[i]["ID"].ToString();
string sqlReportValue = "select * from ReportValue where FieldName in ('SegmentAbbr','SaleOrgNameAbbr','OperationAbbr') and id=" + id;
DataTable dtReportValue = helper.GetDataSet(sqlReportValue).Tables[0];
if (dtReportValue.Rows.Count > 0)
{
StandRepComToSQL standRepComToSQL = new StandRepComToSQL();
string sqlWhere = standRepComToSQL.getWhereNames(dtReportValue);
string SegmentAbbrWhere = "";
string SalesOrgWhere = "";
string OperationAbbrWhere = "";
for (int j = 0; j < dtReportValue.Rows.Count; j++)
{
if (dtReportValue.Rows[j]["FieldName"].ToString().Equals("SegmentAbbr") && !dtReportValue.Rows[j]["operator"].ToString().Equals("0") && !dtReportValue.Rows[j]["operator"].ToString().Equals(""))
{
SegmentAbbrWhere = " and SegmentAbbr in (select Segment.abbr from Segment,[User],User_Segment where " +
" Segment.Deleted=0 and User_Segment.SegmentID =Segment.ID and " +
" User_Segment.Deleted=0 and User_Segment.UserID= [User].UserID and [User].Deleted =0 and" +
" [User].UserID=" + Session["GeneralSalesOrgMgrID"].ToString() + ")";
}
else if (dtReportValue.Rows[j]["FieldName"].ToString().Equals("SaleOrgNameAbbr") && !dtReportValue.Rows[j]["operator"].ToString().Equals("0") && !dtReportValue.Rows[j]["operator"].ToString().Equals(""))
{
SalesOrgWhere = " and SaleOrgNameAbbr in (select SalesOrg.abbr from SalesOrg,[User],SalesOrg_User where " +
" SalesOrg.Deleted=0 and SalesOrg_User.SalesOrgID =SalesOrg.ID and " +
" SalesOrg_User.Deleted=0 and " +
" SalesOrg_User.UserID= [User].UserID and [User].Deleted =0 and [User].UserID=" + Session["GeneralSalesOrgMgrID"].ToString() + ")";
}
else if (dtReportValue.Rows[j]["FieldName"].ToString().Equals("OperationAbbr") && !dtReportValue.Rows[j]["operator"].ToString().Equals("0") && !dtReportValue.Rows[j]["operator"].ToString().Equals(""))
{
OperationAbbrWhere = " AND OperationAbbr IN (select Operation.abbr from Operation,[User],User_Operation where " +
" Operation.Deleted=0 and User_Operation.OperationID =Operation.ID and " +
" User_Operation.Deleted=0 and User_Operation.UserID= [User].UserID and [User].Deleted =0 and [User].UserID=" + Session["GeneralSalesOrgMgrID"].ToString() + ")";
}
}
string strCount = string.Format("select count(*) count from BookingsSales where 0=0 {0} {1} {2} {3}", sqlWhere, SegmentAbbrWhere, SalesOrgWhere, OperationAbbrWhere);
DataTable dtCount = helper.GetDataSet(strCount).Tables[0];
if (Convert.ToInt32(dtCount.Rows[0][0].ToString()) > 0)
{
strID = strID + dataSet.Tables[0].Rows[i]["ID"].ToString() + ",";
}
}
else
{
strID = strID + dataSet.Tables[0].Rows[i]["ID"].ToString() + ",";
}
}
else
{
strID = strID + dataSet.Tables[0].Rows[i]["ID"].ToString() + ",";
}
}
if (strID.Length > 0)
{
strID = strID.Substring(0, strID.Length - 1);
query_region = "select * from ReportList where FlagShare=0 and id in (" + strID + ") order by ID desc";
dataSet = helper.GetDataSet(query_region);
}
else
{
dataSet = null;
}
}
else
{
dataSet = null;
}
//by yyan 20110518 item 53 add end
return dataSet;
}
