/// <summary> /// Get SalesOrder Via Key /// </summary> /// <param name="dataset">sale order key</param> /// <returns>dataset</returns> public DataSet GetSalesOrderByKey(string orderKey) { //get dynamic dataset constructor DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet(); //define sqlCommand String[] sqlCommand = new String[5]; try { if (orderKey != string.Empty) { sqlCommand[0] = @"SELECT * FROM POR_SALES_ORDER_ITEM_ATTR WHERE SALES_ORDER_ITEM_KEY IN (SELECT DISTINCT SALES_ORDER_ITEM_KEY FROM POR_SALES_ORDER_ITEM WHERE SALES_ORDER_KEY = '" + orderKey + "') ORDER BY 1"; DataTable dtTable1 = new DataTable(); Database db = DatabaseFactory.CreateDatabase(); dtTable1 = db.ExecuteDataSet(CommandType.Text, sqlCommand[0]).Tables[0]; dtTable1.TableName = POR_SALES_ORDER_ITEM_ATTR_FIELDS.DATABASE_TABLE_NAME; dataDs.Merge(dtTable1, false, MissingSchemaAction.Add); sqlCommand[1] = @"SELECT * FROM POR_SALES_ORDER_ITEM WHERE SALES_ORDER_KEY = '" + orderKey + "' ORDER BY 1"; DataTable dtTable2 = new DataTable(); dtTable2 = db.ExecuteDataSet(CommandType.Text, sqlCommand[1]).Tables[0]; dtTable2.TableName = POR_SALES_ORDER_ITEM_FIELDS.DATABASE_TABLE_NAME; dataDs.Merge(dtTable2, false, MissingSchemaAction.Add); sqlCommand[2] = "SELECT a.*, b." + BASE_ATTRIBUTE_FIELDS.FIELDS_DATA_TYPE + " FROM " + POR_SALES_ORDER_ATTR_FIELDS.DATABASE_TABLE_NAME + " a," + " " + BASE_ATTRIBUTE_FIELDS.DATABASE_TABLE_NAME + " b" + " WHERE a." + POR_SALES_ORDER_ATTR_FIELDS.FIELDS_ATTRIBUTE_KEY + " = b." + BASE_ATTRIBUTE_FIELDS.FIELDS_ATTRIBUTE_KEY + " AND a." + POR_SALES_ORDER_ATTR_FIELDS.FIELDS_SALES_ORDER_KEY + " = '" + orderKey + "'"; //sqlCommand[2] = @"SELECT * FROM POR_SALES_ORDER_ATTR WHERE SALES_ORDER_KEY = '" + orderKey + "' ORDER BY 1"; DataTable dtTable3 = new DataTable(); dtTable3 = db.ExecuteDataSet(CommandType.Text, sqlCommand[2]).Tables[0]; dtTable3.TableName = POR_SALES_ORDER_ATTR_FIELDS.DATABASE_TABLE_NAME; dataDs.Merge(dtTable3, false, MissingSchemaAction.Add); sqlCommand[3] = @"SELECT * FROM POR_SALES_ORDER WHERE SALES_ORDER_KEY = '" + orderKey + "' ORDER BY 1"; DataTable dtTable4 = new DataTable(); dtTable4 = db.ExecuteDataSet(CommandType.Text, sqlCommand[3]).Tables[0]; dtTable4.TableName = POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME; dataDs.Merge(dtTable4, false, MissingSchemaAction.Add); //add paramter table SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ""); } } catch (Exception ex) { SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); LogService.LogError("GetSalesOrderByKey Error: " + ex.Message); } return(dataDs); }
public DataSet DeleteMaterialCode(string _code) { //get dynamic dataset constructor DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet(); //define sql String[] sqlCommand = new String[5]; try { if (_code != string.Empty) { using (DbConnection dbConn = db.CreateConnection()) { //Open Connection dbConn.Open(); //Create Transaction DbTransaction dbTran = dbConn.BeginTransaction(); try { string sql01 = string.Format("SELECT MAX(MATERIAL_VERSION) FROM POR_MATERIAL WHERE MATERIAL_CODE = '{0}'", _code); DataSet ds01 = db.ExecuteDataSet(CommandType.Text, sql01); int materialVersion = 1; if (ds01.Tables[0].Rows.Count > 0) { materialVersion = Convert.ToInt32(ds01.Tables[0].Rows[0][0].ToString()); } sqlCommand[0] = "UPDATE POR_MATERIAL SET STATUS = 0 ,MATERIAL_VERSION = '" + materialVersion + "' WHERE MATERIAL_CODE = '" + _code + "' AND STATUS = 1"; db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand[0]); //Commit Transaction dbTran.Commit(); FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ""); } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); //Rollback Transaction dbTran.Rollback(); LogService.LogError("DeleteMaterialCode Error: " + ex.Message); } finally { //Close Connection dbConn.Close(); } } } } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); LogService.LogError("DeleteMaterialCode Error: " + ex.Message); } return(dataDs); }
/// <summary> /// Search SalesOrders /// </summary> /// <param name="dataset">sale order info</param> /// <returns>dataset</returns> public DataSet SearchSalesOrders(DataSet dataset) { //get dynamic dataset constructor DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet(); //define sqlCommand String[] sqlCommand = new String[5]; try { if (dataset != null && dataset.Tables.Contains(POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME)) { DataTable dataTable = dataset.Tables[POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME]; Hashtable hashData = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dataTable); sqlCommand[0] = @"SELECT SALES_ORDER_KEY, ORDER_NUMBER, ORDER_STATE, ORDER_PRIORITY, ENTERED_TIME, PROMISED_TIME, FINISHED_TIME, SHIPPED_TIME, CLOSED_TIME, DESCRIPTIONS FROM POR_SALES_ORDER WHERE ORDER_NUMBER LIKE '%" + hashData["ORDER_NUMBER"] + "%'"; if (hashData["ORDER_STATE"] != null) { sqlCommand[0] += "AND ORDER_STATE LIKE '%" + hashData["ORDER_STATE"] + "%'"; } if (hashData["ORDER_PRIORITY"] != null) { sqlCommand[0] += "AND ORDER_PRIORITY LIKE '%" + hashData["ORDER_PRIORITY"] + "%'"; } sqlCommand[0] += "ORDER BY 1"; } else { sqlCommand[0] = "SELECT * FROM POR_SALES_ORDER ORDER BY 1"; } Database database = DatabaseFactory.CreateDatabase(); DataTable dtTable = database.ExecuteDataSet(CommandType.Text, sqlCommand[0]).Tables[0]; dtTable.TableName = POR_SALES_ORDER_FIELDS.DATABASE_TABLE_NAME; dataDs.Merge(dtTable, false, MissingSchemaAction.Add); //add paramter table SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ""); } catch (Exception ex) { SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); LogService.LogError("SearchSalesOrders Error: " + ex.Message); } return(dataDs); }
/// <summary> /// Add Uda SalesOrderAttr /// </summary> /// <param name="dataset">dataset for add items cloumns and values</param> /// <returns>dataset</returns> public DataSet AddUdaSalesOrderAttr(DataSet dataset) { //get dynamic dataset constructor DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet(); //define sql string sql = ""; try { if (dataset.Tables.Count > 0) { if (dataset.Tables[0].Rows.Count > 0) { sql += "INSERT INTO POR_SALES_ORDER_ATTR("; //repeated check for (int i = 0; i < dataset.Tables[0].Rows.Count; i++) { if (i == 0) { sql += dataset.Tables[0].Rows[i][0].ToString(); } else { sql += ", " + dataset.Tables[0].Rows[i][0].ToString(); } } sql += ") VALUES ("; for (int i = 0; i < dataset.Tables[0].Rows.Count; i++) { if (i == 0) { sql += dataset.Tables[0].Rows[i][1]; } else { sql += ", " + dataset.Tables[0].Rows[i][1]; } } sql += " )"; //excute insert dbEngine.ExecuteNonQuery(sql); SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ""); } } } catch (Exception ex) { SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); LogService.LogError("AddUdaSalesOrderAttr Error: " + ex.Message); } return(dataDs); }
/// <summary> /// 搜索工单信息。可以使用工单号(ORDER_NUMBER),工单状态(ORDER_STATE)和工单优先级(ORDER_PRIORITY)作为搜索条件。 /// </summary> /// <param name="dataset"> /// 包含查询条件的数据集对象。若设置查询条件,数据集中必须包含数据表名为<see cref="POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME"/>的数据表对象, /// 数据表对象包含两个列分别为NAME和VALUE。 列NAME存放查询条件名,列VALUE存放查询条件值。 /// 列NAME可以存放如下查询条件:工单号(ORDER_NUMBER)(可选,模糊查询)、 /// 工单状态(ORDER_STATE)(可选,模糊查询)、工单优先级(ORDER_PRIORITY)(可选,模糊查询)。 /// </param> /// <returns>查询得到的包含工单信息的数据集对象。该数据集对象包含两个数据表对象。 /// 一个名称为<see cref="POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME"/>的数据表对象。 /// 一个名称为paraTable的数据表对象。 /// POR_WORK_ORDER数据表存放查询得到的工单信息。paraTable的数据表对象存放查询的执行结果,paraTable数据表对象中包含两列“ATTRIBUTE_KEY”和“ATTRIBUTE_VALUE”。最多包含两行:“ATTRIBUTE_KEY”列等于“CODE”的行和“ATTRIBUTE_KEY”列等于“MESSAGE”的行。 /// </returns> public object SearchWorkOrder(DataSet dataSet) { DataSet returnDS = AllCommonFunctions.GetDynamicTwoColumnsDataSet(); string sqlCommand = ""; try { //查询条件数据集不为null,且包含名称为POR_WORK_ORDER的数据表对象。 if (dataSet != null && dataSet.Tables.Contains(POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME)) { DataTable dataTable = dataSet.Tables[POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME]; Hashtable hashData = SolarViewer.Hemera.Share.Common.CommonUtils.ConvertToHashtable(dataTable); sqlCommand = @"SELECT WORK_ORDER_KEY, ORDER_NUMBER, ORDER_STATE, ORDER_PRIORITY, ENTERED_TIME, PROMISED_TIME FROM POR_WORK_ORDER WHERE ORDER_NUMBER LIKE '%" + hashData["ORDER_NUMBER"] + "%'"; //包含工单状态的查询条件 if (hashData["ORDER_STATE"] != null) { sqlCommand += "AND ORDER_STATE LIKE '%" + hashData["ORDER_STATE"] + "%'"; } //包含工单优先级的查询条件 if (hashData["ORDER_PRIORITY"] != null) { sqlCommand += "AND ORDER_PRIORITY LIKE '%" + hashData["ORDER_PRIORITY"] + "%'"; } } else { sqlCommand = "SELECT * FROM POR_WORK_ORDER"; } DataTable dtTable = new DataTable(); db = DatabaseFactory.CreateDatabase(); dtTable = db.ExecuteDataSet(CommandType.Text, sqlCommand).Tables[0]; dtTable.TableName = POR_WORK_ORDER_FIELDS.DATABASE_TABLE_NAME; //将查询得到的数据合并到数据集中 returnDS.Merge(dtTable, false, MissingSchemaAction.Add); //add paramter table SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(returnDS, ""); } catch (Exception ex) { SolarViewer.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(returnDS, ex.Message); LogService.LogError("SearchWorkOrder Error: " + ex.Message); } //返回数据集 return(returnDS); }
public DataSet DeleteMatSpecialInf(string _workOrderNum, string _mat, string _paramerTeam) { //get dynamic dataset constructor DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet(); //define sql String[] sqlCommand = new String[5]; try { if (_workOrderNum != string.Empty && _mat != string.Empty && _paramerTeam != string.Empty) { using (DbConnection dbConn = db.CreateConnection()) { //Open Connection dbConn.Open(); //Create Transaction DbTransaction dbTran = dbConn.BeginTransaction(); try { sqlCommand[0] = string.Format(@"UPDATE POR_WORK_ORDER_BOM_EXTENSION SET STATUS = 0 WHERE STATUS = 1 AND ORDER_NUMBER = '{0}' AND MATERIAL_CODE = '{1}' AND MATKL = '{2}'", _workOrderNum, _mat, _paramerTeam); db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand[0]); //Commit Transaction dbTran.Commit(); FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ""); } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); //Rollback Transaction dbTran.Rollback(); LogService.LogError("DeleteMatSpecialInf Error: " + ex.Message); } finally { //Close Connection dbConn.Close(); } } } } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); LogService.LogError("DeleteMatSpecialInf Error: " + ex.Message); } return(dataDs); }
public DataSet DeleteSupplierCode(string supplierCode) { //get dynamic dataset constructor DataSet dataDs = AllCommonFunctions.GetDynamicTwoColumnsDataSet(); //define sql String[] sqlCommand = new String[5]; try { if (supplierCode != string.Empty) { using (DbConnection dbConn = db.CreateConnection()) { //Open Connection dbConn.Open(); //Create Transaction DbTransaction dbTran = dbConn.BeginTransaction(); try { sqlCommand[0] = "DELETE BASE_SUPPLIER WHERE CODE = '" + supplierCode + "'"; db.ExecuteNonQuery(dbTran, CommandType.Text, sqlCommand[0]); //Commit Transaction dbTran.Commit(); FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ""); } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); //Rollback Transaction dbTran.Rollback(); LogService.LogError("DeleteReasonCode Error: " + ex.Message); } finally { //Close Connection dbConn.Close(); } } } } catch (Exception ex) { FanHai.Hemera.Share.Common.ReturnMessageUtils.AddServerReturnMessage(dataDs, ex.Message); LogService.LogError("DeleteReasonCode Error: " + ex.Message); } return(dataDs); }