public static DataSet GetPerfAnalysis(DateTime argAsOnDate, DateTime argFromDate, DateTime argToDate, string argType) { DataSet ds = new DataSet(); string sSql = ""; SqlConnection conn = new SqlConnection(); conn = BsfGlobal.OpenCRMDB(); SqlTransaction tran = conn.BeginTransaction(); bool bOtherExec = false; SqlCommand cmd; SqlDataReader dr; try { string sUser = CommFun.GetOtherExecTrans(conn, tran); sSql = "Select * From [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.UserSuperiorTrans Where sUserId=" + BsfGlobal.g_lUserId + ""; cmd = new SqlCommand(sSql, conn, tran); dr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(dr); if (dt.Rows.Count > 0) { bOtherExec = true; } else { bOtherExec = false; } dr.Close(); sSql = "Select A.UserId ExecutiveId,Case When A.EmployeeName='' Then A.UserName Else A.EmployeeName End As ExecName From " + " [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.Users A " + " Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.Position B on B.PositionId=A.PositionId" + " Where B.PositionType='M'"; if (bOtherExec == false) { sSql = sSql + " Order By EmployeeName"; } else { sSql = sSql + " AND A.UserId In " + " (" + sUser.TrimEnd(',') + ") Order By EmployeeName"; } cmd = new SqlCommand(sSql, conn, tran); dr = cmd.ExecuteReader(); DataTable dtE = new DataTable(); dtE.Load(dr); dtE.TableName = "Executive"; ds.Tables.Add(dtE); //da.Fill(ds, "Executive"); //da.Dispose(); if (argType == "AsOn") { sSql = "Select A.ExecutiveId,COUNT(LeadId) CLead From ( " + " Select B.ExecutiveId,A.LeadId From dbo.CallSheet A " + " Inner Join dbo.LeadExecutiveInfo B On A.LeadId=B.LeadId And A.ExecutiveId=B.ExecutiveId " + " Where TrnDate <= '" + argAsOnDate.ToString("dd-MMM-yyyy") + "'" + " Group by B.ExecutiveId,A.LeadId)A Group By A.ExecutiveId"; } else { sSql = "Select A.ExecutiveId,COUNT(LeadId) CLead From ( " + " Select B.ExecutiveId,A.LeadId From dbo.CallSheet A " + " Inner Join dbo.LeadExecutiveInfo B On A.LeadId=B.LeadId And A.ExecutiveId=B.ExecutiveId " + " Where TrnDate Between '" + argFromDate.ToString("dd-MMM-yyyy") + "' And '" + argToDate.ToString("dd-MMM-yyyy") + "'" + " Group by B.ExecutiveId,A.LeadId)A Group By A.ExecutiveId"; } cmd = new SqlCommand(sSql, conn, tran); dr = cmd.ExecuteReader(); DataTable dtL = new DataTable(); dtL.Load(dr); dtL.TableName = "LeadCount"; ds.Tables.Add(dtL); //da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB); //da.Fill(ds, "LeadCount"); //da.Dispose(); if (argType == "AsOn") { sSql = "Select D.ExecutiveId,Count(A.FlatId) CFlats,SUM(A.BaseAmt) Amt From FlatDetails A " + " Inner Join LeadFlatInfo B on A.FlatId=B.FlatId " + " Inner Join LeadRegister C on C.LeadId=B.LeadId " + " Inner Join LeadExecutiveInfo D On D.LeadId=C.LeadId " + " Inner Join dbo.BuyerDetail BD On BD.FlatId=A.FlatId " + " Where FinaliseDate<= '" + argAsOnDate.ToString("dd-MMM-yyyy") + "' " + " Group by D.ExecutiveId"; } else { sSql = "Select D.ExecutiveId,Count(A.FlatId) CFlats,SUM(A.BaseAmt) Amt From FlatDetails A " + " Inner Join LeadFlatInfo B on A.FlatId=B.FlatId " + " Inner Join LeadRegister C on C.LeadId=B.LeadId " + " Inner Join LeadExecutiveInfo D On D.LeadId=C.LeadId " + " Inner Join dbo.BuyerDetail BD On BD.FlatId=A.FlatId " + " Where FinaliseDate Between '" + argFromDate.ToString("dd-MMM-yyyy") + "' And '" + argToDate.ToString("dd-MMM-yyyy") + "' " + " Group by D.ExecutiveId"; } cmd = new SqlCommand(sSql, conn, tran); dr = cmd.ExecuteReader(); DataTable dtF = new DataTable(); dtF.Load(dr); dtF.TableName = "FlatCount"; ds.Tables.Add(dtF); //da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB); //da.Fill(ds, "FlatCount"); //da.Dispose(); } catch (SqlException ex) { tran.Rollback(); BsfGlobal.CustomException(ex.Message, ex.StackTrace); } finally { conn.Close(); } return(ds); }