public string fnInsert(QuotesD quotesD)
{
string sSql = " INSERT INTO [MNDTquotes_details] " +
" ([order_id] " +
" ,[product_id] " +
" ,[amount] " +
" ,[price] " +
" ,[modify_amount] " +
" ,[modify_price] " +
" ,[description] " +
" ,[create_id] " +
" ,[create_datetime] " +
" ,[modify_id] " +
" ,[modify_datetime]) " +
" VALUES " +
" ('" + quotesD.order_id + "' " +
" ,'" + quotesD.product_id + "' " +
" ,'" + quotesD.amount + "' " +
" ,'" + quotesD.price + "' " +
" ,'" + quotesD.amount + "' " +
" ,'" + quotesD.price + "' " +
" ,'" + quotesD.description + "' " +
" ,'" + quotesD.create_id + "' " +
" ,GETDATE() " +
" ,'" + quotesD.create_id + "' " +
" ,GETDATE()) ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public string fnDelete(QuotesD quotesD)
{
string sSql =
" DELETE [MNDTquotes_details] " +
" WHERE [order_id] = '" + quotesD.order_id + "' " +
" AND [product_id] = '" + quotesD.product_id + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public string fnSelectAmount(QuotesD quotesD)
{
string sSql = " SELECT ISNULL(SUM([modify_amount]), '0') " +
" FROM [MNDTquotes_details] " +
" WHERE [order_id] = '" + quotesD.order_id + "' " +
" AND [product_id] = '" + quotesD.product_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public bool fnIsExist(QuotesD quotesD)
{
string sSql =
" SELECT COUNT([product_id]) " +
" FROM [MNDTquotes_details] " +
" WHERE [order_id] = '" + quotesD.order_id + "' " +
" AND [product_id] = '" + quotesD.product_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public string fnUpdate(QuotesD quotesD)
{
string sSql =
" UPDATE [MNDTquotes_details] " +
" SET [modify_amount] = '" + quotesD.modify_amount + "' " +
" ,[modify_price] = '" + quotesD.modify_price + "' " +
" ,[description] = '" + quotesD.description + "' " +
" ,[modify_id] = '" + quotesD.create_id + "' " +
" ,[modify_datetime] = GETDATE() " +
" WHERE [order_id] = '" + quotesD.order_id + "' " +
" AND [product_id] = '" + quotesD.product_id + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public string fnCount(QuotesD quotesD)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", quotesD.order_id);
string sCountSql =
" SELECT COUNT([order_id]) " +
" FROM [MNDTquotes_details] " +
" WHERE 1 = 1 " + sCondition;
string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT");
return(sPageSize);
}
// 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"));
}
