public string fnCount(ProductMTran productMTran)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[product_id]", productMTran.product_id);
string sCountSql =
" SELECT COUNT(product_id) " +
" FROM [MNDTproduct_master_tran] " +
" WHERE 1 = 1 " + sCondition;
string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT");
return(sPageSize);
}
private string fnInsertSql(ProductMTran productMTran)
{
string sSql =
" INSERT INTO [MNDTproduct_master_tran] " +
" ([product_id] " +
" ,[ip] " +
" ,[status] " +
" ,[create_id] " +
" ,[create_datetime]) " +
" VALUES " +
" ('" + productMTran.product_id + "' " +
" , '" + productMTran.ip + "' " +
" , '" + productMTran.status + "' " +
" , '" + productMTran.create_id + "' " +
" , GETDATE()) ";
return(sSql);
}
private string fnInsertsSql(ProductMTran productMTran)
{
string sSql =
" INSERT INTO [MNDTproduct_master_tran] " +
" ([product_id] " +
" ,[ip] " +
" ,[status] " +
" ,[create_id] " +
" ,[create_datetime]) " +
" SELECT [product_id] " +
" , '" + productMTran.ip + "' " +
" , '" + productMTran.status + "' " +
" , '" + productMTran.create_id + "' " +
" , GETDATE() " +
" FROM [MNDTmaterial] " +
" WHERE [product_id] IN (" + productMTran.product_id + ")";
return(sSql);
}
public string fnDelete(ProductM productM, string sIP)
{
string sDetailsSql = " SELECT COUNT([product_id]) FROM [MNDTproduct_details] WHERE [product_id] = '" + productM.product_id + "' ";
string sCount = PublicApi.fnGetValue(sDetailsSql, "MNDT");
if (sCount == "0")
{
string sSql = " DELETE [MNDTproduct_master] " +
" WHERE [product_id] = '" + productM.product_id + "' ";
ProductMTran productMTran = new ProductMTran();
productMTran.ProductId(productM.product_id)
.IP(sIP)
.Status("D")
.CreateId(productM.create_id);
sSql += fnInsertSql(productMTran);
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
return("錯誤訊息：明細資料大於一筆。");
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(ProductMTran productMTran, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[product_id]", productMTran.product_id);
string sInquireSql =
" SELECT [product_m_t].[product_id], " +
" [product_m_t].[ip], " +
" [product_m_t].[status], " +
" [product_m_t].[create_id], " +
" CONVERT(char, [product_m_t].[create_datetime], 120) 'create_datetime' " +
" FROM (SELECT Row_number() OVER (ORDER BY [create_datetime] ASC) NUM, " +
" * " +
" FROM [MNDTproduct_master_tran] " +
" WHERE 1 = 1 " + sCondition + ") AS [product_m_t] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public string fnUpdate(ProductM productM, string sIP)
{
string sSql = " UPDATE [dbo].[MNDTproduct_master] " +
" SET [company_id] = '" + productM.company_id + "' " +
" ,[name] = '" + productM.name + "' " +
" ,[unit] = '" + productM.unit + "' " +
" ,[currency] = '" + productM.currency + "' " +
" ,[cost] = '" + productM.cost + "' " +
" ,[price] = '" + productM.price + "' " +
" ,[shelf_life] = '" + productM.shelf_life + "' " +
" ,[description] = '" + productM.description + "' " +
" ,[status] = '" + productM.status + "' " +
" WHERE [product_id] = '" + productM.product_id + "' ";
ProductMTran productMTran = new ProductMTran();
productMTran.ProductId(productM.product_id)
.IP(sIP)
.Status("M")
.CreateId(productM.create_id);
sSql += fnInsertSql(productMTran);
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
