public string fnInsert(ReturnD returnD)
{
string sSql =
" INSERT INTO [MNDTreturn_details] " +
" ([seq] " +
" ,[return_id] " +
" ,[material_id] " +
" ,[warehouse_id] " +
" ,[amount] " +
" ,[price] " +
" ,[complete] " +
" ,[pay_complete] " +
" ,[description] " +
" ,[create_id] " +
" ,[create_datetime] " +
" ,[modify_id] " +
" ,[modify_datetime]) " +
" VALUES " +
" ('" + returnD.seq + "' " +
" ,'" + returnD.return_id + "' " +
" ,'" + returnD.material_id + "' " +
" ,'" + returnD.warehouse_id + "' " +
" ,'" + returnD.amount + "' " +
" ,'" + returnD.price + "' " +
" ,'N' " +
" ,'N' " +
" ,'" + returnD.description + "' " +
" ,'" + returnD.create_id + "' " +
" ,GETDATE() " +
" ,'" + returnD.create_id + "' " +
" ,GETDATE()) ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
// iPage 第N頁
// iSize 最大顯示數量
public DataTable fnSelects(ReturnD returnD, int iPage, int iSize)
{
int iStart = (iPage - 1) * iSize + 1;
int iEnd = iPage * iSize;
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[return_id]", returnD.return_id);
string sInquireSql =
" SELECT [return_d].[seq], " +
" [return_d].[material_id], " +
" [return_d].[warehouse_id], " +
" [return_d].[amount], " +
" [return_d].[price], " +
" CASE [return_d].[complete] WHEN 'Y' THEN 'checked' ELSE '' END [complete], " +
" CASE [return_d].[pay_complete] WHEN 'Y' THEN 'checked' ELSE '' END [pay_complete], " +
" [return_d].[description] " +
" FROM (SELECT Row_number() OVER (ORDER BY [seq] ASC) NUM, " +
" * " +
" FROM [MNDTreturn_details] " +
" WHERE 1 = 1 " + sCondition + ") AS [return_d] " +
" WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString() + " ";
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public string fnDelete(ReturnD returnD)
{
string sSql =
" DELETE [MNDTreturn_details] " +
" WHERE [return_id] = '" + returnD.return_id + "' " +
" AND [seq] = '" + returnD.seq + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public bool fnIsExist(ReturnD returnD)
{
string sSql =
" SELECT COUNT([material_id]) " +
" FROM [MNDTreturn_details] " +
" WHERE [return_id] = '" + returnD.return_id + "' " +
" AND [material_id] = '" + returnD.material_id + "' " +
" AND [warehouse_id] = '" + returnD.warehouse_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public string fnAudits(ReturnD returnD)
{
string sSql =
" UPDATE [MNDTreturn_details] " +
" SET [complete] = '" + returnD.complete + "' " +
" ,[modify_id] = '" + returnD.create_id + "' " +
" ,[modify_datetime] = GETDATE() " +
" WHERE [return_id] = '" + returnD.return_id + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public string fnCount(ReturnD returnD)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[return_id]", returnD.return_id);
string sCountSql =
" SELECT COUNT([return_id]) " +
" FROM [MNDTreturn_details] " +
" WHERE 1 = 1 " + sCondition;
string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT");
return(sPageSize);
}
public DataTable fnSelect(ReturnD returnD)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[return_id]", returnD.return_id);
sCondition += PublicApi.fnAddCondition("[material_id]", returnD.material_id);
sCondition += PublicApi.fnAddCondition("[warehouse_id]", returnD.warehouse_id);
string sInquireSql =
" SELECT [return_d].* " +
" FROM [MNDTreturn_details] AS [return_d] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public string fnUpdate(ReturnD returnD)
{
string sSql =
" UPDATE [MNDTreturn_details] " +
" SET [material_id] = '" + returnD.material_id + "' " +
" ,[warehouse_id] = '" + returnD.warehouse_id + "' " +
" ,[amount] = '" + returnD.amount + "' " +
" ,[price] = '" + returnD.price + "' " +
" ,[complete] = '" + returnD.complete + "' " +
" ,[pay_complete] = '" + returnD.pay_complete + "' " +
" ,[description] = '" + returnD.description + "' " +
" ,[modify_id] = '" + returnD.create_id + "' " +
" ,[modify_datetime] = GETDATE() " +
" WHERE [return_id] = '" + returnD.return_id + "' " +
" AND [seq] = '" + returnD.seq + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
public DataTable fnSelects(ReturnD returnD)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[return_id]", returnD.return_id);
string sInquireSql =
" SELECT [return_d].[seq], " +
" [return_d].[material_id], " +
" [return_d].[warehouse_id], " +
" [return_d].[amount], " +
" [return_d].[price], " +
" CASE [return_d].[complete] WHEN 'Y' THEN 'checked' ELSE '' END [complete], " +
" CASE [return_d].[pay_complete] WHEN 'Y' THEN 'checked' ELSE '' END [pay_complete], " +
" [return_d].[description] " +
" FROM [MNDTreturn_details] AS [return_d] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetDt(sInquireSql, "MNDT"));
}
public string fnGetSeq(ReturnD returnD)
{
return(fnGetSeq(returnD.return_id));
}