public static int CheckListSortIdFound(string argName)
{
int ians = 0;
try
{
DataTable dt;
string sSql = "Select Max(SortOrder) SortOrder from dbo.CheckListMaster Where TypeName='" + argName + "'";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
ians = Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[0]["SortOrder"], CommFun.datatypes.vartypenumeric));
}
da.Dispose();
dt.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(ians);
}
public static void UpdateSortOrder(DataTable dt)
{
SqlCommand cmd;
string sSql = "";
try
{
dt.AcceptChanges();
BsfGlobal.OpenCRMDB();
for (int i = 0; i < dt.Rows.Count; i++)
{
int iFlatId = Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[i]["FlatId"].ToString(), CommFun.datatypes.vartypenumeric));
int iOrder = i + 1;
sSql = "Update dbo.FlatDetails Set SortOrder=" + iOrder + " Where FlatId=" + iFlatId + " ";
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
}
public string GetDocumentPath()
{
string sPath = "";
BsfGlobal.OpenWorkFlowDB();
try
{
string sql = "Select DocumentFilePath from CompanyMailSetting";
SqlDataAdapter sda = new SqlDataAdapter(sql, BsfGlobal.g_WorkFlowDB);
DataTable dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
if (dt.Rows.Count > 0)
{
sPath = CommFun.IsNullCheck(dt.Rows[0]["DocumentFilePath"].ToString(), CommFun.datatypes.vartypestring).ToString();
}
dt.Dispose();
BsfGlobal.g_WorkFlowDB.Close();
}
catch (SqlException ee)
{
BsfGlobal.CustomException(ee.Message, ee.StackTrace);
}
return(sPath);
}
public string CCGetExtension(int argTempId, string argFrom)
{
SqlCommand Cmd = null;
BsfGlobal.OpenCRMDB();
string extn = "";
string sql = "";
try
{
if (argFrom == "P")
{
sql = "SELECT Extension From CostCentreTemplate Where TemplateId=" + argTempId + "";
}
else
{
sql = "SELECT Extension From FlatTemplateUpload Where TemplateId=" + argTempId + "";
}
Cmd = new SqlCommand(sql, BsfGlobal.g_CRMDB);
extn = CommFun.IsNullCheck(Cmd.ExecuteScalar(), CommFun.datatypes.vartypestring).ToString();
BsfGlobal.g_CRMDB.Close();
}
catch (SqlException ee)
{
BsfGlobal.CustomException(ee.Message, ee.StackTrace);
}
return(extn);
}
public static void InsertAllotment(int argCCId, int argFlatId, int argBuyerId, decimal argNetAmt, decimal argPaidAmt, decimal argPenaltyAmt, decimal argBalAmt, DateTime argDate,
string argCancelType, string argRemarks, int argBlockId, string argFlatNo, DataTable dtChk, bool argChkSend, string argCCName)
{
SqlConnection conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "INSERT INTO dbo.AllotmentCancel(CostCentreId,FlatId,BuyerId,NetAmount,PaidAmount,PenaltyAmt,BalanceAmount,CancelDate,AllotType,Remarks) VALUES " +
" (" + argCCId + "," + argFlatId + "," + argBuyerId + "," + argNetAmt + "," + argPaidAmt + "," + argPenaltyAmt + "," + argBalAmt + ", " +
" '" + string.Format("{0:dd/MMM/yyyy}", Convert.ToDateTime(CommFun.IsNullCheck(argDate, CommFun.datatypes.VarTypeDate))) + "', " +
" '" + argCancelType + "','" + argRemarks + "') SELECT SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sSql, conn, tran);
int iCancelId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
sSql = string.Format("Select * from dbo.FlatCarPark Where CostCentreId={0} AND FlatId={1}", argCCId, argFlatId);
cmd = new SqlCommand(sSql, conn, tran);
DataTable dt = new DataTable();
SqlDataReader dreader = cmd.ExecuteReader();
dt.Load(dreader);
dreader.Close();
cmd.Dispose();
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
sSql = string.Format("Insert into dbo.CancelledCarPark(CancelId, CostCentreId, FlatId, TypeId, TotalCP) Values({0}, {1}, {2}, {3}, {4})",
iCancelId, argCCId, argFlatId, Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[i]["TypeId"], CommFun.datatypes.vartypenumeric)),
Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[i]["TotalCP"], CommFun.datatypes.vartypenumeric)));
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
CRM.BusinessLayer.UnitDirBL.InsertFlatCheckList(dtChk, argFlatId, "C", argChkSend, argCCId, argFlatNo);
string sStr = "Flat No-" + argFlatNo + " in " + argCCName + " is Cancelled";
BsfGlobal.InsertAlert("CRM-Flat-Cancel", sStr, argCCId, BsfGlobal.g_sCRMDBName);
tran.Commit();
BsfGlobal.InsertLog(DateTime.Now, "Flat-Cancellation-Create", "N", "Flat-Cancellation", iCancelId, argCCId, 0,
BsfGlobal.g_sCRMDBName, argFlatNo, BsfGlobal.g_lUserId);
}
catch (Exception ex)
{
tran.Rollback();
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Dispose();
conn.Close();
}
}
internal static bool CheckUsedInFlatsPayment(int argCCId, int argOCId)
{
BsfGlobal.OpenCRMDB();
bool bCheck = false;
try
{
string sSql = "Select COUNT(*) from dbo.FlatOtherCost Where OtherCostId=" + argOCId + "";
SqlCommand cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
int i_Count = Convert.ToInt32(CommFun.IsNullCheck(cmd.ExecuteScalar(), CommFun.datatypes.vartypenumeric));
cmd.Dispose();
if (i_Count > 0)
{
bCheck = true;
}
else
{
sSql = "Select COUNT(*) from dbo.FlatOtherArea Where OtherCostId=" + argOCId + "";
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
i_Count = Convert.ToInt32(CommFun.IsNullCheck(cmd.ExecuteScalar(), CommFun.datatypes.vartypenumeric));
cmd.Dispose();
if (i_Count > 0)
{
bCheck = true;
}
else
{
sSql = "Select COUNT(*) from dbo.FlatOtherInfra Where OtherCostId=" + argOCId + "";
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
i_Count = Convert.ToInt32(CommFun.IsNullCheck(cmd.ExecuteScalar(), CommFun.datatypes.vartypenumeric));
cmd.Dispose();
if (i_Count > 0)
{
bCheck = true;
}
else
{
bCheck = false;
}
}
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(bCheck);
}
private void btnOk_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (BsfGlobal.FindPermission("Incentive Details-Add") == false)
{
MessageBox.Show("You don't have Rights to Incentive Details-Add");
return;
}
grdIGView.FocusedRowHandle = grdIGView.FocusedRowHandle + 1;
if (DEDate.EditValue == null)
{
return;
}
if (DETo.EditValue == null)
{
return;
}
IncGenBO.DE_Date = Convert.ToDateTime(DEDate.EditValue.ToString());
IncGenBO.DE_From = Convert.ToDateTime(DEFrom.EditValue.ToString());
IncGenBO.DE_To = Convert.ToDateTime(DETo.EditValue.ToString());
IncGenBO.s_RefNo = txtRefNo.Text;
IncGenBO.s_Narration = txtNarration.Text;
IncGenBO.d_TotalAmount = Convert.ToDecimal(CommFun.IsNullCheck(grdIGView.Columns["Amount"].SummaryText.ToString(), CommFun.datatypes.vartypenumeric));
if (s_mName == "A")
{
IncentiveDL.InsertIncGen("A", IncGenBO);
InsertIncentiveTrans();
Close();
}
else
{
IncentiveDL.InsertIncGen("E", IncGenBO);
InsertIncentiveTrans();
bar1.Visible = false;
bar3.Visible = false;
panelControl1.Controls.Clear();
frmIncentiveDetails frmID = new frmIncentiveDetails()
{
TopLevel = false, FormBorderStyle = FormBorderStyle.None, Dock = DockStyle.Fill
};
panelControl1.Controls.Add(frmID);
frmID.Show();
}
}
public static int FindRentTransId(int argRentId)
{
int iRegTransId = 0;
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
SqlDataReader sdr;
DataTable dtTran;
conn = BsfGlobal.OpenCRMDB();
using (SqlTransaction tran = conn.BeginTransaction())
{
string sSql = "";
try
{
dtTran = new DataTable();
sSql = "SELECT Max(RentTransId) MaxNo FROM RentAgreementTrans WHERE RentId=" + argRentId + "";
cmd = new SqlCommand(sSql, conn, tran);
sdr = cmd.ExecuteReader();
dtTran.Load(sdr);
cmd.Dispose();
if (dtTran.Rows.Count > 0)
{
for (int a = 0; a < dtTran.Rows.Count; a++)
{
iRegTransId = Convert.ToInt32(CommFun.IsNullCheck(dtTran.Rows[a]["MaxNo"], CommFun.datatypes.vartypenumeric));
}
}
dtTran.Dispose();
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
//conn.Close();
//conn.Dispose();
}
}
return(iRegTransId);
}
internal static void InsertFlatCheckList(DataTable dt, int argCheckListId, int argFlatTypeId)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
conn = BsfGlobal.OpenCRMDB();
using (SqlTransaction tran = conn.BeginTransaction())
{
string sSql = "";
try
{
sSql = string.Format("Delete From dbo.FlatChecklist Where FlatId in (Select FlatId from dbo.FlatDetails Where FlatTypeId={0})" +
" AND CheckListId={1}", argFlatTypeId, argCheckListId);
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
for (int i = 0; i < dt.Rows.Count; i++)
{
sSql = "INSERT INTO dbo.FlatChecklist (FlatId,CheckListId,ExpCompletionDate,CompletionDate,ExecutiveId,Status,Remarks) VALUES" +
" (" + dt.Rows[i]["FlatId"] + "," + argCheckListId + "," +
"'" + Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[i]["ExpCompletionDate"], CommFun.datatypes.VarTypeDate)).ToString("dd-MMM-yyyy") + "'," +
"'" + Convert.ToDateTime(CommFun.IsNullCheck(dt.Rows[i]["CompletionDate"], CommFun.datatypes.VarTypeDate)).ToString("dd-MMM-yyyy") + "'," +
"" + CommFun.IsNullCheck(dt.Rows[i]["ExecutiveId"], CommFun.datatypes.vartypenumeric) + ",1,'" + dt.Rows[i]["Remarks"] + "')";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
internal static void UpdateBlockSort(DataTable dt, string argType, int argCCId)
{
string sSql = "";
SqlCommand cmd = null;
try
{
dt.AcceptChanges();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (argType == "Block")
{
int iBlockId = Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[i]["BlockId"].ToString(), CommFun.datatypes.vartypenumeric));
int iOrder = i + 1;
sSql = "Update BlockMaster Set SortOrder=" + iOrder + " Where BlockId=" + iBlockId + " And CostCentreId=" + argCCId + "";
}
else if (argType == "Stage")
{
int iStageId = Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[i]["StageId"].ToString(), CommFun.datatypes.vartypenumeric));
int iOrder = i + 1;
sSql = "Update Stages Set SortOrder=" + iOrder + " Where StageId=" + iStageId + " And CostCentreId=" + argCCId + "";
}
else if (argType == "Level")
{
int iLevelId = Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[i]["LevelId"].ToString(), CommFun.datatypes.vartypenumeric));
int iOrder = i + 1;
sSql = "Update LevelMaster Set SortOrder=" + iOrder + " Where LevelId=" + iLevelId + " And CostCentreId=" + argCCId + "";
}
cmd = new SqlCommand(sSql, BsfGlobal.OpenCRMDB());
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
}
public static decimal GetPrevCampaignAmount(int argOppId, int argCampId)
{
BsfGlobal.OpenCRMDB();
decimal dCampAmount = 0;
try
{
string sSql = "Select ISNULL(SUM(NetAmount),0) Amount From dbo.CampaignDetails Where RequestId=" + argOppId + " AND CampaignId<>" + argCampId + "";
SqlCommand cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
dCampAmount = Convert.ToDecimal(CommFun.IsNullCheck(cmd.ExecuteScalar(), CommFun.datatypes.vartypenumeric));
cmd.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dCampAmount);
}
public static DataTable GetProjectTrans(int argCCId, string argType)
{
SqlDataAdapter sda;
string sSql = "";
DataTable dt = new DataTable();
dt.Columns.Add("Step", typeof(Int32));
dt.Columns.Add("Description", typeof(string));
dt.Columns.Add("Status", typeof(Int32));
try
{
BsfGlobal.OpenCRMDB();
sSql = "Select ROW_NUMBER() OVER (ORDER BY A.SortOrder) As Step,A.CheckListId, A.CheckListName Description," +
" Case When ISNULL(B.Status,0) <> 0 then ROW_NUMBER() OVER (ORDER BY A.SortOrder) else null end Status from dbo.CheckListMaster A " +
" Left Join dbo.CCCheckListTrans B On A.CheckListId=B.CheckListId " +
" And B.CostCentreId=" + argCCId + " " +
" Where A.TypeName='P' Order by A.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
DataTable dtT = new DataTable();
sda.Fill(dtT);
sda.Dispose();
sSql = " Select A.CheckListId,B.CompletionDate From CheckListMaster A " +
" Left Join dbo.CCCheckListTrans B On A.CheckListId=B.CheckListId And B.CostCentreId=" + argCCId + "" +
" Where A.TypeName='P' Order by A.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
DataTable dtC = new DataTable();
sda.Fill(dtC);
sda.Dispose();
DataView dv;
DataRow dRow;
int iCheckListId = 0;
int iStatus = 0;
string sDes = "";
int iStep = 0;
for (int i = 0; i < dtT.Rows.Count; i++)
{
iCheckListId = Convert.ToInt32(dtT.Rows[i]["CheckListId"]);
iStep = Convert.ToInt32(dtT.Rows[i]["Step"]);
iStatus = Convert.ToInt32(CommFun.IsNullCheck(dtT.Rows[i]["Status"], CommFun.datatypes.vartypenumeric));
sDes = dtT.Rows[i]["Description"].ToString();
dv = new DataView(dtC);
dv.RowFilter = "CheckListId = " + iCheckListId;
if (dv.ToTable().Rows.Count > 0)
{
sDes = sDes + " (" + Convert.ToDateTime(CommFun.IsNullCheck(dv.ToTable().Rows[0]["CompletionDate"], CommFun.datatypes.VarTypeDate)).ToString("dd-MM-yyyy") + ")";
}
dv.Dispose();
dRow = dt.NewRow();
dRow["Step"] = iStep;
dRow["Description"] = sDes;
dRow["Status"] = dtT.Rows[i]["Status"];
dt.Rows.Add(dRow);
}
dtT.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
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();
}
}
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 Update_Age(DataTable argAge)
{
SqlConnection conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
DataTable dt = new DataTable();
dt = argAge.GetChanges(DataRowState.Added);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand Command = new SqlCommand("Trans_Age", conn, tran)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@Type", "I");
Command.Parameters.AddWithValue("@AgeDesc", CommFun.IsNullCheck(dt.Rows[i]["AgeDesc"], CommFun.datatypes.vartypestring));
Command.Parameters.AddWithValue("@AgeId", 0);
Command.Parameters.AddWithValue("@FromDays", CommFun.IsNullCheck(dt.Rows[i]["FromDays"], CommFun.datatypes.vartypenumeric));
Command.Parameters.AddWithValue("@ToDays", CommFun.IsNullCheck(dt.Rows[i]["ToDays"], CommFun.datatypes.vartypenumeric));
Command.ExecuteNonQuery();
}
dt.Dispose();
}
dt = new DataTable();
dt = argAge.GetChanges(DataRowState.Modified);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand Command = new SqlCommand("Trans_Age", conn, tran)
{
CommandType = CommandType.StoredProcedure
};
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@Type", "U");
Command.Parameters.AddWithValue("@AgeId", dt.Rows[i]["AgeId"]);
Command.Parameters.AddWithValue("@AgeDesc", dt.Rows[i]["AgeDesc"]);
Command.Parameters.AddWithValue("@FromDays", dt.Rows[i]["FromDays"]);
Command.Parameters.AddWithValue("@ToDays", dt.Rows[i]["ToDays"]);
Command.ExecuteNonQuery();
}
dt.Dispose();
}
tran.Commit();
}
catch (Exception ce)
{
tran.Rollback();
System.Windows.Forms.MessageBox.Show(ce.Message, "CRM", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public static bool DeleteRentAgreementRegister(int RegId, int argCostId, string argVouNo, int RegTransId, string argType)
{
int iRegTransId = 0;
string sRefNo = "";
string sRegDate, sStDate, sEndDate;
string sRentDuration = "";
string sIntDuration = "";
string sTerms = "";
string sRenewType = "";
decimal dGPriod = 0;
string sRentType = "";
// DateTime RegDate,StDate, EndDate;
decimal dRent = 0;
decimal dNetRent = 0;
decimal dAdvance = 0;
decimal dIntRate = 0;
string sSql = "";
bool bSuccess = false;
DataTable dtTran, dtUpTran;
SqlDataReader sdr, sdrT;
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
sSql = String.Format("DELETE FROM RentAgreementTrans WHERE RentTransId={0}", RegTransId);
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (argType == "Renewal" || argType == "Cancel")
{
dtTran = new DataTable();
sSql = "SELECT Max(RentTransId) MaxNo FROM RentAgreementTrans WHERE RentId=" + RegId + "";
cmd = new SqlCommand(sSql, conn, tran);
sdr = cmd.ExecuteReader();
dtTran.Load(sdr);
cmd.Dispose();
if (dtTran.Rows.Count > 0)
{
for (int a = 0; a < dtTran.Rows.Count; a++)
{
iRegTransId = Convert.ToInt32(CommFun.IsNullCheck(dtTran.Rows[a]["MaxNo"], CommFun.datatypes.vartypenumeric));
}
}
dtTran.Dispose();
dtUpTran = new DataTable();
sSql = "SELECT * FROM RentAgreementTrans WHERE RentTransId=" + iRegTransId + "";
cmd = new SqlCommand(sSql, conn, tran);
sdrT = cmd.ExecuteReader();
dtUpTran.Load(sdrT);
cmd.Dispose();
if (dtUpTran.Rows.Count > 0)
{
for (int b = 0; b < dtUpTran.Rows.Count; b++)
{
sRefNo = dtUpTran.Rows[b]["RefNo"].ToString();
sRegDate = String.Format("{0:dd-MMM-yyyy}", CommFun.IsNullCheck(dtUpTran.Rows[b]["RegDate"].ToString(), CommFun.datatypes.VarTypeDate));
sStDate = String.Format("{0:dd-MMM-yyyy}", CommFun.IsNullCheck(dtUpTran.Rows[b]["StartDate"].ToString(), CommFun.datatypes.VarTypeDate));
sEndDate = String.Format("{0:dd-MMM-yyyy}", CommFun.IsNullCheck(dtUpTran.Rows[b]["EndDate"].ToString(), CommFun.datatypes.VarTypeDate));
//RegDate = Convert.ToDateTime(dtUpTran.Rows[b]["RegDate"].ToString());
//StDate = Convert.ToDateTime(dtUpTran.Rows[b]["StartDate"].ToString());
//EndDate = Convert.ToDateTime(dtUpTran.Rows[b]["EndDate"].ToString());
dRent = Convert.ToDecimal(dtUpTran.Rows[b]["Rent"].ToString());
dNetRent = Convert.ToDecimal(dtUpTran.Rows[b]["NetRent"].ToString());
sRentDuration = dtUpTran.Rows[b]["RentDuration"].ToString();
dAdvance = Convert.ToDecimal(dtUpTran.Rows[b]["Advance"].ToString());
dIntRate = Convert.ToDecimal(dtUpTran.Rows[b]["IntRate"].ToString());
sIntDuration = dtUpTran.Rows[b]["IntDuration"].ToString();
sTerms = dtUpTran.Rows[b]["Terms"].ToString();
sRenewType = dtUpTran.Rows[b]["RenewType"].ToString();
dGPriod = Convert.ToDecimal(dtUpTran.Rows[b]["GracePriod"].ToString());
sRentType = dtUpTran.Rows[b]["RentType"].ToString();
sSql = "UPDATE RentDetail SET RefNo='" + sRefNo + "',RegDate='" + sRegDate + "',StartDate='" + sStDate + "',EndDate='" + sEndDate + "'," +
" Rent='" + dRent + "',NetRent='" + dNetRent + "',RentDuration='" + sRentDuration + "',Advance='" + dAdvance + "',IntRate='" + dIntRate + "',IntDuration='" + sIntDuration + "'," +
" Terms='" + sTerms + "',RenewType='" + sRenewType + "',GracePriod='" + dGPriod + "',RentType='" + sRentType + "' WHERE RentId=" + RegId + " ";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
dtUpTran.Dispose();
}
else
{
sSql = String.Format("DELETE FROM RentDetail WHERE RentId={0}", RegId);
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
sSql = String.Format("DELETE FROM RentSchTrans WHERE RentTransId={0}", RegTransId);
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
BsfGlobal.InsertLog(DateTime.Now, "Rent-Delete", "D", "Rent", RegId, argCostId, 0, BsfGlobal.g_sCRMDBName, argVouNo, BsfGlobal.g_lUserId);
tran.Commit();
bSuccess = true;
tran.Dispose();
}
catch (Exception ce)
{
tran.Rollback();
System.Windows.Forms.MessageBox.Show(ce.Message, "PMS", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
BsfGlobal.CustomException(ce.Message, ce.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
return(bSuccess);
}
internal static DataTable InsertIncentiveTrans(string s_Mode, DataTable dt, TargetEntryBO TargetBO)
{
BsfGlobal.OpenCRMDB();
string ssql = "";
DataTable dtIncenTrans = null;
SqlCommand sCmd;
try
{
//if (s_Mode == "A")
//{
ssql = "Delete From IncentiveTrans Where TargetId=" + TargetBO.i_TargetId + "";
sCmd = new SqlCommand(ssql, BsfGlobal.g_CRMDB);
sCmd.ExecuteNonQuery();
sCmd.Dispose();
for (int i = 0; i < dt.Rows.Count; i++)
{
if (s_Mode == "A")
{
if (TargetBO.s_IncenType == "L")
{
ssql = String.Format("Insert into IncentiveTrans(TargetId, FromValue, ToValue, IncValue) Values({0} , {1}, {2}, {3})", TargetBO.i_TargetId, CommFun.IsNullCheck(dt.Rows[i]["From"], CommFun.datatypes.vartypenumeric), CommFun.IsNullCheck(dt.Rows[i]["To"], CommFun.datatypes.vartypenumeric), CommFun.IsNullCheck(dt.Rows[i]["Amount"], CommFun.datatypes.vartypenumeric));
}
else
{
ssql = String.Format("Insert into IncentiveTrans(TargetId, FromValue, ToValue, IncValue) Values({0} , {1}, {2}, {3})", TargetBO.i_TargetId, CommFun.IsNullCheck(dt.Rows[i]["From"], CommFun.datatypes.vartypenumeric), CommFun.IsNullCheck(dt.Rows[i]["To"], CommFun.datatypes.vartypenumeric), CommFun.IsNullCheck(dt.Rows[i]["RateofIncentive"], CommFun.datatypes.vartypenumeric));
}
}
else
{
ssql = String.Format("Insert into IncentiveTrans(TargetId, FromValue, ToValue, IncValue) Values({0} , {1}, {2}, {3})", TargetBO.i_TargetId, CommFun.IsNullCheck(dt.Rows[i]["FromValue"], CommFun.datatypes.vartypenumeric), CommFun.IsNullCheck(dt.Rows[i]["ToValue"], CommFun.datatypes.vartypenumeric), CommFun.IsNullCheck(dt.Rows[i]["IncValue"], CommFun.datatypes.vartypenumeric));
}
sCmd = new SqlCommand(ssql, BsfGlobal.g_CRMDB);
sCmd.ExecuteNonQuery();
sCmd.Dispose();
}
//}
//else
//{
// ssql = String.Format("Update IncentiveTrans set FromValue={0}, ToValue={1}, IncValue={2} Where IncentiveId={3} AND TargetId={4}", dt.Rows[i]["FromValue"], dt.Rows[i]["ToValue"], dt.Rows[i]["IncValue"], TargetBO.i_IncentiveId, TargetBO.i_TargetId);
// sCmd = new SqlCommand(ssql, BsfGlobal.g_CRMDB);
// sCmd.ExecuteNonQuery();
//}
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dtIncenTrans);
}
public static void InsertExtraItemFlatType(DataTable dt, int argFlatTypeId, DataTable argdtQualifier)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd; string sSql = "";
conn = BsfGlobal.OpenCRMDB();
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
sSql = "Delete from dbo.FlatTypeExtraItem Where FlatTypeId= " + argFlatTypeId;
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Delete from dbo.FlatTypeExtraItemQualifier Where FlatTypeId= " + argFlatTypeId;
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
for (int i = 0; i < dt.Rows.Count; i++)
{
sSql = "INSERT INTO dbo.FlatTypeExtraItem(FlatTypeId,ExtraItemId,ExtraRate,Qty,Amount,NetAmount) VALUES" +
" (" + argFlatTypeId + "," + dt.Rows[i]["ExtraItemId"] + "," + dt.Rows[i]["Rate"] + "," +
" " + dt.Rows[i]["Qty"] + "," + dt.Rows[i]["Amount"] + "," + dt.Rows[i]["NetAmount"] + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (argdtQualifier != null && argdtQualifier.Rows.Count > 0)
{
DataRow[] drow = argdtQualifier.Select("ExtraItemId=" + dt.Rows[i]["ExtraItemId"] + "");
for (int j = 0; j <= drow.Length - 1; j++)
{
sSql = "Insert into dbo.FlatTypeExtraItemQualifier(FlatTypeId,ExtraItemId,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge," +
"EDCess,ExpValue,ExpPerValue,SurValue,EDValue,Amount,HEDPer,HEDValue,NetPer,TaxableValue,TaxablePer) " +
"Values(" + argFlatTypeId + "," + Convert.ToInt32(CommFun.IsNullCheck(drow[j]["ExtraItemId"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToInt32(CommFun.IsNullCheck(drow[j]["QualifierId"], CommFun.datatypes.vartypenumeric)) +
",'" + CommFun.IsNullCheck(drow[j]["Expression"], CommFun.datatypes.vartypestring).ToString() +
"'," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["ExpPer"], CommFun.datatypes.vartypenumeric)) +
",'" + CommFun.IsNullCheck(drow[j]["Add_Less_Flag"], CommFun.datatypes.vartypestring).ToString() +
"'," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["SurCharge"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["EDCess"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["ExpValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["ExpPerValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["SurValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["EDValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["Amount"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["HEDPer"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["HEDValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["NetPer"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["TaxableValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["TaxablePer"], CommFun.datatypes.vartypenumeric)) + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
tran.Rollback();
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public static void InsertExtraItemFlat(DataTable dt, int argFlatId, DataTable argdtQualifier, int argCCId)
{
bool bInsert = false;
SqlConnection conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
int iFlatExtraItemId = 0;
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
string sSql = "";
SqlCommand cmd = null;
decimal dNetAmt = 0;
string sLog = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
iFlatExtraItemId = Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[i]["FlatExtraItemId"], CommFun.datatypes.vartypenumeric));
dNetAmt = Convert.ToDecimal(CommFun.IsNullCheck(dt.Rows[i]["NetAmount"], CommFun.datatypes.vartypenumeric));
if (iFlatExtraItemId == 0)
{
sSql = "INSERT INTO dbo.FlatExtraItem(FlatId,ExtraItemId,Quantity,Rate,Amount,NetAmount,Updatefrom,Sel,Approve) VALUES" +
" (" + argFlatId + "," + dt.Rows[i]["ExtraItemId"] + "," + dt.Rows[i]["Qty"] + "," + dt.Rows[i]["Rate"] + "," +
" " + dt.Rows[i]["Amount"] + "," + dNetAmt + ",'" + dt.Rows[i]["Updatefrom"] +
"','" + Convert.ToBoolean(CommFun.IsNullCheck(dt.Rows[i]["Sel"], CommFun.datatypes.varTypeBoolean)) +
"','" + CommFun.IsNullCheck(dt.Rows[i]["Approve"], CommFun.datatypes.vartypestring).ToString() + "') SELECT SCOPE_IDENTITY();";
cmd = new SqlCommand(sSql, conn, tran);
iFlatExtraItemId = Convert.ToInt32(CommFun.IsNullCheck(cmd.ExecuteScalar(), CommFun.datatypes.vartypenumeric));
cmd.Dispose();
sLog = "Y";
}
else
{
sSql = "Update dbo.FlatExtraItem Set FlatId=" + argFlatId + ",ExtraItemId=" + dt.Rows[i]["ExtraItemId"] + "," +
"Quantity=" + dt.Rows[i]["Qty"] + ",Rate=" + dt.Rows[i]["Rate"] + ",Amount=" + dt.Rows[i]["Amount"] + "," +
"NetAmount=" + dNetAmt + ",Updatefrom='" + dt.Rows[i]["Updatefrom"] + "',WebUpdate=0," +
"Sel='" + Convert.ToBoolean(CommFun.IsNullCheck(dt.Rows[i]["Sel"], CommFun.datatypes.varTypeBoolean)) + "'," +
"Approve='" + CommFun.IsNullCheck(dt.Rows[i]["Approve"], CommFun.datatypes.vartypestring).ToString() + "'" +
" Where FlatExtraItemId=" + iFlatExtraItemId + "";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (sLog == "")
{
sLog = "N";
}
}
sSql = "Delete dbo.FlatExtraItemQualifier Where FlatId=" + argFlatId + " AND ExtraItemId=" + dt.Rows[i]["ExtraItemId"];
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (argdtQualifier != null && argdtQualifier.Rows.Count > 0)
{
DataRow[] drow = argdtQualifier.Select("ExtraItemId=" + dt.Rows[i]["ExtraItemId"] + "");
for (int j = 0; j <= drow.Length - 1; j++)
{
sSql = "Insert into dbo.FlatExtraItemQualifier(FlatId,ExtraItemId,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge," +
"EDCess,ExpValue,ExpPerValue,SurValue,EDValue,Amount,HEDPer,HEDValue,NetPer,TaxableValue,TaxablePer) " +
"Values(" + argFlatId + "," + Convert.ToInt32(CommFun.IsNullCheck(drow[j]["ExtraItemId"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToInt32(CommFun.IsNullCheck(drow[j]["QualifierId"], CommFun.datatypes.vartypenumeric)) +
",'" + CommFun.IsNullCheck(drow[j]["Expression"], CommFun.datatypes.vartypestring).ToString() +
"'," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["ExpPer"], CommFun.datatypes.vartypenumeric)) +
",'" + CommFun.IsNullCheck(drow[j]["Add_Less_Flag"], CommFun.datatypes.vartypestring).ToString() +
"'," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["SurCharge"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["EDCess"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["ExpValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["ExpPerValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["SurValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["EDValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["Amount"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["HEDPer"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["HEDValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["NetPer"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["TaxableValue"], CommFun.datatypes.vartypenumeric)) +
"," + Convert.ToDecimal(CommFun.IsNullCheck(drow[j]["TaxablePer"], CommFun.datatypes.vartypenumeric)) + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
//sSql = "Delete from dbo.FlatExtraItem Where FlatId=" + argFlatId + " AND FlatExtraItemId NOT IN(" + sFlatExtraItemId + ")";
//cmd = new SqlCommand(sSql, conn, tran);
//cmd.ExecuteNonQuery();
//cmd.Dispose();
//sSql = "Delete from dbo.FlatExtraItemQualifier Where FlatId=" + argFlatId + " AND FlatExtraItemId NOT IN(" + sFlatExtraItemId + ")";
//cmd = new SqlCommand(sSql, conn, tran);
//cmd.ExecuteNonQuery();
//cmd.Dispose();
sSql = "Select ISNULL(FlatNo,'') FlatNo from dbo.FlatDetails Where FlatId=" + argFlatId;
cmd = new SqlCommand(sSql, conn, tran);
SqlDataReader dreader = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(dreader);
dreader.Close();
cmd.Dispose();
string sFlatNo = "";
if (dt.Rows.Count > 0)
{
sFlatNo = CommFun.IsNullCheck(dt.Rows[0]["FlatNo"], CommFun.datatypes.vartypestring).ToString();
}
tran.Commit();
bInsert = true;
if (bInsert == true && sLog == "Y")
{
BsfGlobal.InsertLog(DateTime.Now, "Extra Item-Add", "N", "Extra Item", argFlatId, argCCId, 0, BsfGlobal.g_sCRMDBName,
sFlatNo, BsfGlobal.g_lUserId, dNetAmt, 0);
}
}
catch (Exception ex)
{
tran.Rollback();
bInsert = false;
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public static void UpdateBroker(int argBrokerId, int argVendorId, DataTable argdt)
{
SqlConnection conn; SqlTransaction tran; conn = BsfGlobal.OpenCRMDB();
tran = conn.BeginTransaction();
SqlCommand cmd;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
if (argBrokerId == 0)
{
sSql = String.Format("INSERT INTO dbo.BrokerDet(VendorId) VALUES({0}) SELECT SCOPE_IDENTITY()", argVendorId);
cmd = new SqlCommand(sSql, conn, tran);
argBrokerId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
//CommFun.InsertLog(DateTime.Now, "Broker Details-Add", "N", "Add Broker Details", BsfGlobal.g_lUserId, 0, 0, 0, BsfGlobal.g_sCRMDBName);
BsfGlobal.InsertLog(DateTime.Now, "Broker Details-Add", "N", "Add Broker Details", argBrokerId, 0, 0, BsfGlobal.g_sCRMDBName, "", BsfGlobal.g_lUserId);
}
else
{
sSql = String.Format("UPDATE dbo.BrokerDet SET VendorId={0}" +
" WHERE BrokerId={1}", argVendorId, argBrokerId);
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
//CommFun.InsertLog(DateTime.Now, "Broker Details-Edit", "E", "Edit Broker Details", BsfGlobal.g_lUserId, 0, 0, 0, BsfGlobal.g_sCRMDBName);
BsfGlobal.InsertLog(DateTime.Now, "Broker Details-Edit", "E", "Edit Broker Details", argBrokerId, 0, 0, BsfGlobal.g_sCRMDBName, "", BsfGlobal.g_lUserId);
}
sSql = String.Format("DELETE FROM dbo.BrokerCC WHERE BrokerId={0}", argBrokerId);
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
DataView dview = new DataView(argdt)
{
RowFilter = "Sel='" + true + "'"
};
DataTable dtFilter = new DataTable();
dtFilter = dview.ToTable();
if (dtFilter != null)
{
for (int i = 0; i < dtFilter.Rows.Count; i++)
{
string sCommType = CommFun.IsNullCheck(dtFilter.Rows[i]["CommType"], CommFun.datatypes.vartypestring).ToString();
if (sCommType == "Percentage")
{
sCommType = "P";
}
else if (sCommType == "Amount")
{
sCommType = "A";
}
else
{
sCommType = "N";
}
string sPerBased = CommFun.IsNullCheck(dtFilter.Rows[i]["PerBased"], CommFun.datatypes.vartypestring).ToString();
if (sPerBased == "NetAmount")
{
sPerBased = "N";
}
else if (sPerBased == "BaseAmount")
{
sPerBased = "B";
}
else
{
sPerBased = "N";
}
sSql = "INSERT INTO dbo.BrokerCC(BrokerId,CostCentreId,CommPer,Amount,CommType,PerBased) VALUES" +
" (" + argBrokerId + "," + dtFilter.Rows[i]["CostCentreId"] + "," + dtFilter.Rows[i]["CommPer"] + "" +
"," + dtFilter.Rows[i]["Amount"] + ",'" + sCommType + "','" + sPerBased + "')";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
}
public static bool InsertMainDetails(DataTable dt, DataTable argQTrans)
{
int iMainId = 0, iMainTransId = 0;
bool bUpdate;
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
conn = BsfGlobal.OpenCRMDB();
using (SqlTransaction tran = conn.BeginTransaction())
{
string sSql = "";
try
{
sSql = "INSERT INTO dbo.MaintenanceDet(RefNo,CostCentreId,FlatId,RegDate,StartDate,EndDate,Duration,IntRate,IntDuration," +
" Terms,GracePeriod,Approve) Values( '" + MaintenanceBO.RefNo + "'," + MaintenanceBO.CostCentreId + "," + MaintenanceBO.FlatId + ",'" + MaintenanceBO.RegDate + "','" + MaintenanceBO.StartDate + "'," +
" '" + MaintenanceBO.EndDate + "','" + MaintenanceBO.Duration + "'," + MaintenanceBO.IntRate + ",'" + MaintenanceBO.IntDuration + "', " +
" '" + MaintenanceBO.Terms + "', " + MaintenanceBO.GracePeriod + ", '" + MaintenanceBO.Approve + "') SELECT SCOPE_IDENTITY()";
cmd = new SqlCommand(sSql, conn, tran);
iMainId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
if (dt.Rows.Count > 0)
{
for (int k = 0; k < dt.Rows.Count; k++)
{
int iRowId = Convert.ToInt32(CommFun.IsNullCheck(dt.Rows[k]["RowId"], CommFun.datatypes.vartypenumeric));
sSql = String.Format("INSERT INTO dbo.MaintenanceSchTrans(MaintenanceId,FromDate,ToDate,Amount,NetAmount) Values({0},'{1:dd-MMM-yyyy}','{2:dd-MMM-yyyy}',{3},{4})SELECT SCOPE_IDENTITY()", iMainId, Convert.ToDateTime(dt.Rows[k]["FromDate"]), Convert.ToDateTime(dt.Rows[k]["ToDate"]), dt.Rows[k]["Amount"], dt.Rows[k]["NetAmount"]);
cmd = new SqlCommand(sSql, conn, tran);
iMainTransId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
DataView dv = new DataView(argQTrans);
dv.RowFilter = "RowId=" + iRowId + "";
DataTable dtQ = new DataTable();
dtQ = dv.ToTable();
for (int i = 0; i < dtQ.Rows.Count; i++)
{
sSql = "Insert Into dbo.MaintenanceQualifier (MainTransId,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount,ExpValue,ExpPerValue,SurValue,EDValue,NetPer,HEDPer,TaxablePer,TaxableValue) " +
"Values (" + iMainTransId + "," + dtQ.Rows[i]["QualifierId"].ToString() + ",'" + dtQ.Rows[i]["Expression"].ToString() + "'," + dtQ.Rows[i]["ExpPer"].ToString() + "," +
" '" + dtQ.Rows[i]["Add_Less_Flag"].ToString() + "'," + dtQ.Rows[i]["SurCharge"].ToString() + "," +
" " + dtQ.Rows[i]["EDCess"].ToString() + "," + dtQ.Rows[i]["Amount"].ToString() + "," + dtQ.Rows[i]["ExpValue"].ToString() + "," +
" " + dtQ.Rows[i]["ExpPerValue"].ToString() + "," + dtQ.Rows[i]["SurValue"].ToString() + "," + dtQ.Rows[i]["EDValue"].ToString() + "," +
" " + dtQ.Rows[i]["NetPer"].ToString() + "," + dtQ.Rows[i]["HEDPer"].ToString() + "," + dtQ.Rows[i]["TaxablePer"].ToString() + "," +
" " + dtQ.Rows[i]["TaxableValue"].ToString() + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
////Qualifier
//for (int u = 0; u < argQTrans.Rows.Count; u++)
//{
// sSql = "Insert Into dbo.MaintenanceQualifier (MainTransId,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount,ExpValue,ExpPerValue,SurValue,EDValue,NetPer,HEDPer,TaxablePer,TaxableValue) " +
// "Values (" + iMainId + "," + argQTrans.Rows[u]["QualifierId"].ToString() + ",'" + argQTrans.Rows[u]["Expression"].ToString() + "'," + argQTrans.Rows[u]["ExpPer"].ToString() + "," +
// " '" + argQTrans.Rows[u]["Add_Less_Flag"].ToString() + "'," + argQTrans.Rows[u]["SurCharge"].ToString() + "," +
// " " + argQTrans.Rows[u]["EDCess"].ToString() + "," + argQTrans.Rows[u]["Amount"].ToString() + "," + argQTrans.Rows[u]["ExpValue"].ToString() + "," +
// " " + argQTrans.Rows[u]["ExpPerValue"].ToString() + "," + argQTrans.Rows[u]["SurValue"].ToString() + "," + argQTrans.Rows[u]["EDValue"].ToString() + "," +
// " " + argQTrans.Rows[u]["NetPer"].ToString() + "," + argQTrans.Rows[u]["HEDPer"].ToString() + "," + argQTrans.Rows[u]["TaxablePer"].ToString() + "," +
// " " + argQTrans.Rows[u]["TaxableValue"].ToString() + ")";
// cmd = new SqlCommand(sSql, conn, tran);
// cmd.ExecuteNonQuery();
//}
tran.Commit();
bUpdate = true;
BsfGlobal.InsertLog(DateTime.Now, "CRM-Maintenance-Bill-Add", "N", "CRM-Maintenance-Bill", iMainId, MaintenanceBO.CostCentreId, 0,
BsfGlobal.g_sCRMDBName, MaintenanceBO.RefNo, BsfGlobal.g_lUserId);
//BsfGlobal.InsertLog(DateTime.Now, "Rent-Add", "N", "Rent", iFTypeId, RentDetBO.CostCentreId, 0, BsfGlobal.g_sCRMDBName, RentDetBO.RefNo, BsfGlobal.g_lUserId);
}
catch (Exception ex)
{
tran.Rollback();
bUpdate = false;
System.Windows.Forms.MessageBox.Show(ex.Message, "PMS", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
return(bUpdate);
}
public static DataTable GetPlotTrans(int argFlatId, string argType)
{
SqlDataAdapter sda;
string sSql = "";
DataTable dt = new DataTable();
dt.Columns.Add("Step", typeof(Int32));
dt.Columns.Add("Description", typeof(string));
dt.Columns.Add("Status", typeof(Int32));
DataTable dtT = new DataTable();
dtT.Columns.Add("Step", typeof(Int32));
dtT.Columns.Add("CheckListId", typeof(Int32));
dtT.Columns.Add("Description", typeof(string));
dtT.Columns.Add("Status", typeof(Int32));
try
{
BsfGlobal.OpenCRMDB();
sSql = "Select A.CheckListId From [" + BsfGlobal.g_sRateAnalDBName + "].dbo.PlotDetailsCheckList A" +
" Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.CheckListMaster B On A.CheckListId=B.CheckListId" +
" Where A.PlotDetailsId=" + argFlatId + " And B.Type='" + argType + "' Order by B.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
DataTable dtS = new DataTable();
sda.Fill(dtS);
sda.Dispose();
sSql = "Select ROW_NUMBER() OVER (ORDER BY B.SortOrder) As Step,A.CheckListId, B.CheckListName Description" +
" From [" + BsfGlobal.g_sRateAnalDBName + "].dbo.PlotTypeCheckList A Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.CheckListMaster B On A.CheckListId=B.CheckListId " +
" Left Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.PlotDetailsCheckList C On A.CheckListId=C.CheckListId And C.PlotDetailsId=" + argFlatId + " Where A.PlotTypeId " +
" IN (Select PlotTypeId From [" + BsfGlobal.g_sRateAnalDBName + "].dbo.LandPlotDetails Where PlotDetailsId=" + argFlatId + ") and B.Type='" + argType + "' Order by B.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
DataTable dtSt = new DataTable();
sda.Fill(dtSt);
sda.Dispose();
if (dtSt.Rows.Count > 0)
{
for (int i = 0; i < dtSt.Rows.Count; i++)
{
DataRow dr;
dr = dtT.NewRow();
dr["Step"] = dtSt.Rows[i]["Step"];
dr["CheckListId"] = dtSt.Rows[i]["CheckListId"];
dr["Description"] = dtSt.Rows[i]["Description"];
dr["Status"] = DBNull.Value;
dtT.Rows.Add(dr);
}
}
if (dtS.Rows.Count > 0)
{
for (int i = 0; i < dtS.Rows.Count; i++)
{
if (dtT.Rows.Count > 0)
{
for (int j = 0; j < dtT.Rows.Count; j++)
{
if (Convert.ToInt32(dtT.Rows[j]["CheckListId"]) == Convert.ToInt32(dtS.Rows[i]["CheckListId"]))
{
dtT.Rows[j]["Status"] = dtT.Rows[j]["Step"];
}
//else dtT.Rows[j]["Status"] = DBNull.Value;
}
}
//else
//{
// dtT.Rows[i]["Status"] = DBNull.Value;
//}
}
}
sSql = " Select A.CheckListId,A.ExpCompletionDate,C.CompletionDate From [" + BsfGlobal.g_sRateAnalDBName + "].dbo.PlotTypeCheckList A " +
" Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.CheckListMaster B On A.CheckListId=B.CheckListId " +
" Left Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.PlotDetailsCheckList C On A.CheckListId=C.CheckListId And C.PlotDetailsId=" + argFlatId + " Where A.PlotTypeId " +
" IN (Select PlotTypeId From [" + BsfGlobal.g_sRateAnalDBName + "].dbo.LandPlotDetails Where PlotDetailsId=" + argFlatId + ") and B.Type='" + argType + "' Order by B.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
DataTable dtC = new DataTable();
sda.Fill(dtC);
sda.Dispose();
DataView dv;
DataRow dRow;
int iCheckListId = 0;
int iStatus = 0;
string sDes = "";
int iStep = 0;
for (int i = 0; i < dtT.Rows.Count; i++)
{
iCheckListId = Convert.ToInt32(dtT.Rows[i]["CheckListId"]);
iStep = Convert.ToInt32(dtT.Rows[i]["Step"]);
iStatus = Convert.ToInt32(CommFun.IsNullCheck(dtT.Rows[i]["Status"], CommFun.datatypes.vartypenumeric));
sDes = dtT.Rows[i]["Description"].ToString();
dv = new DataView(dtC);
dv.RowFilter = "CheckListId = " + iCheckListId;
if (dv.ToTable().Rows.Count > 0)
{
if (iStatus == 0)
{
sDes = sDes + " (" + Convert.ToDateTime(CommFun.IsNullCheck(dv.ToTable().Rows[0]["ExpCompletionDate"], CommFun.datatypes.VarTypeDate)).ToString("dd-MM-yyyy") + ")";
}
else
{
sDes = sDes + " (" + Convert.ToDateTime(CommFun.IsNullCheck(dv.ToTable().Rows[0]["CompletionDate"], CommFun.datatypes.VarTypeDate)).ToString("dd-MM-yyyy") + ")";
}
}
dv.Dispose();
dRow = dt.NewRow();
dRow["Step"] = iStep;
dRow["Description"] = sDes;
dRow["Status"] = dtT.Rows[i]["Status"];
dt.Rows.Add(dRow);
}
dtT.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
internal static void UpdateProjInfo(ProjectInfoBO argObj)
{
BsfGlobal.OpenCRMDB();
string ssql = "";
SqlCommand cmd;
int i = 0;
try
{
ssql = "Update ProjectInfo Set TotalFlats= " + argObj.i_TotalFlats + ",TotalBlocks = " + argObj.i_TotalBlocks + ", " +
"TotalArea = '" + argObj.s_TotalArea + "',FSIIndex = " + argObj.d_FSIIndex + ",Rate = " + argObj.d_Rate + "," +
"NoOfFloors = " + argObj.i_NoOfFloors + ",GuideLineValue = " + argObj.d_GuideLineValue + "," +
"LandCost=" + argObj.d_LandRate + ", LandArea=" + argObj.d_LandArea + ",BuildArea=" + argObj.d_BuildArea + ", " +
" NetLandArea=" + argObj.d_NetLandArea + ",WithHeld=" + argObj.d_WithHeld + ", " +
" LCBasedon='" + argObj.b_LCB + "',ProjectwiseUDS='" + argObj.b_UDS + "', " +
" StartDate=@StartDate," +
" EndDate=@EndDate," +
"Registration = " + argObj.d_RegValue + ", InterestBasedOn='" + argObj.c_InterestBasedOn + "' " +
"Where CostCentreId = " + argObj.i_CostCentreId;
cmd = new SqlCommand(ssql, BsfGlobal.g_CRMDB);
SqlParameter parameterDate = new SqlParameter()
{
DbType = DbType.DateTime, ParameterName = "@StartDate"
};
if (Convert.ToDateTime(CommFun.IsNullCheck(argObj.dt_StartDate, CommFun.datatypes.VarTypeDate)) == DateTime.MinValue)
{
parameterDate.Value = System.Data.SqlTypes.SqlDateTime.Null;
}
else
{
parameterDate.Value = argObj.dt_StartDate;
}
cmd.Parameters.Add(parameterDate);
parameterDate = new SqlParameter()
{
DbType = DbType.DateTime, ParameterName = "@EndDate"
};
if (Convert.ToDateTime(CommFun.IsNullCheck(argObj.dt_EndDate, CommFun.datatypes.VarTypeDate)) == DateTime.MinValue)
{
parameterDate.Value = System.Data.SqlTypes.SqlDateTime.Null;
}
else
{
parameterDate.Value = argObj.dt_EndDate;
}
cmd.Parameters.Add(parameterDate);
i = cmd.ExecuteNonQuery();
cmd.Dispose();
if (i == 0)
{
ssql = "Insert into ProjectInfo(CostCentreId,TotalFlats,TotalBlocks,TotalArea,FSIIndex,Rate,NoOfFloors,GuideLineValue,LandCost,LandArea," +
" LCBasedon,ProjectwiseUDS,BuildArea,NetLandArea,WithHeld,StartDate,EndDate,Registration,CancelPenalty,InterestBasedOn) " +
"Values( " + argObj.i_CostCentreId + "," + argObj.i_TotalFlats + "," + argObj.i_TotalBlocks + ",'" + argObj.s_TotalArea + "'," +
"" + argObj.d_FSIIndex + "," + argObj.d_Rate + "," + argObj.i_NoOfFloors + "," + argObj.d_GuideLineValue + "," +
"" + argObj.d_LandRate + "," + argObj.d_LandArea + ",'" + argObj.b_LCB + "','" + argObj.b_UDS + "'," + argObj.d_BuildArea + ", " +
" " + argObj.d_NetLandArea + "," + argObj.d_WithHeld + ",@StartDate,@EndDate, " +
"" + argObj.d_RegValue + "," + argObj.d_CancelPenalty + ", '" + argObj.c_InterestBasedOn + "')";
cmd = new SqlCommand(ssql, BsfGlobal.g_CRMDB);
parameterDate = new SqlParameter()
{
DbType = DbType.DateTime, ParameterName = "@StartDate"
};
if (Convert.ToDateTime(CommFun.IsNullCheck(argObj.dt_StartDate, CommFun.datatypes.VarTypeDate)) == DateTime.MinValue)
{
parameterDate.Value = System.Data.SqlTypes.SqlDateTime.Null;
}
else
{
parameterDate.Value = argObj.dt_StartDate;
}
cmd.Parameters.Add(parameterDate);
parameterDate = new SqlParameter()
{
DbType = DbType.DateTime, ParameterName = "@EndDate"
};
if (Convert.ToDateTime(CommFun.IsNullCheck(argObj.dt_EndDate, CommFun.datatypes.VarTypeDate)) == DateTime.MinValue)
{
parameterDate.Value = System.Data.SqlTypes.SqlDateTime.Null;
}
else
{
parameterDate.Value = argObj.dt_EndDate;
}
cmd.Parameters.Add(parameterDate);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
}
//public DataTable GetTemplateList()
//{
// DataTable dt;
// SqlDataAdapter sda;
// string sql = "";
// BsfGlobal.OpenCRMDB();
// try
// {
// sql = "Select TemplateId,TemplateName,Case When TempDoc is Null Then Convert(bit,1,1) Else Convert(bit,0,0) End FileFound from Template Order by TemplateName";
// sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
// dt = new DataTable();
// sda.Fill(dt);
// sda.Dispose();
// BsfGlobal.g_CRMDB.Close();
// }
// catch (SqlException ee)
// {
// throw new Exception(ee.Message);
// }
// return dt;
//}
public DataTable GetFlatDetails(int argFlatId)
{
DataTable dt;
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
try
{
sql = "SELECT LD.LeadName BuyerName,P1.FatherName,CASE When P1.DOB='01 Jan 1900' Then 0 Else DATEDIFF(Year,P1.DOB,GetDate()) END as Age, " +
" B.BlockName,L.LevelName,LC.Address1, LC.Address2,LC.City,LC.State,CM.CountryName,LC.PinCode,LC.PanNo, " +
" ISNULL(F.FlatNo,'')FlatNo,ISNULL(F.USLand,0)USLand,ISNULL(F.Area,0)Area,ISNULL(E.FinaliseDate,'') FinalizationDate,GETDATE() SystemDate, " +
" O.CostCentreName,WC.Address1 CCAddress1,WC.Address2 CCAddress2,CCM.CityName CCCity,WC.Pincode CCPincode, " +
" WM.CompanyName,WM.Address1 CompanyAddress,WM.Phone CompanyPhone, " +
" CA.CoApplicantName,BM.BankName,CASE When CA.DOB='01 Jan 1900' Then 0 Else DATEDIFF(Year,CA.DOB,GetDate()) END as CoApplicantAge," +
" P2.CoAddress1 CoApplicantAddress1, P2.CoAddress2 CoApplicantAddress2, P2.CoPanNo CoApplicantPANNo, " +
" BD.Branch BankBranch,BD.Address1 BankAddress,BD.ContPerson BankContactPerson," +
" E.LoanAccNo BankLoanAccNo,LD.Mobile MobileNo,LD.Email, ISNULL(FT.TypeName,'') FlatType, " +
" ISNULL(F.USLand,0) UDS, [UDS(in words)]=dbo.NumberToWords(ISNULL(F.USLand,0)), " +
" ISNULL(F.Rate,0) Rate, [Rate(in words)]=dbo.NumberToWords(ISNULL(F.Rate,0)), " +
" ISNULL(F.LandRate,0) LandCost, [LandCost(in words)]=dbo.NumberToWords(ISNULL(F.LandRate,0)), " +
" ISNULL(F.AdvAmount,0) Advance, [Advance(in words)]=dbo.NumberToWords(ISNULL(F.AdvAmount,0)), " +
" (ISNULL(F.LandRate,0)-ISNULL(F.AdvAmount,0)) SaleAgreementLandValue, " +
" [SaleAgreementLandValue(in words)]=dbo.NumberToWords((ISNULL(F.LandRate,0)-ISNULL(F.AdvAmount,0))), " +
" ISNULL(F.OtherCostAmt, 0) OtherCostAmt, " +
" RegistrationValue=ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=1 And B.FlatId=" + argFlatId + "),0), " +
" [RegistrationValue(in words)]=dbo.NumberToWords(ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=1 And B.FlatId=" + argFlatId + "),0)), " +
" LegalCharges=ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=7 And B.FlatId=" + argFlatId + "),0), " +
" [LegalCharges(in words)]=dbo.NumberToWords(ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=7 And B.FlatId=" + argFlatId + "),0)), " +
" CorpusFund=ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=8 And B.FlatId=" + argFlatId + "),0), " +
" [CorpusFund(in words)]=dbo.NumberToWords(ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=8 And B.FlatId=" + argFlatId + "),0)), " +
" MaintenanceCharge=ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=3 And B.FlatId=" + argFlatId + "),0), " +
" [MaintenanceCharge(in words)]=dbo.NumberToWords(ISNULL((Select B.Amount From dbo.OtherCostMaster A Inner Join dbo.FlatOtherCost B On A.OtherCostId=B.OtherCostId Where A.OCTypeId=3 And B.FlatId=" + argFlatId + "),0)), " +
" ISNULL(F.BaseAmt,0) BasicCost,[BasicCost(in words)]=dbo.NumberToWords(ISNULL(F.BaseAmt,0)), " +
" ISNULL(F.NetAmt,0) FlatCost, [FlatCost(in words)]=dbo.NumberToWords(ISNULL(F.NetAmt,0)), " +
" ISNULL(F.NetAmt,0) Amount, [Amount(in words)]=dbo.NumberToWords(ISNULL(F.NetAmt,0)), " +
" (ISNULL(F.NetAmt,0)+ISNULL(F.QualifierAmt,0)) NetAmount, [NetAmount(in words)]=dbo.NumberToWords(ISNULL(F.NetAmt,0)+ISNULL(F.QualifierAmt,0)), " +
" PaidAmount=(Select Sum(ISNULL(PaidAmount,0)) From dbo.PaymentScheduleFlat Where FlatId=" + argFlatId + "), " +
" [PaidAmount(in words)]=dbo.NumberToWords((Select Sum(ISNULL(PaidAmount,0)) From dbo.PaymentScheduleFlat Where FlatId=" + argFlatId + ")), " +
" BalanceAmount=(Select Sum(ISNULL(NetAmount,0)-ISNULL(PaidAmount,0))BalanceAmount From dbo.PaymentScheduleFlat Where FlatId=" + argFlatId + "), " +
" [BalanceAmount(in words)]=dbo.NumberToWords((Select Sum(ISNULL(NetAmount,0)-ISNULL(PaidAmount,0))BalanceAmount From dbo.PaymentScheduleFlat Where FlatId=" + argFlatId + ")), " +
" ISNULL(P4.TypeName,'') CarParkType, ISNULL(F.QualifierAmt,0) QualifierAmount FROM FlatDetails F " +
" INNER JOIN FlatType FT ON F.FlatTypeId=FT.FlatTypeId " +
" LEFT JOIN LevelMaster L ON F.LevelId=L.LevelId " +
" LEFT JOIN BlockMaster B ON F.BlockId=B.BlockId " +
" LEFT JOIN BuyerDetail E ON E.FlatId=F.FlatId AND E.LeadId=F.LeadId " +
" LEFT JOIN LeadRegister LD ON E.LeadId=LD.LeadId " +
" LEFT JOIN LeadCommAddressInfo LC ON LD.LeadId=LC.LeadId " +
" LEFT JOIN Countrymaster CM ON LC.Country=CM.CountryId " +
" LEFT JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre O On O.CostCentreId=F.CostCentreId" +
" LEFT JOIN LeadCoApplicantInfo CA On LD.LeadId=CA.LeadId" +
" LEFT JOIN BankDetails BD On BD.BranchId=E.BranchId" +
" LEFT JOIN BankMaster BM On BD.BankId=BM.BankId" +
" LEFT JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.CostCentre WC On WC.CostCentreId=B.CostCentreId" +
" LEFT JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.CompanyMaster WM On WM.CompanyId=WC.CompanyId" +
" LEFT JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.CityMaster CCM ON CCM.CityId=WC.CityId " +
" LEFT JOIN dbo.LeadPersonalInfo P1 ON P1.LeadId=LD.LeadId " +
" LEFT JOIN dbo.LeadCoAppAddressInfo P2 ON P2.LeadId=LD.LeadId " +
" LEFT JOIN dbo.FlatCarPark P3 ON F.FlatId=P3.FlatId AND P3.TotalCP<>0 " +
" LEFT JOIN dbo.CarParkTypeMaster P4 ON P3.TypeId=P4.TypeId " +
" INNER JOIN dbo.ProjectInfo P5 ON F.CostCentreId=P5.CostCentreId " +
" WHERE F.FlatId=" + argFlatId;
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
dt.Columns.Add("ChequeNo", typeof(string));
dt.Columns.Add("ReceiptDate", typeof(string));
sql = "Select A.ChequeNo,A.ReceiptDate From ReceiptRegister A Inner Join ReceiptTrans B On A.ReceiptId=B.ReceiptId " +
" Where B.FlatId=" + argFlatId + " Group By A.ChequeNo,A.ReceiptDate";
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
DataTable dtR = new DataTable();
sda.Fill(dtR);
sda.Dispose();
string sChequeNo = "", sReceiptDate = "";
for (int i = 0; i < dtR.Rows.Count; i++)
{
string sNo = dtR.Rows[i]["ChequeNo"].ToString();
DateTime dChequeDate = Convert.ToDateTime(CommFun.IsNullCheck(dtR.Rows[i]["ReceiptDate"], CommFun.datatypes.VarTypeDate));
string sDate = "";
if (dChequeDate == DateTime.MinValue)
{
sDate = "";
}
else
{
sDate = string.Format("{0:dd-MMM-yyyy}", dChequeDate);
}
sChequeNo = sChequeNo + sNo + ",";
sReceiptDate = sReceiptDate + sDate + ",";
}
sChequeNo = sChequeNo.TrimEnd(',');
sReceiptDate = sReceiptDate.TrimEnd(',');
if (dt.Rows.Count > 0)
{
dt.Rows[0]["ChequeNo"] = sChequeNo.ToString();
dt.Rows[0]["ReceiptDate"] = sReceiptDate.ToString();
}
BsfGlobal.g_CRMDB.Close();
}
catch (SqlException ee)
{
throw new Exception(ee.Message);
}
return(dt);
}
public int InsertExtraBill(ExtraBillRegBO argBRegBo, DataTable argBTrans, DataTable argQTrans, int argFlatId)
{
int iBillRegId = 0;
SqlConnection conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string frmdat = string.Format("{0:dd MMM yyyy}", argBRegBo.BillDate);
string Sql = "DELETE FROM dbo.ExtraBillRegister WHERE FlatId=" + argFlatId;
SqlCommand cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "DELETE FROM dbo.ExtraBillTrans WHERE BillRegId=" + argBRegBo.BillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "DELETE FROM dbo.ExtraBillRateQ WHERE BillRegId=" + argBRegBo.BillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
Sql = "DELETE FROM dbo.ExtraBillRateQAbs WHERE BillRegId=" + argBRegBo.BillRegId;
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
//Bill Register
Sql = "Insert Into dbo.ExtraBillRegister(BillDate,BillNo,CostCentreId,FlatId,BillAmount,NetAmount,Narration) " +
"Values ('" + frmdat + "','" + argBRegBo.RefNo + "'," + argBRegBo.CCId + "," + argBRegBo.FlatId + "," + argBRegBo.BillAmt +
"," + argBRegBo.NetAmt + ",'" + argBRegBo.Narration + "') Select SCOPE_IDENTITY();";
cmd = new SqlCommand(Sql, conn, tran);
iBillRegId = Convert.ToInt32(CommFun.IsNullCheck(cmd.ExecuteScalar(), CommFun.datatypes.vartypenumeric));
cmd.Dispose();
//BillTrans
for (int u = 0; u < argBTrans.Rows.Count; u++)
{
Sql = "Insert Into dbo.ExtraBillTrans(BillRegId,ExtraItemId,Qty,Rate,Amount,NetAmount) " +
"Values (" + iBillRegId + "," + argBTrans.Rows[u]["EItemId"].ToString() + "," + argBTrans.Rows[u]["Qty"].ToString() +
"," + argBTrans.Rows[u]["Rate"].ToString() + "," + argBTrans.Rows[u]["Amt"].ToString() + "," + argBTrans.Rows[u]["NetAmt"].ToString() + ")";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
//Bill Qualifier
decimal d_TotalTaxAmt = 0;
for (int u = 0; u < argQTrans.Rows.Count; u++)
{
d_TotalTaxAmt = d_TotalTaxAmt + Convert.ToInt32(argQTrans.Rows[u]["Amount"]);
Sql = "Insert Into dbo.ExtraBillRateQ(BillRegId,QualifierId,Expression,ExpPer,Add_Less_Flag,SurCharge,EDCess,Amount, " +
" ExpValue,ExpPerValue,SurValue,EdValue,FlatId,HEDPer,NetPer,TaxablePer,TaxableValue) Values(" + iBillRegId +
"," + argQTrans.Rows[u]["QualifierId"].ToString() + ",'" + argQTrans.Rows[u]["Expression"].ToString() +
"'," + argQTrans.Rows[u]["ExpPer"].ToString() + ",'" + argQTrans.Rows[u]["Add_Less_Flag"].ToString() +
"'," + argQTrans.Rows[u]["SurCharge"].ToString() + "," + argQTrans.Rows[u]["EDCess"].ToString() + "," + argQTrans.Rows[u]["Amount"].ToString() +
"," + argQTrans.Rows[u]["ExpValue"].ToString() + "," + argQTrans.Rows[u]["ExpPerValue"].ToString() +
"," + argQTrans.Rows[u]["SurValue"].ToString() + "," + argQTrans.Rows[u]["EdValue"].ToString() +
"," + argBRegBo.FlatId + "," + argQTrans.Rows[u]["HEDPer"].ToString() + "," + argQTrans.Rows[u]["NetPer"].ToString() +
"," + argQTrans.Rows[u]["TaxablePer"].ToString() + "," + argQTrans.Rows[u]["TaxableValue"].ToString() + ")";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
if (argQTrans.Rows.Count > 0)
{
Sql = "Insert Into dbo.ExtraBillRateQAbs(BillRegId,QualifierId,Amount,Add_Less_Flag) " +
"Values (" + iBillRegId + "," + argQTrans.Rows[0]["QualifierId"].ToString() + "," + d_TotalTaxAmt +
",'" + argQTrans.Rows[0]["Add_Less_Flag"].ToString() + "')";
cmd = new SqlCommand(Sql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
tran.Rollback();
}
finally
{
conn.Dispose();
conn.Close();
}
return(iBillRegId);
}
public static DataTable GetFlatVDetails(int argCCId, int argFlatId, string argType, int argLandId)
{
DataTable dt = new DataTable();
try
{
BsfGlobal.OpenCRMDB(); decimal dQualAmt = 0;
string sSql = "";
if (argType == "L")
{
sSql = "Select Sum(Case When Add_Less_Flag='-' Then A.Amount*-1 Else A.Amount End) Amount" +
" from [" + BsfGlobal.g_sRateAnalDBName + "].dbo.PlotReceiptQualifier A Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.PlotReceiptType B On A.SchId=B.SchId" +
" Where B.PlotDetailsId=" + argFlatId + "";
SqlCommand cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dtQ = new DataTable();
dtQ.Load(dr);
if (dtQ.Rows.Count > 0)
{
dQualAmt = Convert.ToDecimal(CommFun.IsNullCheck(dtQ.Rows[0]["Amount"], CommFun.datatypes.vartypenumeric));
}
sSql = "Select A.PlotNo,B.PlotTypeName,F.LeadName BuyerName,A.SurveyNo,A.PattaNo,A.PattaName,A.Area,A.Rate,A.BaseAmount," +
" A.AdvanceAmount,A.GuideLine,A.OtherCost,(" + dQualAmt + "+A.BaseAmount+A.OtherCost) NetAmount," +
" (Case A.Status When 'S' Then 'Sold' When 'B' Then 'Block' When 'R' Then 'Reserve' " +
" When 'U' Then 'UnSold' End) Status,North,South,East,West from [" + BsfGlobal.g_sRateAnalDBName + "].dbo.LandPlotDetails A " +
" Left Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.LandPlotType B on A.PlotTypeId=B.PlotTypeId " +
" Left Join dbo.BuyerDetail E On A.BuyerId=E.LeadId And A.PlotDetailsId=E.PlotId" +
" Left Join dbo.LeadRegister F On E.LeadId=F.LeadId " +
" Where A.LandRegisterId= " + argLandId + " And A.PlotDetailsId=" + argFlatId + "";
}
else
{
sSql = "Select A.FlatNo " + CommFun.m_sFuncName + "No,B.BlockName Block,C.LevelName Level,D.TypeName," +
" (Case A.Status When 'S' Then 'Sold' When 'B' Then 'Block' When 'R' Then 'Reserve' When 'U' Then 'UnSold' End) Status, " +
" G.Description Facing, " +
" F.LeadName BuyerName,Case E.CustomerType When 'I' Then 'Investor' When 'B' Then 'Buyer' Else ''" +
" End CustomerType,A.Area,A.Rate,A.BaseAmt,A.AdvAmount,A.USLand UDSLandArea,A.USLandAmt LandAmount, " +
" A.TotalCarPark CarPark,A.OtherCostAmt,A.NetAmt from dbo.FlatDetails A " +
" Left Join dbo.BlockMaster B on A.BlockId=B.BlockId " +
" Left Join dbo.LevelMaster C on A.LevelId=C.LevelId " +
" Left Join dbo.FlatType D on A.FlatTypeId=D.FlatTypeId " +
" Left Join dbo.BuyerDetail E On A.LeadId=E.LeadId " +
" Left Join dbo.LeadRegister F On E.LeadId=F.LeadId " +
" Left Join dbo.Facing G On A.FacingId=G.FacingId And G.CostCentreId= " + argCCId + "" +
" Where A.CostCentreId= " + argCCId + " And A.FlatId=" + argFlatId + " ";
}
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_CRMDB.Close();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
