//GetInputHistory
public DataTable GetInputHistory(string conn)
{
DataTable tb_inputhistory = new DataTable();
tb_inputhistory = ConnectionDB.GetData("SELECT * FROM [tb_InputHistory] " + conn + " order by InPutID DESC");
return(tb_inputhistory);
}
//GetAllInputHistory
public DataTable GetAllInputHistory(string conn)
{
DataTable tb_inputhistory = new DataTable();
tb_inputhistory = ConnectionDB.GetData("select * from [tb_InputHistory] " + conn);
return(tb_inputhistory);
}
//get Product
public DataTable GetProduct(string con)
{
DataTable dt = new DataTable();
dt = ConnectionDB.GetData("SELECT * FROM [tb_Product] " + con);
return(dt);
}
//get Category for form main
public DataTable GetCategoryMain(string cond)
{
DataTable dt = new DataTable();
dt = ConnectionDB.GetData("select distinct tb_Category.CategoryID, tb_Category.CategoryName from tb_Category inner join tb_Product on tb_Category.CategoryID = tb_Product.CategoryID " + cond);
return(dt);
}
//get dataTable
public DataTable GetSalesperson(string con)
{
DataTable dt = new DataTable();
dt = ConnectionDB.GetData("SELECT * from [tb_Salesperson] " + con);
return(dt);
}
//get Category for form setting
public DataTable GetCategorySetting(string cond)
{
DataTable dt = new DataTable();
dt = ConnectionDB.GetData("select * from tb_Category " + cond);
return(dt);
}
//get OrderDetail
public DataTable getOrderDetail(string con)
{
string sql = "SELECT * FROM tb_OrderDetail " + con;
//System.Diagnostics.Debug.WriteLine(sql);
return(ConnectionDB.GetData(sql));
}
//get Customer
public DataTable GetCustomer(string con)
{
DataTable dt = new DataTable();
dt = ConnectionDB.GetData("select * from tb_Customer " + con);
return(dt);
}
//GetStatisticData
public DataTable GetStatisticData(string _keyWord, string _date, int _categoryID)
{
string sql = "select pr.[ProductID], pr.[ShortName], pr.[LongName], cat.[CategoryName], (case when [tb_temp].[Total] > 0 then [tb_temp].[Total] else 0 end) as SoldQuantity, pr.[InventoryCount] as QuantityAvailable from [tb_Product] as pr " +
"inner join [tb_Category] as cat on pr.[CategoryID] = cat.[CategoryID] " +
"left outer join (select odd.[ProductID], sum(odd.[Qty]) as Total from [tb_Order] as od inner join [tb_OrderDetail] as odd on od.[OrderID] = odd.[OrderID] where od.[OrderDate] like '%" + _date + "%' group by odd.[ProductID]) as tb_temp " +
"on pr.[ProductID] = [tb_temp].[ProductID] where (pr.[ShortName] like '%" + _keyWord + "%' or pr.[LongName] like '%" + _keyWord + "%') and pr.[CategoryID] = " + _categoryID + " order by SoldQuantity desc;";
System.Diagnostics.Debug.WriteLine(sql);
return(ConnectionDB.GetData(sql));
}
//GetTotalCapitalYear
public DataTable GetTotalCapitalYear(string _str_year, string condition, bool sql_database_type)
{
string sql = "";
if (sql_database_type == false)
{
sql = "select strftime('%m', [tb_Order].[OrderDate]) as CapitalMonth, sum([tb_OrderDetail].[Qty] * [tb_OrderDetail].[Cost]) as TotalCapitalMoth from [tb_Order] join [tb_OrderDetail] on [tb_Order].[OrderID] = [tb_OrderDetail].[OrderID] where strftime('%Y', [tb_Order].[OrderDate]) = '" + _str_year + "' " + condition + " group by CapitalMonth";
}
else
{
sql = "select month([tb_Order].[OrderDate]) as CapitalMonth, sum([tb_OrderDetail].[Qty] * [tb_OrderDetail].[Cost]) as TotalCapitalMoth from [tb_Order] join [tb_OrderDetail] on [tb_Order].[OrderID] = [tb_OrderDetail].[OrderID] where year([tb_Order].[OrderDate]) = '" + _str_year + "' " + condition + " group by month([tb_Order].[OrderDate])";
}
return(ConnectionDB.GetData(sql));
}
//GetTotalRevenueYear
public DataTable GetTotalRevenueYear(string _str_year, string condition, bool sql_database_type)
{
string sql = "";
if (sql_database_type == false)
{
sql = "select strftime('%m', [tb_Order].[OrderDate]) as RevenueMonth, sum([tb_Order].[TotalAmount]) as TotalRevenueMonth from [tb_Order] where strftime('%Y', [tb_Order].[OrderDate]) = '" + _str_year + "' " + condition + " group by RevenueMonth";
}
else
{
sql = "select month([tb_Order].[OrderDate]) as RevenueMonth, sum([tb_Order].[TotalAmount]) as TotalRevenueMonth from [tb_Order] where year([tb_Order].[OrderDate]) = '" + _str_year + "' " + condition + " group by month([tb_Order].[OrderDate])";
}
return(ConnectionDB.GetData(sql));
}
public DataTable GetYearFromOrder(string condition, bool sql_database_type)
{
string sql = "";
if (sql_database_type == false)
{
sql = "select distinct strftime('%Y', [tb_Order].[OrderDate]) as Year from [tb_Order] " + condition;
}
else
{
sql = "select distinct year([tb_Order].[OrderDate]) as Year from [tb_Order] " + condition;
}
tb_year = ConnectionDB.GetData(sql);
return(tb_year);
}
//GetTotalRevenueAll
public DataTable GetTotalRevenueAll(string condition, bool sql_database_type)
{
string sql = "";
DataTable tb_revenue_all = new DataTable();
if (sql_database_type == false)
{
sql = "select strftime('%Y', [tb_Order].[OrderDate]) as RevenueAll, sum([tb_Order].[TotalAmount]) as TotalRevenueAll from [tb_Order] " + condition + " group by RevenueAll";
}
else
{
sql = "select year([tb_Order].[OrderDate]) as RevenueAll, sum([tb_Order].[TotalAmount]) as TotalRevenueAll from [tb_Order] " + condition + " group by year([tb_Order].[OrderDate])";
}
tb_revenue_all = ConnectionDB.GetData(sql);
return(tb_revenue_all);
}
//get order follow paging
public DataTable GetOrderFollowPaging(int be_limit, int af_limit, string con1, string con2, bool sql_database_type)
{
string sql = "";
DataTable tb = new DataTable();
if (sql_database_type == false)
{
sql = "select * from [tb_Order] " + con1 + " limit " + be_limit + ", " + af_limit;
}
else
{
sql = "select top(" + af_limit + ") * from [tb_Order] where [OrderID] not in (select top(" + be_limit + ") [OrderID] from [tb_Order] " + con1 + ") " + con2;
}
tb = ConnectionDB.GetData(sql);
return(tb);
}
//get product follow paging
public DataTable GetProductFollowPaging(int be_limit, int af_limit, string con1, string con2, bool sql_database_type, string _oderby)
{
DataTable tb = new DataTable();
string sql = "";
if (sql_database_type == false)
{
sql = "select *, (select sum(Qty) from tb_OrderDetail where tb_OrderDetail.ProductID = tb_Product.ProductID group by ProductID) as Total from [tb_Product] " + con1 + " " + _oderby + " limit " + be_limit + "," + af_limit;
}
else
{
//sql = "select top(" + af_limit + ") * from [tb_Product] where [ProductID] not in (select top(" + be_limit + ") [ProductID] from [tb_Product] " + con1 + ") " + con2 + " " + _oderby;
sql = "select *, (select sum(Qty) from tb_OrderDetail where tb_OrderDetail.ProductID = tb_Product.ProductID group by ProductID) as Total from [tb_Product] " + con1 + " " + _oderby + " OFFSET " + be_limit + " ROWS FETCH NEXT " + af_limit + " ROWS ONLY";
}
tb = ConnectionDB.GetData(sql);
return(tb);
}
//GetTotalCapitalAll
public DataTable GetTotalCapitalAll(string condition, bool sql_database_type)
{
string sql = "";
DataTable tb_capital_all = new DataTable();
if (sql_database_type == false)
{
sql = "select strftime('%Y', [tb_Order].[OrderDate]) as CapitalAll, sum([tb_OrderDetail].[Cost] * [tb_OrderDetail].[Qty]) as TotalCapitalAll from [tb_Order] join [tb_OrderDetail] on [tb_Order].[OrderID] = [tb_OrderDetail].[OrderID] " + condition + " group by CapitalAll";
}
else
{
sql = "select year([tb_Order].[OrderDate]) as CapitalAll, sum([tb_OrderDetail].[Cost] * [tb_OrderDetail].[Qty]) as TotalCapitalAll from [tb_Order] join [tb_OrderDetail] on [tb_Order].[OrderID] = [tb_OrderDetail].[OrderID] " + condition + " group by year([tb_Order].[OrderDate])";
}
tb_capital_all = ConnectionDB.GetData(sql);
return(tb_capital_all);
}
//get User
public DataTable GetUser(string con)
{
string sql = "SELECT * FROM [tb_User] " + con;
return(ConnectionDB.GetData(sql));
}
//GetQuatityOrderDetail
public DataTable GetQuatityOrderDetail(string condition)
{
string sql = "select [tb_OrderDetail].[ProductID], sum([tb_OrderDetail].[Qty]) as Qty from [tb_OrderDetail] JOIN [tb_Product] ON [tb_Product].[ProductID] = [tb_OrderDetail].[ProductID] WHERE [tb_Product].[Active] = 1 " + condition + " group by [tb_Product].[ProductID]";
return(ConnectionDB.GetData(sql));
}
//get Payment
public DataTable GetPayment(string con)
{
string sql = "select * from tb_Payment " + con;
return(ConnectionDB.GetData(sql));
}
//get Setting
public DataTable GetSetting(string con)
{
string sql = "SELECT * FROM [tb_Currency] " + con;
return(ConnectionDB.GetData(sql));
}
//GetOrderPrint
public DataTable GetOrderPrint(string sql)
{
return(ConnectionDB.GetData(sql));
}
//get Order
public DataTable GetOrder(string con)
{
return(ConnectionDB.GetData(" SELECT * FROM [tb_Order] " + con));
}
