// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(Company company, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[company_id]", company.company_id);
sCondition += PublicApi.fnAddCondition("[name]", company.name);
sCondition += PublicApi.fnAddCondition("[phone]", company.phone);
sCondition += PublicApi.fnAddCondition("[status]", company.status);
string sInquireSql =
" SELECT [company].[company_id], " +
" [company].[name], " +
" [company].[phone], " +
" CONVERT(CHAR, [company].[create_datetime], 111) [create_datetime], " +
" [company].[NUM] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + company.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTcompany] " +
" WHERE 1 = 1 " + sCondition + ") AS [company] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(GroupD groupD, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[group_id]", groupD.group_id);
string sInquireSql =
" SELECT [group_d].[id], " +
" [user].[name], " +
" [group_d].[create_id], " +
" CONVERT(CHAR, [group_d].[create_datetime], 111) [create_datetime], " +
" [group_d].[modify_id], " +
" CONVERT(CHAR, [group_d].[modify_datetime], 111) [modify_datetime] " +
" FROM (SELECT Row_number() OVER (ORDER BY [id] ASC) NUM, " +
" * " +
" FROM [MNDTgroup_details] " +
" WHERE 1 = 1 " + sCondition + ") AS [group_d] " +
" LEFT JOIN [MNDTUser] [user] " +
" ON [group_d].[id] = [user].[id] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString();
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(User user, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[id]", user.id);
sCondition += PublicApi.fnAddCondition("[account]", user.account);
sCondition += PublicApi.fnAddCondition("[name]", user.name);
sCondition += PublicApi.fnAddCondition("[status]", user.status);
string sInquireSql =
" SELECT [user].[id], " +
" [user].[account], " +
" [user].[name], " +
" [user].[phone], " +
" CONVERT(CHAR, [user].[create_datetime], 111) [create_datetime], " +
" [user].[NUM] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + user.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTuser] " +
" WHERE 1 = 1 " + sCondition + ") AS [user] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(ReceiveD receiveD, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", receiveD.order_id);
string sInquireSql =
" SELECT [receive_d].[seq], " +
" [receive_d].[material_id], " +
" [receive_d].[warehouse_id], " +
" [receive_d].[amount], " +
" [receive_d].[price], " +
" CASE [receive_d].[complete] WHEN 'Y' THEN 'checked' ELSE '' END [complete], " +
" CASE [receive_d].[pay_complete] WHEN 'Y' THEN 'checked' ELSE '' END [pay_complete], " +
" [receive_d].[description] " +
" FROM (SELECT Row_number() OVER (ORDER BY [seq] ASC) NUM, " +
" * " +
" FROM [MNDTreceive_details] " +
" WHERE 1 = 1 " + sCondition + ") AS [receive_d] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(GroupM groupM, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[group_id]", groupM.group_id);
sCondition += PublicApi.fnAddCondition("[name]", groupM.name);
string sAccountSql = fnGetAccountCondition(groupM.user);
if (sAccountSql != "")
{
sCondition += " AND [group_id] IN " + sAccountSql;
}
string sInquireSql =
" SELECT [group_m].[NUM], " +
" [group_m].[group_id], " +
" [group_m].[name], " +
" CONVERT(CHAR, [group_m].[create_datetime], 111) [create_datetime], " +
" CONVERT(CHAR, [group_m].[modify_datetime], 111) [modify_datetime] " +
" FROM (SELECT Row_number() OVER(ORDER BY[" + groupM.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTgroup_master] " +
" WHERE 1 = 1 " + sCondition + ") AS [group_m] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString();
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(ReceiveM receiveM, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", receiveM.order_id);
sCondition += PublicApi.fnAddCondition("[id]", receiveM.id);
sCondition += PublicApi.fnAddCondition("CONVERT(varchar(10), [datetime], 126)", receiveM.datetime);
string sSql =
" SELECT [receive_m].[NUM], " +
" [receive_m].[order_id], " +
" [receive_m].[id], " +
" [receive_m].[complete], " +
" CONVERT(CHAR, [receive_m].[datetime], 111) [datetime] " +
" FROM (SELECT Row_number() OVER(ORDER BY[" + receiveM.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTreceive_master] " +
" WHERE 1 = 1 " + sCondition + ") AS [receive_m] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString();
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(ProductM productM, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[product_id]", productM.product_id);
sCondition += PublicApi.fnAddCondition("[company_id]", productM.company_id);
sCondition += PublicApi.fnAddCondition("[name]", productM.name);
string sInquireSql =
" SELECT [product_m].[NUM], " +
" [product_m].[product_id], " +
" [product_m].[company_id], " +
" [product_m].[name], " +
" CONVERT(CHAR, [product_m].[create_datetime], 111) [create_datetime] " +
" FROM (SELECT Row_number() OVER(ORDER BY[" + productM.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTproduct_master] " +
" WHERE 1 = 1 " + sCondition + ") AS [product_m] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString();
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public DataTable fnSelectShopItem(ShopItem shopItem, string categoryName = "")
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[shopItem].[product_id]", shopItem.product_id);
sCondition += PublicApi.fnAddCondition("[shopItem].[category]", shopItem.category);
sCondition += PublicApi.fnAddCondition("[shopItem].[type]", shopItem.type);
sCondition += PublicApi.fnAddCondition("[kind_d].[name] ", "%" + categoryName + "%");
sCondition += PublicApi.fnAddCondition("[shopItem].[status]", "N");
string sInquireSql =
" SELECT [shopItem].*, " +
" [product_m].[name], " +
" ISNULL([product_m].[price] * [shopItem].[fold], [product_m].[price]) [price], " +
" ISNULL([stock].[amount], 0) [amount], " +
" [kind_d].[name] [category_name] " +
" FROM [MNDTshop_item] [shopItem] " +
" LEFT JOIN [MNDTproduct_master] [product_m] " +
" ON [shopItem].[product_id] = [product_m].[product_id] " +
" LEFT JOIN [MNDTproduct_stock] [stock] " +
" ON [stock].[product_id] = [shopItem].[product_id] " +
" AND [stock].[warehouse_id] = '01' " +
" LEFT JOIN [MNDTkind_details] [kind_d] " +
" ON [kind_d].[code_id] = [shopItem].[category] " +
" AND [kind_d].[kind_id] = 'SIC' " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(ShopItem shopItem, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[product_id]", shopItem.product_id);
sCondition += PublicApi.fnAddCondition("[status]", shopItem.status);
string sInquireSql =
" SELECT [shopItem].[product_id], " +
" [shopItem].[category], " +
" [shopItem].[type], " +
" [shopItem].[fold], " +
" [shopItem].[status], " +
" CONVERT(CHAR, [shopItem].[create_datetime], 111) [create_datetime], " +
" [shopItem].[NUM] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + shopItem.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTshop_item] " +
" WHERE 1 = 1 " + sCondition + ") AS [shopItem] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public DataTable fnSelect(ProductStock productStock)
{
string sSql = " SELECT TOP 1 * " +
" FROM [MNDTproduct_stock] [productStock] " +
" WHERE [productStock].[product_id] = '" + productStock.product_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelect(Material material)
{
string sSql = " SELECT TOP 1 * " +
" FROM [MNDTmaterial] [material] " +
" WHERE [material].[material_id] = '" + material.material_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelect(MaterialStock materialStock)
{
string sSql = " SELECT TOP 1 * " +
" FROM [MNDTmaterial_stock] [materialStock] " +
" WHERE [materialStock].[material_id] = '" + materialStock.material_id + "' " +
" AND [materialStock].[warehouse_id] = '" + materialStock.warehouse_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectList()
{
string sSql =
" SELECT [material].[material_id] [value], " +
" [material].[name] " +
" FROM MNDTmaterial AS [material] " +
" WHERE [material].[status] <> 'D' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectList()
{
string sSql =
" SELECT [product_m].[product_id] [value], " +
" [product_m].[name] " +
" FROM MNDTproduct_master AS [product_m] " +
" WHERE [product_m].[status] <> 'D' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectList()
{
string sSql =
" SELECT [company].[company_id] [value], " +
" [company].[name] " +
" FROM MNDTcompany AS [company] " +
" WHERE [status] <> 'D' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectList()
{
string sSql =
" SELECT [program_id] [value], " +
" [name] " +
" FROM [MNDTprogram_master] " +
" WHERE [parent] <> 'ROOT' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectList()
{
string sSql =
" SELECT [account].[id] [value], " +
" [account].[name] " +
" FROM MNDTuser AS [account] " +
" WHERE [status] <> 'D' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelects(ProgramD programD)
{
string sSql = " SELECT [program_id] " +
" ,CASE [read] WHEN 'Y' THEN 'checked' ELSE '' END [read] " +
" ,CASE [write] WHEN 'Y' THEN 'checked' ELSE '' END [write] " +
" ,CASE [execute] WHEN 'Y' THEN 'checked' ELSE '' END [execute] " +
" FROM [MNDTprogram_details] " +
" WHERE [group_id] = '" + programD.group_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectList()
{
string sSql =
" SELECT [receive_m].[order_id] [value], " +
" [receive_m].[order_id] [name] " +
" FROM [MNDTreceive_master] AS [receive_m] " +
" WHERE [status] <> 'D' " +
" AND [complete] = 'Y' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelect(Carousel carousel)
{
string sSql = " SELECT TOP 1 [carousel].[carousel_id] " +
" ,[carousel].[name] " +
" ,[carousel].[remarks] " +
" ,[carousel].[status] " +
" FROM [MNDTCarousel] [carousel] " +
" WHERE [carousel].[carousel_id] = '" + carousel.carousel_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelectList(KindD kindD)
{
string sSql =
" SELECT [kind_d].[code_id] [value], " +
" [kind_d].[name], " +
" [kind_d].[parameter] " +
" FROM [MNDTkind_details] AS [kind_d] " +
" WHERE [kind_id] = '" + kindD.kind_id + "' " +
" AND [status] = 'N' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelect(ReceiveD receiveD)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", receiveD.order_id);
sCondition += PublicApi.fnAddCondition("[seq]", receiveD.seq);
string sInquireSql =
" SELECT [receive_d].* " +
" FROM [MNDTreceive_details] AS [receive_d] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public DataTable fnSelect(User user)
{
string sSql = " SELECT TOP 1 [user].[id] " +
" ,[user].[account] " +
" ,[user].[password] " +
" ,[user].[name] " +
" ,[user].[address] " +
" ,[user].[phone] " +
" ,[user].[status] " +
" FROM [MNDTuser] [user] " +
" WHERE [user].[id] = '" + user.id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelects(ProductStock productStock)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[product_id]", productStock.product_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", productStock.warehouse_id);
sCondition += " ORDER BY [" + productStock.order + "] ";
string sInquireSql =
" SELECT * " +
" FROM MNDTproduct_stock " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public DataTable fnSelects(MaterialStock materialStock)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", materialStock.warehouse_id);
sCondition += " ORDER BY [" + materialStock.order + "] ";
string sInquireSql =
" SELECT * " +
" FROM MNDTmaterial_stock " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public DataTable fnSelect(KindM kindM)
{
string sSql = " SELECT TOP 1 [kind_id] " +
" ,[name] " +
" ,[description] " +
" ,[create_id] " +
" ,CONVERT(char, [create_datetime], 120) [create_datetime] " +
" ,[modify_id] " +
" ,CONVERT(char, [modify_datetime], 120) [modify_datetime] " +
" FROM [MNDTkind_master] " +
" WHERE [kind_id] = '" + kindM.kind_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelect(ProductM productM)
{
string sSql =
" SELECT TOP 1 [product_m].*, " +
" ISNULL((SELECT Sum([product_d].[amount] * [material].[price]) " +
" FROM [MNDTproduct_details] [product_d] " +
" LEFT JOIN [MNDTmaterial] [material] " +
" ON [product_d].[material_id] = " +
" [material].[material_id] " +
" WHERE [product_m].[product_id] = [product_d].[product_id]), 0) [suggest_cost] " +
" FROM [MNDTproduct_master] [product_m] " +
" WHERE [product_m].[product_id] = '" + productM.product_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelects(Material material)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", material.material_id);
sCondition += PublicApi.fnAddCondition("[name]", material.name);
sCondition += PublicApi.fnAddCondition("[status]", material.status);
sCondition += " ORDER BY [" + material.order + "] ";
string sInquireSql =
" SELECT * " +
" FROM MNDTmaterial " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public DataTable fnSelect(ReceiveM receiveM)
{
string sSql = " SELECT TOP 1 [order_id] " +
" ,[id] " +
" ,CONVERT(varchar(10), [datetime], 126) [datetime] " +
" ,[complete] " +
" ,[status] " +
" ,[description] " +
" ,[create_id] " +
" ,CONVERT(char, [create_datetime], 120) [create_datetime] " +
" FROM [MNDTreceive_master] " +
" WHERE [order_id] = '" + receiveM.order_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public DataTable fnSelect(SReturnD sreturnD)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[return_id]", sreturnD.return_id);
sCondition += PublicApi.fnAddCondition("[product_id]", sreturnD.product_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", sreturnD.warehouse_id);
string sInquireSql =
" SELECT [return_d].* " +
" FROM [MNDTSreturn_details] AS [return_d] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}