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));
}
