public static DataTable GetFlatPayScheduleDate(int argPaySchId)
{
return(PaymentScheduleDL.GetFlatPayScheduleDate(argPaySchId));
}
public static void InsertFlatUnit(int argCCId, int argBlockId, int argLevelId, int argFlatTypeId, string argFlatNo)
{
SqlConnection conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
int iFlatId = 0;
try
{
string sSql = "Select * From dbo.FlatType Where FlatTypeId = " + argFlatTypeId;
SqlCommand cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader sdreader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(sdreader);
sdreader.Close();
cmd.Dispose();
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["FloorwiseRate"].ToString() == "N")
{
sSql = "Insert into dbo.FlatDetails(FlatNo,FlatTypeId,PayTypeId,BlockId,LevelId,Area,Rate,BaseAmt,AdvPercent," +
" AdvAmount,Guidelinevalue,USLand,USLandAmt,OtherCostAmt,LandRate,TotalCarPark,NetAmt,IntPercent,CreditDays,CostCentreId,Status,FacingId)" +
" Values('" + argFlatNo + "'," + argFlatTypeId + "," + dt.Rows[0]["PayTypeId"] + "," + argBlockId + "," +
" " + argLevelId + "," + dt.Rows[0]["Area"] + "," + dt.Rows[0]["Rate"] + "," + dt.Rows[0]["BaseAmt"] + "," +
" " + dt.Rows[0]["AdvPercent"] + "," + dt.Rows[0]["AdvAmount"] + "," + dt.Rows[0]["Guidelinevalue"] + "," + dt.Rows[0]["USLandArea"] + "," + dt.Rows[0]["LandAmount"] + "," +
" " + dt.Rows[0]["OtherCostAmt"] + "," + dt.Rows[0]["LandRate"] + "," + dt.Rows[0]["TotalCarpark"] + "," + dt.Rows[0]["NetAmt"] + "," +
" " + dt.Rows[0]["IntPercent"] + "," + dt.Rows[0]["CreditDays"] + "," + argCCId + ",'U'," + dt.Rows[0]["FacingId"] + ") SELECT SCOPE_IDENTITY();";
}
else
{
sSql = "SELECT F.FlatTypeId,R.LevelId,R.Rate FROM dbo.FloorRate R INNER JOIN dbo.FlatType F ON F.FlatTypeId=R.FlatTypeId" +
" WHERE R.LevelId=" + argLevelId + " AND R.FlatTypeId=" + argFlatTypeId + " AND F.ProjId=" + argCCId + "";
DataTable dtR = new DataTable();
dtR = CommFun.FillRcd(sSql, conn, tran);
decimal dRate = 0;
if (dtR.Rows.Count > 0)
{
dRate = Convert.ToDecimal(dtR.Rows[0]["Rate"]);
}
else
{
dRate = Convert.ToDecimal(CommFun.IsNullCheck(dt.Rows[0]["Rate"], CommFun.datatypes.vartypenumeric));
}
decimal dBAmt = Convert.ToDecimal(dt.Rows[0]["Area"]) * dRate;
decimal dNAmt = dBAmt + Convert.ToDecimal(dt.Rows[0]["OtherCostAmt"]);
decimal dAdvAmt = Convert.ToDecimal(dt.Rows[0]["AdvAmount"]);
decimal dAdvPer = decimal.Round((dAdvAmt / dNAmt) * 100, 2);
sSql = "Insert Into dbo.FlatDetails(FlatNo,FlatTypeId,PayTypeId,BlockId,LevelId,Area,Rate,BaseAmt,AdvPercent," +
" AdvAmount,Guidelinevalue,USLand,USLandAmt,OtherCostAmt,LandRate,TotalCarPark,NetAmt,IntPercent,CreditDays,CostCentreId,Status,FacingId)" +
" Values('" + argFlatNo + "'," + argFlatTypeId + "," + dt.Rows[0]["PayTypeId"] + "," + argBlockId + "," +
" " + argLevelId + "," + dt.Rows[0]["Area"] + "," + dRate + "," + dBAmt + "," + dAdvPer + "," +
" " + dAdvAmt + "," + dt.Rows[0]["Guidelinevalue"] + "," + dt.Rows[0]["USLandArea"] + "," + dt.Rows[0]["LandAmount"] + "," +
" " + dt.Rows[0]["OtherCostAmt"] + "," + dt.Rows[0]["LandRate"] + "," + dt.Rows[0]["TotalCarpark"] + "," + dNAmt + "," +
" " + dt.Rows[0]["IntPercent"] + "," + dt.Rows[0]["CreditDays"] + "," + argCCId + ",'U'," + dt.Rows[0]["FacingId"] + ") SELECT SCOPE_IDENTITY();";
}
cmd = new SqlCommand(sSql, conn, tran);
iFlatId = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Dispose();
sSql = "SELECT Max(SortOrder)SortOrder FROM dbo.FlatDetails Where CostCentreId=" + argCCId + " And BlockId=" + argBlockId + " And LevelId=" + argLevelId + " ";
DataTable dtSO = new DataTable();
dtSO = CommFun.FillRcd(sSql, conn, tran);
if (dtSO.Rows.Count > 0)
{
int iSortOrder = Convert.ToInt32(dtSO.Rows[0]["SortOrder"]) + 1;
sSql = "Update dbo.FlatDetails Set SortOrder=" + iSortOrder + " Where FlatId=" + iFlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
sSql = "SELECT * FROM dbo.FlatTypeArea WHERE FlatTypeId=" + argFlatTypeId + " AND CostCentreId=" + argCCId + "";
DataTable dtFT = new DataTable();
dtFT = CommFun.FillRcd(sSql, conn, tran);
if (dtFT.Rows.Count > 0)
{
for (int x = 0; x < dt.Rows.Count; x++)
{
sSql = "INSERT INTO dbo.FlatArea (CostCentreId,FlatId,AreaId,AreaSqft)VALUES (" + argCCId + "," + iFlatId + "," + dt.Rows[x]["AreaId"] + "," + dt.Rows[x]["AreaSqft"] + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
}
sSql = "SELECT F.*,M.OtherCostName FROM dbo.FlatTypeOtherCost F Inner Join dbo.OtherCostMaster M On F.OtherCostId=M.OtherCostId " +
" WHERE FlatTypeId=" + argFlatTypeId + " ";
DataTable dt1 = new DataTable();
dt1 = CommFun.FillRcd(sSql, conn, tran);
if (dt1.Rows.Count > 0)
{
for (int x = 0; x < dt1.Rows.Count; x++)
{
sSql = "INSERT INTO dbo.FlatOtherCost (FlatId,OtherCostId,Area,Rate,Flag,Amount)VALUES (" + iFlatId + "," + dt1.Rows[x]["OtherCostId"] + "," +
" " + dt1.Rows[x]["Area"] + "," + dt1.Rows[x]["Rate"] + ",'" + dt1.Rows[x]["Flag"] + "'," + dt1.Rows[x]["Amount"] + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
}
sSql = "SELECT * FROM FlatTypeOtherArea WHERE FlatTypeId=" + argFlatTypeId;
DataTable dtOA = new DataTable();
dtOA = CommFun.FillRcd(sSql, conn, tran);
if (dtOA.Rows.Count > 0)
{
for (int x = 0; x < dtOA.Rows.Count; x++)
{
sSql = "INSERT INTO FlatOtherArea (FlatId,OtherCostId,Area,Unit,Rate,Amount)VALUES (" + iFlatId + "," + dtOA.Rows[x]["OtherCostId"] + "," +
" " + dtOA.Rows[x]["Area"] + "," + dtOA.Rows[x]["Unit"] + "," + dtOA.Rows[x]["Rate"] + "," + dtOA.Rows[x]["Amount"] + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
}
sSql = "SELECT * FROM FlatTypeOtherInfra WHERE FlatTypeId=" + argFlatTypeId;
DataTable dtOI = new DataTable();
dtOI = CommFun.FillRcd(sSql, conn, tran);
if (dtOI.Rows.Count > 0)
{
for (int x = 0; x < dtOI.Rows.Count; x++)
{
sSql = "INSERT INTO FlatOtherInfra (FlatId,OtherCostId,AmountType,[Percent],Amount)VALUES (" + iFlatId + "," + dtOI.Rows[x]["OtherCostId"] + "," +
" '" + dtOI.Rows[x]["AmountType"] + "'," + dtOI.Rows[x]["Percent"] + "," + dtOI.Rows[x]["Amount"] + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
}
sSql = "SELECT * FROM FlatTypeTax WHERE FlatTypeId=" + argFlatTypeId;
DataTable dtTax = new DataTable();
dtTax = CommFun.FillRcd(sSql, conn, tran);
if (dtTax.Rows.Count > 0)
{
for (int x = 0; x < dtTax.Rows.Count; x++)
{
sSql = "INSERT INTO FlatTax (FlatId,QualifierId,Amount)VALUES (" + iFlatId + "," + dtTax.Rows[x]["QualifierId"] + "," +
" " + dtTax.Rows[x]["Amount"] + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
}
sSql = "SELECT * FROM dbo.FlatTypeQualifier WHERE FlatTypeId=" + argFlatTypeId + " AND CostCentreId=" + argCCId + "";
DataTable dt4 = new DataTable();
dt4 = CommFun.FillRcd(sSql, conn, tran);
if (dt4.Rows.Count > 0)
{
for (int x = 0; x < dt4.Rows.Count; x++)
{
sSql = "INSERT INTO dbo.FlatQualifier (CostCentreId,FlatId,OtherCostId,QualiId,Expression,QualiAmt,Flag)VALUES (" + argCCId + "," + iFlatId + "," + dt4.Rows[x]["OtherCostId"] + "," + dt4.Rows[x]["QualiId"] + "," +
" '" + dt4.Rows[x]["Expression"] + "'," + dt4.Rows[x]["QualiAmt"] + ",'" + dt4.Rows[x]["Flag"] + "')";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
}
sSql = "SELECT A.CostCentreId,A.FlatTypeId,A.TypeId,A.TotalCP,B.NoOfSlots,B.AllottedSlots" +
" FROM FlatTypeCarPark A Inner Join CarParkMaster B On A.TypeId=B.TypeId And A.CostCentreId=B.CostCentreId" +
" WHERE FlatTypeId=" + argFlatTypeId + " AND" +
" A.CostCentreId=" + argCCId + " And BlockId=" + argBlockId + "";
DataTable dtCP = new DataTable();
dtCP = CommFun.FillRcd(sSql, conn, tran);
CRM.BL.ProjectInfoBL.UpdateCarParkSlot(argBlockId, argCCId, conn, tran);
int iSlots = 0; int iTotCP = 0;
if (dtCP.Rows.Count > 0)
{
for (int x = 0; x < dtCP.Rows.Count; x++)
{
iSlots = Convert.ToInt32(dtCP.Rows[x]["NoOfSlots"]) - Convert.ToInt32(dtCP.Rows[x]["AllottedSlots"]);
if (iSlots < 0)
{
iSlots = 0;
}
if (iSlots >= Convert.ToInt32(dtCP.Rows[x]["TotalCP"]))
{
iSlots = Convert.ToInt32(dtCP.Rows[x]["TotalCP"]);
sSql = "INSERT INTO FlatCarPark (CostCentreId,FlatId,TypeId,TotalCP)VALUES (" + argCCId + "," + iFlatId + "," +
" " + dtCP.Rows[x]["TypeId"] + "," + iSlots + ")";
}
else
{
sSql = "INSERT INTO FlatCarPark (CostCentreId,FlatId,TypeId,TotalCP)VALUES (" + argCCId + "," + iFlatId + "," +
" " + dtCP.Rows[x]["TypeId"] + "," + iSlots + ")";
}
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
iTotCP = iTotCP + iSlots;
}
}
if (iTotCP < 0)
{
iTotCP = 0;
}
sSql = "Update FlatDetails Set TotalCarPark=" + iTotCP + " Where FlatId=" + iFlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
CRM.BL.ProjectInfoBL.UpdateCarParkSlot(argBlockId, argCCId, conn, tran);
CRM.BusinessLayer.UnitDirBL.InsertFlatCar(iFlatId, argCCId, conn, tran);
sSql = "Select * From dbo.FlatTypeExtraItem " +
" Where FlatTypeId=" + argFlatTypeId;
DataTable dt2 = new DataTable();
dt2 = CommFun.FillRcd(sSql, conn, tran);
if (dt2.Rows.Count > 0)
{
for (int x = 0; x < dt2.Rows.Count; x++)
{
sSql = "INSERT INTO dbo.FlatExtraItem (FlatId,ExtraItemId,Quantity,Rate,Amount)VALUES " +
" (" + iFlatId + "," + dt2.Rows[x]["ExtraItemId"] + "," + dt2.Rows[x]["Qty"] + "," +
" " + dt2.Rows[x]["ExtraRate"] + "," + dt2.Rows[x]["Amount"] + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
}
sSql = "SELECT F.* FROM dbo.FlatTypeCheckList F WHERE FlatTypeId=" + argFlatTypeId + " AND CostCentreId=" + argCCId + "";
DataTable dt3 = new DataTable();
dt3 = CommFun.FillRcd(sSql, conn, tran);
if (dt3.Rows.Count > 0)
{
for (int x = 0; x < dt3.Rows.Count; x++)
{
sSql = "INSERT INTO dbo.FlatCheckList (CheckListId,FlatId,Status,ExpCompletionDate)VALUES" +
" (" + dt3.Rows[x]["CheckListId"] + "," + iFlatId + ",'" + dt3.Rows[x]["Status"] + "','" + Convert.ToDateTime(dt3.Rows[x]["ExpCompletionDate"]).ToString("dd-MMM-yyyy") + "')";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
sSql = " Update dbo.ProjectInfo Set TotalFlats=(Select Count(FlatId) TotalFlat From dbo.FlatDetails" +
" Where CostCentreId=" + argCCId + ") Where CostCentreId=" + argCCId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
decimal dBaseAmt = 0;
sSql = "SELECT BaseAmt FROM dbo.FlatDetails WHERE FlatId=" + iFlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
sdreader = cmd.ExecuteReader();
DataTable dtB = new DataTable();
dtB.Load(sdreader);
sdreader.Close();
cmd.Dispose();
if (dtB.Rows.Count > 0)
{
dBaseAmt = Convert.ToDecimal(CommFun.IsNullCheck(dtB.Rows[0]["BaseAmt"], CommFun.datatypes.vartypenumeric));
}
decimal dOtherAmt = 0;
sSql = "Select Sum(Case When Flag='-' then Amount*(-1) else Amount End) Amount from dbo.FlatOtherCost " +
" Where FlatId =" + iFlatId + " and OtherCostId not in (Select OtherCostId from dbo.OXGross " +
" Where CostCentreId=" + argCCId + ")";
cmd = new SqlCommand(sSql, conn, tran);
sdreader = cmd.ExecuteReader();
DataTable dtOCost = new DataTable();
dtOCost.Load(sdreader);
sdreader.Close();
cmd.Dispose();
if (dtOCost.Rows.Count > 0)
{
dOtherAmt = Convert.ToDecimal(CommFun.IsNullCheck(dtOCost.Rows[0]["Amount"], CommFun.datatypes.vartypenumeric));
}
decimal dNetAmt = dBaseAmt + dOtherAmt;
sSql = "UPDATE dbo.FlatDetails SET NetAmt=" + dNetAmt + " WHERE FlatId=" + iFlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
PaymentScheduleDL.InsertFlatScheduleI(iFlatId, conn, tran);
FlatDetailsDL.UpdateFlatQualAmt(Convert.ToInt32(dt.Rows[0]["PayTypeId"]), iFlatId, conn, tran);
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
conn.Dispose();
conn.Close();
}
}
public static void InsertCashDetails(DataTable dtPay, int argCCId, int argBuyerId, decimal argAmt, DateTime argDate, string argType)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
conn = BsfGlobal.OpenCRMDB();
string sSql = ""; int iPaySchId = 0; int iFlatId = 0;
decimal dNetAmt = 0, dArea = 0, dRate = 0, dBaseAmt = 0, dQualAmt = 0;
int iEntryId = 0; bool bAns = false;
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
if (dtPay != null)
{
sSql = "Insert Into dbo.CashReceiptRegister(CashDate,LeadId,CostCentreId,Amount)Values(" +
"'" + argDate.ToString("dd/MMM/yyyy") + "'," + argBuyerId + "," + argCCId + "," + argAmt + ")SELECT SCOPE_IDENTITY()";
cmd = new SqlCommand(sSql, conn, tran);
iEntryId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
for (int i = 0; i < dtPay.Rows.Count; i++)
{
if (Convert.ToDecimal(dtPay.Rows[i]["CurrentAmount"]) > 0)
{
iPaySchId = Convert.ToInt32(dtPay.Rows[i]["PaySchId"]);
iFlatId = Convert.ToInt32(dtPay.Rows[i]["FlatId"]);
dArea = Convert.ToDecimal(dtPay.Rows[i]["Area"]);
dRate = Convert.ToDecimal(dtPay.Rows[i]["Rate"]);
dBaseAmt = dArea * dRate;
dNetAmt = Convert.ToDecimal(dtPay.Rows[i]["NetAmt"]);
dQualAmt = Convert.ToDecimal(dtPay.Rows[i]["QualifierAmt"]);
sSql = "Insert Into dbo.CashReceiptTrans(CashReceiptId,FlatId,PaySchId,PBillId,PaySchAmount,PaySchNetAmount,CashAmount,FlatRate,FlatAmount)Values(" + iEntryId + ", " +
" " + dtPay.Rows[i]["FlatId"] + "," + dtPay.Rows[i]["PaySchId"] + "," + dtPay.Rows[i]["PBillId"] + "," + dtPay.Rows[i]["Amount"] + "," + dtPay.Rows[i]["NetAmount"] + "," + dtPay.Rows[i]["CurrentAmount"] + "," + dRate + "," +
" " + dNetAmt + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Update PaymentScheduleFlat Set Amount=" + dtPay.Rows[i]["Amount"] + "-" + dtPay.Rows[i]["CurrentAmount"] + ",NetAmount=" + dtPay.Rows[i]["NetAmount"] + "-" + dtPay.Rows[i]["CurrentAmount"] + " Where PaymentSchId=" + iPaySchId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (argType == "PB")
{
sSql = "Update dbo.ProgressBillRegister Set BillAmount=" + dtPay.Rows[i]["Amount"] + "-" + dtPay.Rows[i]["CurrentAmount"] + ",NetAmount=" + dtPay.Rows[i]["NetAmount"] + "-" + dtPay.Rows[i]["CurrentAmount"] + " Where PaySchId=" + iPaySchId + " And PBillId=" + dtPay.Rows[i]["PBillId"] + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Update dbo.ProgressBillMaster Set NetAmount=NetAmount-" + dtPay.Rows[i]["CurrentAmount"] + " Where ProgRegId=" + dtPay.Rows[i]["ProgRegId"] + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
decimal NetAmount = dNetAmt - argAmt;
decimal BaseAmount = dBaseAmt - argAmt;
decimal Rate = BaseAmount / dArea;
sSql = "Update dbo.FlatDetails Set Rate=" + Rate + ",BaseAmt=" + BaseAmount + ",NetAmt=" + NetAmount + " Where FlatId=" + iFlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Select * From dbo.PaymentScheduleFlat Where FlatId=" + iFlatId + " And TemplateId<>0";
cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
cmd.Dispose();
if (dt.Rows.Count > 0)
{
for (int x = 0; x < dt.Rows.Count; x++)
{
decimal dSchPer = Convert.ToDecimal(dt.Rows[x]["Amount"]) / (NetAmount + dQualAmt) * 100;
//decimal dSchPer = Convert.ToDecimal(dt.Rows[x]["Amount"]) / (NetAmount + dQualAmt) * 100;
sSql = "Update dbo.PaymentScheduleFlat Set SchPercent=" + dSchPer + " Where PaymentSchId=" + dt.Rows[x]["PaymentSchId"] + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
sSql = "Select PaymentSchId,FlatId,TemplateId,CostCentreId,SchType,Description,SchDescId,StageId,OtherCostId,SchDate," +
" DateAfter, Duration,DurationType,SchPercent,Amount,PreStageTypeId,SortOrder,BillPassed,PaidAmount From dbo.PaymentScheduleFlat " +
" Where FlatId=" + iFlatId + " And TemplateId<>0 Order By SortOrder";
cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader dr1 = cmd.ExecuteReader();
DataTable dtF = new DataTable();
dtF.Load(dr1);
cmd.Dispose();
if (dtF.Rows.Count > 0)
{
PaymentScheduleDL.UpdateCashBuyerScheduleQual(iFlatId, dtF, conn, tran);
}
}
}
bAns = true;
if (bAns == true)
{
BsfGlobal.InsertLog(DateTime.Now, "Buyer-Receipt-Cash-Add", "N", "", iEntryId, argCCId, 0, BsfGlobal.g_sCRMDBName, "", BsfGlobal.g_lUserId, argAmt, 0);
}
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public static DataSet GetFlatAdvReceipt(int argTempId, string argType, int argPayTypeId, int argCCId)
{
return(PaymentScheduleDL.GetFlatAdvReceipt(argTempId, argType, argPayTypeId, argCCId));
}
public static bool CheckNetAmt(int argFlatId, int argPaySchId, DataTable dtAmt)
{
return(PaymentScheduleDL.CheckNetAmt(argFlatId, argPaySchId, dtAmt));
}
public static bool GetAdvFound(int argPayTypeId, int argCCId)
{
return(PaymentScheduleDL.GetAdvFound(argPayTypeId, argCCId));
}
public static DataTable GetReceiptTypes(int argTempId, string argType, int argPayTypeId, int argCCId)
{
return(PaymentScheduleDL.GetReceiptTypes(argTempId, argType, argPayTypeId, argCCId));
}
public static DataTable PaySchType(int argCCId)
{
return(PaymentScheduleDL.PaySchType(argCCId));
}
public static void DeletePay(int argTempId)
{
PaymentScheduleDL.DeletePay(argTempId);
}
public static void UpdatePayDate(int argCCId, int argTempId, DataTable argPayTrans)
{
PaymentScheduleDL.UpdatePayDate(argCCId, argTempId, argPayTrans);
}
public static void UpdatePayPercent(int argCCId, int argTempId, decimal argPer)
{
PaymentScheduleDL.UpdatePayPercent(argCCId, argTempId, argPer);
}
public static DataTable GetFlatPreviousStages(int argTempId, int argCCId, int argPayTypeId, int argFlatId)
{
return(PaymentScheduleDL.GetFlatPreviousStages(argTempId, argCCId, argPayTypeId, argFlatId));
}
public static bool CheckDown(int argTempId, int argCheckId)
{
return(PaymentScheduleDL.CheckDown(argTempId, argCheckId));
}
public static void UpdateTemplatePer(int argId, decimal argPer)
{
PaymentScheduleDL.UpdateTemplatePer(argId, argPer);
}
public static DataTable GetStagesBlock(int argBlockId, int argCCId)
{
return(PaymentScheduleDL.GetStagesBlock(argBlockId, argCCId));
}
public static bool CheckPaymentScheduleDesUsed(int argId)
{
return(PaymentScheduleDL.CheckPaymentScheduleDesUsed(argId));
}
public static DataTable GetStagesLevel(int argLevelId, int argCCId)
{
return(PaymentScheduleDL.GetStagesLevel(argLevelId, argCCId));
}
public static void UpdateFlatPaySchDate(int argFlatId, int argTempId, int argTypeId, bool argAfter, string argDurType, int argduration, string argDate, int argCCId, int argPayTypeId)
{
PaymentScheduleDL.UpdateFlatPaySchDate(argFlatId, argTempId, argTypeId, argAfter, argDurType, argduration, argDate, argCCId, argPayTypeId);
}
public static DataTable GetReceiptTypeOrder(int argPayTypeId, int argCCId)
{
return(PaymentScheduleDL.GetReceiptTypeOrder(argPayTypeId, argCCId));
}
public static DataTable GetAllFlats(int argCCId)
{
return(PaymentScheduleDL.GetAllFlats(argCCId));
}
public static DataTable GetQualifierMaster(string argType, int argId, string argFlatType, int argFlatId, string argQualType)
{
return(PaymentScheduleDL.GetQualifierMaster(argType, argId, argFlatType, argFlatId, argQualType));
}
public static bool CheckBillPassed(int argFlatId)
{
return(PaymentScheduleDL.CheckBillPassed(argFlatId));
}
public static void UpdateReceiptTypeM(int argTempId, DataTable argR, DataTable dtTax)
{
PaymentScheduleDL.UpdateReceiptTypeM(argTempId, argR, dtTax);
}
public static decimal CheckReceiptAmt(int argFlatId, int argPaySchId, int argReceiptId, int argOCId, decimal argAmt)
{
return(PaymentScheduleDL.CheckReceiptAmt(argFlatId, argPaySchId, argReceiptId, argOCId, argAmt));
}
public static string GetAdvPer(int argPayTypeId, int argCCId)
{
return(PaymentScheduleDL.GetAdvPer(argPayTypeId, argCCId));
}
public static string GetStatus(int argFlatId)
{
return(PaymentScheduleDL.GetStatus(argFlatId));
}
internal static void UpdateBuyerDet(string argMode, FinalizationBO BOFIN, DataTable dtFinal, string argFlatNo, bool argChkSend)
{
SqlConnection conn = new SqlConnection();
String sSql = "";
SqlCommand cmd = null;
string ValidUpto = "", FinalDate = "";
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
if (argMode == "E")
{
ValidUpto = string.Format("{0:dd/MMM/yyyy}", BOFIN.DE_ValidUpto);
FinalDate = string.Format("{0:dd/MMM/yyyy}", BOFIN.DE_Final);
if (ValidUpto != "01/Jan/0001")
{
sSql = "Update dbo.BuyerDetail Set Status='" + BOFIN.s_Status + "'," +
" CustomerType='" + BOFIN.s_CustomerType + "', PaymentOption='" + BOFIN.s_PaymentOption + "'," +
" BrokerId=" + BOFIN.i_BrokerId + ",BrokerComm=" + BOFIN.d_BrokerComm + ",BrokerAmount=" + BOFIN.d_BrokerAmt + "," +
" ValidUpto='" + ValidUpto + "',FinaliseDate='" + FinalDate + "',PostSaleExecId=" + BOFIN.i_PostExecId + ", " +
" Advance=" + BOFIN.d_AdvAmt + " Where FlatId=" + BOFIN.i_FlatId + " AND CostCentreId=" + BOFIN.i_CostCentreId + "";
}
else
{
ValidUpto = null;
sSql = "Update dbo.BuyerDetail Set Status='" + BOFIN.s_Status + "'," +
" CustomerType='" + BOFIN.s_CustomerType + "', PaymentOption='" + BOFIN.s_PaymentOption + "'," +
" BrokerId=" + BOFIN.i_BrokerId + ",BrokerComm=" + BOFIN.d_BrokerComm + ",BrokerAmount=" + BOFIN.d_BrokerAmt + "," +
" ValidUpto='" + ValidUpto + "',FinaliseDate='" + FinalDate + "',PostSaleExecId=" + BOFIN.i_PostExecId + ", " +
" Advance=" + BOFIN.d_AdvAmt + " Where FlatId=" + BOFIN.i_FlatId + " AND CostCentreId=" + BOFIN.i_CostCentreId + "";
}
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Update dbo.FollowUp Set ExecutiveId=" + BOFIN.i_PostExecId + "" +
" Where FlatId=" + BOFIN.i_FlatId + " AND CostCentreId=" + BOFIN.i_CostCentreId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Update dbo.PaymentScheduleFlat Set SchDate='" + FinalDate + "'" +
" Where TemplateId=0 And FlatId=" + BOFIN.i_FlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Update dbo.PaymentScheduleFlat Set SchDate='" + FinalDate + "'" +
" Where TemplateId=-1 And FlatId=" + BOFIN.i_FlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
SqlDataReader sdr, sdr1, sdr2; DataTable dt, dt1; int iStgId = 0, iTemplateId = 0;
int iDateAfter = 0, iDuration = 0; string sDurType = ""; DateTime SchDate; int iSortOrder = 0;
DateTime StartDate = DateTime.Now; DateTime EndDate = DateTime.Now; int ipre = 0;
sSql = "Select TemplateId,PreStageTypeId from dbo.PaymentScheduleFlat Where FlatId=" + BOFIN.i_FlatId + " And PreStageTypeId=-1";
cmd = new SqlCommand(sSql, conn, tran);
sdr = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(sdr); cmd.Dispose();
if (dt.Rows.Count > 0)
{
iStgId = Convert.ToInt32(dt.Rows[0]["PreStageTypeId"]);
iTemplateId = Convert.ToInt32(dt.Rows[0]["TemplateId"]);
}
dt.Dispose();
sSql = "Select SortOrder From dbo.PaymentScheduleFlat Where FlatId=" + BOFIN.i_FlatId + " And TemplateId=" + iTemplateId + "";
cmd = new SqlCommand(sSql, conn, tran);
sdr2 = cmd.ExecuteReader();
dt1 = new DataTable();
dt1.Load(sdr2); cmd.Dispose();
dt1.Dispose();
if (dt1.Rows.Count > 0)
{
iSortOrder = Convert.ToInt32(dt1.Rows[0]["SortOrder"]);
}
sSql = "select StartDate,EndDate From ProjectInfo Where CostCentreId= " + BOFIN.i_CostCentreId;
cmd = new SqlCommand(sSql, conn, tran);
dt = new DataTable();
sdr = cmd.ExecuteReader();
dt.Load(sdr);
dt.Dispose();
if (dt.Rows.Count > 0)
{
StartDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["StartDate"], CommFun.datatypes.VarTypeDate));
EndDate = Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[0]["EndDate"], CommFun.datatypes.VarTypeDate));
}
sSql = "Update dbo.PaymentScheduleFlat Set SchDate='" + FinalDate + "'" +
" Where TemplateId=" + iTemplateId + " And FlatId=" + BOFIN.i_FlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Update dbo.PaymentScheduleFlat Set SchDate='" + FinalDate + "'" +
" Where TemplateId=0 And FlatId=" + BOFIN.i_FlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (iStgId == -1)
{
if (iStgId == -1)
{
sSql = "Select A.PreStageTypeId,A.CostCentreId,A.TemplateId,A.DateAfter,A.Duration,A.Durationtype from dbo.PaymentScheduleFlat A" +
" Left Join dbo.ProgressBillRegister B On A.FlatId=B.FlatId " +
" Where A.FlatId=" + BOFIN.i_FlatId + " And A.SortOrder>=" + iSortOrder + "" +
" And A.PaymentSchId Not In " +
" (Select PaySchId From dbo.ProgressBillRegister Where FlatId=" + BOFIN.i_FlatId + ") Order By A.SortOrder";
}
cmd = new SqlCommand(sSql, conn, tran);
sdr1 = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(sdr1);
cmd.Dispose();
for (int i = 0; i < dt.Rows.Count; i++)
{
iTemplateId = Convert.ToInt32(dt.Rows[i]["TemplateId"]);
ipre = Convert.ToInt32(dt.Rows[i]["PreStageTypeId"]);
iDateAfter = Convert.ToInt32(dt.Rows[i]["DateAfter"]);
iDuration = Convert.ToInt32(dt.Rows[i]["Duration"]);
sDurType = dt.Rows[i]["DurationType"].ToString();
if (ipre == -1)
{
}
else if (ipre == -2)
{
}
else if (ipre == -3)
{
}
else if (ipre == 0)
{
}
else
{
iTemplateId = ipre;
}
sSql = "Select SchDate From dbo.PaymentScheduleFlat Where CostCentreId=" + dt.Rows[i]["CostCentreId"] + " And FlatId=" + BOFIN.i_FlatId + "" +
" And TemplateId=" + iTemplateId + "";
cmd = new SqlCommand(sSql, conn, tran);
DataTable dtDate = new DataTable();
sdr = cmd.ExecuteReader();
dtDate.Load(sdr);
dtDate.Dispose();
if (ipre == -1)
{
SchDate = Convert.ToDateTime(CommFun.IsNullCheck(FinalDate, CommFun.datatypes.VarTypeDate));
}
else if (ipre == -2)
{
SchDate = StartDate;
}
else if (ipre == -3)
{
SchDate = EndDate;
}
else
{
SchDate = Convert.ToDateTime(CommFun.IsNullCheck(dtDate.Rows[0]["SchDate"], CommFun.datatypes.VarTypeDate));
}
if (sDurType == "D")
{
if (iDateAfter == 0)
{
SchDate = SchDate.AddDays(iDuration);
}
else
{
SchDate = SchDate.AddDays(-iDuration);
}
}
else if (sDurType == "M")
{
if (iDateAfter == 0)
{
SchDate = SchDate.AddMonths(iDuration);
}
else
{
SchDate = SchDate.AddDays(-iDuration);
}
}
sSql = "Update dbo.PaymentScheduleFlat Set SchDate='" + string.Format(Convert.ToDateTime(SchDate).ToString("dd-MMM-yyyy")) + "'" +
" Where TemplateId=" + dt.Rows[i]["TemplateId"] + " And FlatId=" + BOFIN.i_FlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
UnitDirDL.InsertFlatChk(dtFinal, BOFIN.i_FlatId, "F", argChkSend, argFlatNo, BOFIN.i_CostCentreId, conn, tran);
sSql = "Select AdvAmount From dbo.FlatDetails Where FlatId=" + BOFIN.i_FlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dtA = new DataTable();
dtA.Load(dr);
cmd.Dispose();
if (dtA.Rows.Count > 0)
{
decimal dAdvAmt = Convert.ToDecimal(dtA.Rows[0]["AdvAmount"]);
if (dAdvAmt != Convert.ToDecimal(BOFIN.d_AdvAmt))
{
sSql = "Update FlatDetails Set AdvAmount=" + BOFIN.d_AdvAmt + " WHERE FlatId=" + BOFIN.i_FlatId + " ";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
PaymentScheduleDL.InsertFlatScheduleI(BOFIN.i_FlatId, conn, tran);
dtA.Dispose();
cmd.Dispose();
}
}
}
//PaymentScheduleDL.InsertFlatScheduleI(BOFIN.i_FlatId, conn, tran);
//FlatDetailsDL.UpdateFlatQualAmt(BOFIN.i_, BOFIN.i_FlatId, conn, tran);
tran.Commit();
if (argMode == "E")
{
BsfGlobal.InsertLog(DateTime.Now, "Flat-Finalisation-Modify", "N", "Flat Finalisation", BOFIN.i_FlatId, BOFIN.i_CostCentreId, 0, BsfGlobal.g_sCRMDBName, argFlatNo, BsfGlobal.g_lUserId, BOFIN.d_AdvAmt, 0);
}
else
{
BsfGlobal.InsertLog(DateTime.Now, "Flat-Finalisation-Create", "C", "Flat Finalisation", BOFIN.i_FlatId, BOFIN.i_CostCentreId, 0, BsfGlobal.g_sCRMDBName, argFlatNo, BsfGlobal.g_lUserId, BOFIN.d_AdvAmt, 0);
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
internal static DataTable GetEMISchedule(int argBranchId, int argPayTypeId)
{
return(PaymentScheduleDL.GetEMISchedule(argBranchId, argPayTypeId));
}
public static void UpdateDetails(int argCashRepId, DataTable dtPay, int argCCId, int argBuyerId, decimal argAmt, DateTime argDate)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
conn = BsfGlobal.OpenCRMDB();
string sSql = ""; int iPaySchId = 0; int iFlatId = 0;
decimal dNetAmt = 0, dArea = 0, dRate = 0, dBaseAmt = 0, dQualAmt = 0;
int iEntryId = 0;
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
if (dtPay != null)
{
sSql = "Delete From dbo.CashReceiptRegister Where CashReceiptId=" + argCashRepId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Delete From dbo.CashReceiptTrans Where CashReceiptId=" + argCashRepId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Select FlatId From dbo.FlatDetails Where CostCentreId=" + argCCId + " And LeadId=" + argBuyerId + "";
cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader sdr = cmd.ExecuteReader();
DataTable dtFlat = new DataTable();
dtFlat.Load(sdr);
cmd.Dispose();
for (int n = 0; n < dtFlat.Rows.Count; n++)
{
DataTable dt = new DataTable();
dt = dtPay;
DataView dv = new DataView(dt);
dv.RowFilter = "FlatId=" + dtFlat.Rows[n]["FlatId"] + "";
dt = dv.ToTable();
sSql = "Insert Into dbo.CashReceiptRegister(CashDate,LeadId,CostCentreId,Amount)Values(" +
"'" + argDate.ToString("dd/MMM/yyyy") + "'," + argBuyerId + "," + argCCId + "," + argAmt + ")SELECT SCOPE_IDENTITY()";
cmd = new SqlCommand(sSql, conn, tran);
iEntryId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
for (int i = 0; i < dt.Rows.Count; i++)
{
iPaySchId = Convert.ToInt32(dt.Rows[i]["PaySchId"]);
iFlatId = Convert.ToInt32(dt.Rows[i]["FlatId"]);
dArea = Convert.ToDecimal(dt.Rows[i]["Area"]);
dRate = Convert.ToDecimal(dt.Rows[0]["Rate"]);
dBaseAmt = dArea * dRate;
dNetAmt = Convert.ToDecimal(dt.Rows[0]["NetAmt"]);
dQualAmt = Convert.ToDecimal(dt.Rows[i]["QualifierAmt"]);
sSql = "Insert Into dbo.CashReceiptTrans(CashReceiptId,FlatId,PaySchId,PaySchAmount,PaySchNetAmount,CashAmount,FlatRate,FlatAmount)Values(" + iEntryId + ", " +
" " + iFlatId + "," + dt.Rows[i]["PaySchId"] + "," + dt.Rows[i]["Amount"] + "," + dt.Rows[i]["NetAmount"] + "," + dt.Rows[i]["CurrentAmount"] + "," + dRate + "," +
" " + dNetAmt + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
decimal NetAmount = dNetAmt - argAmt;
decimal BaseAmount = dBaseAmt - argAmt;
decimal Rate = dRate - decimal.Round(dRate * (argAmt / dNetAmt), 3);
sSql = "Update dbo.FlatDetails Set Rate=" + Rate + ",BaseAmt=" + BaseAmount + ",NetAmt=" + NetAmount + " Where FlatId=" + iFlatId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Select * From dbo.PaymentScheduleFlat Where FlatId=" + iFlatId + " And TemplateId<>0";
cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dtPS = new DataTable();
dtPS.Load(dr);
cmd.Dispose();
if (dtPS.Rows.Count > 0)
{
for (int x = 0; x < dtPS.Rows.Count; x++)
{
decimal dAmount = decimal.Round((NetAmount + dQualAmt) * 100 / Convert.ToDecimal(dtPS.Rows[x]["SchPercent"]), 3);
sSql = "Update dbo.PaymentScheduleFlat Set Amount=" + dAmount + " Where PaymentSchId=" + dtPS.Rows[x]["PaymentSchId"] + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
sSql = "Select PaymentSchId,FlatId,TemplateId,CostCentreId,SchType,Description,SchDescId,StageId,OtherCostId,SchDate," +
" DateAfter, Duration,DurationType,SchPercent,Amount,PreStageTypeId,SortOrder,BillPassed,PaidAmount From dbo.PaymentScheduleFlat " +
" Where FlatId=" + iFlatId + " And TemplateId<>0 Order By SortOrder";
cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader dr1 = cmd.ExecuteReader();
DataTable dtF = new DataTable();
dtF.Load(dr1);
cmd.Dispose();
if (dtF.Rows.Count > 0)
{
PaymentScheduleDL.UpdateCashBuyerScheduleQual(iFlatId, dtF, conn, tran);
}
}
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public static void UpdateReceiptTypeSortOrder(DataTable argDt)
{
PaymentScheduleDL.UpdateReceiptTypeSortOrder(argDt);
}