public string fnCount(PurchaseMTran purchaseMTran)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", purchaseMTran.order_id);
string sCountSql =
" SELECT COUNT(order_id) " +
" FROM [MNDTpurchase_master_tran] " +
" WHERE 1 = 1 " + sCondition;
string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT");
return(sPageSize);
}
public string fnChange(PurchaseM purchaseM, string sIP)
{
string sSql = " UPDATE [dbo].[MNDTpurchase_master] " +
" SET [complete] = 'Y' " +
" WHERE [order_id] = '" + purchaseM.order_id + "' ";
PurchaseMTran purchaseMTran = new PurchaseMTran();
purchaseMTran.OrderId(purchaseM.order_id)
.IP(sIP)
.Status("M")
.CreateId(purchaseM.create_id);
sSql += fnInsertSql(purchaseMTran);
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
private string fnInsertSql(PurchaseMTran purchaseMTran)
{
string sSql =
" INSERT INTO [MNDTpurchase_master_tran] " +
" ([order_id] " +
" ,[ip] " +
" ,[status] " +
" ,[create_id] " +
" ,[create_datetime]) " +
" VALUES " +
" ('" + purchaseMTran.order_id + "' " +
" ,'" + purchaseMTran.ip + "' " +
" ,'" + purchaseMTran.status + "' " +
" ,'" + purchaseMTran.create_id + "' " +
" ,GETDATE()) ";
return(sSql);
}
public string fnUpdate(PurchaseM purchaseM, string sIP)
{
string sSql = " UPDATE [dbo].[MNDTpurchase_master] " +
" SET [id] = '" + purchaseM.id + "' " +
" ,[datetime] = '" + purchaseM.datetime + "' " +
" ,[complete] = '" + purchaseM.complete + "' " +
" ,[status] = '" + purchaseM.status + "' " +
" ,[description] = '" + purchaseM.description + "' " +
" WHERE [order_id] = '" + purchaseM.order_id + "' ";
PurchaseMTran purchaseMTran = new PurchaseMTran();
purchaseMTran.OrderId(purchaseM.order_id)
.IP(sIP)
.Status("D")
.CreateId(purchaseM.create_id);
sSql += fnInsertSql(purchaseMTran);
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(PurchaseMTran purchaseMTran, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", purchaseMTran.order_id);
string sInquireSql =
" SELECT [purchase_m_tran].[ip], " +
" [purchase_m_tran].[status], " +
" [purchase_m_tran].[create_id], " +
" CONVERT(char, [purchase_m_tran].[create_datetime], 120) 'create_datetime' " +
" FROM (SELECT Row_number() OVER (ORDER BY [create_datetime] ASC) NUM, " +
" * " +
" FROM [MNDTpurchase_master_tran] " +
" WHERE 1 = 1 " + sCondition + ") AS [purchase_m_tran] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
