//private string fnInsertsSql(ProductStockTran productStockTran) //{ // string sSql = // " INSERT INTO [MNDTproduct_stock_tran] " + // " ([product_id] " + // " ,[ip] " + // " ,[status] " + // " ,[create_id] " + // " ,[create_datetime]) " + // " SELECT [product_id] " + // " , '" + productStockTran + "' " + // " , '" + productStockTran.status + "' " + // " , '" + productStockTran.create_id + "' " + // " , GETDATE() " + // " FROM [MNDTproduct_stock] " + // " WHERE [product_id] IN (" + productStockTran.product_id + ")"; // return sSql; //} public string fnCount(ProductStockTran productStockTran) { string sCondition = ""; sCondition += PublicApi.fnAddCondition("[product_id]", productStockTran.product_id); sCondition += PublicApi.fnAddCondition("[warehouse_id]", productStockTran.warehouse_id); string sCountSql = " SELECT COUNT(product_id) " + " FROM [MNDTproduct_stock_tran] " + " WHERE 1 = 1 " + sCondition; string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT"); return(sPageSize); }
public string fnUpdateAmountSql(ProductStock productStock) { ProductStockTran productStockTran = new ProductStockTran(); productStockTran.WarehouseId(productStock.warehouse_id) .ProductId(productStock.product_id) .Description("系統進出貨正常變更") .CreateId(productStock.create_id); string sSql = fnInsertSql(productStockTran); sSql += " UPDATE [dbo].[MNDTproduct_stock] " + " SET [amount] = [amount] + " + productStock.amount + " " + " WHERE [product_id] = '" + productStock.product_id + "' " + " AND [warehouse_id] = '" + productStock.warehouse_id + "' "; return(sSql); }
public string fnUpdate(ProductStock productStock, string sDescription, string sIP) { ProductStockTran productStockTran = new ProductStockTran(); productStockTran.WarehouseId(productStock.warehouse_id) .ProductId(productStock.product_id) .Description(sDescription) .CreateId(productStock.create_id); string sSql = fnInsertSql(productStockTran); sSql += " UPDATE [dbo].[MNDTproduct_stock] " + " SET [amount] = '" + productStock.amount + "' " + " ,[safe_amount] = '" + productStock.safe_amount + "' " + " WHERE [product_id] = '" + productStock.product_id + "' " + " AND [warehouse_id] = '" + productStock.warehouse_id + "' "; return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }
// iPage 第N頁 // iSize 最大顯示數量 public DataTable fnSelects(ProductStockTran productStockTran, int iPage, int iSize) { int iStart = (iPage - 1) * iSize + 1; int iEnd = iPage * iSize; string sCondition = ""; sCondition += PublicApi.fnAddCondition("[product_id]", productStockTran.product_id); sCondition += PublicApi.fnAddCondition("[warehouse_id]", productStockTran.warehouse_id); string sInquireSql = " SELECT [product_s].[tran_amount], " + " [product_s].[description], " + " [product_s].[create_id], " + " CONVERT(char, [product_s].[create_datetime], 120) 'create_datetime' " + " FROM (SELECT Row_number() OVER (ORDER BY [create_datetime] ASC) NUM, " + " * " + " FROM [MNDTproduct_stock_tran] " + " WHERE 1 = 1 " + sCondition + ") AS [product_s] " + " WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " "; return(PublicApi.fnGetDt(sInquireSql, "MNDT")); }
private string fnInsertSql(ProductStockTran productStockTran) { string sSql = " INSERT INTO [MNDTproduct_stock_tran] " + " ([warehouse_id], " + " [product_id], " + " [tran_amount], " + " [description], " + " [create_id], " + " [create_datetime]) " + " SELECT [warehouse_id], " + " [product_id], " + " [amount], " + " '" + productStockTran.description + "', " + " '" + productStockTran.create_id + "', " + " GETDATE() " + " FROM [MNDTproduct_stock] " + " WHERE [warehouse_id] = '" + productStockTran.warehouse_id + "' " + " AND [product_id] = '" + productStockTran.product_id + "' "; return(sSql); }