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