//导出数据 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)); }