public DataPointHistoryData FindBy(string id)
{
DataPointHistoryData dataPointHistoryData = new DataPointHistoryData();
DataTable dataTable;
MySqlParameter[] mySqlPrarameters = { new MySqlParameter("@DataPointHistoryDataId", id) };
string sqlQuery = baseSelectQuery +
"where DataPointHistoryDataId=@DataPointHistoryDataId;";
try
{
dataTable = dbUtility.ExecuteDataTable(sqlQuery, CommandType.Text, mySqlPrarameters);
if (null != dataTable)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataPointHistoryData = DataMapperFactory.GetDataMapper <DataPointHistoryData>().ConverFrom(dataTable.Rows, i);
}
}
}
catch (Exception ex)
{
string message = "查询失败！" + ex.Message;
LoggingFactory.GetLogger().WriteDebugLogger(message);
return(dataPointHistoryData);
}
return(dataPointHistoryData);
}
public static DataTable QueryData(string bt, string et, string wotype, string woproducttype, string wowidth, string wothinkness, string worksite, string classname)
{
string sql = "call prOutputReport('" + bt + "','"
+ et + "','','" + woproducttype + "','" + wothinkness + "','" + wotype + "','" + wowidth + "','" + worksite + "','" + classname + "');";
return(dbhelp.ExecuteDataTable(sql, null));
}
public Module FindBy(int id)
{
Module module = new Module();
DataTable dataTable;
MySqlParameter[] mySqlPrarameters = { new MySqlParameter("@Id", id) };
string moduleQuery = "select ModuleId,Number,Name,Description " +
"from numericalcontrolsystem.module " +
"where ModuleId=@Id;";
try
{
dataTable = dbUtility.ExecuteDataTable(moduleQuery, CommandType.Text, mySqlPrarameters);
if (null != dataTable)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
module = DataMapperFactory.GetDataMapper <Module>().ConverFrom(dataTable.Rows, i);
}
}
}
catch (Exception ex)
{
string message = "查询失败！" + ex.Message;
LoggingFactory.GetLogger().WriteDebugLogger(message);
return(module);
}
return(module);
}
public static DataTable Exist(string type, string paraid)
{
string sql = "select paratype, paraid,paraname,createtime,createuser From jh_mes.tparaconfig where 1=1 ";
if (type != "All")
{
sql += " and paratype = '" + type + "' and paraid = '" + paraid + "'";
}
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable Exist(string eqpid)
{
string sql = "select eqpid From jh_mes.teqpinfo where 1=1 ";
if (eqpid != "All")
{
sql += " and eqpid = '" + eqpid + "' ;";
}
return(dbhelp.ExecuteDataTable(sql, null));
}
private void FormFindEmp_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt = db.ExecuteDataTable("select FEERACK_ID,FEERACK_NAME from XH_FEERACK_T where DELETE_FLAG='0'");
CB_EmpStation.DataSource = dt;
CB_EmpStation.DisplayMember = "FEERACK_NAME";
CB_EmpStation.ValueMember = "FEERACK_ID";
CB_EmpStation.SelectedIndex = 0;
// FindEmp();
}
public static void SetWOCbx(CheckBoxList cbl)
{
string sql = "select workorderid from jh_mes.tworkorderinfo where status='open' ;";
DataTable dt = dbhelp.ExecuteDataTable(sql, null);
cbl.DataSource = dt;
cbl.DataTextField = "workorderid";
cbl.DataValueField = "workorderid";
cbl.DataBind();
}
private void ChangeConfigForm_Load(object sender, EventArgs e)
{
//数据绑定
String str = "SELECT FEERACK_ID,FEERACK_NAME,PRINTEDBOOKS_ID FROM XH_FEERACK_T WHERE DELETE_FLAG='0'";
//绑定料架
DataTable dt = new DataTable();
dt = db.ExecuteDataTable(str);
CB_Station.DataSource = dt;
CB_Station.ValueMember = "FEERACK_ID";
CB_Station.DisplayMember = "FEERACK_NAME";
//绑定表格
DataTable tb1 = (DataTable)F.dataGridView1.DataSource;
tb2 = tb1.Copy();
this.dataGridView1.DataSource = tb2;
this.dataGridView1.ClearSelection();
//for (int i = 0; i < tb1.Rows.Count; i++)
//{
// dataGridView1.Rows.Add();//在gridview2中添加一空行
// //为空行添加列值
// for (int j = 0; j < tb1.Rows[i].; j++)
// {
// dataGridView1.Rows[i].Cells[j].Value = F.dataGridView1.Rows[i].Cells[j].Value;
// }
//}
}
public static DataTable QueryLabeltimeByStation(string lot, string stationid)
{
string sql = "select lotid,a.createtime from tlotsplit a INNER JOIN tworkflow b on a.flowid=b.flowid and a.flowidno = b.flowidno "
+ " where b.worksiteid='" + stationid + "' and a.sublotid = '" + lot + "';";
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryData(string warehousecode, string type, string lotid)
{
string sql = " SELECT ( "
+ " SELECT warehousecode "
+ " FROM twarehousecodeconfig "
+ " WHERE id = b.warehousecode "
+ " ) warehousecode "
+ " ,a.lotid "
+ " ,a.validlength "
+ " ,a.validwidth "
+ " ,a.ValidLength * a.ValidWidth / 1000 area "
+ " ,b.pinmin "
+ " ,b.type "
+ " ,b.warehousetime "
+ " FROM tlotbasis a "
+ " INNER JOIN twarehouseinfo b ON a.lotid = b.lotid "
+ " WHERE a.warehouse = 'Y' "
+ " AND shipment IS NULL ";
if (warehousecode != "All")
{
sql += " and b.warehousecode='" + warehousecode + "' ";
}
if (type != "")
{
sql += " and b.type='" + type + "' ";
}
if (lotid != "")
{
sql += " and a.lotid = '" + lotid + "'";
}
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryData(string workshopid, string shipmentid, string bt, string et, string lotid, string pinmin, string type)
{
string sql = " SELECT a.shipmentid,a.lotid "
+ " ,a.workshopid"
+ " ,a.shipmentuser "
+ " ,a.tempshipmenttime "
+ " ,b.pinmin "
+ " ,b.length validlength"
+ " ,b.width validwidth"
+ " ,b.type "
//+ " ,ifnull(b.shipment,'N') shipment "
//+ " ,shipmentID "
+ " FROM jh_mes.tshipmentinfo a "
+ " INNER JOIN jh_mes.twarehouseinfo b ON a.lotid = b.lotid "
+ " WHERE 1=1 and shipment is null ";
//============出库单号======================================
if (shipmentid != "")
{
sql += " and a.shipmentID = '" + shipmentid + "' ";
}
if (workshopid != "")
{
sql += " and b.workshopid = '" + workshopid + "' ";
}
if (bt != "" && et != "")
{
sql += " and a.tempshipmenttime between '" + bt + "' and '" + et + "' ";
}
if (pinmin != "")
{
sql += " and a.pinmin = '" + pinmin + "'";
}
if (lotid != "")
{
sql += " and a.lotid ='" + lotid + "'";
}
if (type != "")
{
sql += " and a.type = '" + type + "'";
}
return(dbhelp.ExecuteDataTable(sql, null));
}
public DataPoint FindBy(int id)
{
DataPoint dataPoint = new DataPoint();
DataTable dataTable;
MySqlParameter[] mySqlPrarametersForDataPointQuery = { new MySqlParameter("@Id", id) };
string dataPointQuery = this.baseSelectQuery +
"where DataPointId=@Id;";
//string moduleQuery = "select ModuleId,Number,Name,Description " +
// "from numericalcontrolsystem.module " +
// "where ModuleId=@Id;";
try
{
dataTable = dbUtility.ExecuteDataTable(dataPointQuery, CommandType.Text, mySqlPrarametersForDataPointQuery);
if (null != dataTable)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
dataPoint = DataMapperFactory.GetDataMapper <DataPoint>().ConverFrom(dataTable.Rows, i);
}
}
//MySqlParameter[] mySqlPrarametersForModuleQuery = { new MySqlParameter("@Id", dataPoint.ModuleBelongTo.Id) };
//dataTable = dbUtility.ExecuteDataTable(moduleQuery, CommandType.Text, mySqlPrarametersForDataPointQuery);
//if (null != dataTable)
//{
// for (int i = 0; i < dataTable.Rows.Count; i++)
// {
// dataPoint.ModuleBelongTo = DataMapperFactory.GetDataMapper<Module>().ConverFrom(dataTable.Rows, i);
// }
//}
}
catch (Exception ex)
{
string message = "查询失败！" + ex.Message;
LoggingFactory.GetLogger().WriteDebugLogger(message);
return(dataPoint);
}
return(dataPoint);
}
public static DataTable QueryWOType(string wotype, string wo, string type)
{
string sql = @"select TQ001,TQ002,TQ003,TQ004,MB003 as type
from MOCTQ a
INNER JOIN
INVMB b on a.TQ004 = b.MB001
where TQ001 = '" + wotype + "' and TQ002 = '" + wo + "' and TQ003 = (select MB001 from INVMB where MB003='" + type + "' and MB001 LIKE 'P%' ) ";
return(erphelp.ExecuteDataTable(sql, null));
}
public static DataTable MouldDataHistory(string bt, string et, string eqpid,
string workshopid, string mouldlotid,
string carvelotid, string outwardlotid,
string worksiteid)
{
string sql = " SELECT a.lotid "
+ " ,a.carvelotid "
+ " ,a.outwardlotid "
+ " ,a.eqpid "
+ " ,a.type "
+ " ,a.createtime "
+ " ,(select paraname from tparaconfig where paratype = 'paratype' and paraid= b.paratype) paratype "
+ " ,b.paraid "
+ " ,a.workshopid "
+ " ,(select paraname from tparaconfig where paraid = a.worksiteid) worksiteid "
+ " ,a.lotcount "
+ " FROM twiplotlog a inner join tlotbasis c on a.lotid = c.lotid "
+ " left JOIN twiplotdetail b ON a.lotserial = b.lotserial "
+ " where c.lottype='Mould' and a.createtime between '" + bt + "' and '" + et + "' ";
if (eqpid != "")
{
sql += " and a.eqpid = '" + eqpid + "' ";
}
if (workshopid != "")
{
sql += " and a.workshopid = '" + workshopid + "' ";
}
if (mouldlotid != "")
{
sql += " and a.lotid = '" + mouldlotid + "' ";
}
if (carvelotid != "")
{
sql += " and a.carvelotid = '" + carvelotid + "' ";
}
if (outwardlotid != "")
{
sql += " and a.outwardlotid = '" + outwardlotid + "' ";
}
if (worksiteid != "")
{
sql += " and a.worksiteid = '" + worksiteid + "' ";
}
sql += " ORDER BY a.createtime ";
return(dbhelp.ExecuteDataTable(sql, null));
}
/// <summary>
/// 加载时连接数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
try
{
String str = "SELECT FEERACK_ID,FEERACK_NAME,PRINTEDBOOKS_ID FROM XH_FEERACK_T WHERE DELETE_FLAG='0'";
//绑定料架
DataTable dt = new DataTable();
dt = db.ExecuteDataTable(str);
CB_Station.DataSource = dt;
CB_Station.ValueMember = "FEERACK_ID";
CB_Station.DisplayMember = "FEERACK_NAME";
dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText;
CB_Category.SelectedIndex = 0;
CB_MaterialNo.SelectedIndex = 0;
}
catch (Exception ex)
{
MessageBox.Show("数据库连接错误！");
}
}
private void button1_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(feerack_Name.Text) && !String.IsNullOrEmpty(anothername.Text))
{
string sql = "select FEERACK_ID from dbo.XH_FEERACK_T where FEERACK_NAME='" + feerack_Name.Text + "' and ANOTHERNAME='" + anothername.Text + "' and DELETE_FLAG='0';";
DataTable dt = new DataTable();
dt = db.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
MessageBox.Show("添加的料架号已存在！");
}
else
{
string sql1 = "insert into dbo.XH_FEERACK_T (FEERACK_NAME,ANOTHERNAME,CREATE_TIME,DELETE_FLAG,PRINTEDBOOKS_ID)values('" + feerack_Name.Text + "','" + anothername.Text + "',GETDATE(),'0',2)";
db.ExecuteNonQuery(sql1);
DataBind();
}
}
else
{
MessageBox.Show("料架名称为空或别名为空！");
}
}
private void FormAddEmp_Load(object sender, EventArgs e)
{
try
{
DataTable dt = new DataTable();
dt = db.ExecuteDataTable("select FEERACK_ID,FEERACK_NAME from XH_FEERACK_T where DELETE_FLAG='0'");
CB_EmpStation.DataSource = dt;
CB_EmpStation.DisplayMember = "FEERACK_NAME";
CB_EmpStation.ValueMember = "FEERACK_ID";
}
catch (Exception ex)
{
MessageBox.Show("数据库连接失败！");
}
}
/// <summary>
/// 显示产品状态的业务方法
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
public void showProductionState(object source, System.Timers.ElapsedEventArgs e)
{
OverrideTimer tt = (OverrideTimer)source;
tt.Stop();
try
{
//显示产品种类
BeginInvoke((MethodInvoker) delegate() { LB_ProductionType.Text = productionTypeconfig; });
//显示今日计划
BeginInvoke((MethodInvoker) delegate() { LB_TodayPlan.Text = productionTodayPlan + " ea"; });
//显示已经完成的产量
int fishedControl = Convert.ToInt32(BussinessFacde.GetOpcConfigXml("", "1", "2"));
//if(Convert.ToInt32(OPC.ReadItem(fishedControl).ToString())==1)
//{
string sql = "select distinct sn from OfflinePack where DT between '" + DateTime.Now.ToString("yyyy.MM.dd") + " 00:00:00 ' and '" + DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss") + "'";
DataTable dt = new DataTable();
dt = db.ExecuteDataTable(sql);
int finishedNumber = dt.Rows.Count;
BeginInvoke((MethodInvoker) delegate() { LB_Fished.Text = finishedNumber + " ea"; });
double fishedP = (double)finishedNumber / Convert.ToInt32(productionTodayPlan);
BeginInvoke((MethodInvoker) delegate() { LB_FishenP.Text = fishedP.ToString("P"); });
//}
int moduleAddr = Convert.ToInt32(BussinessFacde.GetOpcConfigXml("", "4", "2"));
string moduleS = OPC.ReadItem(moduleAddr).ToString();
if (moduleS == "1")
{
BeginInvoke((MethodInvoker) delegate() { moduleStatus.Text = showText; });
BeginInvoke((MethodInvoker) delegate() { moduleStatus.ForeColor = Color.Red; });
}
else
{
BeginInvoke((MethodInvoker) delegate() { moduleStatus.Text = ""; });
}
}
catch
{
}
finally
{
tt.Start();
}
}
public static DataTable QueryData(string bt, string et, string worksiteid)
{
string sql = " select a.lotid,a.worksiteid," +
" (select paraname from tparaconfig where paratype = 'worksite' and paraid = a.worksiteid ) worksitename," +
" a.eqpid,a.createtime,a.createuser,a.result,a.MouldLevel," +
" b.paratype,b.parasubtype,b.paraid ,b.result subresult" +
" from jh_mes.tqclog a LEFT JOIN jh_mes.tqcdetail b " +
" on a.lotserial =b.lotserial " +
" where a.createtime between '" + bt + "' and '" + et + "'";
if (worksiteid != "")
{
sql += " and a.worksiteid = '" + worksiteid + "'";
}
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryWorkorderIno(string WO)
{
string sql = "select " +
"bomid ," +
"(select distinct remark from jh_mes.tworkflow where flowid = a.flowid) FlowidAlias ," +
"flowid," +
"workshopid ," +
"createtime," +
"createuser ," +
"MouldPinMin ," +
"MouldThinkness ," +
"MouldLength ," +
"MouldWidth ," +
"MouldType ," +
"MouldPETType ," +
"workordertype from jh_mes.tworkorderinfo a where workorderid = '" + WO + "';";
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable Retrieve(string bt, string et)
{
string sql = @" select workorderid,
bomid
,flowid
,workshopid
,createtime
,createuser
,mouldpinmin
,mouldthinkness
,mouldlength
,mouldwidth
,mouldtype
,mouldpettype
,workordertype
,status
,transfertime
from jh_mes.tworkorderinfo where createtime >'" + bt + "' and createtime <'" + et + "'; ";
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryData(string shipmentID)
{
string sql = " SELECT a.shipmentid,a.lotid "
+ " ,a.workshopid"
+ " ,a.shipmentuser "
+ " ,a.tempshipmenttime "
+ " ,b.pinmin "
+ " ,b.length validlength"
+ " ,b.width validwidth"
+ " ,b.type "
//+ " ,ifnull(b.shipment,'N') shipment "
//+ " ,shipmentID "
+ " FROM jh_mes.tshipmentinfo a "
+ " INNER JOIN jh_mes.twarehouseinfo b ON a.lotid = b.lotid "
+ " WHERE 1=1 and shipment is null ";
//============出库单号======================================
if (shipmentID != "")
{
sql += " and a.shipmentID = '" + shipmentID + "' ";
}
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryData(string type, string querycontent)
{
string sql = "call prWIPReporttest('','','" + type + "','" + querycontent + "');";
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryData(string lot)
{
string sql = "call prGetLotHistory('" + lot + "');";
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryLot(string lot)
{
string sql = "select BASELOTNO,PMAX,ISC,VOC,IPM,VPM,RS,RSH,FF,\"Comment\" EQP,TESTTIME TIME from tblwiplotbasis where baselotno = '" + lot + "'";
return(iModuleHelp.ExecuteDataTable(sql, null));
}
public static DataTable Query(string sql)
{
return(dbhelp.ExecuteDataTable(sql, null));
}
public string Additem(string item)
{
string allowNull = ConfigurationManager.AppSettings["allowNull"];
if (allowNull.ToLower().Equals("false") && string.IsNullOrEmpty(item))
{
return("输入不能为空");
}
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
string tableName = ConfigurationManager.AppSettings["tableName"];
string timeColName = ConfigurationManager.AppSettings["timeColumnName"];
string columnName = ConfigurationManager.AppSettings["columnName"];
DateTime time = DateTime.Parse(DateTime.Now.ToShortDateString()).AddDays(-1);
DbUtility dbutil = new DbUtility(connection, dbType());
string sql = "select count(*) from " + tableName + " where " + timeColName + " = @dateTime";
List <DbParameter> parameters = new List <DbParameter>
{
new SqlParameter {
DbType = DbType.DateTime, ParameterName = "@dateTime", Value = time
}
};
//是否存在该时间数据
int count = 0;
DataTable dataCount = dbutil.ExecuteDataTable(sql, parameters);
if (dataCount.Rows.Count > 0)
{
count = int.Parse(dataCount.Rows[0][0].ToString());
}
else
{
return("未找到时间列");
}
if (count == 0)
{
//不存在
sql = "INSERT INTO " + tableName + " (" + timeColName + "," + columnName + ") Values(@dateTime,@itemValue) ";
parameters = new List <DbParameter>
{
new SqlParameter {
DbType = DbType.DateTime, ParameterName = "@dateTime", Value = time
},
new SqlParameter {
DbType = dataType(), ParameterName = "@itemValue", Value = item
}
};
count = dbutil.ExecuteNonQuery(sql, parameters);
if (count > 0)
{
return("添加成功");
}
else
{
return("添加失败");
}
}
else
{
//存在
sql = "UPDATE " + tableName + " SET " + columnName + " =@itemValue where " + timeColName + "=@dateTime ";
parameters = new List <DbParameter>
{
new SqlParameter {
DbType = DbType.DateTime, ParameterName = "@dateTime", Value = time
},
new SqlParameter {
DbType = dataType(), ParameterName = "@itemValue", Value = item
}
};
count = dbutil.ExecuteNonQuery(sql, parameters);
if (count > 0)
{
return("更新成功");
}
else
{
return("更新失败");
}
}
}
public static DataTable QueryShipmentNoticeData(string shipmentnotice)
{
string sql = " select distinct type,shipmentqty from terpshipmentnoticeinfo where shipmentnotice = '" + shipmentnotice + "' ;";
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable GetPackaInfo(string lot)
{
string sql = " select * from tpackinginfo where lotid = '" + lot + "'";
return(dbhelp.ExecuteDataTable(sql, null));
}
public static DataTable QueryQCData(string lot)
{
string sql = " call prRuncard_QCData('" + lot + "');";
return(dbhelp.ExecuteDataTable(sql, null));
}
