public bool fnIsExist(KindM kindM)
{
string sSql = " SELECT COUNT([kind_id]) " +
" FROM [MNDTkind_master] " +
" WHERE [kind_id] = '" + kindM.kind_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public string fnUpdate(KindM kindM)
{
string sSql = " UPDATE [dbo].[MNDTkind_master] " +
" SET [name] = '" + kindM.name + "' " +
" ,[description] = '" + kindM.description + "' " +
" ,[modify_id] = '" + kindM.create_id + "' " +
" ,[modify_datetime] = GETDATE() " +
" WHERE [kind_id] = '" + kindM.kind_id + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public string fnDelete(KindM kindM)
{
string sDetailsSql = " SELECT COUNT([kind_id]) FROM [MNDTkind_details] WHERE [kind_id] = '" + kindM.kind_id + "' ";
string sCount = PublicApi.fnGetValue(sDetailsSql, "MNDT");
if (sCount == "0")
{
string sSql = " DELETE [MNDTkind_master] " +
" WHERE [kind_id] = '" + kindM.kind_id + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
return("錯誤訊息：明細資料大於一筆。");
}
public DataTable fnSelect(KindM kindM)
{
string sSql = " SELECT TOP 1 [kind_id] " +
" ,[name] " +
" ,[description] " +
" ,[create_id] " +
" ,CONVERT(char, [create_datetime], 120) [create_datetime] " +
" ,[modify_id] " +
" ,CONVERT(char, [modify_datetime], 120) [modify_datetime] " +
" FROM [MNDTkind_master] " +
" WHERE [kind_id] = '" + kindM.kind_id + "' ";
return(PublicApi.fnGetDt(sSql, "MNDT"));
}
public string fnCount(KindM kindM)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[kind_id]", kindM.kind_id);
sCondition += PublicApi.fnAddCondition("[name]", kindM.name);
string sCountSql =
" SELECT COUNT([kind_id]) " +
" FROM [MNDTkind_master] " +
" WHERE 1 = 1 " + sCondition;
string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT");
return(sPageSize);
}
// sNum 排行
public string fnSelectKindId(KindM kindM, string sNum)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[kind_id]", kindM.kind_id);
sCondition += PublicApi.fnAddCondition("[name]", kindM.name);
string sSql = " SELECT [kind_m].[kind_id] " +
" FROM (SELECT Row_number() OVER (ORDER BY [" + kindM.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTkind_master] " +
" WHERE 1 = 1" + sCondition + ") AS [kind_m] " +
" WHERE [kind_m].[NUM] = '" + sNum + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public string fnInsert(KindM kindM)
{
string sSql = " INSERT INTO [dbo].[MNDTkind_master] " +
" ([kind_id] " +
" ,[name] " +
" ,[description] " +
" ,[create_id] " +
" ,[create_datetime] " +
" ,[modify_id] " +
" ,[modify_datetime]) " +
" VALUES " +
" ('" + kindM.kind_id + "' " +
" ,'" + kindM.name + "' " +
" ,'" + kindM.description + "' " +
" ,'" + kindM.create_id + "' " +
" ,GETDATE() " +
" ,'" + kindM.create_id + "' " +
" ,GETDATE()) ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(KindM kindM, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[kind_id]", kindM.kind_id);
sCondition += PublicApi.fnAddCondition("[name]", kindM.name);
string sInquireSql =
" SELECT [kind_m].[NUM], " +
" [kind_m].[kind_id], " +
" [kind_m].[name], " +
" CONVERT(CHAR, [kind_m].[create_datetime], 111) [create_datetime], " +
" CONVERT(CHAR, [kind_m].[modify_datetime], 111) [modify_datetime] " +
" FROM (SELECT Row_number() OVER(ORDER BY[" + kindM.order + "] ASC) NUM, " +
" * " +
" FROM [MNDTkind_master] " +
" WHERE 1 = 1 " + sCondition + ") AS [kind_m] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString();
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
