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);
}
