// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(MaterialStock materialStock, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material_id]", materialStock.material_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", materialStock.warehouse_id);
string sInquireSql =
" SELECT [materialStock].[material_id], " +
" [materialStock].[warehouse_id], " +
" [materialStock].[amount], " +
" [materialStock].[safe_amount], " +
" [materialStock].[NUM] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + materialStock.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTmaterial_stock] " +
" WHERE 1 = 1 " + sCondition + ") AS [materialStock] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(Carousel carousel, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[carousel_id]", carousel.carousel_id);
sCondition += PublicApi.fnAddCondition("[status]", carousel.status);
string sInquireSql =
" SELECT [carousel].[carousel_id], " +
" [carousel].[name], " +
" [carousel].[status], " +
" CONVERT(CHAR, [carousel].[create_datetime], 111) [create_datetime], " +
" [carousel].[NUM] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + carousel.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTCarousel] " +
" WHERE 1 = 1 " + sCondition + ") AS [carousel] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// sNum 排行
public string fnSelectGroupId(GroupM groupM, string sNum)
{
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 sSql = " SELECT [group_m].[group_id] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + groupM.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTgroup_master] " +
" WHERE 1 = 1" + sCondition + ") AS [group_m] " +
" WHERE [group_m].[NUM] = '" + sNum + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public DataTable fnExport(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 [material].[material_id] '編號', " +
" [material].[company_id] '公司ID', " +
" [material].[name] '名子', " +
" [material].[unit] '單位', " +
" [material].[currency] '貨幣', " +
" [material].[price] '價格', " +
" [material].[shelf_life] '期限', " +
" [material].[description] '描述', " +
" [material].[status] '狀態' " +
" FROM MNDTmaterial AS [material] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(SReturnD sreturnD, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[return_id]", sreturnD.return_id);
string sInquireSql =
" SELECT [return_d].[seq], " +
" [return_d].[product_id], " +
" [return_d].[warehouse_id], " +
" [return_d].[amount], " +
" [return_d].[price], " +
" [return_d].[description] " +
" FROM (SELECT Row_number() OVER (ORDER BY [seq] ASC) NUM, " +
" * " +
" FROM [MNDTSreturn_details] " +
" WHERE 1 = 1 " + sCondition + ") AS [return_d] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(KindM kindM, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[kind_id]", kindM.kind_id);
sCondition += PublicApi.fnAddCondition("[name]", kindM.name);
string sInquireSql =
" SELECT [kind_m].[NUM], " +
" [kind_m].[kind_id], " +
" [kind_m].[name], " +
" CONVERT(CHAR, [kind_m].[create_datetime], 111) [create_datetime], " +
" CONVERT(CHAR, [kind_m].[modify_datetime], 111) [modify_datetime] " +
" FROM (SELECT Row_number() OVER(ORDER BY[" + kindM.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTkind_master] " +
" WHERE 1 = 1 " + sCondition + ") AS [kind_m] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString();
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(QuotesD quotesD, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", quotesD.order_id);
string sInquireSql =
" SELECT [quotes_d].[product_id], " +
" [quotes_d].[amount], " +
" [quotes_d].[price], " +
" [quotes_d].[modify_amount], " +
" [quotes_d].[modify_price], " +
" [quotes_d].[description] " +
" FROM (SELECT Row_number() OVER (ORDER BY [product_id] ASC) NUM, " +
" * " +
" FROM [MNDTquotes_details] " +
" WHERE 1 = 1 " + sCondition + ") AS [quotes_d] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public DataTable fnReport(string sCompanyId, string sDateS, string sDateE)
{
string sCondition1 = "";
string sCondition2 = "";
sCondition1 += PublicApi.fnAddCondition("[material].[company_id]", sCompanyId);
sCondition2 = sCondition1;
if (sDateS.Length > 0)
{
sCondition2 += " AND CONVERT(varchar(10), [receive_m].[datetime], 126) >= '" + sDateS + "' ";
}
if (sDateS.Length > 0)
{
sCondition2 += " AND CONVERT(varchar(10), [receive_m].[datetime], 126) <= '" + sDateE + "' ";
}
string sSql =
" SELECT [data].* " +
" FROM (SELECT [material].[company_id], " +
" [receive_d].[material_id], " +
" [material].[name], " +
" [kind_d].[name] [unit_name], " +
" [company].[name] [company_name], " +
" [company].[email], " +
" [company].[address], " +
" [company].[tax_id], " +
" [company].[tel], " +
" [company].[phone], " +
" Sum([receive_d].[amount]) [amount], " +
" Sum([receive_d].[price]) [price], " +
" '進貨' [type] " +
" FROM [mndtreceive_master] [receive_m] " +
" LEFT JOIN [mndtreceive_details] [receive_d] " +
" ON [receive_m].[order_id] = [receive_d].[order_id] " +
" LEFT JOIN [mndtmaterial] [material] " +
" ON [receive_d].[material_id] = [material].[material_id] " +
" LEFT JOIN [mndtkind_details] [kind_d] " +
" ON [kind_d].[kind_id] = 'U1' " +
" AND [material].[unit] = [kind_d].[code_id] " +
" LEFT JOIN [mndtcompany] [company] " +
" ON [material].[company_id] = [company].[company_id] " +
" WHERE [receive_m].[status] <> 'D' " +
" AND [receive_m].[complete] = 'Y' " +
" AND [receive_d].[pay_complete] <> 'Y' " + sCondition2 +
" GROUP BY [material].[company_id], " +
" [material].[name], " +
" [kind_d].[name], " +
" [company].[name], " +
" [company].[email], " +
" [company].[address], " +
" [company].[tax_id], " +
" [company].[tel], " +
" [company].[phone], " +
" [receive_d].[material_id] " +
" UNION " +
" SELECT [material].[company_id], " +
" [return_d].[material_id], " +
" [material].[name], " +
" [kind_d].[name] [unit_name], " +
" [company].[name] [company_name], " +
" [company].[email], " +
" [company].[address], " +
" [company].[tax_id], " +
" [company].[tel], " +
" [company].[phone], " +
" -Sum([return_d].[amount]) [amount], " +
" -Sum([return_d].[price]) [price], " +
" '退貨' [type] " +
" FROM [mndtreturn_master] [return_m] " +
" LEFT JOIN [mndtreturn_details] [return_d] " +
" ON [return_m].[return_id] = [return_d].[return_id] " +
" LEFT JOIN [mndtmaterial] [material] " +
" ON [return_d].[material_id] = [material].[material_id] " +
" LEFT JOIN [mndtkind_details] [kind_d] " +
" ON [kind_d].[kind_id] = 'U1' " +
" AND [material].[unit] = [kind_d].[code_id] " +
" LEFT JOIN [mndtcompany] [company] " +
" ON [material].[company_id] = [company].[company_id] " +
" WHERE [return_m].[status] <> 'D' " +
" AND [return_m].[complete] = 'Y' " +
" AND [return_d].[pay_complete] <> 'Y' " + sCondition1 +
" GROUP BY [material].[company_id], " +
" [material].[name], " +
" [kind_d].[name], " +
" [company].[name], " +
" [company].[email], " +
" [company].[address], " +
" [company].[tax_id], " +
" [company].[tel], " +
" [company].[phone], " +
" [return_d].[material_id]) [data] " +
" ORDER BY [data].[type] DESC ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}