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