//导出数据 By 阮创 2017/11/30
public DataTable ExportExcel(string field)
{
string select = "SELECT * FROM";
string from =
@"SELECT T1.F_PRODUCT_ID,
T1.F_QUANTITY,
T1.F_MAKE_DATE,
(CASE T1.F_ENABLE_MARK
WHEN 1 THEN
'有效'
WHEN 0 THEN
'无效'
ELSE
'类型错误'
END) AS F_ENABLE_MARK,
T2.F_NAME F_PRODUCT_NAME,
T1.F_CREATE_TIME
FROM T_PRODUCT_MAKE T1
LEFT JOIN T_PRODUCT T2 ON T1.F_PRODUCT_ID=T2.F_ID
WHERE T1.F_DELETE_MARK = 0 AND T2.F_DELETE_MARK=0 AND T2.F_ENABLE_MARK=1";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
string sql = string.Format("{0} ({1})", select, from);
return(Repository().FindTableBySql(sql));
}
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));
}
//导出数据 By 阮创 2017/11/30
public DataTable ExportExcel(string field)
{
string select = "SELECT * FROM";
string from =
@"SELECT T1.F_NAME,
T1.F_CODE,
T1.F_SPEC,
T1.F_UNIT,
(CASE T1.F_ENABLE_MARK
WHEN 1 THEN
'有效'
WHEN 0 THEN
'无效'
ELSE
'类型错误'
END) AS F_ENABLE_MARK,
T1.F_CREATE_TIME
FROM T_PRODUCT T1
WHERE T1.F_DELETE_MARK = 0";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
string sql = string.Format("{0} ({1})", select, from);
return(Repository().FindTableBySql(sql));
}
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));
}
public DataTable Export(string field, string search)
{
string select = "SELECT * FROM", where = "WHERE 1=1";
string from =
@"SELECT T1.*
FROM V_PART T1";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
if (!string.IsNullOrEmpty(search))
{
where += string.Format(" AND (F_NAME LIKE '%{0}%' OR F_CODE LIKE '%{0}%' OR F_SPEC LIKE '%{0}%')", search);
}
string sql = string.Format("{0} ({1}) TT {2}", select, from, where);
return(new Repository <T_Part>().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));
}
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 ExportExcel(string field, string query)
{
string select = "SELECT * FROM";
if (!string.IsNullOrEmpty(field))
{
select = ConditionBuilder.GetSelectSql(field.JsonToList <Column>());
}
bool flag = false;
string whereBom = "WHERE 1=1";
string bom = "T_BOM";
string whereTotal = "WHERE 1=1";
string querySql = "";
if (!string.IsNullOrEmpty(query))
{
IList conditions = query.JsonToList <Condition>();
foreach (Condition con in conditions)
{
if (con.ParamType == "1")
{
flag = true;
switch (con.Operation)
{
case ConditionOperate.Like:
whereBom += " and " + con.ParamName + " like" + "'%" + con.ParamValue + "%' ";
break;
case ConditionOperate.Equal:
whereBom += " and " + con.ParamName + " = '" + con.ParamValue + "' ";
break;
case ConditionOperate.AfterDay:
whereBom += " and " + con.ParamName + " >= '" + con.ParamValue + "' ";
break;
case ConditionOperate.BeforeDay:
whereBom += " and " + con.ParamName + " <= '" + con.ParamValue + "' ";
break;
default:
break;
}
}
else
{
switch (con.Operation)
{
case ConditionOperate.Like:
whereTotal += " and " + con.ParamName + " like" + "'%" + con.ParamValue + "%' ";
break;
case ConditionOperate.Equal:
whereTotal += " and " + con.ParamName + " = '" + con.ParamValue + "' ";
break;
case ConditionOperate.AfterDay:
whereTotal += " and " + con.ParamName + " >= '" + con.ParamValue + "' ";
break;
case ConditionOperate.BeforeDay:
whereTotal += " and " + con.ParamName + " <= '" + con.ParamValue + "') ";
break;
default:
break;
}
}
}
if (flag)
{
bom = string.Format(@"(SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY F_DATE DESC) RN,
T1.*
FROM T_BOM T1 {0}) TB WHERE RN=1) ", whereBom);
}
querySql = string.Format(@"SELECT * FROM (SELECT T1.F_ID,T1.F_NUM,
T2.F_NAME AS F_BOM_NAME,T2.F_CODE AS F_BOM_CODE,T2.F_VERSION AS F_BOM_VERSION,T2.F_DATE AS F_BOM_DATE,
T3.F_NAME AS F_PART_NAME,T3.F_CODE AS F_PART_CODE,T3.F_SPEC AS F_PART_SPEC
FROM T_BOM_DETAIL T1
INNER JOIN {0} T2
ON T1.F_BOM_ID=T2.F_ID
INNER JOIN V_PART T3
ON T1.F_PART_CODE=T3.F_CODE
WHERE T2.F_DELETE_MARK=0) T {1}", bom, whereTotal);
}
string orderby = "ORDER BY F_PART_CODE DESC";
//return Repository().FindTablePageBySql(querySql, ref gp);
string sql = string.Format("{0} ({1}) TB", select, querySql);
return(Repository().FindTableBySql(sql));
}
public DataTable Export(string field, string query, 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(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(new Repository <T_Part>().FindTableBySql(sql));
}