//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);
}
