public Vendors getVendorGroupShopId(MySqlConnection con, int vendorGroupId, string vendorGroupName)
{
Vendors vendor = new Vendors();
string strSql = "SELECT ShopID FROM VendorGroup WHERE VendorGroupID=" + vendorGroupId + " AND VendorGroupName='" + vendorGroupName + "'";
MySqlCommand cmd = new MySqlCommand(strSql, con);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
vendor.ShopId = reader.GetInt32(0);
}
}
return(vendor);
}
public Vendors getEachVendorGroup(MySqlConnection conn, int shopId)
{
Vendors vendor = new Vendors();
string strSql = "SELECT a.*, b.ProductLevelName FROM vendorgroup a " +
" LEFT JOIN productlevel b " +
" ON a.ShopId=b.ProductLevelID " +
" WHERE a.ShopID=" + shopId + " AND a.Deleted=0";
MySqlCommand cmd = new MySqlCommand(strSql, conn);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (reader["VendorGroupID"] != DBNull.Value)
{
vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
}
if (reader["ShopID"] != DBNull.Value)
{
vendor.ShopId = reader.GetInt32("ShopID");
}
if (reader["VendorGroupCode"] != DBNull.Value)
{
vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
}
if (reader["VendorGroupName"] != DBNull.Value)
{
vendor.VendorGroupName = reader.GetString("VendorGroupName");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
vendor.VendorInvName = reader.GetString("ProductLevelName");
}
}
reader.Close();
return(vendor);
}
public List <Vendors> ListVendorGroup(CDBUtil dbUtil, MySqlConnection conn)
{
string sql = " SELECT a.*, b.ProductLevelName FROM vendorgroup a, productlevel b WHERE a.ShopID=b.ProductLevelID AND a.Deleted=0";
// if (ShopId != 0)
// sql += " AND ShopID=" + ShopId;
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
List <Vendors> vendorList = new List <Vendors>();
while (reader.Read())
{
Vendors vendor = new Vendors();
if (reader["VendorGroupID"] != DBNull.Value)
{
vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
}
if (reader["ShopID"] != DBNull.Value)
{
vendor.ShopId = reader.GetInt32("ShopID");
}
if (reader["VendorGroupCode"] != DBNull.Value)
{
vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
}
if (reader["VendorGroupName"] != DBNull.Value)
{
vendor.VendorGroupName = reader.GetString("VendorGroupName");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
vendor.VendorInvName = reader.GetString("ProductLevelName");
}
vendorList.Add(vendor);
}
reader.Close();
return(vendorList);
}
public List <Vendors> GetVendors(CDBUtil dbUtil, MySqlConnection conn, int vendorGroupId)
{
string strSql = "SELECT a.*, b.*, c.ProductLevelName FROM vendors a JOIN vendorgroup b " +
" ON (a.VendorGroupID=b.VendorGroupID AND a.ShopID=b.ShopID) " +
" JOIN productlevel c ON (a.ShopID=c.ProductLevelID) WHERE a.Deleted=0 and b.Deleted=0";
if (vendorGroupId > 0)
{
strSql += " AND b.VendorGroupID=" + vendorGroupId + " AND b.ShopID=" + ShopId;
}
//if(ShopId != 1) strSql += " AND a.ShopID = " + ShopId; // hq เห็นหมด
MySqlDataReader reader = dbUtil.sqlRetrive(strSql, conn);
List <Vendors> listVendor = new List <Vendors>();
while (reader.Read())
{
Vendors vendor = new Vendors();
if (reader["VendorID"] != DBNull.Value)
{
vendor.VendorId = reader.GetInt32("VendorID");
}
if (reader["VendorGroupID"] != DBNull.Value)
{
vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
}
if (reader["ShopID"] != DBNull.Value)
{
vendor.ShopId = reader.GetInt32("ShopID");
}
if (reader["VendorCode"] != DBNull.Value)
{
vendor.VendorCode = reader.GetString("VendorCode");
}
if (reader["VendorName"] != DBNull.Value)
{
vendor.VendorName = reader.GetString("VendorName");
}
if (reader["VendorFirstName"] != DBNull.Value)
{
vendor.VendorFirstName = reader.GetString("VendorFirstName");
}
if (reader["VendorLastName"] != DBNull.Value)
{
vendor.VendorLastName = reader.GetString("VendorLastName");
}
if (reader["VendorAddress1"] != DBNull.Value)
{
vendor.VendorAddress1 = reader.GetString("VendorAddress1");
}
if (reader["VendorAddress2"] != DBNull.Value)
{
vendor.VendorAddress2 = reader.GetString("VendorAddress2");
}
if (reader["VendorCity"] != DBNull.Value)
{
vendor.VendorCity = reader.GetString("VendorCity");
}
if (reader["VendorProvince"] != DBNull.Value)
{
vendor.VendorProvice = reader.GetInt32("VendorProvince");
}
if (reader["VendorZipCode"] != DBNull.Value)
{
vendor.VendorZipCode = reader.GetString("VendorZipCode");
}
if (reader["VendorTelephone"] != DBNull.Value)
{
vendor.VendorTel = reader.GetString("VendorTelephone");
}
if (reader["VendorMobile"] != DBNull.Value)
{
vendor.VendorMobile = reader.GetString("VendorMobile");
}
if (reader["VendorFax"] != DBNull.Value)
{
vendor.VendorFax = reader.GetString("VendorFax");
}
if (reader["VendorEmail"] != DBNull.Value)
{
vendor.VendorEmail = reader.GetString("VendorEmail");
}
if (reader["VendorAdditional"] != DBNull.Value)
{
vendor.VendorAdditional = reader.GetString("VendorAdditional");
}
if (reader["InsertDate"] != DBNull.Value)
{
vendor.InsertDate = reader.GetDateTime("InsertDate");
}
if (reader["VendorFirstName"] != DBNull.Value)
{
vendor.VendorFirstName = reader.GetString("VendorFirstName");
}
if (reader["InputBy"] != DBNull.Value)
{
vendor.InputBy = reader.GetInt32("InputBy");
}
if (reader["UpdateDate"] != DBNull.Value)
{
vendor.UpdateDate = reader.GetDateTime("UpdateDate");
}
if (reader["VendorTermOfPayment"] != DBNull.Value)
{
vendor.VendorTermOfPayMent = reader.GetInt32("VendorTermOfPayment");
}
if (reader["VendorCreditDay"] != DBNull.Value)
{
vendor.VendorCreditDay = reader.GetInt32("VendorCreditDay");
}
if (reader["VendorGroupCode"] != DBNull.Value)
{
vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
}
if (reader["VendorGroupName"] != DBNull.Value)
{
vendor.VendorGroupName = reader.GetString("VendorGroupName");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
vendor.VendorInvName = reader.GetString("ProductLevelName");
}
listVendor.Add(vendor);
}
reader.Close();
return(listVendor);
}
public Vendors getEachVendor(MySqlConnection conn, int vendorId)
{
Vendors vendor = new Vendors();
string strSql = "SELECT a.*, b.*, c.ProductLevelName FROM vendors a LEFT JOIN vendorgroup b " +
" ON (a.VendorGroupID=b.VendorGroupID AND a.ShopID=b.ShopID) " +
" LEFT JOIN productlevel c ON (a.ShopID=c.ProductLevelID) " +
" WHERE a.VendorID=" + vendorId + " AND a.Deleted=0 and b.Deleted=0";
MySqlCommand cmd = new MySqlCommand(strSql, conn);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (reader["VendorID"] != DBNull.Value)
{
vendor.VendorId = reader.GetInt32("VendorID");
}
if (reader["VendorGroupID"] != DBNull.Value)
{
vendor.VendorGroupId = reader.GetInt32("VendorGroupID");
}
if (reader["ShopID"] != DBNull.Value)
{
vendor.ShopId = reader.GetInt32("ShopID");
}
if (reader["VendorCode"] != DBNull.Value)
{
vendor.VendorCode = reader.GetString("VendorCode");
}
if (reader["VendorName"] != DBNull.Value)
{
vendor.VendorName = reader.GetString("VendorName");
}
if (reader["VendorFirstName"] != DBNull.Value)
{
vendor.VendorFirstName = reader.GetString("VendorFirstName");
}
if (reader["VendorLastName"] != DBNull.Value)
{
vendor.VendorLastName = reader.GetString("VendorLastName");
}
if (reader["VendorAddress1"] != DBNull.Value)
{
vendor.VendorAddress1 = reader.GetString("VendorAddress1");
}
if (reader["VendorAddress2"] != DBNull.Value)
{
vendor.VendorAddress2 = reader.GetString("VendorAddress2");
}
if (reader["VendorCity"] != DBNull.Value)
{
vendor.VendorCity = reader.GetString("VendorCity");
}
if (reader["VendorProvince"] != DBNull.Value)
{
vendor.VendorProvice = reader.GetInt32("VendorProvince");
}
if (reader["VendorZipCode"] != DBNull.Value)
{
vendor.VendorZipCode = reader.GetString("VendorZipCode");
}
if (reader["VendorTelephone"] != DBNull.Value)
{
vendor.VendorTel = reader.GetString("VendorTelephone");
}
if (reader["VendorMobile"] != DBNull.Value)
{
vendor.VendorMobile = reader.GetString("VendorMobile");
}
if (reader["VendorFax"] != DBNull.Value)
{
vendor.VendorFax = reader.GetString("VendorFax");
}
if (reader["VendorEmail"] != DBNull.Value)
{
vendor.VendorEmail = reader.GetString("VendorEmail");
}
if (reader["VendorAdditional"] != DBNull.Value)
{
vendor.VendorAdditional = reader.GetString("VendorAdditional");
}
if (reader["InsertDate"] != DBNull.Value)
{
vendor.InsertDate = reader.GetDateTime("InsertDate");
}
if (reader["VendorFirstName"] != DBNull.Value)
{
vendor.VendorFirstName = reader.GetString("VendorFirstName");
}
if (reader["InputBy"] != DBNull.Value)
{
vendor.InputBy = reader.GetInt32("InputBy");
}
if (reader["UpdateDate"] != DBNull.Value)
{
vendor.UpdateDate = reader.GetDateTime("UpdateDate");
}
if (reader["VendorTermOfPayment"] != DBNull.Value)
{
vendor.VendorTermOfPayMent = reader.GetInt32("VendorTermOfPayment");
}
if (reader["VendorCreditDay"] != DBNull.Value)
{
vendor.VendorCreditDay = reader.GetInt32("VendorCreditDay");
}
if (reader["VendorGroupCode"] != DBNull.Value)
{
vendor.VendorGroupCode = reader.GetString("VendorGroupCode");
}
if (reader["VendorGroupName"] != DBNull.Value)
{
vendor.VendorGroupName = reader.GetString("VendorGroupName");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
vendor.VendorInvName = reader.GetString("ProductLevelName");
}
try
{
if (reader["DefaultTaxType"] != DBNull.Value)
{
vendor.DefaultTaxType = reader.GetInt32("DefaultTaxType");
}
}
catch
{
}
}
reader.Close();
return(vendor);
}
