internal static DataTable SelectAmenities(int argCCID, string argType)
{
BsfGlobal.OpenCRMDB();
string ssql = "";
DataTable dtAme = null;
SqlDataAdapter sda;
try
{
if (argType == "P")
{
ssql = String.Format("Select A.AmenityId, A.AmenityName, Case When B.AmenityId IS NULL then" +
" Convert(bit, 0, 0) else CONVERT(bit, 1, 1) End as Sel from AmenityMaster A" +
" Left Join CCAmentityTrans B ON A.AmenityId=B.AmenityId AND B.CostCentreId={0}", argCCID);
}
else
{
ssql = String.Format("Select A.AmenityId, A.AmenityName, Case When B.AmenityId IS NULL then" +
" Convert(bit, 0, 0) else CONVERT(bit, 1, 1) End as Sel from AmenityMaster A" +
" Left Join CompetitorAmenityTrans B ON A.AmenityId=B.AmenityId AND B.ProjectId={0}", argCCID);
}
sda = new SqlDataAdapter(ssql, BsfGlobal.g_CRMDB);
dtAme = new DataTable();
sda.Fill(dtAme);
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dtAme);
}
public DataTable GetOpportunity()
{
DataTable dt = null;
try
{
BsfGlobal.OpenCRMDB();
string sSql = "Select RequestNo,RequestId,OpportunityName,C.CostCentreName,C.CostCentreId From dbo.OpportunityRequest O" +
" Inner Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C On O.CCId=C.CostCentreId" +
" Order By OpportunityName";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
dt.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public DataTable GetCampaignDetails()
{
DataTable dt = null;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
SqlCommand cmd;
sSql = "Update CampaignDetails Set NetAmount=TotAmount Where NetAmount=0";
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = "Select Distinct A.RequestId,A.CampaignId,A.CampaignName From dbo.CampaignDetails A" +
" Order By CampaignName";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
dt.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public DataTable GetPhotos(string argStr, int argCCID)
{
DataTable dt = new DataTable();
string sSql = "";
try
{
sSql = "Select ImageId,PhotoImage,ImageDate,Description from dbo.ProjectPhoto " +
"Where CostCentreId = " + argCCID;
BsfGlobal.OpenCRMDB();
if (argStr != "All")
{
sSql = sSql + " And CONVERT(CHAR(10),ImageDate,120) = '" + Convert.ToDateTime(argStr).ToString("yyyy-MM-dd") + "'";
}
sSql = sSql + " Order by ImageDate,ImageId";
SqlCommand cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
dr.Close();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public bool OppNameFound(int argReqId, string argoppName, int argCCId)
{
bool bans = false;
try
{
DataTable dt;
string sSql = "Select RequestId From dbo.OpportunityRequest Where RequestId <> " + argReqId + " " +
"and OpportunityName = '" + argoppName + "' And CCId=" + argCCId + "";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count > 0)
{
bans = true;
}
dt.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(bans);
}
public static DataTable PopulateProject(int Id)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
sSql = "SELECT CostCentreId,CostCentreName From [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre" +
" Where ProjectDB in(Select ProjectName From [" + BsfGlobal.g_sRateAnalDBName + "].dbo.ConceptionRegister" +
" Where BusinessType In('B','L')) and CostCentreId not in (Select CostCentreId " +
" From [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.UserCostCentreTrans Where " +
" UserId=" + Id + ") Order By CostCentreName";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public static DataTable Populate_SerOrderBillRegisterChange(int argRegId)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
sSql = "SELECT A.RegBillId,A.Billdate,A.BillRefNo,A.CostcentreID,B.CostCentreName,S.RefNo QuoteNo,C.FlatNo,E.LeadName BuyerName,A.NetAmt,A.Approve FROM SerOrderBillReg A " +
"Inner Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre B on A.CostCentreId=B.CostCentreId " +
"Inner Join FlatDetails C on A.FlatId=C.FlatId " +
"Left Join ServiceQuoteReg S on A.QuoteRegId=S.RegisterId " +
"Left Join LeadRegister E on S.LeadId=E.LeadId " +
"WHERE A.RegBillId=" + argRegId + "";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public DataTable GetCCTemplateView(string frmWhere, int argID, int argFlatId)
{
DataTable dt = null;
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
try
{
if (frmWhere == "P")
{
sql = "Select A.CostCentreId ID,B.TemplateName,A.TemplateDoc,A.Extension from CostCentreTemplate A" +
"Inner join Template on B A.TemplateId=B.TemplateId Where A.CostCentreId=" + argID + "";
}
else
{
sql = "Select A.FlatId ID,B.TemplateName,A.TemplateDoc,A.Extension from FlatTemplateUpload A " +
"Inner join Template on B A.TemplateId=B.TemplateId Where A.FlatId=" + argFlatId + "";
}
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
BsfGlobal.g_CRMDB.Close();
}
catch (SqlException ee)
{
BsfGlobal.CustomException(ee.Message, ee.StackTrace);
}
return(dt);
}
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 void InsertCCTempname(string frmWhere, int argTempID)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
string sSql = "";
try
{
if (frmWhere == "P")
{
sSql = "Insert into CostCentreTemplate(TemplateId) Values('" + argTempID + "')";
}
else
{
sSql = "Insert into FlatTemplateUpload(TemplateId) Values('" + argTempID + "')";
}
SqlCommand Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
conn.Close();
}
}
public void DeleteCCTempate(string frmWhere, int argTempId, int argCCId, int argFlatId)
{
BsfGlobal.OpenCRMDB();
string sSql = "";
try
{
if (frmWhere == "P")
{
sSql = "Delete from CostCentreTemplate Where TemplateId = " + argTempId + " and CostCentreId= " + argCCId;
}
else
{
sSql = "Delete from FlatTemplateUpload Where TemplateId = " + argTempId + " and FlatId = " + argFlatId;
}
SqlCommand Command = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
Command.ExecuteNonQuery();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
}
internal static DataTable GetOtherCost(int argCCId)
{
BsfGlobal.OpenCRMDB();
string sSql = "";
DataTable dtOC = null;
SqlDataAdapter sda;
try
{
//sSql = "Select A.OtherCostId,A.OtherCostName, Case When B.OtherCostId IS NULL then " +
// " Convert(bit, 0, 0) else CONVERT(bit, 1, 1) End as Sel from dbo.OtherCostMaster A " +
// " Left Join dbo.CCOtherCost B ON A.OtherCostId=B.OtherCostId AND B.CostCentreId=" + argCCId + "";
sSql = "Select SysDefault,A.OtherCostId,A.OtherCostName, " +
" Case When B.OtherCostId IS NOT NULL OR SysDefault=1 then Convert(bit, 1, 1) " +
" else CONVERT(bit, 0, 0) End as Sel from dbo.OtherCostMaster A " +
" Left Join dbo.CCOtherCost B ON A.OtherCostId=B.OtherCostId " +
" AND B.CostCentreId=" + argCCId + " Order By A.SortOrder";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dtOC = new DataTable();
sda.Fill(dtOC);
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dtOC);
}
internal static DataTable SelectCompetitor(ProjectInfoBO CompBO)
{
BsfGlobal.OpenCRMDB();
string ssql = "";
DataTable dtComp = null;
SqlDataAdapter sda;
try
{
ssql = " Select A.ProjectId,A.CompetitorId,C.CompetitorName,A.ProjectName,Case When B.ProjectId IS NULL " +
" then Convert(bit, 0, 0) else CONVERT(bit, 1, 1) End as Sel from dbo.CompetitiveProjects A " +
" LEFT JOIN dbo.CompetitorMaster C ON A.CompetitorId=C.CompetitorId " +
" Left Join dbo.CCCompetitorTrans B ON A.ProjectId=B.ProjectId AND B.CostCentreId=" + CompBO.i_CostCentreId + "";
sda = new SqlDataAdapter(ssql, BsfGlobal.g_CRMDB);
dtComp = new DataTable();
sda.Fill(dtComp);
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dtComp);
}
internal static string UnitUsed(int argCCId)
{
string sUnit = "";
try
{
BsfGlobal.OpenCRMDB();
string sSql = "Select Unit_Name From dbo.ProjectInfo A Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.UOM B On A.UnitId=B.Unit_ID" +
" Where CostCentreId=" + argCCId + "";
SqlDataAdapter sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
sUnit = dt.Rows[0]["Unit_Name"].ToString();
}
dt.Dispose();
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(sUnit);
}
public static DataTable Fill_TenantDetChange(int TenantId)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
sSql = "SELECT A.TenantId,A.TransDate,A.RefNo,C.CostCentreName,D.FlatNo,A.TenantName,B.CityName,A.Mobile,A.Email,A.Approve " +
" From TenantRegister A " +
" Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.CityMaster B on A.CityId=B.CityId " +
" Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C on A.CostCentreId=C.CostCentreId " +
" Left join FlatDetails D on A.FlatId=D.FlatId " +
"where A.TenantId=" + TenantId + "";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public DataTable GetFlatDocCreate(int argCCId, int argTempId)
{
DataTable dt = null;
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
try
{
sql = "Select A.FlatId,A.FlatNo,B.BlockName,'' Path,Convert(bit,0,0) Sel from FlatDetails A " +
"Left Join BlockMaster B on A.BlockId=B.BlockId " +
"Where A.Status='S' and A.CostCentreId=" + argCCId + " and " +
"A.FlatId not in (Select FlatId from FlatTemplateDocTrans Where TemplateId=" + argTempId + ")";
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
BsfGlobal.g_CRMDB.Close();
}
catch (SqlException ee)
{
BsfGlobal.CustomException(ee.Message, ee.StackTrace);
}
return(dt);
}
private void btnDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (grdDescView.FocusedRowHandle < 0)
{
return;
}
int iSchId = Convert.ToInt32(CommFun.IsNullCheck(grdDescView.GetFocusedRowCellValue("SchDescId"), CommFun.datatypes.vartypenumeric));
if (PaymentScheduleBL.CheckPaymentScheduleDesUsed(iSchId) == true)
{
MessageBox.Show("Schedule Description Used, Do not Delete");
return;
}
try
{
if (MessageBox.Show("Do you want to Delete Row?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
string sSql = "DELETE FROM dbo.SchDescription WHERE SchDescId=" + iSchId + " AND Type='" + m_cDescType + "'";
CommFun.CRMExecute(sSql);
grdDescView.DeleteRow(grdDescView.FocusedRowHandle);
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
}
public void InsertFlatDocTrans(DataTable argDtTrans, int argTempId)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = null;
string sSql = "";
try
{
//sSql = "Delete from FlatTemplateDocTrans where TemplateId=" + argTempId;
//cmd = new SqlCommand(sSql, conn, tran);
//cmd.ExecuteNonQuery();
for (int s = 0; s < argDtTrans.Rows.Count; s++)
{
sSql = "Insert into FlatTemplateDocTrans(FlatId,TemplateId,DocPath) Values(" + argDtTrans.Rows[s]["FlatId"].ToString() + "," + argTempId + ",'" + argDtTrans.Rows[s]["Path"].ToString() + "')";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
}
tran.Commit();
}
catch (SqlException ee)
{
BsfGlobal.CustomException(ee.Message, ee.StackTrace);
}
finally
{
conn.Close();
}
}
public static DataTable Populate_SerOrderBillRegister(DateTime frmDate, DateTime toDate)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
string frmdat = string.Format("{0:dd MMM yyyy}", frmDate);
string tdat = string.Format("{0:dd MMM yyyy}", toDate.AddDays(0));
sSql = "SELECT A.RegBillId,A.Billdate,A.BillRefNo,A.CostcentreID,B.CostCentreName,S.RefNo QuoteNo,C.FlatNo,E.LeadName BuyerName,A.NetAmt,A.Approve FROM SerOrderBillReg A " +
"Inner Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre B on A.CostCentreId=B.CostCentreId " +
"Inner Join FlatDetails C on A.FlatId=C.FlatId " +
"Left Join ServiceQuoteReg S on A.QuoteRegId=S.RegisterId " +
"Left Join LeadRegister E on S.LeadId=E.LeadId " +
"WHERE A.Billdate between '" + frmdat + "' And '" + tdat + "' Order by A.Billdate,A.BillRefNo";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
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 void UpdatePhoto(byte[] FileByteArray, System.IO.FileStream o, int argCCId, string argDesc)
{
SqlCommand cmd = default(SqlCommand);
BsfGlobal.OpenCRMDB();
try
{
string sSql = "";
if (FileByteArray != null)
{
sSql = "Insert into dbo.ProjectPhoto(CostCentreId,PhotoImage,Description) " +
"Values(" + argCCId + ",@Logo,'" + argDesc + "')";
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
cmd.Parameters.Add("@Logo", SqlDbType.Binary, Convert.ToInt32(o.Length)).Value = FileByteArray;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
}
public static DataTable GetTenant()
{
DataTable dtTen = null;
SqlDataAdapter sda;
// string sSql = "";
BsfGlobal.OpenCRMDB();
try
{
// sSql = "select TenantId,TenantName from TenantDet where TenantId <> 0 ";
//// sda = new SqlDataAdapter(sSql, BsfGlobal.OpenWorkFlowDB());
// sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
// dtTen = new DataTable();
// sda.Fill(dtTen);
// BsfGlobal.g_CRMDB.Close();
sda = new SqlDataAdapter("SELECT TenantId,TenantName from TenantRegister where TenantId <> 0", BsfGlobal.g_CRMDB);
dtTen = new DataTable();
sda.Fill(dtTen);
BsfGlobal.g_CRMDB.Close();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
return(dtTen);
}
public void UpdateData()
{
try
{
DataTable dtM = new DataTable();
DataTable dtA = new DataTable();
dtA = m_dt.GetChanges(DataRowState.Added);
dtM = m_dt.GetChanges(DataRowState.Modified);
if (dtA != null)
{
for (int i = 0; i < dtA.Rows.Count; i++)
{
ComplaintDetBL.InsertCompliantMaater(dtA.Rows[i]["NatureComplaint"].ToString());
//sql = String.Format("INSERT INTO Nature_Complaint (NatureComplaint) VALUES('{0}')", dtA.Rows[i]["NatureComplaint"]);
// CommFun.CRMExecute(sql);
}
}
if (dtM != null)
{
for (int i = 0; i < dtM.Rows.Count; i++)
{
ComplaintDetBL.UpdateCompliantMaater(dtM.Rows[i]["NatureComplaint"].ToString(), Convert.ToInt32(dtM.Rows[i]["ComplaintId"]));
//sql = String.Format("UPDATE Nature_Complaint SET NatureComplaint='{0}' WHERE ComplaintId={1} ", dtM.Rows[i]["NatureComplaint"], dtM.Rows[i]["ComplaintId"]);
// CommFun.CRMExecute(sql);
}
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
}
public static DataTable PopulateFlat(int ProId, int FlatId)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
sSql = "SELECT A.FlatId,A.FlatNo FROM FlatDetails A LEFT OUTER JOIN TenantRegister B on A.FlatId=B.FlatId where B.FlatId IS NULL and A.CostCentreId=" + ProId + " and A.BlockId=" + FlatId + " and A.Status in('S','R') ORDER BY A.FlatNo ";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public static void InsertCampaignAmount(DataTable argDt, int argCampId)
{
try
{
SqlCommand cmd;
string sSql = "";
if (argDt.Rows.Count > 0)
{
sSql = "Delete From dbo.CampaignAmount Where CampaignId=" + argCampId + "";
BsfGlobal.OpenCRMDB();
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
for (int i = 0; i < argDt.Rows.Count; i++)
{
sSql = "Insert Into dbo.CampaignAmount(CampaignId,Description,Flag,Amount)Values(" + argCampId + "," +
" '" + argDt.Rows[i]["Description"] + "','" + argDt.Rows[i]["Flag"] + "'," + argDt.Rows[i]["Amount"] + ")";
BsfGlobal.OpenCRMDB();
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
}
public static DataTable PopulateFlatSt(int ProId, int FlatId)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
sSql = "SELECT FlatId,FlatNo FROM FlatDetails WHERE CostCentreId=" + ProId + " and BlockId=" + FlatId + " and Status in('S','R') ORDER BY FlatNo";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public DataTable GetCampaign(int argCCId)
{
DataTable dt = null;
try
{
string sSql = "Select A.RequestId,A.CampaignId,A.CampaignName From dbo.CampaignDetails A" +
" Inner Join dbo.OpportunityRequest B On A.RequestId=B.RequestId Where B.CCId=" + argCCId + "" +
" Order By CampaignName ";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
dt.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public static DataTable Fill_Tenantregister(DateTime frmDate, DateTime toDate)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
string frmdat = string.Format("{0:dd MMM yyyy}", frmDate);
string tdat = string.Format("{0:dd MMM yyyy}", toDate.AddDays(0));
sSql = "SELECT A.TenantId,A.TransDate,A.RefNo,C.CostCentreName,D.FlatNo,A.TenantName,B.CityName,A.Mobile,A.Email,A.Approve " +
" From TenantRegister A " +
" Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.CityMaster B on A.CityId=B.CityId " +
" Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C on A.CostCentreId=C.CostCentreId " +
" Left join FlatDetails D on A.FlatId=D.FlatId " +
"Where A.TransDate between '" + frmdat + "' And '" + tdat + "' ORDER BY A.TransDate,A.RefNo";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public DataTable GetOPPReqDetails(int argReqId)
{
DataTable dt = null;
try
{
string sSql = "Select A.*,B.CostCentreName From dbo.OpportunityRequest A Inner Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre B" +
" On A.CCId=B.CostCentreId where RequestId=" + argReqId + " ";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
dt.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
internal static bool CheckAmenityUsed(int argAId)
{
bool bAns = false;
try
{
BsfGlobal.OpenCRMDB();
string sSql = "Select AmenityId from CCAmentityTrans Where AmenityId = " + argAId + " " +
"Union All " +
"Select AmenityId from CompetitorAmenityTrans Where AmenityId = " + argAId;
SqlDataAdapter sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
bAns = true;
}
dt.Dispose();
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(bAns);
}
