public static DataSet Get_SchReceivable(DateTime arg_dAsOn, int arg_iBlockId, DateTime arg_dSchDate, bool arg_bSch, int arg_iCCId, int argPaySchId)
{
string sSql = "";
string sCond = string.Empty;
string sCond1 = string.Empty;
SqlDataAdapter sda = null;
DataSet ds = new DataSet();
string sAgeName = string.Empty;
int iAgeDays = 0;
int iSLId = 0;
string fStr = "";
DataRow[] drowT;
decimal dAmt = 0;
try
{
BsfGlobal.OpenCRMDB();
ds = new DataSet();
if (arg_iCCId > 0)
{
sCond = "AND A.CostCentreId=" + arg_iCCId;
sCond1 = "AND B.CostCentreId=" + arg_iCCId;
}
sSql = String.Format("SELECT AgeId, AgeDesc, FromDays, ToDays, CAST(0 as Decimal(18,3)) Amount FROM AgeSetUp");
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
sda.Fill(ds, "AgeSetup");
if (arg_bSch == true)
{
//sSql = "SELECT 0 TemplateId,'Advance' Description,L.LeadType,D.Status,SUM(P.NetAmount)-SUM(P.PaidAmount) Receivable FROM PaymentSchedule A " +
// " INNER JOIN PaymentScheduleFlat P ON P.TemplateId=A.TemplateId" +
// " INNER JOIN FlatDetails B ON P.FlatId=B.FlatId INNER JOIN BuyerDetail D ON P.FlatId=D.FlatId " +
// " INNER JOIN LeadRegister L ON L.LeadId=D.LeadId WHERE L.LeadType='Buyer' and D.Status='S' and A.SchDate <='" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' " +
// " AND A.CostCentreId=" + arg_iCCId + " AND A.TypeId=" + argPaySchId + " " +
// " GROUP BY D.Status,L.LeadType " +
// " UNION ALL " +
sSql = " SELECT A.TemplateId,A.Description,L.LeadType,D.Status,SUM(P.NetAmount)-SUM(P.PaidAmount) Receivable FROM PaymentSchedule A " +
" INNER JOIN PaymentScheduleFlat P ON P.TemplateId=A.TemplateId " +
" INNER JOIN FlatDetails B ON P.FlatId=B.FlatId INNER JOIN BuyerDetail D ON P.FlatId=D.FlatId " +
" INNER JOIN LeadRegister L ON L.LeadId=D.LeadId WHERE L.LeadType='Buyer' and D.Status='S' and A.SchDate <='" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' " +
" AND A.CostCentreId=" + arg_iCCId + " And A.TypeId=" + argPaySchId + " And P.StageDetId<>0 " +
" GROUP BY A.TemplateId,A.Description,D.Status,L.LeadType";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
}
else
{
//sSql = String.Format("SELECT E.LeadId ,E.LeadName BuyerName,E.LeadType,D.Status,Sum(A.NetAmount)-SUM(A.PaidAmount) Receivable From dbo.ProgressBillRegister A " +
// "INNER JOIN PaymentScheduleFlat B on A.PaySchId=B.PaymentSchId INNER JOIN FlatDetails C on C.FlatId=A.FlatId " +
// "INNER JOIN BuyerDetail D on B.FlatId=D.FlatId INNER JOIN LeadRegister E on E.LeadId=A.LeadId " +
// "WHERE B.BillPassed=1 and E.LeadType='Buyer' AND D.Status='S' And A.AsOnDate <='{0}' " + sCond1 + " " +
// "GROUP BY E.LeadName,E.LeadType,E.LeadId,D.Status", String.Format("{0:dd/MMM/yyyy}", arg_dAsOn));
sSql = "SELECT B.TemplateId,P.Description,E.LeadType,D.Status,Sum(A.NetAmount)-SUM(A.PaidAmount) Receivable From dbo.ProgressBillRegister A " +
" INNER JOIN PaymentScheduleFlat B on A.PaySchId=B.PaymentSchId INNER JOIN PaymentSchedule P ON P.TemplateId=B.TemplateId " +
" INNER JOIN FlatDetails C on C.FlatId=A.FlatId " +
" INNER JOIN BuyerDetail D on B.FlatId=D.FlatId INNER JOIN LeadRegister E on E.LeadId=A.LeadId " +
" WHERE B.BillPassed=1 and E.LeadType='Buyer' AND D.Status='S' And A.AsOnDate <='" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' " +
" AND B.CostCentreId=" + arg_iCCId + " And P.TypeId=" + argPaySchId + " And B.StageDetId<>0 " +
" GROUP BY B.TemplateId,P.Description,E.LeadType,D.Status";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
}
sda.Fill(ds, "Receivable");
sda.Fill(ds, "ReceivableDet");
if (arg_bSch == true)
{
//sSql = "SELECT A.TemplateId,SUM(Receivable)Receivable,AgeDays FROM ( SELECT 0 TemplateId,A.NetAmount-A.PaidAmount Receivable, " +
// " ISNULL((SELECT ToDays FROM AgeSetup Where ToDays >= Datediff(d,DateAdd(d,0,A.SchDate),'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "') " +
// " AND FromDays <= Datediff(d,DateAdd(d,0,A.SchDate),'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "')),0) AgeDays FROM PaymentScheduleFlat A " +
// " INNER JOIN PaymentSchedule P ON P.TemplateId=A.TemplateId " +
// " INNER JOIN FlatDetails B on A.FlatID=B.FlatId INNER JOIN LeadRegister C ON C.LeadId=B.LeadId " +
// " WHERE DateAdd(d,0,A.SchDate)<'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' AND B.CostCentreId=" + arg_iCCId + " And P.TypeId=" + argPaySchId + " ) A Group By TemplateId,AgeDays " +
// " UNION ALL " +
sSql = " SELECT A.TemplateId,SUM(Receivable)Receivable,AgeDays FROM ( SELECT A.TemplateId,A.NetAmount-A.PaidAmount Receivable, " +
" ISNULL((SELECT ToDays FROM AgeSetup Where ToDays >= Datediff(d,DateAdd(d,0,A.SchDate),'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "') " +
" AND FromDays <= Datediff(d,DateAdd(d,0,A.SchDate),'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "')),0) AgeDays FROM PaymentScheduleFlat A " +
" INNER JOIN PaymentSchedule P ON P.TemplateId=A.TemplateId " +
" INNER JOIN FlatDetails B on A.FlatID=B.FlatId INNER JOIN LeadRegister C ON C.LeadId=B.LeadId " +
" WHERE DateAdd(d,0,A.SchDate)<'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' AND B.CostCentreId=" + arg_iCCId + " " +
" And P.TypeId=" + argPaySchId + " AND A.StageDetId<>0) A Group By TemplateId,AgeDays";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
}
else
{
//sSql = "Select LeadId,SUM(Receivable)Receivable,AgeDays FROM ( " +
// " SELECT C.LeadId,A.NetAmount-A.PaidAmount Receivable, ISNULL((SELECT ToDays FROM AgeSetup " +
// " Where ToDays >= Datediff(d,DateAdd(d,0,AsOnDate),'" + String.Format("{0:dd/MMM/yyyy}", arg_dAsOn) + "') " +
// " AND FromDays <= Datediff(d,DateAdd(d,0,AsOnDate),'" + String.Format("{0:dd/MMM/yyyy}", arg_dAsOn) + "')),0) AgeDays FROM dbo.ProgressBillRegister A " +
// " INNER JOIN FlatDetails B on A.FlatID=B.FlatId INNER JOIN LeadRegister C ON C.LeadId=B.LeadId " +
// " WHERE DateAdd(d,0,AsOnDate)<'" + String.Format("{0:dd/MMM/yyyy}", arg_dAsOn) + "' " + sCond1 + " ) A " +
// " Group By LeadId,AgeDays";
sSql = "Select A.TemplateId,SUM(Receivable)Receivable,AgeDays FROM (SELECT P.TemplateId,A.NetAmount-A.PaidAmount Receivable, " +
" ISNULL((SELECT ToDays FROM AgeSetup Where ToDays >= Datediff(d,DateAdd(d,0,AsOnDate),'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "') " +
" AND FromDays <= Datediff(d,DateAdd(d,0,AsOnDate),'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "')),0) AgeDays FROM dbo.ProgressBillRegister A " +
" INNER JOIN PaymentScheduleFlat P ON P.PaymentSchId=A.PaySchId INNER JOIN PaymentSchedule P1 ON P1.TemplateId=P.TemplateId " +
" INNER JOIN FlatDetails B ON A.FlatID=B.FlatId INNER JOIN LeadRegister C ON C.LeadId=B.LeadId " +
" WHERE DateAdd(d,0,AsOnDate)<'" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' " +
" AND B.CostCentreId=" + arg_iCCId + " AND P1.TypeId=" + argPaySchId + " AND P.StageDetId<>0) A Group By A.TemplateId,AgeDays";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
}
sda.Fill(ds, "AgeDet");
if (arg_bSch == true)
{
//sSql = String.Format("SELECT B.LeadId,C.SchDate,A.FlatNo,C.Description,F.CostCentreName,C.NetAmount,C.PaidAmount,(C.NetAmount-C.PaidAmount) Balance FROM FlatDetails A INNER JOIN LeadRegister B " +
// "On A.LeadId=B.LeadId INNER JOIN PaymentScheduleFlat C On A.FlatId=C.FlatId INNER JOIN BuyerDetail D ON C.FlatId=D.FlatId " +
// "INNER JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre F On C.CostCentreId=F.CostCentreId " +
// "WHERE LeadType='Buyer' and D.Status='S' and C.SchDate <='{0}' " + sCond + " And C.StageDetId<>0 " +
// " ORDER BY C.SortOrder", String.Format("{0:dd/MMM/yyyy}", arg_dSchDate));
sSql = "SELECT C.TemplateId,B.LeadId,C.SchDate,A.FlatNo,B.LeadName,F.CostCentreName,C.NetAmount,C.PaidAmount,(C.NetAmount-C.PaidAmount) Balance FROM FlatDetails A " +
" INNER JOIN LeadRegister B On A.LeadId=B.LeadId INNER JOIN PaymentScheduleFlat C On A.FlatId=C.FlatId " +
" INNER JOIN BuyerDetail D ON C.FlatId=D.FlatId INNER JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre F On C.CostCentreId=F.CostCentreId " +
" WHERE LeadType='Buyer' AND D.Status='S' AND C.SchDate <='" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' AND A.CostCentreId=" + arg_iCCId + " " +
" AND A.PayTypeId=" + argPaySchId + " AND C.StageDetId<>0 ORDER BY C.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
}
else
{
//sSql = String.Format("SELECT E.LeadId,A.PBNo,A.PBDate,C.FlatNo,B.Description,F.CostCentreName,A.NetAmount,A.PaidAmount,(A.NetAmount-A.PaidAmount) Balance " +
// "FROM ProgressBillRegister A INNER JOIN PaymentScheduleFlat B On A.PaySchId=B.PaymentSchId INNER JOIN FlatDetails C On " +
// "C.FlatId=A.FlatId INNER JOIN BuyerDetail D On B.FlatId=D.FlatId INNER JOIN LeadRegister E " +
// "On E.LeadId=A.LeadId INNER JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre F on F.CostCentreId=A.CostCentreId " +
// "WHERE B.BillPassed=1 and E.LeadType='Buyer' AND D.Status='S' AND A.AsOnDate <='{0}' " + sCond + " " +
// "And B.StageDetId<>0 ORDER BY B.SortOrder", String.Format("{0:dd/MMM/yyyy}", arg_dAsOn));
sSql = "SELECT B.TemplateId,E.LeadId,A.PBNo,A.PBDate,C.FlatNo,B.Description,F.CostCentreName,A.NetAmount,A.PaidAmount,(A.NetAmount-A.PaidAmount) Balance " +
" FROM ProgressBillRegister A INNER JOIN PaymentScheduleFlat B On A.PaySchId=B.PaymentSchId INNER JOIN FlatDetails C On C.FlatId=A.FlatId " +
" INNER JOIN BuyerDetail D On B.FlatId=D.FlatId INNER JOIN LeadRegister E On E.LeadId=A.LeadId " +
" INNER JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre F on F.CostCentreId=A.CostCentreId " +
" WHERE B.BillPassed=1 AND E.LeadType='Buyer' AND D.Status='S' AND A.AsOnDate <='" + arg_dSchDate.ToString("dd-MMM-yyyy") + "' " +
" AND A.CostCentreId=" + arg_iCCId + " And C.PayTypeId=" + argPaySchId + " And B.StageDetId<>0 " +
" ORDER BY B.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
}
sda.Fill(ds, "BillDet");
//dAmt = 0;
for (int i = 0; i < ds.Tables["AgeSetup"].Rows.Count; i++)
{
sAgeName = ds.Tables["AgeSetup"].Rows[i]["AgeDesc"].ToString();
iAgeDays = Convert.ToInt32(ds.Tables["AgeSetup"].Rows[i]["ToDays"]);
ds.Tables["ReceivableDet"].Columns.Add(sAgeName, typeof(decimal));
for (int j = 0; j < ds.Tables["ReceivableDet"].Rows.Count; j++)
{
iSLId = (int)ds.Tables["ReceivableDet"].Rows[j]["TemplateId"];
fStr = String.Format("TemplateId={0} AND AgeDays={1}", iSLId, iAgeDays);
drowT = ds.Tables["AgeDet"].Select(fStr);
if (drowT.Length > 0)
{
dAmt = (decimal)drowT[0]["Receivable"];
}
else
{
dAmt = (decimal)0;
}
ds.Tables["ReceivableDet"].Rows[j][sAgeName] = CommFun.FormatNum(dAmt, CommFun.g_iCurrencyDigit);
}
}
BsfGlobal.g_CRMDB.Close();
}
catch (Exception ce)
{
System.Windows.Forms.MessageBox.Show(ce.Message, "CRM", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
}
return(ds);
}
