private void PopulateData(int currentPageNumber, int rowPerPage, string orderbyItem)
{
int skipRecord = currentPageNumber - 1;
if (skipRecord != 0)
{
skipRecord = currentPageNumber * rowPerPage;
}
string query = "SELECT ARTID, Ten, Mota, Anh, Ngaytao, Ngaysua FROM Art Order by " + orderbyItem + " OFFSET " + skipRecord.ToString() + " ROWS FETCH NEXT " + rowPerPage.ToString() + " ROWS ONLY; ";
//dt = new DataTable();
//dtnew = new DataTable();
dtnew = DBAccess.FillDataTable(query, dt);
dvgArt.AutoGenerateColumns = false;
dvgArt.DataSource = dtnew;
int rowCount = dtnew.Rows.Count;
pageSize = rowCount / rowPerPage;
// if any row left after calculated pages, add one more page
if (rowCount % rowPerPage > 0)
{
pageSize += 1;
}
lblTotalPage.Text = "Total rows:" + dtnew.Rows.Count.ToString();
DisablePagingButton(currentPageNumber, pageSize);
}
public static DataTable GetAllType()
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select LoaiID, Ten FROM Type";
dtnew = DBAccess.FillDataTable(query, dt);
return(dtnew);
}
public static DataTable GetAllColor()
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select SonID, Ten FROM Color";
dtnew = DBAccess.FillDataTable(query, dt);
return(dtnew);
}
public static DataTable GetAllRole()
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select RoleID, Ten FROM Roles";
dtnew = DBAccess.FillDataTable(query, dt);
return(dtnew);
}
public static DataTable GetAllProduct()
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select Barcode, Kyhieu FROM Product";
dtnew = DBAccess.FillDataTable(query, dt);
return(dtnew);
}
public static DataTable GetAllSupplier()
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select NhaccID, Ten FROM Supplier";
dtnew = DBAccess.FillDataTable(query, dt);
return(dtnew);
}
public static DataTable GetAllCustomer()
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select KHID, HoTen FROM Customer";
dtnew = DBAccess.FillDataTable(query, dt);
return(dtnew);
}
public static string Lookup(string tablename, string selectColumnName, string whereColumnName, string value)
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select " + selectColumnName + " FROM " + tablename + " WHERE " + whereColumnName + " = '" + value + "'";
string resultLookup = string.Empty;
dtnew = DBAccess.FillDataTable(query, dt);
if (dtnew.Rows.Count > 0)
{
resultLookup = dtnew.Rows[0][0].ToString();
}
return(resultLookup);
}
public static bool isXong(string DonhangID)
{
bool isExisted = false;
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select * FROM [Order] WHERE DonhangID ='" + DonhangID + "' AND Xong= 1;";
dtnew = DBAccess.FillDataTable(query, dt);
if (dtnew != null)
{
if (dtnew.Rows.Count > 0)
{
isExisted = true;
}
}
return(isExisted);
}
public static bool CheckItemExist(string tableName, string whereItem, string whereValue)
{
bool isExisted = false;
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string query = "Select * FROM " + tableName + " WHERE " + whereItem + "='" + whereValue + "';";
dtnew = DBAccess.FillDataTable(query, dt);
if (dtnew != null)
{
if (dtnew.Rows.Count > 0)
{
isExisted = true;
}
}
return(isExisted);
}
public static string getStock(string kyhieu, string donhangID, string chuaInSL, string daInSL, string tPSL, string SPLoiSL, string ngayXuat)
{
DataTable dt = new DataTable();
DataTable dtnew = new DataTable();
string result = string.Empty;
bool isStock = false;
int BTPChuaIn, BTPDaIN, TP, SPLoi;
BTPChuaIn = BTPDaIN = TP = SPLoi = 0;
string query = "Select DISTINCT(NewProduct.Kyhieu) [Ký Hiệu], New.[BTP Chưa in], New.[BTP Đã in], New.[Thành Phẩm], New.[Sản Phẩm Lỗi], Stock.Mieuta [Miêu tả] from Stock join(SELECT Barcode, Kyhieu, MaSP from Product Group by Kyhieu, MaSP, Barcode)NewProduct on Stock.Barcode = NewProduct.Barcode join(SELECT Barcode, SUM(CASE WHEN LoaiID = 0000001 Then Soluongcon ELSE 0 END)[BTP Chưa in], SUM(CASE WHEN LoaiID = 0000002 Then Soluongcon ELSE 0 END)[BTP Đã in], SUM(CASE WHEN LoaiID = 0000003 Then Soluongcon ELSE 0 END)[Thành Phẩm], SUM(CASE WHEN LoaiID = 000004 Then Soluongcon ELSE 0 END)[Sản phẩm lỗi] FROM Stock Where DonhangID not in ('" + donhangID + "') GROUP BY Barcode) New on New.Barcode = Stock.Barcode where CONVERT(NVARCHAR(10), ngaytao, 103) <= '" + ngayXuat + "' And NewProduct.Kyhieu = '" + kyhieu + "'";
dtnew = DBAccess.FillDataTable(query, dt);
if (dtnew != null)
{
if (dtnew.Rows.Count > 0)
{
BTPChuaIn = Convert.ToInt32(dtnew.Rows[0][1].ToString());
BTPDaIN = Convert.ToInt32(dtnew.Rows[0][2].ToString());
TP = Convert.ToInt32(dtnew.Rows[0][3].ToString());
SPLoi = Convert.ToInt32(dtnew.Rows[0][4].ToString());
if (Convert.ToInt32(chuaInSL) <= BTPChuaIn && Convert.ToInt32(daInSL) <= BTPDaIN && Convert.ToInt32(tPSL) <= TP && Convert.ToInt32(SPLoiSL) <= SPLoi)
{
isStock = true;
}
if (!isStock)
{
result = "BTP Chưa in: " + BTPChuaIn + " BTP Đã in: " + BTPDaIN + " Thành Phẩm: " + TP + " Sản Phẩm Lỗi: " + SPLoi;
}
}
else
{
result = "BTP Chưa in: " + BTPChuaIn + " BTP Đã in: " + BTPDaIN + " Thành Phẩm: " + TP + " Sản Phẩm Lỗi: " + SPLoi;
}
}
else
{
result = "BTP Chưa in: " + BTPChuaIn + " BTP Đã in: " + BTPDaIN + " Thành Phẩm: " + TP + " Sản Phẩm Lỗi: " + SPLoi;
}
return(result);
}