public void UpdateGradeTrans(Vendor.BusinessLayer.RegisterBL argRegister)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Update_GradeTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@VendorId", argRegister.VendorId);
Command.Parameters.AddWithValue("@SGradeId", argRegister.SGradeId);
Command.Parameters.AddWithValue("@CGradeId", argRegister.CGradeId);
Command.Parameters.AddWithValue("@HGradeId", argRegister.HGradeId);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public DataTable GetRegTransLatest(int argVendorID)
{
DataTable dt = null;
try
{
string sSql = "Select A.RegisterId,A.VendorId,A.Supply,R.RegNo,Convert(Varchar(10),R.RegDate,103) RegDate, A.Contract,A.Service,Convert(Varchar(10),A.STDate,103) STDate,Convert(Varchar(10),A.CTDate,103) CTDate,Convert(Varchar(10),A.HTDate,103) HTDate," +
"A.SLife,A.CLife,A.HLife,A.SSuspend,A.CSuspend,A.HSuspend,A.SBlackList,A.CBlackList,A.HBlackList," +
"A.SGradeId,A.CGradeId,A.HGradeID,B.GradeName SGrade,C.GradeName CGrade,D.GradeName HGrade From RegTransLatest A " +
"Inner Join dbo.Registration R on A.RegisterId = R.RegisterId " +
"Left Join dbo.GradeMaster B on A.SGradeId = B.GradeID " +
"Left Join dbo.GradeMaster C on A.CGradeId = B.GradeID " +
"Left Join dbo.GradeMaster D on A.HGradeId = B.GradeID " +
"Where A.VendorId= " + argVendorID;
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_VendorDB.Close();
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public void InsertCheckListTrans(Vendor.BusinessLayer.CheckListBL argCheckList)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Insert_VendorCheckListTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@VendorId", argCheckList.VendorId);
Command.Parameters.AddWithValue("@CheckListId", argCheckList.CheckListId);
Command.Parameters.AddWithValue("@RegType", argCheckList.RegType);
Command.Parameters.AddWithValue("@Points", argCheckList.Points);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void InsertAssessment(Vendor.BusinessLayer.RegisterBL argRegister)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Insert_VendorAssesment", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@VendorId", argRegister.VendorId);
Command.Parameters.AddWithValue("@SupplyMaxPoint", argRegister.SupplyMaxPoint);
Command.Parameters.AddWithValue("@SupplyPoints", argRegister.SupplyPoints);
Command.Parameters.AddWithValue("@ContractMaxPoint", argRegister.ContractMaxPoint);
Command.Parameters.AddWithValue("@ContractPoints", argRegister.ContractPoints);
Command.Parameters.AddWithValue("@ServiceMaxPoint", argRegister.ServiceMaxPoint);
Command.Parameters.AddWithValue("@ServicePoints", argRegister.ServicePoints);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public bool CheckRegNo(int argRegId, string argRegNo)
{
DataTable dt = null;
bool bFound = false;
try
{
string sSql = "SELECT RegisterId from dbo.Registration " +
"Where RegisterId <> " + argRegId + " and RegNo=' " + argRegNo + "'";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_VendorDB.Close();
if (dt.Rows.Count > 0)
{
bFound = true;
}
dt.Dispose();
}
catch (Exception e)
{
throw e;
}
return(bFound);
}
public bool CheckRegTrans(int argVendorId)
{
DataTable dt = null;
bool bFound = false;
try
{
string sSql = "Select RegTransId from dbo.RegTrans Where VendorId= " + argVendorId;
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_VendorDB.Close();
if (dt.Rows.Count > 0)
{
bFound = true;
}
dt.Dispose();
}
catch (Exception e)
{
throw e;
}
return(bFound);
}
public DataTable GetRegTrans(int argVendorID)
{
DataTable dt = null;
try
{
string sSql = "Select A.RegTransId,A.VendorID,A.RegId,A.RDate,A.RefNo,V.VendorName,A.StatusType,A.Supply,A.Contract,A.Service,A.SGradeId,B.GradeName SGrade," +
"A.CGradeId,C.GradeName CGrade,A.HGradeId,D.GradeName HGrade,A.SFDate,A.STDate,A.CFDate,A.CTDate,A.HFDate,A.HTDate,A.SLifeTime," +
"A.CLifeTime,A.HLifeTime,A.Remarks from dbo.RegTrans A " +
"Inner Join VendorMaster V on A.VendorId=V.VendorId " +
"Left Join GradeMaster B on A.SGradeId =B.GradeID " +
"Left Join GradeMaster C on A.CGradeId =C.GradeID " +
"Left Join GradeMaster D on A.HGradeId =D.GradeID " +
"Where A.VendorId = " + argVendorID + " Order by RegTransId Desc";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_VendorDB.Close();
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public DataTable GetServiceGroup()
{
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
string sSql = "";
try
{
BsfGlobal.OpenVendorAnalDB();
sSql = "Select ServiceGroupId,ServiceGroupName from ServiceGroup ";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_VendorDB.Close();
}
return(dt);
}
public DataTable GetServiceDetails(int argSId)
{
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
string sSql = "";
try
{
BsfGlobal.OpenVendorAnalDB();
sSql = "Select ServiceCode,ServiceName,ServiceGroupId,UnitId From ServiceMaster Where ServiceId =" + argSId + " ";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_VendorDB.Close();
}
return(dt);
}
public void DeleteRegistration(int argRegId)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Delete_Registration", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@RegId", argRegId);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void UpdateVendorType(int argVendorId, bool argSupply, bool argContract, bool argService)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Update_VendorType", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@VendorId", argVendorId);
Command.Parameters.AddWithValue("@Supply", argSupply);
Command.Parameters.AddWithValue("@Contract", argContract);
Command.Parameters.AddWithValue("@Service", argService);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public DataTable GetServiceMaster()
{
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
string sSql = "";
try
{
BsfGlobal.OpenVendorAnalDB();
sSql = "Select A.ServiceId,A.ServiceCode Code,A.ServiceName Name,B.ServiceGroupName GroupName,C.Unit_Name Unit from ServiceMaster A " +
"Inner Join ServiceGroup B On A.ServiceGroupId=B.ServiceGroupId " +
"Inner Join [" + BsfGlobal.g_sRateAnalDBName + "]..UOM C On A.UnitId=C.Unit_ID ";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_VendorDB.Close();
}
return(dt);
}
public int GetMaxRegTransId(int argVendorID)
{
int Id = 0;
DataTable dt = null;
try
{
string sSql = "Select ISNULL(MAX(RegTransID),0) MRegTransID from RegTrans Where VendorId= " + argVendorID;
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_VendorDB.Close();
if (dt.Rows.Count > 0)
{
Id = Convert.ToInt32(dt.Rows[0]["MRegTransID"].ToString());
}
dt.Dispose();
}
catch (Exception e)
{
throw e;
}
return(Id);
}
public DataTable GetStatusRegister()
{
DataTable dt = null;
try
{
string sSql = "Select G.RegTransID,G.VendorId,G.RDate,G.RefNo, " +
"G.RegNo,G.VendorName,G.Status,G.Supply,G.Contract,G.Service from " +
"(Select A.RegisterId RegTransID,A.VendorId,Convert(char(10),A.RegDate,103) as RDate,A.RegNo RefNo," +
"A.RegNo,B.VendorName,'Register' Status,A.Supply,A.Contract,A.Service " +
"from dbo.Registration A Inner Join VendorMaster B on A.VendorId=B.VendorId " +
"Union All " +
"Select A.RegTransID,A.VendorId,Convert(char(10),A.RDate,103) as RDate,A.RefNo,C.RegNo,B.VendorName," +
"Case When A.StatusType='R' then 'Renewal' else Case When A.StatusType='S' then 'Suspend' else "+
"Case When A.StatusType='B' then 'BlackList' end end end Status,A.Supply,A.Contract,A.Service " +
"from RegTrans A Inner Join dbo.VendorMaster B on A.VendorId=B.VendorId " +
"Inner Join dbo.Registration C on A.RegId=C.RegisterId) G Order by G.RDate,G.RefNo";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_VendorDB.Close();
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public void UpdateStatusEntry(Vendor.BusinessLayer.StatusBL argSts)
{
SqlCommand cmd;
try
{
cmd = new SqlCommand("Update_StatusEntry", BsfGlobal.OpenVendorAnalDB());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@StatusId", argSts.StatusId);
cmd.Parameters.AddWithValue("@VendorId", argSts.VendorId);
cmd.Parameters.AddWithValue("@StatusType", argSts.StatusType);
cmd.Parameters.AddWithValue("@RegType", argSts.RegType);
cmd.Parameters.AddWithValue("@RegisterId", argSts.RegisterId);
cmd.Parameters.AddWithValue("@RefNo", argSts.RefNo);
cmd.Parameters.AddWithValue("@SDate", argSts.SDate);
cmd.Parameters.AddWithValue("@ValidFrom", argSts.ValidFrom);
cmd.Parameters.AddWithValue("@ValidTo", argSts.ValidTo);
cmd.Parameters.AddWithValue("@LifTime", argSts.LifTime);
cmd.Parameters.AddWithValue("@GradeId", argSts.GradeId);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
}
public void DeleteCheckList(int argId)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSql = "Delete from CheckListMaster Where CheckListId = " + argId;
SqlCommand cmd = new SqlCommand(sSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void UpdateCheckList(DataTable argCheckList)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
DataTable dt;
dt = new DataTable();
dt = argCheckList.GetChanges(DataRowState.Added);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand Command = new SqlCommand("Insert_CheckList", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@Description", dt.Rows[i]["Description"]);
Command.Parameters.AddWithValue("@Supply", dt.Rows[i]["Supply"]);
Command.Parameters.AddWithValue("@Contract", dt.Rows[i]["Contract"]);
Command.Parameters.AddWithValue("@Service", dt.Rows[i]["Service"]);
Command.Parameters.AddWithValue("@MaxPoint", dt.Rows[i]["MaxPoint"]);
Command.ExecuteNonQuery();
}
}
dt = new DataTable();
dt = argCheckList.GetChanges(DataRowState.Modified);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand Command = new SqlCommand("Update_CheckList", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@CheckListId", dt.Rows[i]["CheckListId"]);
Command.Parameters.AddWithValue("@Description", dt.Rows[i]["Description"]);
Command.Parameters.AddWithValue("@Supply", dt.Rows[i]["Supply"]);
Command.Parameters.AddWithValue("@Contract", dt.Rows[i]["Contract"]);
Command.Parameters.AddWithValue("@Service", dt.Rows[i]["Service"]);
Command.Parameters.AddWithValue("@MaxPoint", dt.Rows[i]["MaxPoint"]);
Command.ExecuteNonQuery();
}
}
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public DataTable GetUnit()
{
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
string sSql = "";
try
{
BsfGlobal.OpenVendorAnalDB();
sSql = "Select * from [" + BsfGlobal.g_sRateAnalDBName + "]..UOM";
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_VendorDB.Close();
}
return(dt);
}
public void UpdateGrade(DataTable argGrade)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
DataTable dt;
dt = new DataTable();
dt = argGrade.GetChanges(DataRowState.Added);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand Command = new SqlCommand("Insert_Grade", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@GradeName", dt.Rows[i]["GradeName"]);
Command.Parameters.AddWithValue("@FValue", dt.Rows[i]["FValue"]);
Command.Parameters.AddWithValue("@TValue", dt.Rows[i]["TValue"]);
Command.ExecuteNonQuery();
}
}
dt = new DataTable();
dt = argGrade.GetChanges(DataRowState.Modified);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand Command = new SqlCommand("Update_Grade", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@GradeId", dt.Rows[i]["GradeId"]);
Command.Parameters.AddWithValue("@GradeName", dt.Rows[i]["GradeName"]);
Command.Parameters.AddWithValue("@FValue", dt.Rows[i]["FValue"]);
Command.Parameters.AddWithValue("@TValue", dt.Rows[i]["TValue"]);
Command.ExecuteNonQuery();
}
}
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void InsertRegTrans(Vendor.BusinessLayer.RegisterBL argRegister, int argTypeId)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Insert_RegTrans", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@VendorId", argRegister.VendorId);
Command.Parameters.AddWithValue("@RegId", argRegister.RegisterId);
Command.Parameters.AddWithValue("@RDate", argRegister.RegDate);
Command.Parameters.AddWithValue("@RefNo ", argRegister.RegNo);
Command.Parameters.AddWithValue("@StatusType ", argRegister.StatusType);
Command.Parameters.AddWithValue("@Supply", argRegister.Supply);
Command.Parameters.AddWithValue("@Contract", argRegister.Contract);
Command.Parameters.AddWithValue("@Service", argRegister.Service);
Command.Parameters.AddWithValue("@SGradeId", argRegister.SGradeId);
Command.Parameters.AddWithValue("@CGradeId", argRegister.CGradeId);
Command.Parameters.AddWithValue("@HGradeId", argRegister.HGradeId);
Command.Parameters.AddWithValue("@SFDate", argRegister.SFDate);
Command.Parameters.AddWithValue("@STDate", argRegister.STDate);
Command.Parameters.AddWithValue("@CFDate", argRegister.CFDate);
Command.Parameters.AddWithValue("@CTDate", argRegister.CTDate);
Command.Parameters.AddWithValue("@HFDate", argRegister.HFDate);
Command.Parameters.AddWithValue("@HTDate", argRegister.HTDate);
Command.Parameters.AddWithValue("@SLifeTime", argRegister.SLife);
Command.Parameters.AddWithValue("@CLifeTime", argRegister.CLife);
Command.Parameters.AddWithValue("@HLifeTime", argRegister.HLife);
Command.Parameters.AddWithValue("@Remarks", argRegister.Remarks);
Command.ExecuteNonQuery();
//Command = new SqlCommand("Update_MaxNo", conn, tran);
//Command.CommandType = CommandType.StoredProcedure;
//Command.Parameters.Clear();
//Command.Parameters.AddWithValue("@TypeId", argTypeId);
//Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void InsertVendorStatutory(Vendor.BusinessLayer.StatutoryBL argStatutory)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Insert_Statutory", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@VendorId", argStatutory.VendorId);
Command.Parameters.AddWithValue("@FirmType", argStatutory.FirmType);
Command.Parameters.AddWithValue("@EYear", argStatutory.EYear);
Command.Parameters.AddWithValue("@PANNo", argStatutory.PANNo);
Command.Parameters.AddWithValue("@TANNo", argStatutory.TANNo);
Command.Parameters.AddWithValue("@CSTNo", argStatutory.CSTNo);
Command.Parameters.AddWithValue("@TINNo", argStatutory.TINNo);
Command.Parameters.AddWithValue("@BankAccountNo", argStatutory.BankAccountNo);
Command.Parameters.AddWithValue("@AccountType", argStatutory.AccountType);
Command.Parameters.AddWithValue("@BankName", argStatutory.BankName);
Command.Parameters.AddWithValue("@BranchName", argStatutory.BranchName);
Command.Parameters.AddWithValue("@BranchCode", argStatutory.BranchCode);
Command.Parameters.AddWithValue("@ServiceTaxNo", argStatutory.ServiceTaxNo);
Command.Parameters.AddWithValue("@TNGSTNo", argStatutory.TNGSTNo);
Command.Parameters.AddWithValue("@MICRCode", argStatutory.MICRCode);
Command.Parameters.AddWithValue("@IFSCCode", argStatutory.IFSCCode);
//SSIREGDNo,ServiceTaxCir,EPFNo,ESINo,ExciseVendor,ExciseRegNo,Excisedivision,ExciseRange,ECCno
Command.Parameters.AddWithValue("@SSIREGDNo", argStatutory.SSIREGDNo);
Command.Parameters.AddWithValue("@ServiceTaxCir", argStatutory.ServiceTaxCir);
Command.Parameters.AddWithValue("@EPFNo", argStatutory.EPFNo);
Command.Parameters.AddWithValue("@ESINo", argStatutory.ESINo);
Command.Parameters.AddWithValue("@ExciseVendor", argStatutory.ExciseVendor);
Command.Parameters.AddWithValue("@ExciseRegNo", argStatutory.ExciseRegNo);
Command.Parameters.AddWithValue("@Excisedivision", argStatutory.Excisedivision);
Command.Parameters.AddWithValue("@ExciseRange", argStatutory.ExciseRange);
Command.Parameters.AddWithValue("@ECCno", argStatutory.ECCno);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public static string GetCodeCheckVendor()
{
string s_Code = "";
SqlCommand cmd;
SqlDataReader sdr;
string sSql = "";
DataTable dt = new DataTable();
try
{
BsfGlobal.OpenVendorAnalDB();
sSql = "Select * from VendorCodeType";
cmd = new SqlCommand(sSql, BsfGlobal.g_VendorDB);
dt = new DataTable();
sdr = cmd.ExecuteReader();
dt.Load(sdr);
sdr.Dispose();
if (dt.Rows.Count > 0)
{
string codeP = "";
string codeS = "";
int m_iMaxN = 0;
codeP = dt.Rows[0]["CodePrefix"].ToString().Trim();
codeS = dt.Rows[0]["Suffix"].ToString().Trim();
m_iMaxN = Convert.ToInt32(dt.Rows[0]["MaxNo"].ToString());
if (codeP != "")
{
m_iMaxN = m_iMaxN + 1;
if (codeS != "")
{
s_Code = codeP + codeS + "000" + m_iMaxN;
}
else
{
s_Code = codeP + "000" + m_iMaxN;
}
}
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_VendorDB.Close();
}
return(s_Code);
}
public void UpdateBranch(Vendor.BusinessLayer.BranchBL argBranch, DataTable dtContact)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
string sSql = "";
try
{
SqlCommand Command = new SqlCommand("Update_Branch", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@BranchId", argBranch.BranchId);
Command.Parameters.AddWithValue("@VendorId", argBranch.VendorId);
Command.Parameters.AddWithValue("@BranchName", argBranch.BranchName);
Command.Parameters.AddWithValue("@Address", argBranch.BranchAddress);
Command.Parameters.AddWithValue("@CityId", argBranch.CityId);
Command.Parameters.AddWithValue("@TINNo", argBranch.TINNo);
Command.Parameters.AddWithValue("@Phone", argBranch.Phone);
Command.Parameters.AddWithValue("@ChequeNo", argBranch.ChequeNo);
Command.ExecuteNonQuery();
if (dtContact.Rows.Count > 0)
{
sSql = "Delete BranchTrans Where BranchId=" + argBranch.BranchId + " ";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
foreach (DataRow dr in dtContact.Rows)
{
sSql = "Insert Into BranchTrans (BranchId,ContactPerson,Designation,ContactNo,Email,Fax) Values " +
"(" + argBranch.BranchId + ",'" + dr["ContactPerson"].ToString() + "','" + dr["Designation"].ToString() + "', " +
"'" + dr["ContactNo"].ToString() + "','" + dr["Email"].ToString() + "','" + dr["Fax"].ToString() + "') ";
Command = new SqlCommand(sSql, conn, tran);
Command.ExecuteNonQuery();
}
}
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public static DataTable PopulateVendorDetList(int argVendorID, string argSuppType, string argListVendorId)
{
DataTable dt;
SqlDataAdapter sda;
string sSql = "";
try
{
BsfGlobal.OpenVendorAnalDB();
if (argSuppType == "M")
{
sSql = "Select VendorId,VendorName,Convert(bit,0,0) Sel from VendorMaster where VendorId <> " + argVendorID + " " +
" and VendorId Not in (select SupplierVendorId from VendorSupplierDet Where SupplierType in ('D' ,'S') and VendorId =" + argVendorID + " )";
if (argListVendorId != "")
{
sSql = sSql + " and VendorId Not in (" + argListVendorId + ")";
}
}
else if (argSuppType == "D")
{
sSql = "Select VendorId,VendorName,Convert(bit,0,0) Sel from VendorMaster where VendorId <> " + argVendorID + " " +
" and VendorId Not in (select SupplierVendorId from VendorSupplierDet Where SupplierType in ('M' ,'S') and VendorId =" + argVendorID + " )";
if (argListVendorId != "")
{
sSql = sSql + " and VendorId Not in (" + argListVendorId + ")";
}
}
else if (argSuppType == "S")
{
sSql = "Select VendorId,VendorName,Convert(bit,0,0) Sel from VendorMaster where VendorId <> " + argVendorID + " " +
" and VendorId Not in (select SupplierVendorId from VendorSupplierDet Where SupplierType in ('D' ,'M') and VendorId =" + argVendorID + ")";
if (argListVendorId != "")
{
sSql = sSql + " and VendorId Not in (" + argListVendorId + ")";
}
}
sda = new SqlDataAdapter(sSql, BsfGlobal.g_VendorDB);
dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
}
catch (Exception ce)
{
throw ce;
}
finally
{
BsfGlobal.g_VendorDB.Close();
}
return(dt);
}
public static bool IsChkSupplierValid(int argVendorID, string argType)
{
SqlCommand cmd;
SqlDataReader sdr;
string sSql = "";
DataTable dt = new DataTable();
bool bReturn = false;
try
{
BsfGlobal.OpenVendorAnalDB();
if (argType == "M")
{
sSql = "select VendorId from VendorSupplierDet Where VendorId=" + argVendorID + " and SupplierType in ('D' ,'S')";
}
else if (argType == "D")
{
sSql = "select VendorId from VendorSupplierDet Where VendorId=" + argVendorID + " and SupplierType in ('M' ,'S')";
}
else if (argType == "S")
{
sSql = "select VendorId from VendorSupplierDet Where VendorId=" + argVendorID + " and SupplierType in ('M' ,'D')";
}
cmd = new SqlCommand(sSql, BsfGlobal.g_VendorDB);
dt = new DataTable();
sdr = cmd.ExecuteReader();
dt.Load(sdr);
sdr.Dispose();
if (dt.Rows.Count > 0)
{
bReturn = true;
}
else
{
bReturn = false;
}
}
catch (Exception ex)
{
BsfGlobal.CustomException(ex.Message, ex.StackTrace);
}
finally
{
BsfGlobal.g_VendorDB.Close();
}
return(bReturn);
}
public int InsertVendorMaster(Vendor.BusinessLayer.VendorBL argVendor)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
int iCId = 0;
try
{
SqlCommand Command = new SqlCommand("Insert_VendorMaster", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@VendorId", argVendor.VendorId);
Command.Parameters["@VendorId"].Direction = ParameterDirection.Output;
Command.Parameters.AddWithValue("@VendorName", argVendor.VendorName);
Command.Parameters.AddWithValue("@Supply", argVendor.Supply);
Command.Parameters.AddWithValue("@Contract", argVendor.Contract);
Command.Parameters.AddWithValue("@Service", argVendor.Service);
Command.Parameters.AddWithValue("@Address", argVendor.RegAddress);
Command.Parameters.AddWithValue("@CityId", argVendor.CityId);
Command.Parameters.AddWithValue("@Pin", argVendor.PinNo);
Command.Parameters.AddWithValue("@STypeId", argVendor.STypeId);
Command.Parameters.AddWithValue("@Company", argVendor.iCompany);
Command.Parameters.AddWithValue("@SuppTypeId", argVendor.SuppTypeId);
Command.Parameters.AddWithValue("@ChequeNo", argVendor.ChequeNo);
Command.Parameters.AddWithValue("@Code", argVendor.Code);
Command.ExecuteNonQuery();
iCId = (int)Command.Parameters["@VendorId"].Value;
tran.Commit();
if (BsfGlobal.g_bVendorDB == true)
{
BsfGlobal.RefreshSubLedger(1);
}
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
return(iCId);
}
public void InsertRegTransLatest(Vendor.BusinessLayer.RegUpdateBL argRegister)
{
SqlConnection conn;
conn = new SqlConnection();
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand Command = new SqlCommand("Update_RegTransLatest", conn, tran);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Clear();
Command.Parameters.AddWithValue("@RegisterId", argRegister.RegisterId);
Command.Parameters.AddWithValue("@VendorId", argRegister.VendorId);
Command.Parameters.AddWithValue("@Supply", argRegister.Supply);
Command.Parameters.AddWithValue("@Contract", argRegister.Contract);
Command.Parameters.AddWithValue("@Service", argRegister.Service);
Command.Parameters.AddWithValue("@STDate", argRegister.STDate);
Command.Parameters.AddWithValue("@CTDate", argRegister.CTDate);
Command.Parameters.AddWithValue("@HTDate", argRegister.HTDate);
Command.Parameters.AddWithValue("@SLifeTime", argRegister.SLife);
Command.Parameters.AddWithValue("@CLifeTime", argRegister.CLife);
Command.Parameters.AddWithValue("@HLifeTime", argRegister.HLife);
Command.Parameters.AddWithValue("@SSuspend", argRegister.SSuspend);
Command.Parameters.AddWithValue("@CSuspend", argRegister.CSuspend);
Command.Parameters.AddWithValue("@HSuspend", argRegister.HSuspend);
Command.Parameters.AddWithValue("@SBlackList", argRegister.SBlackList);
Command.Parameters.AddWithValue("@CBlackList", argRegister.CBlackList);
Command.Parameters.AddWithValue("@HBlackList", argRegister.HBlackList);
Command.Parameters.AddWithValue("@SGradeId", argRegister.SGradeId);
Command.Parameters.AddWithValue("@CGradeId", argRegister.CGradeId);
Command.Parameters.AddWithValue("@HGradeId", argRegister.HGradeId);
Command.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void DeleteStatusEntry(int StsId)
{
SqlCommand cmd;
try
{
cmd = new SqlCommand("Delete_StatusEntry", BsfGlobal.OpenVendorAnalDB());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@StatusId", StsId);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
}
public DataTable GetTransportMode()
{
DataTable dt = null;
try
{
string sSql = "SELECT Distinct TransportMode from dbo.Logistics Order by TransportMode";
SqlDataAdapter da = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
dt = new DataTable();
da.Fill(dt);
da.Dispose();
BsfGlobal.g_VendorDB.Close();
}
catch (Exception e)
{
throw e;
}
return(dt);
}
public void InsertVendorTemp(string sSql)
{
SqlConnection conn;
conn = new SqlConnection();
SqlDataAdapter sda;
DataTable dt;
conn = BsfGlobal.OpenVendorAnalDB();
SqlTransaction tran = conn.BeginTransaction();
string sTSql = "";
try
{
sTSql = "Truncate Table TmpVendor";
SqlCommand cmd = new SqlCommand(sTSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
dt = new DataTable();
sda = new SqlDataAdapter(sSql, BsfGlobal.OpenVendorAnalDB());
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sTSql = "Insert Into TmpVendor (VendorId) Values (" + Convert.ToInt16(dt.Rows[i]["VendorId"]) + ")";
cmd = new SqlCommand(sTSql, conn, tran);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
tran.Commit();
}
catch
{
tran.Rollback();
}
finally
{
conn.Close();
BsfGlobal.g_VendorDB.Close();
}
}