public DataTable Export(string field, string query)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from =
@"SELECT T1.*,
T2.F_NAME F_PART_NAME,
T2.F_SPEC F_PART_SPEC,
T2.F_UNIT F_PART_UNIT
FROM T_PART_BUY T1
LEFT JOIN V_PART T2
ON T2.F_CODE = T1.F_PART_CODE
WHERE T1.F_DELETE_MARK = 0";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
if (!string.IsNullOrEmpty(query))
{
where += ConditionBuilder.GetWhereSql2(query.JsonToList <Condition>());
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(Repository().FindTableBySql(sql));
}
public DataTable GetGridList(GridParam gp)
{
StringBuilder sb = new StringBuilder(@"SELECT * FROM (SELECT T1.*,T2.F_NAME AS F_PRODUCT_NAME FROM T_BOM T1 INNER JOIN T_PRODUCT T2 ON T1.F_PRODUCT_ID=T2.F_ID WHERE T1.F_DELETE_MARK = 0) T2 WHERE 1=1");
if (!string.IsNullOrEmpty(gp.query))
{
sb.Append(ConditionBuilder.GetWhereSql2(gp.query.JsonToList <Condition>()));
}
return(Repository().FindTablePageBySql(sb.ToString(), ref gp));
}
//获取表格列表 By 阮创 2017/11/30
public List <T_User_Log> GetGridList(GridParam gp)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from = "SELECT T1.* FROM T_USER_LOG T1 WHERE T1.F_DELETE_MARK = 0";
if (!string.IsNullOrEmpty(gp.query))
{
where += ConditionBuilder.GetWhereSql2(gp.query.JsonToList <Condition>());
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(Repository().FindListPageBySql(sql, ref gp));
}
public DataTable Export(string field, string query)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from = @"
SELECT
T1.*, T2.F_OPER_TIME F_PREV_OPER_TIME,
(
CASE
WHEN T2.F_OPER_TIME IS NULL THEN
GETDATE()
ELSE
T2.F_OPER_TIME + T1.F_PERIOD
END
) F_NEXT_OPER_TIME
FROM
(
SELECT
T.*
FROM
T_EQUIPMENT T
WHERE
T.F_DELETE_MARK = 0
) T1
LEFT JOIN (
SELECT
T.F_EQUIPMENT_ID,
MAX (T.F_OPER_TIME) F_OPER_TIME
FROM
T_MAINTENANCE T
WHERE
T.F_DELETE_MARK = 0
GROUP BY
T.F_EQUIPMENT_ID
) T2 ON T2.F_EQUIPMENT_ID = T1.F_ID";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
if (!string.IsNullOrEmpty(query))
{
where += ConditionBuilder.GetWhereSql2(query.JsonToList <Condition>());
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(Repository().FindTableBySql(sql));
}
//导出数据 By 阮创 2017/11/30
public DataTable Export(string field, string query)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from =
@"SELECT T1.F_ACCOUNT,
T1.F_IPADDRESS,
T1.F_MENU,
T1.F_ACTION,
(CASE T1.F_RESULT_MARK
WHEN 1 THEN
'成功'
WHEN 2 THEN
'失败'
WHEN 3 THEN
'异常'
ELSE
'类型错误'
END) AS F_RESULT_MARK,
(CASE T1.F_ENABLE_MARK
WHEN 1 THEN
'有效'
WHEN 0 THEN
'无效'
ELSE
'类型错误'
END) AS F_ENABLE_MARK,
T1.F_CREATE_TIME
FROM T_USER_LOG T1
WHERE T1.F_DELETE_MARK = 0";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
if (!string.IsNullOrEmpty(query))
{
where += ConditionBuilder.GetWhereSql2(query.JsonToList <Condition>());
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(Repository().FindTableBySql(sql));
}
//获取表格列表 By 阮创 2017/11/30
public List <T_Product_Make> GetGridList(GridParam gp)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from =
@"SELECT T1.*
FROM T_PRODUCT_MAKE T1
WHERE T1.F_DELETE_MARK = 0";
if (!string.IsNullOrEmpty(gp.search))
{
where += string.Format(" AND (F_NAME LIKE '%{0}%' OR F_CODE LIKE '%{0}%')", gp.search);
}
if (!string.IsNullOrEmpty(gp.query))
{
where += ConditionBuilder.GetWhereSql2(gp.query.JsonToList <Condition>()).Replace("F_Make_Date", "to_date(F_Make_Date,'yyyy-mm-dd')");
}
string sql = string.Format("{0} ({1}) {2}", select, from, where);
return(Repository().FindListPageBySql(sql, ref gp));
}
public DataTable ExportExcel(string field, string query)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from = @"SELECT T2.F_NAME AS F_PRODUCT_NAME,T1.F_NAME,T1.F_CODE,T1.F_VERSION,T1.F_DATE,
CASE T1.F_ENABLE_MARK WHEN 1 THEN '有效' ELSE '无效' END F_ENABLE_MARK ,
T1.F_CREATE_BY,T1.F_CREATE_TIME FROM T_BOM T1 INNER JOIN T_PRODUCT T2 ON T1.F_PRODUCT_ID=T2.F_ID
WHERE T1.F_DELETE_MARK = 0";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
if (!string.IsNullOrEmpty(query))
{
where += ConditionBuilder.GetWhereSql2(query.JsonToList <Condition>());
}
string orderby = "order by 创建时间 DESC";
string sql = string.Format("{0} ({1}) T {2} {3}", select, from, where, orderby);
return(Repository().FindTableBySql(sql));
}
public DataTable GetGridList(GridParam gp)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from =
@"SELECT T1.*,
T2.F_NAME F_PART_NAME,
T2.F_SPEC F_PART_SPEC,
T2.F_UNIT F_PART_UNIT
FROM T_PART_BUY T1
LEFT JOIN V_PART T2
ON T2.F_CODE = T1.F_PART_CODE
WHERE T1.F_DELETE_MARK = 0";
if (!string.IsNullOrEmpty(gp.query))
{
where += ConditionBuilder.GetWhereSql2(gp.query.JsonToList <Condition>());
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(Repository().FindTablePageBySql(sql, ref gp));
}
//获取表格列表 By 阮创 2017/11/30
public DataTable GetGridList(GridParam gp, string F_Equipment_Id = null)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from = @"
SELECT
T1.*, T2.F_CODE F_EQUIPMENT_CODE
FROM
T_MAINTENANCE T1
LEFT JOIN T_EQUIPMENT T2 ON T2.F_ID = T1.F_EQUIPMENT_ID
WHERE
T1.F_DELETE_MARK = 0" ;
if (!string.IsNullOrEmpty(F_Equipment_Id))
{
from += string.Format(" AND T1.F_EQUIPMENT_ID = '{0}'", F_Equipment_Id);
}
if (!string.IsNullOrEmpty(gp.query))
{
where += ConditionBuilder.GetWhereSql2(gp.query.JsonToList <Condition>());
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(Repository().FindTablePageBySql(sql, ref gp));
}
public DataTable GetGridList(GridParam gp, string productId = null)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from = @"
SELECT
T1.*
FROM
(
SELECT
T1.F_NAME F_PART_NAME,
T1.F_CODE F_PART_CODE,
T1.F_SPEC F_PART_SPEC,
T1.F_UNIT F_PART_UNIT,
T2.F_QUANTITY F_STOCK_QUANTITY,
T3.F_QUANTITY F_CONSUME_QUANTITY,
T4.F_QUANTITY F_ONWAY_QUANTITY,
ISNULL(T2.F_QUANTITY, 0) - ISNULL(T3.F_QUANTITY, 0) F_AVAILABLE_QUANTITY
FROM
(
SELECT
T.F_NAME,
T.F_CODE,
T.F_SPEC,
T.F_UNIT
FROM
V_PART T
) T1
LEFT JOIN (
SELECT
T.F_PART_CODE,
T.F_QUANTITY
FROM
V_PART_STOCK T
) T2 ON T2.F_PART_CODE = T1.F_CODE
LEFT JOIN (
SELECT
T3.F_PART_CODE,
SUM (T1.F_QUANTITY * T3.F_NUM) F_QUANTITY
FROM
(
SELECT
T.F_PRODUCT_ID,
SUM (T.F_QUANTITY) F_QUANTITY
FROM
T_PRODUCT_MAKE T
WHERE
T.F_DELETE_MARK = 0
AND T.F_MAKE_DATE > GETDATE()
GROUP BY
T.F_PRODUCT_ID
) T1
LEFT JOIN (
SELECT
T1.F_PRODUCT_ID,
T1.F_VERSION,
T2.F_ID
FROM
(
SELECT
T.F_PRODUCT_ID,
MAX (T.F_VERSION) F_VERSION
FROM
T_BOM T
WHERE
T.F_DELETE_MARK = 0
AND T.F_DATE < GETDATE()
GROUP BY
T.F_PRODUCT_ID
) T1
LEFT JOIN T_BOM T2 ON T1.F_PRODUCT_ID = T2.F_PRODUCT_ID
AND T1.F_VERSION = T2.F_VERSION
) T2 ON T2.F_PRODUCT_ID = T1.F_PRODUCT_ID
LEFT JOIN (
SELECT
T.F_BOM_ID,
T.F_PART_CODE,
T.F_NUM
FROM
T_BOM_DETAIL T
WHERE
T.F_DELETE_MARK = 0
) T3 ON T3.F_BOM_ID = T2.F_ID
GROUP BY
T3.F_PART_CODE
) T3 ON T3.F_PART_CODE = T1.F_CODE
LEFT JOIN (
SELECT
T.F_PART_CODE,
SUM (
CASE
WHEN T.F_ATA IS NULL THEN
T.F_QUANTITY
END
) F_QUANTITY
FROM
T_PART_BUY T
WHERE
T.F_DELETE_MARK = 0
GROUP BY
T.F_PART_CODE
) T4 ON T4.F_PART_CODE = T1.F_CODE
) T1" ;
if (!string.IsNullOrEmpty(productId))
{
from += string.Format(@"
RIGHT JOIN (
SELECT DISTINCT
T2.F_PART_CODE
FROM
(
SELECT
T1.F_PRODUCT_ID,
T1.F_VERSION,
T2.F_ID
FROM
(
SELECT
T.F_PRODUCT_ID,
MAX (T.F_VERSION) F_VERSION
FROM
T_BOM T
WHERE
T.F_DELETE_MARK = 0
AND F_PRODUCT_ID = {0}
AND T.F_DATE < GETDATE()
GROUP BY
T.F_PRODUCT_ID
) T1
LEFT JOIN T_BOM T2 ON T1.F_PRODUCT_ID = T2.F_PRODUCT_ID
AND T1.F_VERSION = T2.F_VERSION
) T1
LEFT JOIN (
SELECT
T.F_BOM_ID,
T.F_PART_CODE
FROM
T_BOM_DETAIL T
WHERE
T.F_DELETE_MARK = 0
) T2 ON T2.F_BOM_ID = T1.F_ID
) T2 ON T2.F_PART_CODE = T1.F_PART_CODE", productId);
}
if (!string.IsNullOrEmpty(gp.query))
{
where += ConditionBuilder.GetWhereSql2(gp.query.JsonToList <Condition>());
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(new Repository <T_Part>().FindTablePageBySql(sql, ref gp));
}
