public T FindByid(string sql, string parmName, string parmValue)
{
Debug.Assert(!string.IsNullOrWhiteSpace(parmName));
Debug.Assert(!string.IsNullOrWhiteSpace(parmValue));
T result = default(T);
iDB2Parameter[] db2Parms = new iDB2Parameter[] { new iDB2Parameter('@' + parmName, parmValue) };
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms))
{
if (rdr.Read())
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try
{
pi.SetValue(t, rdr.GetValue(i++));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
result = t;
}
}
return(result);
}
public JsonResult savecoordinate(List <ReqRpt216coordinate> savelist)
{
try
{
//首先检查该RecipeName是否有坐标存在
string sql = string.Format("select count(1) as num from ISTRPT.RPT_WAT_Recipe_coordinate where RecipeName='{0}'", savelist.First().RECIPENAME);
DB2Helper db2 = new DB2Helper();
db2.GetSomeData(sql);
DataTable dt = db2.dt;
if (Convert.ToInt32(dt.Rows[0][0]) > 0)
{
return(Json("exist"));
}
List <String> list = new List <string>();
String sql2 = "";
foreach (ReqRpt216coordinate o in savelist)
{
sql2 = string.Format("insert into ISTRPT.RPT_WAT_Recipe_coordinate(RECIPENAME,SITENAME,COORDINATE,CREATETIME,OWNER) values('{0}','{1}','{2}','{3}','{4}') ;", o.RECIPENAME, o.SITENAME, o.COORDINATEX + ',' + o.COORDINATEY, o.CREATETIME, o.OWNER);
list.Add(sql2);
}
db2.UpdateBatchCommand(list);
return(Json("success"));
}
catch (Exception)
{
return(Json("error"));
}
}
public List <T> FindAll(string sql)
{
List <T> list = new List <T>();
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql))
{
if (rdr.Read())
{
do
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try
{
pi.SetValue(t, rdr.GetValue(i++));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
list.Add(t);
} while (rdr.Read());
}
}
return(list);
}
private void DB2Oper()
{
DB2Helper dB2 = new DB2Helper();
dB2.GetSomeData(sql);
foreach (DataRow dr in dB2.dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (dB2.dt.Columns.Contains(pi.Name))
{
if (!pi.CanWrite)
{
continue;
}
object value = dr[pi.Name];
if (value != DBNull.Value)
{
pi.SetValue(t, value, null);
}
}
}
entities.EntityList.Add(t);
}
//部分更新的表需要设定最后的一条记录时间
if (entities.CompareTimeColumnName != "" && entities.EntityList.Count > 0)
{
T t = entities.EntityList.OrderBy(o => o.GetType().GetProperty(entities.CompareTimeColumnName)).LastOrDefault();
LastRecordTime = (DateTime)t.GetType().GetProperty(entities.CompareTimeColumnName).GetValue(t, null);
}
}
private void DB2Oper()
{
DB2Helper dB2 = new DB2Helper();
dB2.GetSomeData(sql);
foreach (DataRow dr in dB2.dt.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (dB2.dt.Columns.Contains(pi.Name))
{
if (!pi.CanWrite)
{
continue;
}
object value = dr[pi.Name];
if (value != DBNull.Value)
{
pi.SetValue(t, value, null);
}
}
}
entities.EntityList.Add(t);
}
}
//querycoordinate
public JsonResult querycoordinate()
{
string RecipeName = Request["RecipeName"].ToString();
string sql = string.Format("select RecipeName,SiteName,Coordinate,CreateTime,Owner from ISTRPT.RPT_WAT_Recipe_coordinate where RecipeName='{0}'", RecipeName);
DB2Helper db2 = new DB2Helper();
db2.GetSomeData(sql);
DataTable dt = db2.dt;
dt.Columns.Add("COORDINATEX", Type.GetType("System.String"));
dt.Columns.Add("COORDINATEY", Type.GetType("System.String"));
if (dt.Rows.Count != 0)
{
JsonSerializerSettings setting = new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
//JavaScriptSerializer serialize = new JavaScriptSerializer();
string str = JsonConvert.SerializeObject(dt, setting);
return(Json(str, JsonRequestBehavior.AllowGet));
}
else
{
//return Json("该RecipeName没有Coordinate，请先添加再查询");
return(Json("nodata"));
}
}
public List <User> findAll()
{
string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" ORDER BY USERNAME";
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, null))
{
if (rdr.Read())
{
List <User> list = new List <User>();
do
{
User user = new User();
user.userNo = rdr.GetInt32(0);
user.username = rdr.GetString(1);
user.password = rdr.GetString(2);
list.Add(user);
} while (rdr.Read());
return(list);
}
else
{
return(null);
}
}
}
int GetCount(string eqp, string startTime)
{
DB2Helper db2 = new DB2Helper();
db2.GetSomeData(string.Format("select distinct proc_time from mmview.fhcdatahs_lot where proc_eqp_id='{0}' and proc_time >'{1}'", eqp, startTime));
return(db2.dt.Rows.Count);
}
public ReqRpt025Translator()
{
DB2Helper db2 = new DB2Helper();
db2.GetSomeData("select max(Start_Time) from istrpt.RPT_RealTime_Lin");
LastRecordTime = (DateTime)db2.dt.Rows[0][0];
Translate(LastRecordTime, DateTime.Now);
}
public List <Entidad.RecursosLiq> ConsultaRecursosLIQ(Entidad.RecursosLiqQueryinput Parametros)
{
var libreria = ConfigurationManager.AppSettings["Libreria"];
dtResultado = DB2Helper.ExecuteDb2Query(CommandType.StoredProcedure, "SP_CONSULTA_RECURSOSLIQ", Parametros);
return(dtResultado.ToList <Entidad.RecursosLiq>());
}
public List <Entidad.Historial> ConsultaHistorial(Entidad.Historialinput Parametros)
{
var libreria = ConfigurationManager.AppSettings["Libreria"];
dtResultado = DB2Helper.ExecuteDb2Query(CommandType.StoredProcedure, "SP_CONSULTA_AUDITORIA_ORD_TRABAJO", Parametros);
return(dtResultado.ToList <Entidad.Historial>());
}
public List <Entidad.ProveedoresLiq> ConsultaProveedores()
{
var libreria = ConfigurationManager.AppSettings["Libreria"];
var Resultado = DB2Helper.ExecuteDb2Query(CommandType.Text, "SELECT IDPROV, RAZCOMER FROM " + libreria + ".PROVEELIQCAB_WEB WHERE SESTRG != '*'", null);
return(Resultado.ToList <Entidad.ProveedoresLiq>());
}
private InlineDBEntity GetLikelyEntity(InlineDBEntity entity)
{
var list = inlineDBEntities.Where(a => a.Lot == entity.Lot && a.WaferPosition == entity.WaferPosition && a.WaferSeq != "*" && a.ItemType != "Derived").OrderBy(p => (p.ClaimTime - entity.ClaimTime).Duration());
InlineDBEntity likelyEntity = list.FirstOrDefault();
//如果存在不一致的情况要记录
var testmax = list.Max(p => p.WaferSeq);
var testmin = list.Min(p => p.WaferSeq);
if (testmax != testmin)
{
LogHelper.InlineInfoLog(string.Format("testmax:{0},testmin:{1},lot:{2},claimtime:{3}", testmax, testmin, entity.Lot, entity.ClaimTime.ToString()));
}
//如果没找到，就到数据库中去找
if (likelyEntity is null)
{
string sqlt = string.Format("(select claim_time, wafer_seq,sourcelot,techbology, product,lotype,owner,measoperator,measequipment,measrecipe,procroute,procroutever,procstep,proctime,procoperatoruser,procequipment,procrecipe from istrpt.fvace_inline_dc where claim_time >= '{0}' and wafer_seq not in ('', '*') order by claim_time FETCH FIRST 1 ROWS ONLY) " +
"union (select claim_time, wafer_seq,sourcelot,techbology, product,lotype,owner,measoperator,measequipment,measrecipe,procroute,procroutever,procstep,proctime,procoperatoruser,procequipment,procrecipe from istrpt.fvace_inline_dc where claim_time <= '{0}' and wafer_seq not in ('', '*') order by claim_time desc FETCH FIRST 1 ROWS ONLY)", entity.ClaimTime.ToString("yyyy-MM-dd-HH.mm.ss.ffffff"));
DB2Helper dB2Helper = new DB2Helper();
dB2Helper.GetSomeData(sqlt);
likelyEntity = new InlineDBEntity();
DataRow dr = dB2Helper.dt.NewRow();
if (dB2Helper.dt.Rows.Count == 2 && dB2Helper.dt.Rows[0][1].ToString() != dB2Helper.dt.Rows[1][1].ToString())
{
dr = (DateTime)dB2Helper.dt.Rows[0][0] - entity.ClaimTime < entity.ClaimTime - (DateTime)dB2Helper.dt.Rows[1][0] ? dB2Helper.dt.Rows[0] : dB2Helper.dt.Rows[1];
}
else if (dB2Helper.dt.Rows.Count > 0)
{
dr = dB2Helper.dt.Rows[0];
}
likelyEntity.ClaimTime = (DateTime)dr[0];
likelyEntity.WaferSeq = dr[1].ToString();
likelyEntity.SourceLot = dr[2].ToString();
likelyEntity.Technology = dr[3].ToString();
likelyEntity.Product = dr[4].ToString();
likelyEntity.LotType = dr[5].ToString();
likelyEntity.Owner = dr[6].ToString();
likelyEntity.MeasOperator = dr[7].ToString();
likelyEntity.MeasEquipment = dr[8].ToString();
likelyEntity.MeasRecipe = dr[9].ToString();
likelyEntity.ProcRoute = dr[10].ToString();
likelyEntity.ProcRouteVer = dr[11].ToString();
likelyEntity.ProcStep = dr[12].ToString();
if (dr[13] is null)
{
likelyEntity.ProcTime = null;
}
else
{
likelyEntity.ProcTime = (DateTime)dr[13];
}
likelyEntity.ProcOperatorUser = dr[14].ToString();
likelyEntity.ProcRecipe = dr[15].ToString();
}
return(likelyEntity);
}
public void GetSequenceTest()
{
long id;
using (iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString))
{
id = DB2Helper.GetSequence(conn, "USER");
}
Assert.AreNotEqual(id, 0);
}
public ReqRpt030DeleteSpecialLotResultModel(string LotID)
{
LotID = LotID.Replace("'", "");
LotID = LotID.Replace("\"", "");
List <string> list = LotID.Split(',').ToList();
string sql = string.Format("delete from ISTRPT.RPT_WIP_SPECIAL_LOT where LOT_ID in ('{0}')", string.Join("','", list));
DB2Helper db2 = new DB2Helper();
db2.GetSomeData(sql);
}
public string AccionesDetalle(Entidad.PlanificacionDetalleInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_ACCIONES_PPJNTAOPED", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public ReqRpt030MainViewModel()
{
DB2Helper db2 = new DB2Helper();
db2.GetSomeData("select lot_id from istrpt.rpt_wip_special_lot");
foreach (DataRow dr in db2.dt.Rows)
{
Lot_ID.Add(dr[0].ToString());
}
}
public string Programar(Entidad.ProgramarInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_PROGRAMAR_ORDEN_TRABAJO_WEB_TODOS", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public string AccionesModulos(ModulosInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_ACCIONES_PPMODULOS", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public string AccionesEnvioAlert(Entidad.EnvioAlertaQueryInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_JNTAOPE_ACCIONES_ENVIO_ALERT", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public string AccionesAccesos(AccesoInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_ACCIONES_PPACCESOUSUARIO", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public static void Run()
{
string[] sql = { @"delete from istrpt.rpt_click_count_history where date=to_char(current date, 'yyyy-MM-dd')",
@"insert into istrpt.rpt_click_count_history (
select privilegeid, usage_counter, to_char(current date, 'yyyy-MM-dd'), current timestamp from istrpt.rptfuncusage
)" };
DB2Helper db2 = new DB2Helper();
db2.UpdateBatchCommand(sql.ToList());
}
public string Acciones(Entidad.CitaDPWQueryInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_JNTAOPE_ACCIONES_CITADPW2", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public string ActualizaRCE(Entidad.ActRCEQueryInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_JNTAOPE_UPD_RCE", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public string FalsoServicio(Entidad.FalsoServicioInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_FALSO_SERVICIO_OT_TODOS", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public string LiquidarServicio(Entidad.LiquidarServicioInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_LIQUIDAR_ORD_TRABAJO_WEB_TODOS", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
public string AddRecurso(Entidad.RecursosLiqInput Parametros)
{
var Resultado = DB2Helper.ExecuteDb2Scalar(CommandType.StoredProcedure, "SP_CREAR_RECURSO_LIQ_OT_TODOS", Parametros);
if (Resultado == null)
{
Resultado = "OK";
}
return(Resultado.ToString());
}
private void Initialize()
{
string sql = string.Format("select Priority_Class from mmview.fhopehs where lot_id='{0}' order by Claim_Time desc fetch first 1 rows only", LotID);
var db2 = new DB2Helper();
db2.GetSomeData(sql);
Priority = (int)db2.dt.DefaultView[0][0];
GetDb2Datas();
SetHistEntities();
SetForcastEntities();
SetQtValue();
}
public List <T> nativeQuerySql(string sql, IDictionary <string, object> parms)
{
iDB2Parameter[] db2Parms = null;
List <T> list = new List <T>();
if (parms != null)
{
List <iDB2Parameter> parmsList = new List <iDB2Parameter>();
foreach (var item in parms)
{
Debug.Assert(item.Value != null);
parmsList.Add(new iDB2Parameter('@' + item.Key, item.Value));
}
db2Parms = parmsList.ToArray();
}
try {
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms))
{
if (rdr.Read())
{
do
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try
{
pi.SetValue(t, rdr.GetValue(i++));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
list.Add(t);
} while (rdr.Read());
}
}
}
catch (Exception ex)
{
log.Error("SQL错误:" + ex.Message);
log.Error("SQL错误:" + sql);
if (parms != null)
{
log.Error("SQL错误:" + parms.toJson());
}
throw ex;
}
return(list);
}
public void delete(string username)
{
string sql = "DELETE FROM \"USER\" WHERE USERNAME=@USERNAME";
using (iDB2Connection conn = new iDB2Connection(DB2Helper.ConnectionString))
{
conn.Open();
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@USERNAME", username)
};
DB2Helper.ExecuteNonQuery(conn, CommandType.Text, sql, parms);
}
}