/// <summary> /// 获取记录总数 /// </summary> public int GetRecordCount(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) FROM TMC_ROLL_APPLY "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 根据发运单号获取该发运单计划总数 /// </summary> /// <param name="fydh">发运单号</param> /// <returns></returns> public int GetJHCount(string fydh) { StringBuilder strSql = new StringBuilder(); strSql.Append("select SUM(N_FYZS) FROM TMD_DISPATCHDETAILS WHERE 1=1"); if (fydh.Trim() != "") { strSql.Append(" AND C_DISPATCH_ID='" + fydh + "' "); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 根据发运单号获取已做实绩数量 /// </summary> /// <param name="fydh">发运单号</param> /// <returns></returns> public int GetSJCount(string fydh) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) FROM TSC_SLAB_MAIN WHERE C_MOVE_TYPE='1'"); if (fydh.Trim() != "") { strSql.Append(" AND C_FYDH='" + fydh + "' "); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
public string GetZKDNO(string zkdstr) { StringBuilder strSql = new StringBuilder(); strSql.Append("select MAX(C_ZKD_NO) "); strSql.Append(" FROM TRC_ROLL_ZKD WHERE 1=1 "); if (zkdstr.Trim() != "") { strSql.Append(" AND C_ZKD_NO LIKE 'ZK" + zkdstr + "%' "); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return("0"); } else { return(obj.ToString()); } }
/// <summary> /// 获取记录总数 /// </summary> public int GetRecordCount(string areaID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) FROM TMB_AREAPLAN where N_STATUS=1 "); if (!string.IsNullOrEmpty(areaID)) { strSql.Append("and C_AERA_ID='" + areaID + "'"); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/* * /// <summary> * /// 分页获取数据列表 * /// </summary> * public DataSet GetList(int PageSize,int PageIndex,string strWhere) * { * OracleParameter[] parameters = { * new OracleParameter(":tblName", OracleDbType.Varchar2, 255), * new OracleParameter(":fldName", OracleDbType.Varchar2, 255), * new OracleParameter(":PageSize", OracleDbType.Int16), * new OracleParameter(":PageIndex", OracleDbType.Int16), * new OracleParameter(":IsReCount", OracleType.Clob), * new OracleParameter(":OrderType", OracleType.Clob), * new OracleParameter(":strWhere", OracleDbType.Varchar2,1000), * }; * parameters[0].Value = "TRC_ROLL_ZKD"; * parameters[1].Value = "C_ID"; * parameters[2].Value = PageSize; * parameters[3].Value = PageIndex; * parameters[4].Value = 0; * parameters[5].Value = 0; * parameters[6].Value = strWhere; * return DbHelperOra.RunProcedure("UP_GetRecordByPage",parameters,"ds"); * }*/ #endregion BasicMethod #region ExtensionMethod /// <summary> /// 获取最新的转库单号 /// </summary> /// <param name="C_ZKD_NO">转库单</param> /// <returns></returns> public long GetZKDNO(string C_ZKD_NO) { StringBuilder strSql = new StringBuilder(); strSql.Append("select MAX(C_ZKD_NO) "); strSql.Append(" FROM TRC_ROLL_ZKD WHERE N_STATUS=1 "); if (C_ZKD_NO.Trim() != "") { strSql.Append(" AND C_ZKD_NO LIKE '" + C_ZKD_NO + "%' "); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt64(obj)); } }
/// <summary> /// 根据发运单号获取发运单状态 /// </summary> /// <param name="fydh">发运单</param> /// <returns></returns> public string GetFYDZT(string fydh) { StringBuilder strSql = new StringBuilder(); strSql.Append("select C_STATUS "); strSql.Append(" FROM TMD_DISPATCH WHERE 1=1 "); if (fydh.Trim() != "") { strSql.Append(" AND C_ID = '" + fydh + "' "); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(""); } else { return(obj.ToString()); } }
/// <summary> /// 获取记录总数 /// </summary> /// <param name="custNo">客户编码</param> /// <param name="custName">客户名称</param> /// <param name="stlGrd">钢种</param> /// <param name="techProt">客户协议</param> /// <param name="std">执行标准</param> /// <returns></returns> public int GetRecordCount(string custNo, string custName, string stlGrd, string techProt, string std) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) FROM TB_STD_CONFIG where N_STATUS=1"); if (!string.IsNullOrEmpty(custNo)) { strSql.Append(" and C_CUST_NO like '%" + custNo + "%'"); } if (!string.IsNullOrEmpty(custName)) { strSql.Append(" and C_CUST_NAME like '%" + custName + "%'"); } if (!string.IsNullOrEmpty(stlGrd)) { strSql.Append(" and UPPER(C_STL_GRD) like '%" + stlGrd.ToUpper() + "%'"); } if (!string.IsNullOrEmpty(techProt)) { strSql.Append(" and UPPER(C_CUST_TECH_PROT) like '%" + techProt.ToUpper() + "%'"); } if (!string.IsNullOrEmpty(std)) { strSql.Append(" and UPPER(C_STD_CODE) like '%" + std.ToUpper() + "%'"); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 获取记录总数 /// </summary> public int GetRecordCount(string classID, string empID, string start, string end, string title, string state) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) FROM TMC_TECH_CONSULT where 1=1 "); if (!string.IsNullOrEmpty(empID)) { strSql.Append(" and C_EMP_ID='" + empID + "'"); } if (!string.IsNullOrEmpty(classID)) { strSql.Append(" and C_QUEST_ID='" + classID + "'"); } if (!string.IsNullOrEmpty(title)) { strSql.Append(" and C_STL_GRD like '%" + title + "%'"); } if (!string.IsNullOrEmpty(start) && !string.IsNullOrEmpty(end)) { strSql.Append(" and D_MOD_DT between to_date('" + start + "', 'yyyy-mm-dd hh24:mi:ss') and to_date('" + Convert.ToDateTime(end).AddDays(1).ToString() + "', 'yyyy-mm-dd hh24:mi:ss')"); } if (!string.IsNullOrEmpty(state)) { strSql.Append("and N_STATE=" + state + ""); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 根据工位代码获取工位主键 /// </summary> /// <param name="C_STA_CODE">工位代码</param> /// <returns></returns> public string GetStaIdByCode(string C_STA_CODE) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT C_ID from TB_STA "); strSql.Append(" where C_STA_CODE=:C_STA_CODE AND N_STATUS=1"); OracleParameter[] parameters = { new OracleParameter(":C_STA_CODE", OracleDbType.Varchar2, 100) }; parameters[0].Value = C_STA_CODE; object obj = DbHelperOra.GetSingle(strSql.ToString(), parameters); if (obj == null) { return("0"); } else { return(obj.ToString()); } }
/// <summary> /// 获取记录总数 /// </summary> public int GetRecordCount(string C_NO, string C_NAME) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) FROM TS_CUSTFILE where 1=1"); if (!string.IsNullOrEmpty(C_NO)) { strSql.Append("and C_NO='" + C_NO + "'"); } if (!string.IsNullOrEmpty(C_NAME)) { strSql.Append("and C_NAME like '%" + C_NAME + "%'"); } object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 验证批次号唯一 /// </summary> /// <param name="newBatchNo"></param> /// <returns></returns> public bool ExistBatchNo(string newBatchNo) { // 委外表 var existSql1 = $"SELECT COUNT(1) FROM trc_roll_ww_main WHERE C_BATCH_NO='{newBatchNo}'"; // 线材库表,之前存在的批次 var existSql2 = $"SELECT COUNT(1) FROM trc_roll_prodcut WHERE C_BATCH_NO='{newBatchNo}'"; return(Convert.ToInt32(DbHelperOra.GetSingle(existSql1)) > 0 || Convert.ToInt32(DbHelperOra.GetSingle(existSql2)) > 0); }
/// <summary> /// 校验当前区域下发订单是否满足排产指标 /// </summary> /// <param name="wgt">订单排产量</param> /// <param name="area">区域</param> /// <param name="orderNo">订单号</param> /// <returns></returns> public bool CheckStl_Grd_ZB(decimal wgt, string area, string orderNo, string xday) { //TRUNC((TO_CHAR(SYSDATE, 'DD')-1)/10) bool result = true; string strSql = $@"SELECT T.N_DAY_JK_ZB,T.N_DAY_JP_ZB,T.N_DAY_PZ_ZB,T.C_FLAG,T.C_AREA FROM TMO_ORDER_PCZB T WHERE T.C_AREA = '{area}' AND T.C_FLAG = 'Y' AND T.N_TYPE={xday} AND TO_CHAR(T.D_PLAN_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')"; DataTable dtZB = DbHelperOra.Query(strSql).Tables[0]; //获取排产指标 if (dtZB.Rows.Count > 0) //判定是否指标控制 { string strSql2 = $@"SELECT * FROM V_CON_ORDER_TYPE T WHERE T.C_ORDER_NO='{orderNo}'"; DataTable dt2 = DbHelperOra.Query(strSql2).Tables[0]; //获取当前订单基本信息 if (dt2.Rows.Count > 0) //获取钢种监控/精品/品种 { if (dt2.Rows[0]["C_FLAG"].ToString() == "Y") //监控钢种 { #region //监控 //获取当前区域当月旬监控提报排产总量 string strSql3 = $@"SELECT NVL(SUM(T.N_WGT),0) N_WGT FROM V_ORDER_PLAN T WHERE T.C_FLAG = 'Y' AND T.C_AREA='{dt2.Rows[0]["C_AREA"].ToString()}' AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM') AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}"; decimal pcwgt = Convert.ToDecimal(DbHelperOra.GetSingle(strSql3)) + wgt; if (pcwgt > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_JK_ZB"]))//判定是否超出旬监控指标 { result = false; } else { if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_JK"]))//判定是否满足单项监控指标 { result = false; } } #endregion } else { switch (dt2.Rows[0]["C_TYPE"].ToString()) { case "普碳钢": #region //普碳钢 //获取当前区域当前月当旬品种总排产量 string strSql4 = $@"SELECT NVL(SUM(T.N_WGT), 0) N_WGT FROM V_ORDER_PLAN T WHERE T.C_TYPE IN ('普碳钢') AND T.C_AREA = '{dt2.Rows[0]["C_AREA"].ToString()}' AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM') AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}"; decimal pcwgt_pt = Convert.ToDecimal(DbHelperOra.GetSingle(strSql4)) + wgt; if (pcwgt_pt > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_PZ_ZB"])) //判定是否超出旬品种指标 { result = false; } else { if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_PZ"])) //判定是否满足单项品种指标 { result = false; } } #endregion break; case "高速线材": #region //高速线材 //获取当前区域当前月当旬品种总排产量 string strSql6 = $@"SELECT NVL(SUM(T.N_WGT), 0) N_WGT FROM V_ORDER_PLAN T WHERE T.C_TYPE IN ('普碳钢') AND T.C_AREA = '{dt2.Rows[0]["C_AREA"].ToString()}' AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM') AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}"; decimal pcwgt_pz = Convert.ToDecimal(DbHelperOra.GetSingle(strSql6)) + wgt; if (pcwgt_pz > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_PZ_ZB"])) //判定是否超出旬品种指标 { result = false; } else { if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_PZ"])) //判定是否满足单项品种指标 { result = false; } } #endregion break; case "精品线材": #region //精品线材 //获取当前区域当前月当旬精品总排产量 string strSql5 = $@"SELECT NVL(SUM(T.N_WGT), 0) N_WGT FROM V_ORDER_PLAN T WHERE T.C_TYPE IN ('精品线材') AND T.C_AREA = '{dt2.Rows[0]["C_AREA"].ToString()}' AND TO_CHAR(T.D_DT, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM') AND TRUNC((TO_CHAR(T.D_DT, 'DD') - 1) / 10) ={xday}"; decimal pcwgt_jp = Convert.ToDecimal(DbHelperOra.GetSingle(strSql5)) + wgt; if (pcwgt_jp > Convert.ToDecimal(dtZB.Rows[0]["N_DAY_JP_ZB"])) //判定是否超出旬精品指标 { result = false; } else { if (wgt < Convert.ToDecimal(dtZB.Rows[0]["N_PC_JP"])) //判定是否满足单项精品指标 { result = false; } } #endregion break; } } } } return(result); }
/// <summary> /// 合同号 /// </summary> /// <param name="area">区域代码</param> /// <returns></returns> public string CreateConNo(string area) { try { #region //获取合同号 string max_no = string.Empty; string no = DateTime.Now.Year.ToString().Substring(2, 2) + area; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT MAX(C_CON_XH) FROM TMO_CON WHERE length(C_CON_XH)=15 and C_CON_XH LIKE 'XG-XS-" + no + "%'"); object obj = DbHelperOra.GetSingle(strSql.ToString()); if (obj != null) { max_no = obj.ToString(); } if (!string.IsNullOrEmpty(max_no) && max_no.Length <= 15) { no = (Convert.ToInt64(max_no.Substring(6, max_no.Length - 6)) + 1).ToString(); } else { switch (area) { case "01": //北方 no = no + "00001"; break; case "02": //南方 no = no + "00001"; break; case "03": //出口 no = no + "00001"; break; case "07": //不锈钢 no = no + "00001"; break; case "09": //不良品 no = no + "00001"; break; case "04": //钢坯 no = no + "00001"; break; case "05": //副产品 no = no + "00001"; break; } } no = "XG-XS-" + no; #endregion return(no); } catch (Exception e) { throw e; } }