public static string QueryMaxPinHao(string PinHao, string ProductType) { string sql = ""; if (ProductType == "成品") { sql = "select count(*) from jh_mes.tpackinginfo where producttype = '成品' and left(pinhao,4) = '" + PinHao.Substring(0, 4) + "'; "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } } else { sql = "select count(*) from jh_mes.tpackinginfo where producttype = '半成品' and left(pinhao,5) = '" + PinHao.Substring(0, 5) + "'; "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } } }
//查询批次对应的分条流程ID public static string QuerySubsectionFlowID(string lot) { string sql = "select distinct flowid from jh_mes.tworkflow where mappingflowid = (select flowid from jh_mes.tlotbasis where lotid = '" + lot + "')"; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
public static string ExistWO(string wo) { string sql = " select workorderid from tworkorderinfo where workorderid = '" + wo + "' "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return("success"); } else { return("fail"); } }
public static string QueryShipmentIDByDay() { string sql = "select ifnull(max(right(shipmentid,3)),0) from twarehouseinfo where date(warehousetime) = curdate() "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
/// <summary> /// 查询当年最大的UV成型序列号 /// </summary> /// <param name="UVCompletelot"></param> /// <returns></returns> public static string QueryMaxUVCompleteLotID() { string sql = "select ifnull(max(right(a.uvcompletelotid,5)),0) from jh_mes.tlotbasis a where year(createtime) = year(now()) and a.uvcompletelotid <>'' ; "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
public static bool getUserPower(string userID, string Power) { string sql = "select user_id from tpower as a " + "inner join tgrouppower as b on a.id = b.power_id " + "inner join tgroup as c on c.id = b.group_id " + "inner join tusergroup as d on d.group_id = c.id " + "where a.powername = '" + Power + "' and d.user_id = '" + userID + "' "; object obj = dbhelp.ExecuteScalar(sql, null); if (obj == null) { return(false); } else { if (obj.ToString() == userID) { return(true); } else { return(false); } } }
public static string LotExist(string lot) { string sql = " select lotid from tlotbasis where lotid ='" + lot + "' "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return("success"); } else { return("fail"); } }
public static string CheckSplitSum(string Parentlot) { string sql = "select count(lotid) from tlotsplit where lotid = '" + Parentlot + "' "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
public static string ExistWarehouseID(string warehouseid) { string sql = "select warehouseid from twarehouseinfo where warehouseid = '" + warehouseid + "';"; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return("success"); } else { return("fail"); } }
public static string ExistSubsectionLotid(string lot) { string sql = "select lotid from tlotsplit where sublotid = '" + lot + "';"; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
public static string QueryPinHaoOfTypeInERP(string Type) { string sql = @"select MB001 FROM INVMB WHERE MB001 LIKE 'P%' and MB003 = '" + Type + "' "; object ob = erphelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
/// <summary> /// AG涂布分批,查询母批中子批的最大序列号 /// </summary> /// <param name="lot"></param> /// <returns></returns> public static string QueryMaxAGCoatingLotID(string ParentLotID) { //string sql = "select count(lotid) from tlotsplit where lotid = '" + Parentlot + "' "; //string sql = "select ifnull(max(right(a.uvcompletelotid,5)),0) from jh_mes.tlotbasis a where year(createtime) = year(now()) and a.uvcompletelotid <>''and a.sublotid like '" + eqpid + "%' and a.workshopid = '" + workshopid + "' ; "; string sql = "select ifnull(max(right(a.sublotid,2)),0) from jh_mes.tlotsplit a where lotid = '" + ParentLotID + "' ; "; object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
public DataTable GetDataTable(int pageIndex, int pageSize, out int recordCount) { string funMsg = "function: GetDataTable(int pageIndex, int pageSize, out int recordCount)" + FileUtility.NewLine + _classMsg; var strSql = new StringBuilder(); strSql.Append("select count(1) from [centabldg]"); recordCount = DbUtility.ExecuteScalar <int>(strSql.ToString(), AppSettings.DbConn); strSql.Clear(); strSql.AppendFormat("select top {0} * from [centabldg]", pageSize); if (pageIndex > 1) { strSql.AppendFormat(" where CentaBuildId not in (select top {0} CentaBuildId from [centabldg])", (pageIndex - 1) * pageSize); } return(DbUtility.GetDataTable(strSql.ToString(), AppSettings.DbConn)); }
public static string CheckMouldLabel(string type, string label) { string sql = ""; //外发条码 if (type == "Z") { sql = "select carvelotid from jh_mes.tlotbasis where carvelotid = '" + label + "' or gritlotid = '" + label + "' ;"; } else//自产条码 { sql = "select outwardlotid from jh_mes.tlotbasis where outwardlotid = '" + label + "';"; } object ob = dbhelp.ExecuteScalar(sql, null); if (ob != null) { return(ob.ToString()); } else { return("fail"); } }
public DataTable GetDataTable(CentaBuildType model, int pageIndex, int pageSize, out int recordCount) { string funMsg = "function: GetDataTable(CentaBuildType model,int pageIndex, int pageSize, out int recordCount)" + FileUtility.NewLine + _classMsg; var param = new StringBuilder(); if (!string.IsNullOrEmpty(model.centaest)) { param.AppendFormat("centaest like '%{0}%'", model.centaest); } if (!string.IsNullOrEmpty(model.cestcode)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("cestcode like '%{0}%'", model.cestcode); } if (!string.IsNullOrEmpty(model.centabldg)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("centabldg like '%{0}%'", model.centabldg); } if (!string.IsNullOrEmpty(model.cblgcode)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("cblgcode like '%{0}%'", model.cblgcode); } if (model.lpt_x > 0) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("lpt_x={0}", model.lpt_x); } if (model.lpt_y > 0) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("lpt_y={0}", model.lpt_y); } if (!string.IsNullOrEmpty(model.usage)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("usage like '%{0}%'", model.usage); } if (!string.IsNullOrEmpty(model.c_estate)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("c_estate like '%{0}%'", model.c_estate); } if (!string.IsNullOrEmpty(model.e_estate)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("e_estate like '%{0}%'", model.e_estate); } if (!string.IsNullOrEmpty(model.c_phase)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("c_phase like '%{0}%'", model.c_phase); } if (!string.IsNullOrEmpty(model.e_phase)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("e_phase like '%{0}%'", model.e_phase); } if (!string.IsNullOrEmpty(model.c_property)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("c_property like '%{0}%'", model.c_property); } if (!string.IsNullOrEmpty(model.pc_addr1)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("pc_addr1 like '%{0}%'", model.pc_addr1); } if (!string.IsNullOrEmpty(model.pc_addr2)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("pc_addr2 like '%{0}%'", model.centaest); } if (!string.IsNullOrEmpty(model.pe_addr)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("pe_addr like '%{0}%'", model.pe_addr); } if (!string.IsNullOrEmpty(model.opdate)) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("opdate ='{0}'", model.opdate); } if (model.unit_cnt > 0) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("unit_cnt={0}", model.unit_cnt); } if (model.x_cnt > 0) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("x_cnt={0}", model.x_cnt); } if (model.y_cnt > 0) { if (param.Length > 0) { param.Append(" and "); } param.AppendFormat("y_cnt={0}", model.y_cnt); } var strSql = new StringBuilder(); strSql.Append("select count(1) from [centabldg]"); if (param.Length > 0) { strSql.AppendFormat(" where {0}", param.ToString()); } recordCount = DbUtility.ExecuteScalar <int>(strSql.ToString(), AppSettings.DbConn); strSql.Clear(); if (param.Length > 0) { strSql.AppendFormat("select top {0} * from [centabldg]", pageSize); if (pageIndex > 1) { strSql.AppendFormat( " where CentaBuildId not in (select top {0} CentaBuildId from [centabldg] where {1}) and {1}", (pageIndex - 1) * pageSize, param.ToString()); } else { strSql.AppendFormat(" where {0}", param.ToString()); } } else { strSql.AppendFormat("select top {0} * from [centabldg]", pageSize); if (pageIndex > 1) { strSql.AppendFormat(" where CentaBuildId not in (select top {0} CentaBuildId from [centabldg])", (pageIndex - 1) * pageSize); } } return(DbUtility.GetDataTable(strSql.ToString(), AppSettings.DbConn)); }
/// <summary> /// 增加一条数据 /// </summary> public int AddNew(CentaBuildType model) { var strSql = new StringBuilder(); strSql.Append("insert into [centabldg] ("); strSql.Append( "centaest,cestcode,centabldg,cblgcode,lpt_x,lpt_y,usage,c_estate,e_estate,c_phase,e_phase,c_property,e_property,pc_addr1,pc_addr2,pe_addr,opdate,unit_cnt,x_cnt,y_cnt,scp_id,scp_c,scp_e,scp_mkt,nmark,o_estateid,o_bldgid,estateid,buildingid,address,moddate,pc_street1,pc_street2,pe_street1,pe_street2,pc_stno1,pc_stno2,ppt_rank,org_cenblg,org_cenest,need_clear,x_axis,x_axis2,y_axis,y_axis2,cblk_key)"); strSql.Append(" values ("); strSql.Append( "@centaest,@cestcode,@centabldg,@cblgcode,@lpt_x,@lpt_y,@usage,@c_estate,@e_estate,@c_phase,@e_phase,@c_property,@e_property,@pc_addr1,@pc_addr2,@pe_addr,@opdate,@unit_cnt,@x_cnt,@y_cnt,@scp_id,@scp_c,@scp_e,@scp_mkt,@nmark,@o_estateid,@o_bldgid,@estateid,@buildingid,@address,@moddate,@pc_street1,@pc_street2,@pe_street1,@pe_street2,@pc_stno1,@pc_stno2,@ppt_rank,@org_cenblg,@org_cenest,@need_clear,@x_axis,@x_axis2,@y_axis,@y_axis2,@cblk_key)"); strSql.Append(";select max(CentaBuildId) as id from [centabldg]"); SqlParameter[] parameters = { new SqlParameter("@centaest", SqlDbType.Char, 10), new SqlParameter("@cestcode", SqlDbType.Char, 10), new SqlParameter("@centabldg", SqlDbType.Char, 10), new SqlParameter("@cblgcode", SqlDbType.Char, 10), new SqlParameter("@lpt_x", SqlDbType.Decimal, 9), new SqlParameter("@lpt_y", SqlDbType.Decimal, 9), new SqlParameter("@usage", SqlDbType.Char, 2), new SqlParameter("@c_estate", SqlDbType.Char, 60), new SqlParameter("@e_estate", SqlDbType.Char, 60), new SqlParameter("@c_phase", SqlDbType.Char, 60), new SqlParameter("@e_phase", SqlDbType.Char, 60), new SqlParameter("@c_property", SqlDbType.Char, 50), new SqlParameter("@e_property", SqlDbType.Char, 60), new SqlParameter("@pc_addr1", SqlDbType.Char, 200), new SqlParameter("@pc_addr2", SqlDbType.Char, 100), new SqlParameter("@pe_addr", SqlDbType.Char, 100), new SqlParameter("@opdate", SqlDbType.DateTime), new SqlParameter("@unit_cnt", SqlDbType.Decimal, 9), new SqlParameter("@x_cnt", SqlDbType.Decimal, 9), new SqlParameter("@y_cnt", SqlDbType.Decimal, 9), new SqlParameter("@scp_id", SqlDbType.Char, 3), new SqlParameter("@scp_c", SqlDbType.Char, 30), new SqlParameter("@scp_e", SqlDbType.Char, 50), new SqlParameter("@scp_mkt", SqlDbType.Char, 1), new SqlParameter("@nmark", SqlDbType.Int, 4), new SqlParameter("@o_estateid", SqlDbType.Char, 50), new SqlParameter("@o_bldgid", SqlDbType.Char, 50), new SqlParameter("@estateid", SqlDbType.Char, 50), new SqlParameter("@buildingid", SqlDbType.Char, 50), new SqlParameter("@address", SqlDbType.Char, 50), new SqlParameter("@moddate", SqlDbType.DateTime), new SqlParameter("@pc_street1", SqlDbType.Char, 100), new SqlParameter("@pc_street2", SqlDbType.Char, 100), new SqlParameter("@pe_street1", SqlDbType.Char, 100), new SqlParameter("@pe_street2", SqlDbType.Char, 100), new SqlParameter("@pc_stno1", SqlDbType.Char, 20), new SqlParameter("@pc_stno2", SqlDbType.Char, 20), new SqlParameter("@ppt_rank", SqlDbType.Int, 4), new SqlParameter("@org_cenblg", SqlDbType.Char, 10), new SqlParameter("@org_cenest", SqlDbType.Char, 10), new SqlParameter("@need_clear", SqlDbType.Char, 1), new SqlParameter("@x_axis", SqlDbType.Char, 250), new SqlParameter("@x_axis2", SqlDbType.Char, 250), new SqlParameter("@y_axis", SqlDbType.Char, 250), new SqlParameter("@y_axis2", SqlDbType.Char, 250), new SqlParameter("@cblk_key", SqlDbType.Char, 250) }; parameters[0].Value = ConvertUtility.Trim(model.centaest); parameters[1].Value = ConvertUtility.Trim(model.cestcode); parameters[2].Value = ConvertUtility.Trim(model.centabldg); parameters[3].Value = ConvertUtility.Trim(model.cblgcode); parameters[4].Value = ConvertUtility.ToDecimal(model.lpt_x); parameters[5].Value = ConvertUtility.ToDecimal(model.lpt_y); parameters[6].Value = ConvertUtility.Trim(model.usage); parameters[7].Value = ConvertUtility.Trim(model.c_estate); parameters[8].Value = ConvertUtility.Trim(model.e_estate); parameters[9].Value = ConvertUtility.Trim(model.c_phase); parameters[10].Value = ConvertUtility.Trim(model.e_phase); parameters[11].Value = ConvertUtility.Trim(model.c_property); parameters[12].Value = ConvertUtility.Trim(model.e_property); parameters[13].Value = ConvertUtility.Trim(model.pc_addr1); parameters[14].Value = ConvertUtility.Trim(model.pc_addr2); parameters[15].Value = ConvertUtility.Trim(model.pe_addr); parameters[16].Value = ConvertUtility.ToDateTime(model.opdate); parameters[17].Value = ConvertUtility.ToDecimal(model.unit_cnt); parameters[18].Value = ConvertUtility.ToDecimal(model.x_cnt); parameters[19].Value = ConvertUtility.ToDecimal(model.y_cnt); parameters[20].Value = ConvertUtility.Trim(model.scp_id); parameters[21].Value = ConvertUtility.Trim(model.scp_c); parameters[22].Value = ConvertUtility.Trim(model.scp_e); parameters[23].Value = ConvertUtility.Trim(model.scp_mkt); parameters[24].Value = ConvertUtility.ToInt(model.nmark); parameters[25].Value = ConvertUtility.Trim(model.o_estateid); parameters[26].Value = ConvertUtility.Trim(model.o_bldgid); parameters[27].Value = ConvertUtility.Trim(model.estateid); parameters[28].Value = ConvertUtility.Trim(model.buildingid); parameters[29].Value = ConvertUtility.Trim(model.address); parameters[30].Value = ConvertUtility.ToDateTime(model.moddate); parameters[31].Value = ConvertUtility.Trim(model.pc_street1); parameters[32].Value = ConvertUtility.Trim(model.pc_street2); parameters[33].Value = ConvertUtility.Trim(model.pe_street1); parameters[34].Value = ConvertUtility.Trim(model.pe_street2); parameters[35].Value = ConvertUtility.Trim(model.pc_stno1); parameters[36].Value = ConvertUtility.Trim(model.pc_stno2); parameters[37].Value = ConvertUtility.ToInt(model.ppt_rank); parameters[38].Value = ConvertUtility.Trim(model.org_cenblg); parameters[39].Value = ConvertUtility.Trim(model.org_cenest); parameters[40].Value = ConvertUtility.Trim(model.need_clear); parameters[41].Value = ConvertUtility.Trim(model.x_axis); parameters[42].Value = ConvertUtility.Trim(model.x_axis2); parameters[43].Value = ConvertUtility.Trim(model.y_axis); parameters[44].Value = ConvertUtility.Trim(model.y_axis2); parameters[45].Value = ConvertUtility.Trim(model.cblk_key); return(DbUtility.ExecuteScalar <int>(strSql.ToString(), AppSettings.DbConn, parameters)); }