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