// iPage 第N頁 // iSize 最大顯示數量 public DataTable fnSelects(ReceiveM receiveM, int iPage, int iSize) { int iStart = (iPage - 1) * iSize + 1; int iEnd = iPage * iSize; string sCondition = ""; sCondition += PublicApi.fnAddCondition("[order_id]", receiveM.order_id); sCondition += PublicApi.fnAddCondition("[id]", receiveM.id); sCondition += PublicApi.fnAddCondition("CONVERT(varchar(10), [datetime], 126)", receiveM.datetime); string sSql = " SELECT [receive_m].[NUM], " + " [receive_m].[order_id], " + " [receive_m].[id], " + " [receive_m].[complete], " + " CONVERT(CHAR, [receive_m].[datetime], 111) [datetime] " + " FROM (SELECT Row_number() OVER(ORDER BY[" + receiveM.order + "] ASC) NUM, " + " * " + " FROM [MNDTreceive_master] " + " WHERE 1 = 1 " + sCondition + ") AS [receive_m] " + " WHERE NUM BETWEEN " + iStart.ToString() + " AND " + iEnd.ToString(); return(PublicApi.fnGetDt(sSql, "MNDT")); }
public bool fnIsExist(ReceiveM receiveM) { string sSql = " SELECT COUNT([order_id]) " + " FROM [MNDTreceive_master] " + " WHERE [order_id] = '" + receiveM.order_id + "' "; return(PublicApi.fnGetValue(sSql, "MNDT") == "1"); }
public string fnChangeInsert(ReceiveM receiveM) { string sSql = " INSERT INTO [dbo].[MNDTreceive_master] " + " ([order_id] " + " ,[id] " + " ,[datetime] " + " ,[complete] " + " ,[description] " + " ,[create_id] " + " ,[create_datetime]) " + " VALUES " + " ('" + receiveM.order_id + "' " + " ,'" + receiveM.create_id + "' " + " ,GETDATE() " + " ,'N' " + " ,'' " + " ,'" + receiveM.create_id + "' " + " ,GETDATE()) "; sSql += " INSERT INTO [MNDTreceive_details] " + " ([seq] " + " ,[order_id] " + " ,[material_id] " + " ,[warehouse_id] " + " ,[amount] " + " ,[price] " + " ,[complete] " + " ,[pay_complete] " + " ,[description] " + " ,[create_id] " + " ,[create_datetime] " + " ,[modify_id] " + " ,[modify_datetime]) " + " SELECT Row_number() OVER(ORDER BY[material_id] ASC) NUM " + " ,[order_id] " + " ,[material_id] " + " ,'' " + " ,[modify_amount] " + " ,[modify_price] " + " ,'N' " + " ,'N' " + " ,'' " + " ,'" + receiveM.create_id + "' " + " ,GETDATE() " + " ,'" + receiveM.create_id + "' " + " ,GETDATE() " + " FROM [MNDTpurchase_details] " + " WHERE order_id = '" + receiveM.order_id + "' "; return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }
public string fnDelete(ReceiveM receiveM, string sIP) { string sSql = " UPDATE [MNDTreceive_master] " + " SET [status] = 'D' " + " WHERE [order_id] = '" + receiveM.order_id + "' "; ReceiveMTran receiveMTran = new ReceiveMTran(); receiveMTran.OrderId(receiveM.order_id) .IP(sIP) .Status("D") .CreateId(receiveM.create_id); sSql += fnInsertSql(receiveMTran); return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }
public DataTable fnSelect(ReceiveM receiveM) { string sSql = " SELECT TOP 1 [order_id] " + " ,[id] " + " ,CONVERT(varchar(10), [datetime], 126) [datetime] " + " ,[complete] " + " ,[status] " + " ,[description] " + " ,[create_id] " + " ,CONVERT(char, [create_datetime], 120) [create_datetime] " + " FROM [MNDTreceive_master] " + " WHERE [order_id] = '" + receiveM.order_id + "' "; return(PublicApi.fnGetDt(sSql, "MNDT")); }
public string fnCount(ReceiveM receiveM) { string sCondition = ""; sCondition += PublicApi.fnAddCondition("[order_id]", receiveM.order_id); sCondition += PublicApi.fnAddCondition("[id]", receiveM.id); sCondition += PublicApi.fnAddCondition("CONVERT(varchar(10), [datetime], 126)", receiveM.datetime); string sCountSql = " SELECT COUNT([order_id]) " + " FROM [MNDTreceive_master] " + " WHERE 1 = 1 " + sCondition; string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT"); return(sPageSize); }
// sNum 排行 public string fnSelectOrderId(ReceiveM receiveM, string sNum) { string sCondition = ""; sCondition += PublicApi.fnAddCondition("[order_id]", receiveM.order_id); sCondition += PublicApi.fnAddCondition("[id]", receiveM.id); sCondition += PublicApi.fnAddCondition("CONVERT(varchar(10), [datetime], 126)", receiveM.datetime); string sSql = " SELECT [receive_m].[order_id] " + " FROM (SELECT Row_number() OVER (ORDER BY [" + receiveM.order + "] ASC) NUM, " + " * " + " FROM [MNDTreceive_master] " + " WHERE 1 = 1" + sCondition + ") AS [receive_m] " + " WHERE [receive_m].[NUM] = '" + sNum + "' "; return(PublicApi.fnGetValue(sSql, "MNDT")); }
public string fnUpdate(ReceiveM receiveM, string sIP) { string sSql = " UPDATE [dbo].[MNDTreceive_master] " + " SET [id] = '" + receiveM.id + "' " + " ,[datetime] = '" + receiveM.datetime + "' " + " ,[complete] = '" + receiveM.complete + "' " + " ,[status] = '" + receiveM.status + "' " + " ,[description] = '" + receiveM.description + "' " + " WHERE [order_id] = '" + receiveM.order_id + "' "; ReceiveMTran receiveMTran = new ReceiveMTran(); receiveMTran.OrderId(receiveM.order_id) .IP(sIP) .Status("M") .CreateId(receiveM.create_id); sSql += fnInsertSql(receiveMTran); return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }
public string fnChange(ReceiveM receiveM, string sIP) { string sSql = " UPDATE [dbo].[MNDTreceive_master] " + " SET [complete] = 'Y' " + " WHERE [order_id] = '" + receiveM.order_id + "' " + " UPDATE [dbo].[MNDTreceive_details] " + " SET [complete] = 'Y' " + " WHERE [order_id] = '" + receiveM.order_id + "' "; ReceiveMTran receiveMTran = new ReceiveMTran(); receiveMTran.OrderId(receiveM.order_id) .IP(sIP) .Status("M") .CreateId(receiveM.create_id); sSql += fnInsertSql(receiveMTran); return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }
public string fnInsert(ReceiveM receiveM) { string sSql = " INSERT INTO [dbo].[MNDTreceive_master] " + " ([order_id] " + " ,[id] " + " ,[datetime] " + " ,[complete] " + " ,[description] " + " ,[create_id] " + " ,[create_datetime]) " + " VALUES " + " ('" + receiveM.order_id + "' " + " ,'" + receiveM.id + "' " + " ,'" + receiveM.datetime + "' " + " ,'N' " + " ,'" + receiveM.description + "' " + " ,'" + receiveM.create_id + "' " + " ,GETDATE()) "; return(PublicApi.fnExecuteSQL(sSql, "MNDT")); }