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();
}
}
internal static void UpdateFlatQualAmt(int argPayTypeId, int argFlatId, SqlConnection conn, SqlTransaction tran)
{
FlatDetailsDL.UpdateFlatQualAmt(argPayTypeId, argFlatId, conn, tran);
}