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);
}
internal static DataTable Get_CostCentre()
{
DataTable dtData = null;
SqlCommand command;
SqlDataReader sdr; string sSql = "";
try
{
sSql = "Select CostCentreId,CostCentreName From dbo.OperationalCostCentre" +
" Where ProjectDB In(Select ProjectName From " +
" [" + BsfGlobal.g_sRateAnalDBName + "].dbo.ConceptionRegister Where BusinessType IN('B'))" +
" and CostCentreId Not In (Select CostCentreId From dbo.UserCostCentreTrans " +
" Where UserId=" + BsfGlobal.g_lUserId + ") Order By CostCentreName";
command = new SqlCommand(sSql, BsfGlobal.OpenWorkFlowDB())
{
CommandType = CommandType.Text
};
dtData = new DataTable();
sdr = command.ExecuteReader(CommandBehavior.CloseConnection);
dtData.Load(sdr);
dtData.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(dtData);
}
public void UpdateCity(Vendor.BusinessLayer.CityBL argCity)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenWorkFlowDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "Update CityMaster set CityName= '" + argCity.CityName + "',StateId= " + argCity.StateId + "," +
"CountryId=" + argCity.CountryId + " Where CityID = " + argCity.CityId;
SqlCommand Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void InsertCity(Vendor.BusinessLayer.CityBL argCity)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenWorkFlowDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "Insert into CityMaster(CityName,StateId,CountryID) " +
"Values('" + argCity.CityName + "'," + argCity.StateId + "," + argCity.CountryId + ")";
SqlCommand Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public int InsertCountry(string argCountry)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenWorkFlowDB();
SqlTransaction tran = conn.BeginTransaction();
int iCId = 0;
try
{
string sSql = "Insert into CountryMaster(CountryName) Values('" + argCountry + "') SELECT SCOPE_IDENTITY();";
SqlCommand Command = new SqlCommand(sSql, conn, tran);
iCId = int.Parse(Command.ExecuteScalar().ToString());
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
return(iCId);
}
public DataTable GetCostCentreList()
{
DataTable dt = new DataTable();
BsfGlobal.OpenWorkFlowDB();
try
{
//string sSql = "Select CostCentreId,CostCentreName from OperationalCostCentre Order by CostCentreName ";
string sSql = "Select CostCentreId,CostCentreName From 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 dbo.UserCostCentreTrans Where " +
" UserId=" + BsfGlobal.g_lUserId + ") Order By CostCentreName";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(dt);
}
public bool CheckState(int argStateId)
{
bool status = false;
DataTable dt;
string sSql = "";
SqlDataAdapter sda;
try
{
BsfGlobal.OpenWorkFlowDB();
sSql = "Select StateId from CityMaster Where StateId=" + argStateId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
status = true;
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(status);
}
public void DeleteCountry(int argId)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenWorkFlowDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "Delete from dbo.CountryMaster Where CountryId = " + argId;
SqlCommand Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void UpdateCountry(int argId, string argName)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenWorkFlowDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "Update CountryMaster Set CountryName= '" + argName + "' Where CountryId = " + argId;
SqlCommand Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public static DataTable GetCostCentre()
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql = "";
BsfGlobal.OpenWorkFlowDB();
try
{
sSql = "Select CostCentreId,CostCentreName from 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 dbo.UserCostCentreTrans Where UserId=" + BsfGlobal.g_lUserId + ") Order by CostCentreName";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (SqlException ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(dt);
}
public static void CheckListAlertUpdate(string argAlertName, string argAlterDesc, int argCCId, int argFlatId)
{
BsfGlobal.OpenWorkFlowDB();
string sSql = "SELECT AlertId FROM AlertMaster WHERE " +
"AlertName= '" + argAlertName + "'";
SqlCommand cmd = new SqlCommand(sSql, BsfGlobal.g_WorkFlowDB);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dr.Close();
cmd.Dispose();
int iAlertId = 0;
if (dt.Rows.Count > 0)
{
iAlertId = Convert.ToInt32(dt.Rows[0]["AlertId"].ToString());
}
dt.Dispose();
sSql = "Insert into AlertTransUsers " +
"(AlertId,AlertDescription,AlertDate,UserId) " +
"Select " + iAlertId + ",'" + argAlertName + "','" + DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss") + "',UserId from UserAlertTrans " +
"Where UserId Not in (Select UserId from UserCostCentreTrans Where CostCentreId= " + argCCId + ")";
cmd = new SqlCommand(sSql, BsfGlobal.g_WorkFlowDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
//if (argFlatId != 0)
//{
// int iBuyerId = 0;
// sSql = "Select LeadId from [" + BsfGlobal.g_sCRMDBName + "].dbo.FlatDetails Where FlatId= " + argFlatId;
// cmd = new SqlCommand(sSql, BsfGlobal.g_WorkFlowDB);
// dr = cmd.ExecuteReader();
// dt = new DataTable();
// dt.Load(dr);
// dr.Close();
// cmd.Dispose();
// if (dt.Rows.Count > 0) { iBuyerId = Convert.ToInt32(dt.Rows[0]["LeadId"]); }
// dt.Dispose();
// if (iBuyerId != 0)
// {
// sSql = "Insert into AlertTransBuyer(AlertId,AlertDescription,AlertDate,BuyerId) " +
// "Select " + iAlertId + ",'" + argAlertName + "','" + DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss") + "',UserId from UserAlertTrans " +
// "Where UserId Not in (Select UserId from UserCostCentreTrans Where CostCentreId= " + argCCId + ")";
// cmd = new SqlCommand(sSql, BsfGlobal.g_WorkFlowDB);
// cmd.ExecuteNonQuery();
// cmd.Dispose();
// }
//}
BsfGlobal.g_WorkFlowDB.Close();
}
public DataTable GetFaCC(string CompanyName)
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql;
try
{
sSql = "select CostCentreId,CostCentreName from dbo.CostCentre";
sda = new SqlDataAdapter(sSql, BsfGlobal.OpenWorkFlowDB());
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
public DataTable GetStateData(int argID)
{
DataTable dt = null;
try
{
string sSql = "SELECT StateName,CountryId from dbo.StateMaster Where StateID = " + argID;
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenWorkFlowDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_WorkFlowDB.Close();
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public DataTable GetCountryList()
{
DataTable dt = null;
try
{
string sSql = "SELECT CountryId,CountryName from dbo.CountryMaster Order by CountryName";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenWorkFlowDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_WorkFlowDB.Close();
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public DataTable GetCompany()
{
DataTable dt = null;
SqlDataAdapter sda;
string sSql;
try
{
sSql = "SELECT CompanyId,CompanyName from dbo.CompanyMaster";
sda = new SqlDataAdapter(sSql, BsfGlobal.OpenWorkFlowDB());
dt = new DataTable();
sda.Fill(dt);
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
public DataTable GetStateList()
{
DataTable dt = null;
try
{
string sSql = "Select A.StateId,A.StateName,B.CountryName,A.CountryId from StateMaster A " +
"Inner Join dbo.CountryMaster B on A.CountryId=B.CountryId Order by A.StateName";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenWorkFlowDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_WorkFlowDB.Close();
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public static DataTable FillRecord2(string sql)
{
BsfGlobal.OpenWorkFlowDB();
DataTable dt = null;
try
{
SqlDataAdapter sda = new SqlDataAdapter(sql, BsfGlobal.g_WorkFlowDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(dt);
}
public void DeleteCity(int argCityId)
{
SqlCommand cmd;
string sSql = "";
try
{
BsfGlobal.OpenWorkFlowDB();
sSql = "Delete CityMaster Where CityId=" + argCityId + " ";
cmd = new SqlCommand(sSql, BsfGlobal.g_WorkFlowDB);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
}
public static DataTable FillCC()
{
BsfGlobal.OpenWorkFlowDB();
DataTable dt = null;
try
{
string sSql = "SELECT CostCentreId,CostCentreName FROM OperationalCostCentre ORDER BY CostCentreName";
SqlDataAdapter sda = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(dt);
}
public DataTable GetCostCentreList()
{
DataTable dt = new DataTable();
BsfGlobal.OpenWorkFlowDB(); string sSql = "";
try
{
sSql = "Select CostCentreId,CostCentreName,B.BusinessType,B.LandId from OperationalCostCentre A " +
" Inner Join [" + BsfGlobal.g_sRateAnalDBName + "].dbo.ConceptionRegister B on A.ProjectDB=B.ProjectName " +
" Where B.BusinessType in('B','L') And CostCentreId Not In (Select CostCentreId From dbo.UserCostCentreTrans Where UserId=" + BsfGlobal.g_lUserId + ")" +
" Order By CostCentreName ";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_WorkFlowDB.Close();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
return(dt);
}
internal static DataTable GetMandrillSetting(int argReportTypeId)
{
BsfGlobal.OpenWorkFlowDB();
DataTable dt = null;
try
{
string sql = string.Empty;
sql = "SELECT * FROM dbo.MailTemplate Where ReportTypeId=" + argReportTypeId + "";
SqlDataAdapter sda = new SqlDataAdapter(sql, BsfGlobal.g_WorkFlowDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(dt);
}
public DataTable GetCostCentre()
{
DataTable dt = null;
try
{
string sSql = "Select CostCentreId, CostCentreName,ProjectDB From dbo.OperationalCostCentre";
BsfGlobal.OpenWorkFlowDB();
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
dt = new DataTable();
da.Fill(dt);
da.Dispose();
dt.Dispose();
}
catch (Exception e)
{
BsfGlobal.CustomException(e.Message, e.StackTrace);
}
finally
{
BsfGlobal.g_WorkFlowDB.Close();
}
return(dt);
}
public bool CheckCity(int argCityId)
{
bool status = false;
string sSql = "";
SqlDataAdapter sda;
DataTable dt;
try
{
BsfGlobal.OpenVendorAnalDB();
BsfGlobal.OpenWorkFlowDB();
BsfGlobal.OpenRateAnalDB();
sSql = "Select CityId from Branch Where CityId=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select CityId from VendorLocation Where CityId=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select CityId from VendorMaster Where CityId=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select CityId from CompanyMaster Where CityId=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select CityId from CostCentre Where CityId=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select CityId from Users Where CityId=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_WorkFlowDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select Location_Id from ConceptionRegister Where Location_Id=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_RateAnalDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select Location_Id from IOW_Rate Where Location_Id=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_RateAnalDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select Location_Id from IOW_Rate_Q Where Location_Id=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_RateAnalDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select Location_Id from Project_List Where Location_Id=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_RateAnalDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select Location_Id from Project_List Where Location_Id=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_RateAnalDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select Location_Id from Resource_Rate Where Location_Id=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_RateAnalDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
sSql = "Select Location_Id from Resource_Rate_Q Where Location_Id=" + argCityId + " ";
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.g_RateAnalDB);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
status = false; return(status);
}
status = true;
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_VendorDB.Close();
BsfGlobal.g_WorkFlowDB.Close();
BsfGlobal.g_RateAnalDB.Close();
}
return(status);
}