public string fnInsertSql(PurchaseD purchaseD) { string sSql = " INSERT INTO [MNDTpurchase_details] " + " ([order_id] " + " ,[material_id] " + " ,[amount] " + " ,[price] " + " ,[modify_amount] " + " ,[modify_price] " + " ,[description] " + " ,[create_id] " + " ,[create_datetime] " + " ,[modify_id] " + " ,[modify_datetime]) " + " VALUES " + " ('" + purchaseD.order_id + "' " + " ,'" + purchaseD.material_id + "' " + " ,'" + purchaseD.amount + "' " + " ,'" + purchaseD.price + "' " + " ,'" + purchaseD.amount + "' " + " ,'" + purchaseD.price + "' " + " ,'" + purchaseD.description + "' " + " ,'" + purchaseD.create_id + "' " + " ,GETDATE() " + " ,'" + purchaseD.create_id + "' " + " ,GETDATE()) "; return(sSql); }
public string fnDelete(PurchaseD purchaseD) { string sSql = " DELETE [MNDTpurchase_details] " + " WHERE [order_id] = '" + purchaseD.order_id + "' " + " AND [material_id] = '" + purchaseD.material_id + "' "; return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }
public string fnSelectAmount(PurchaseD purchaseD) { string sSql = " SELECT ISNULL(SUM([modify_amount]), '0') " + " FROM [MNDTpurchase_details] " + " WHERE [order_id] = '" + purchaseD.order_id + "' " + " AND [material_id] = '" + purchaseD.material_id + "' "; return(PublicApi.fnGetValue(sSql, "MNDT")); }
public bool fnIsExist(PurchaseD purchaseD) { string sSql = " SELECT COUNT([material_id]) " + " FROM [MNDTpurchase_details] " + " WHERE [order_id] = '" + purchaseD.order_id + "' " + " AND [material_id] = '" + purchaseD.material_id + "' "; return(PublicApi.fnGetValue(sSql, "MNDT") == "1"); }
public string fnAccumulateUpdateSql(PurchaseD purchaseD) { string sSql = " UPDATE [MNDTpurchase_details] " + " SET [modify_amount] = modify_amount + '" + purchaseD.modify_amount + "' " + " ,[modify_price] = modify_price + '" + purchaseD.modify_price + "' " + " ,[modify_id] = '" + purchaseD.create_id + "' " + " ,[modify_datetime] = GETDATE() " + " WHERE [order_id] = '" + purchaseD.order_id + "' " + " AND [material_id] = '" + purchaseD.material_id + "' "; return(sSql); }
public string fnUpdate(PurchaseD purchaseD) { string sSql = " UPDATE [MNDTpurchase_details] " + " SET [modify_amount] = '" + purchaseD.modify_amount + "' " + " ,[modify_price] = '" + purchaseD.modify_price + "' " + " ,[description] = '" + purchaseD.description + "' " + " ,[modify_id] = '" + purchaseD.create_id + "' " + " ,[modify_datetime] = GETDATE() " + " WHERE [order_id] = '" + purchaseD.order_id + "' " + " AND [material_id] = '" + purchaseD.material_id + "' "; return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }
public string fnCount(PurchaseD purchaseD) { string sCondition = ""; sCondition += PublicApi.fnAddCondition("[order_id]", purchaseD.order_id); string sCountSql = " SELECT COUNT([order_id]) " + " FROM [MNDTpurchase_details] " + " WHERE 1 = 1 " + sCondition; string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT"); return(sPageSize); }
// iPage 第N頁 // iSize 最大顯示數量 public DataTable fnSelects(PurchaseD purchaseD, int iPage, int iSize) { int iStart = (iPage - 1) * iSize + 1; int iEnd = iPage * iSize; string sCondition = ""; sCondition += PublicApi.fnAddCondition("[order_id]", purchaseD.order_id); string sInquireSql = " SELECT [purchase_d].[material_id], " + " [purchase_d].[amount], " + " [purchase_d].[price], " + " [purchase_d].[modify_amount], " + " [purchase_d].[modify_price], " + " [purchase_d].[description] " + " FROM (SELECT Row_number() OVER (ORDER BY [material_id] ASC) NUM, " + " * " + " FROM [MNDTpurchase_details] " + " WHERE 1 = 1 " + sCondition + ") AS [purchase_d] " + " WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " "; return(PublicApi.fnGetDt(sInquireSql, "MNDT")); }
public string fnInsert(PurchaseD purchaseD) { return(PublicApi.fnExecuteSQL(fnInsertSql(purchaseD), "MNDT")); }