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