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