protected DataTable GetMaterials(CDBUtil dbUtil, MySqlConnection conn, int productLevelId,
int shopId, string countType)
{
string sql = "";
DataTable dtMaterials = new DataTable();
//sql = " SELECT a.*, b.*, c.*, d.*, e.*, f.* FROM materialdept a " +
// " LEFT JOIN materials b " +
// " ON a.MaterialDeptID=b.MaterialDeptID " +
// " LEFT JOIN unitsmall c " +
// " ON b.UnitSmallID=c.UnitSmallID " +
// " LEFT JOIN unitratio d " +
// " ON c.UnitSmallID = d.UnitLargeID " +
// " LEFT JOIN unitlarge e " +
// " ON d.UnitLargeID = e.UnitLargeID " +
// " LEFT JOIN " + countType + " f " +
// " ON b.MaterialID=f.MaterialID " +
// " WHERE b.Deleted = 0 AND f.ProductLevelID=" + productLevelId +
// " AND f.ShopID=" + shopId + " ORDER BY b.MaterialCode, b.MaterialDeptID ";
sql = " SELECT a.*, b.*, c.*, d.*, e.* FROM materials a " +
" INNER JOIN unitsmall b " +
" ON a.UnitSmallID=b.UnitSmallID " +
" INNER JOIN unitratio c " +
" ON b.UnitSmallID = c.UnitLargeID " +
" INNER JOIN unitlarge d " +
" ON c.UnitLargeID = d.UnitLargeID " +
" INNER JOIN " + countType + " e " +
" ON a.MaterialID=e.MaterialID " +
" WHERE a.Deleted = 0 AND e.ProductLevelID=" + productLevelId +
" AND e.ShopID=" + shopId + " ORDER BY a.MaterialCode, a.MaterialDeptID ";
return(dtMaterials = dbUtil.List(sql, conn));
}
protected DataTable GetMaterialDept(CDBUtil dbUtil, MySqlConnection conn, int productLevelId,
int shopId, string countType)
{
string sql = "";
DataTable dtMaterialDept = new DataTable();
sql = "SELECT a.MaterialDeptID, a.MaterialDeptName, " +
" b.materialgroupid, b.MaterialGroupName " +
" FROM materialdept a " +
" LEFT JOIN materialgroup b " +
" ON a.MaterialGroupID = b.MaterialGroupID ";
//sql = " SELECT a.*, b.*, c.*, d.*, e.*, f.*, g.* FROM materialgroup g " +
// " INNER JOIN materialdept a " +
// " ON g.MaterialGroupID=a.MaterialGroupID " +
// " INNER JOIN materials b " +
// " ON a.MaterialDeptID=b.MaterialDeptID " +
// " INNER JOIN unitsmall c " +
// " ON b.UnitSmallID=c.UnitSmallID " +
// " INNER JOIN unitratio d " +
// " ON c.UnitSmallID = d.UnitLargeID " +
// " INNER JOIN unitlarge e " +
// " ON d.UnitLargeID = e.UnitLargeID " +
// " INNER JOIN " + countType + " f " +
// " ON b.MaterialID=f.MaterialID " +
// " WHERE b.Deleted = 0 AND f.ProductLevelID=" + productLevelId +
// " AND f.ShopID=" + shopId + " GROUP BY g.MaterialGroupID ORDER BY b.MaterialCode, b.MaterialDeptID";
return(dtMaterialDept = dbUtil.List(sql, conn));
}
public List <ProductLevel> ListInv(CDBUtil dbUtil, MySqlConnection conn, int staffRoleId)
{
bool isHQ = false;
string sql = "";
// Check shop is hq
sql = " SELECT * FROM property WHERE HeadOrBranch=1";
MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
isHQ = true;
}
else
{
isHQ = false;
}
reader.Close();
sql = "";
if (isHQ)
{
sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 AND IsInv=1 ";
}
else
{
sql = " SELECT * FROM ProductLevel WHERE Deleted = 0 AND IsInv=1 AND ProductLevelID <> 1 ";
}
System.Data.DataTable dtResult = dbUtil.List("SELECT * FROM staffroleviewinventory WHERE StaffRoleID=" + staffRoleId, conn);
if (dtResult.Rows.Count > 0)
{
sql += " AND ProductLevelID IN ( ";
for (int i = 0; i <= dtResult.Rows.Count - 1; i++)
{
sql += dtResult.Rows[i]["ProductLevelID"].ToString();
if (i < dtResult.Rows.Count - 1)
{
sql += " , ";
}
}
sql += " ) ";
}
reader = dbUtil.sqlRetrive(sql, conn);
List <ProductLevel> invList = new List <ProductLevel>();
while (reader.Read())
{
ProductLevel invInfo = new ProductLevel();
if (reader["ProductLevelID"] != DBNull.Value)
{
invInfo.ProductLevelId = reader.GetInt32("ProductLevelID");
}
if (reader["ProductLevelCode"] != DBNull.Value)
{
invInfo.ProductLevelCode = reader.GetString("ProductLevelCode");
}
if (reader["ProductLevelName"] != DBNull.Value)
{
invInfo.ProductLevelName = reader.GetString("ProductLevelName");
}
invList.Add(invInfo);
}
reader.Close();
return(invList);
}