internal static DataTable PopulateLevelBlock(int i_BlockId, int argCCId)
{
string sSql = "";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = null;
DataTable dt = new DataTable();
try
{
if (i_BlockId != 0)
{
//sSql = string.Format("Select A.LevelName, A.LevelId, Case When B.LevelId IS NULL Then" +
// " Convert(bit,0,0) else Convert(bit,1,1) END as Sel from LevelMaster A LEFT JOIN BlockLevelTrans B" +
// " ON A.LevelId=B.LevelId AND B.BlockId={0} Where A.CostCentreId={1}", i_BlockId, argCCId);
sSql = string.Format("Select A.LevelName, A.LevelId From dbo.LevelMaster A LEFT JOIN dbo.BlockLevelTrans B" +
" ON A.LevelId=B.LevelId AND B.BlockId={0} Where A.CostCentreId={1}", i_BlockId, argCCId);
da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
da.Fill(dt);
da.Dispose();
}
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public static DataTable Populate_ComplaintRegisterChange(int argEntryId)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
sSql = "SELECT C.ComplaintId,C.TransDate,C.ComplaintNo,C.CostCentreId,C1.CostCentreName,F.FlatNo,N.NatureComplaint,E.EmployeeName AttendedBy," +
"C.AttDate DateAttented,C.Approve FROM Complaint_Entry C " +
"Left JOIN FlatDetails F ON C.FlatId=F.FlatId " +
"Left Join Nature_Complaint N on C.NatureId=N.ComplaintId " +
"Left JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C1 ON C.CostCentreId=C1.CostCentreId " +
"Left JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.Users E ON C.ExecutiveId=E.UserId " +
"Where C.ComplaintId=" + argEntryId + "";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public static void InsertCompliantMaater(string argName)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
conn = BsfGlobal.OpenCRMDB();
using (SqlTransaction tran = conn.BeginTransaction())
{
string sSql = "";
try
{
sSql = "INSERT INTO Nature_Complaint (NatureComplaint) VALUES ('" + argName + "')";
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();
}
}
}
public DataTable GetFMaster()
{
DataTable dt = null;
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
try
{
sql = "Select * from dbo.FeatureListMaster";
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
dt.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public int InsertFDesc(string argDescription)
{
int iTempId = 0;
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "Insert into dbo.FeatureListMaster(FeatureDesc) Values('" + argDescription + "') SELECT SCOPE_IDENTITY();";
SqlCommand Command = new SqlCommand(sSql, conn, tran);
iTempId = int.Parse(Command.ExecuteScalar().ToString());
tran.Commit();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
tran.Rollback();
}
finally
{
conn.Close();
conn.Dispose();
}
return(iTempId);
}
public static DataTable GetExtraBill(int argFlatId)
{
DataTable dt = new DataTable();
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
try
{
sql = "Select F.FlatId,F.ExtraItemId,F.Rate,F.Quantity,F.Amount,M.ItemCode,M.ItemDescription,U.Unit_Name" +
" From dbo.ExtraBillTrans T " +
" Inner Join dbo.FlatExtraItem F On T.ExtraItemId=F.ExtraItemId " +
" Inner Join dbo.ExtraBillRegister R On R.BillRegId=T.BillRegId And F.FlatId=R.FlatId " +
" Inner Join dbo.ExtraItemMaster M On M.ExtraItemId=F.ExtraItemId" +
" Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.UOM U On U.Unit_ID=M.UnitId" +
" Where R.FlatId=" + argFlatId + " And F.Approve='Y'";
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
sda.Fill(dt);
sda.Dispose();
BsfGlobal.g_CRMDB.Close();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
public DataTable GetExtraBillList(DateTime frmDate, DateTime toDate)
{
DataTable dt = null;
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
string frmdat = string.Format("{0:dd MMM yyyy}", frmDate);
string tdat = string.Format("{0:dd MMM yyyy}", toDate.AddDays(1));
try
{
sql = "Select E.LeadName,A.CostCentreId,A.BillRegId,A.BillDate,A.BillNo,B.FlatNo,C.CostCentreName,C.ProjectDB,D.BlockName," +
" A.NetAmount BillAmount from dbo.ExtraBillRegister A " +
" Inner Join dbo.FlatDetails B on A.FlatId=B.FlatId " +
" Left Join dbo.BlockMaster D on B.BlockId=D.BlockId " +
" Inner Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C on A.CostCentreId=C.CostCentreId " +
" Inner Join dbo.LeadRegister E On B.LeadId=E.LeadId " +
" Where A.BillDate between '" + frmdat + "' And '" + tdat + "' Order by A.BillDate,A.BillNo";
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
BsfGlobal.g_CRMDB.Close();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
internal static DataTable PopulateLevel(int argCCId)
{
string sSql = "";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = null;
DataTable dt = new DataTable();
try
{
sSql = "Select LevelId,LevelName from LevelMaster Where CostCentreId = " + argCCId + " Order By SortOrder";
da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
da.Fill(dt);
da.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
internal static void InsertCarParkCodeSetUp(int argCCId, int argBlockId, string argType, string argPrefix, string argSuffix, int argStartNo, int argWidth)
{
SqlConnection conn = BsfGlobal.OpenCRMDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "Delete dbo.CarParkCodeSetup Where BlockId=" + argBlockId + " AND CostCentreId=" + argCCId + "";
SqlCommand cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
if (argType == "A")
{
sSql = "Insert into dbo.CarParkCodeSetup(CostCentreId, BlockId, Type, Prefix, Suffix, StartNo, Width) " +
"Values(" + argCCId + "," + argBlockId + ",'" + argType + "','" + argPrefix + "','" + argSuffix + "'," + argStartNo + "," + argWidth + ")";
cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
conn.Dispose();
conn.Close();
}
}
internal static int InsertLevel(StageDetBO BOstageDet)
{
string sSql = "";
int iId = 0;
BsfGlobal.OpenCRMDB();
SqlCommand cmd = null;
try
{
sSql = string.Format("Insert into LevelMaster(LevelName, CostCentreId, SortOrder)" +
" Values('{0}', {1}, {2}) SELECT SCOPE_IDENTITY();", BOstageDet.s_LevelName,
BOstageDet.i_CostCentreId, BOstageDet.i_SortOrderLevel);
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
iId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
sSql = " Update ProjectInfo Set NoOfFloors=(Select Count(LevelId) NoOfFloor From LevelMaster " +
" Where CostCentreId=" + BOstageDet.i_CostCentreId + ") Where CostCentreId=" + BOstageDet.i_CostCentreId + "";
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();
}
return(iId);
}
internal static void InsertStageLevel(int argLevelId, string argStr)
{
string sSql = "";
BsfGlobal.OpenCRMDB();
SqlCommand cmd = null;
try
{
sSql = "Delete from LevelStageTrans Where LevelId = " + argLevelId + " AND StageId=" + argStr + "";
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
cmd.ExecuteNonQuery();
if (argStr != "")
{
sSql = "Insert into LevelStageTrans(LevelId,StageId) " +
"Select " + argLevelId + ",StageId from Stages Where StageId in (" + argStr + ")";
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();
}
}
internal static int InsertStage(StageDetBO BOstageDet)
{
string sSql = "";
int iId = 0;
BsfGlobal.OpenCRMDB();
SqlCommand cmd = null;
try
{
sSql = string.Format("Insert into Stages(StageName, CostCentreId, SortOrder)" +
" Values('{0}', {1}, {2}) SELECT SCOPE_IDENTITY();", BOstageDet.s_StageName,
BOstageDet.i_CostCentreId, BOstageDet.i_SorOrderStage);
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
iId = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(iId);
}
internal static bool CheckBlockFound(StageDetBO BOstageDet)
{
string sSql = "";
bool bAns = false;
BsfGlobal.OpenCRMDB();
SqlCommand cmd = null; SqlDataReader dr;
try
{
sSql = "Select BlockName From BlockMaster Where BlockName='" + BOstageDet.s_BlockName + "' And CostCentreId=" + BOstageDet.i_CostCentreId + "";
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
if (dt.Rows.Count > 0)
{
bAns = true;
}
cmd.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(bAns);
}
internal static void DeletLevel(int i_LID, int argCCId)
{
string sSql = "";
BsfGlobal.OpenCRMDB();
SqlCommand cmd = null;
try
{
sSql = string.Format("Delete LevelMaster Where LevelId={0}", i_LID);
cmd = new SqlCommand(sSql, BsfGlobal.g_CRMDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
sSql = " Update ProjectInfo Set NoOfFloors=(Select Count(LevelId) NoOfFloor From LevelMaster " +
" Where CostCentreId=" + argCCId + ") Where CostCentreId=" + argCCId + "";
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 GetProsDetails(int argEnqId)
{
DataTable dt = new DataTable();
string sSql = "";
SqlDataAdapter sda;
try
{
BsfGlobal.OpenCRMDB();
sSql = "Select C.CostCentreName ProjectName,B.EmployeeName ExecutiveName,Convert(Varchar(10),A.TrnDate,103) CallDate," +
" Convert(Varchar(10),A.NextCallDate,103) NextCallDate,D.Description Status, " +
"E.Description Nature,F.Description CallType,A.Remarks from dbo.CallSheet A " +
"Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.Users B On A.ExecutiveId=B.UserId " +
"Inner Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C On A.ProjectId=C.CostCentreId " +
"Left Join dbo.StatusMaster D On A.StatusID=D.StatusId " +
"Left Join dbo.NatureMaster E On A.NatureID=E.NatureId " +
"Left Join dbo.CallType F On A.CallTypeID=F.CallTypeId " +
"Where LeadId = " + argEnqId + " ";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
internal static DataTable PopulateStageBlock(int i_BlockId, int argCCId)
{
string sSql = "";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = null;
DataTable dt = new DataTable();
try
{
sSql = string.Format("Select A.StageName, A.StageId from Stages A INNER JOIN BlockStageTrans B" +
" ON A.StageId=B.StageId AND B.BlockId={0} Where A.CostCentreId={1}", i_BlockId, argCCId);
da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
da.Fill(dt);
da.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public DataTable GetProject()
{
DataTable dt = null;
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
try
{
//sql = "Select CostCentreId,CostCentreName,ProjectDB from ["+ BsfGlobal.g_sWorkFlowDBName +"].dbo.OperationalCostCentre Where CostCentreId<>0 Order By CostCentreName";
sql = "Select CostCentreId,CostCentreName,ProjectDB from [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre " +
" Where ProjectDB in(Select ProjectName from [" + BsfGlobal.g_sRateAnalDBName + "].dbo.ConceptionRegister " +
" Where BusinessType ='B') and CostCentreId not in (Select CostCentreId " +
" From [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.UserCostCentreTrans Where UserId=" + BsfGlobal.g_lUserId + ") Order By CostCentreName";
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
BsfGlobal.g_CRMDB.Close();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
internal static int Get_SubLedgerType(string arg_sType)
{
BsfGlobal.OpenCRMDB();
SqlDataAdapter sda;
DataTable dtData = null;
int iSLTypeId = 0;
try
{
sda = new SqlDataAdapter(String.Format("SELECT SubLedgerTypeId FROM [{0}].dbo.SubLedgerType WHERE SubLedgerTypeName='" + arg_sType + "'", BsfGlobal.g_sFaDBName), BsfGlobal.OpenCRMDB());
sda.SelectCommand.CommandType = CommandType.Text;
dtData = new DataTable();
sda.Fill(dtData);
if (dtData.Rows.Count != 0)
{
iSLTypeId = Convert.ToInt32(dtData.Rows[0]["SubLedgerTypeId"].ToString());
}
dtData.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(iSLTypeId);
}
public DataTable GetExtraItemDetails(int argFlatId, string argProjName)
{
DataTable dt = null;
SqlDataAdapter sda;
string sql = "";
BsfGlobal.OpenCRMDB();
try
{
sql = "Select F.ExtraItemId,T.Rate,T.Qty,T.Amount,L.LeadName,M.ItemCode Code,M.ItemDescription Description,U.Unit_Name Unit," +
" str(T.Qty) + ' ' + U.Unit_Name WorkingQty,T.NetAmount From dbo.ExtraBillTrans T " +
" Inner Join dbo.FlatExtraItem F On T.ExtraItemId=F.ExtraItemId " +
" Inner Join dbo.ExtraBillRegister R On R.BillRegId=T.BillRegId And F.FlatId=R.FlatId " +
" Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.ExtraItemMaster M On M.ExtraItemId=F.ExtraItemId" +
" Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.UOM U On U.Unit_ID=M.UnitId" +
" Inner Join BuyerDetail B On B.FlatId=F.FlatId" +
" Inner Join LeadRegister L On L.LeadId=B.LeadId" +
" Where R.FlatId=" + argFlatId + " And F.Approve='Y'";
sda = new SqlDataAdapter(sql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
BsfGlobal.g_CRMDB.Close();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
internal static string Get_CompanyDB(int arg_iCompanyId, DateTime arg_dPVDate)
{
BsfGlobal.OpenCRMDB();
SqlDataAdapter sda;
DataTable dtData = null;
string sCompDBName = string.Empty;
try
{
sda = new SqlDataAdapter(String.Format("SELECT DBName FROM [{0}].dbo.FiscalYear WHERE FYearId = " +
"(SELECT FYearId FROM [{1}].dbo.FiscalYearTrans WHERE CompanyId='{2}' AND '{3}' " +
"BETWEEN FromDate AND ToDate)", BsfGlobal.g_sFaDBName, BsfGlobal.g_sFaDBName, arg_iCompanyId, arg_dPVDate.ToString("dd/MMM/yyyy")), BsfGlobal.OpenCRMDB());
sda.SelectCommand.CommandType = CommandType.Text;
dtData = new DataTable();
sda.Fill(dtData);
if (dtData.Rows.Count != 0)
{
sCompDBName = dtData.Rows[0]["DBName"].ToString();
}
dtData.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(sCompDBName);
}
public bool CheckUsed(int argId)
{
bool Fchek = false;
SqlDataAdapter sda = null;
DataTable dt = null;
BsfGlobal.OpenCRMDB();
try
{
string sSql = "Delete Count(FeatureId) from dbo.FTypeFeatureTrans Where FeatureId = " + argId;
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
Fchek = true;
}
else
{
Fchek = false;
}
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(Fchek);
}
internal static int Get_BuyerType()
{
BsfGlobal.OpenCRMDB();
SqlDataAdapter sda;
DataTable dtData = null;
int iBuyerTypeId = 0;
try
{
sda = new SqlDataAdapter(String.Format("SELECT AccountId FROM [{0}].dbo.AccountMaster WHERE IsBuyer=1", BsfGlobal.g_sFaDBName), BsfGlobal.OpenCRMDB());
sda.SelectCommand.CommandType = CommandType.Text;
dtData = new DataTable();
sda.Fill(dtData);
if (dtData.Rows.Count != 0)
{
iBuyerTypeId = Convert.ToInt32(dtData.Rows[0]["AccountId"].ToString());
}
dtData.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(iBuyerTypeId);
}
public DataTable GetFlatTypeFeatureList(int argFlatTypeID)
{
DataTable dt = null;
SqlDataAdapter sda;
String sSql;
BsfGlobal.OpenCRMDB();
try
{
sSql = "Select A.FeatureId,FeatureDesc, Case When B.FeatureId IS NULL THEN " +
"CONVERT(bit, 0, 0) else CONVERT(bit, 1, 1) End as Sel " +
"from dbo.FeatureListMaster A " +
"Left Join dbo.FTypeFeatureTrans B On A.FeatureId=B.FeatureId and B.FlatTypeID = " + argFlatTypeID;
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
dt.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
internal static DataTable Get_AllLead()
{
BsfGlobal.OpenCRMDB();
SqlDataAdapter sda;
DataTable dtData = null;
try
{
sda = new SqlDataAdapter("SELECT A.LeadId,B.ExecutiveId,A.LeadName FROM LeadRegister A " +
" Inner Join dbo.LeadExecutiveInfo B On A.LeadId=B.LeadId Order By A.LeadName", BsfGlobal.g_CRMDB);
sda.SelectCommand.CommandType = CommandType.Text;
dtData = new DataTable();
sda.Fill(dtData);
dtData.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dtData);
}
public static DataTable Populate_ComplaintRegister(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 C.ComplaintId,C.TransDate,C.ComplaintNo,C.CostCentreId,C1.CostCentreName,F.FlatNo,N.NatureComplaint,E.EmployeeName AttendedBy," +
"C.AttDate DateAttented,C.Approve FROM Complaint_Entry C " +
"Left JOIN FlatDetails F ON C.FlatId=F.FlatId " +
"Left Join Nature_Complaint N on C.NatureId=N.ComplaintId " +
"Left JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C1 ON C.CostCentreId=C1.CostCentreId " +
"Left JOIN [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.Users E ON C.ExecutiveId=E.UserId " +
"Where C.TransDate between '" + frmdat + "' And '" + tdat + "' ORDER BY C.TransDate,C.ComplaintNo";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
internal static int Get_Buyer_SL(int arg_iBuyerId)
{
SqlDataAdapter sda;
DataTable dt;
int BuyerSLId = 0;
string sSql = string.Empty;
try
{
sSql = "SELECT SubLedgerId FROM [" + BsfGlobal.g_sFaDBName + "].dbo.SubLedgerMaster WHERE SubLedgerTypeId=3" +
" AND RefId=" + arg_iBuyerId;
sda = new SqlDataAdapter(sSql, BsfGlobal.OpenCRMDB());
sda.SelectCommand.CommandType = CommandType.Text;
dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count != 0)
{
BuyerSLId = Convert.ToInt32(dt.Rows[0]["SubLedgerId"]);
}
dt.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(BuyerSLId);
}
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 ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public static DataTable GetPostFollowupDetails(int argEnqId)
{
DataTable dt = new DataTable();
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenCRMDB();
sSql = "Select Case When U.EmployeeName='' Then U.UserName Else U.EmployeeName End As ExecName,C.CostCentreName,A.LeadName,Convert(Varchar(10),B.BuyerDate,103) Date,F.Description CallType," +
" E.Description Nature,Convert(Varchar(10),B.NextCallDate,103) NextCallDate,B.Remarks " +
" from dbo.LeadRegister A Inner Join dbo.FollowUp B On A.LeadId=B.LeadId " +
" Inner Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.OperationalCostCentre C On B.CostCentreId=C.CostCentreId " +
" Left Join dbo.NatureMaster E On B.NatureID=E.NatureId " +
" Left Join dbo.CallType F On B.CallTypeID=F.CallTypeId " +
" Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.Users U On U.UserId=B.ExecutiveId" +
" Left Join [" + BsfGlobal.g_sWorkFlowDBName + "].dbo.Position P on P.PositionId=U.PositionId " +
" Where A.LeadId " +
" IN (Select LeadId from dbo.LeadFlatInfo Where Buyer=1) And A.LeadId=" + argEnqId + " ";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(dt);
}
public static void UpdateCompliantMaater(string argName, int argId)
{
SqlConnection conn = new SqlConnection();
SqlCommand cmd;
conn = BsfGlobal.OpenCRMDB();
using (SqlTransaction tran = conn.BeginTransaction())
{
string sSql = "";
try
{
sSql = "UPDATE Nature_Complaint SET NatureComplaint='" + argName + "' WHERE ComplaintId=" + argId + "";
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();
}
}
}
public static bool GetFlatNoFound(int argCCId, string argFlatNo)
{
DataTable dt = new DataTable();
bool bAns = false;
try
{
string sSql = "Select FlatNo From dbo.FlatDetails Where FlatNo='" + argFlatNo + "' And CostCentreId=" + argCCId + "";
BsfGlobal.OpenCRMDB();
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_CRMDB);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
bAns = true;
}
da.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_CRMDB.Close();
}
return(bAns);
}