/// <summary>
/// 2010-11-19
/// </summary>
/// <param name="lineTable"></param>
public void SaveLineSchedule(DataTable lineTable)
{
foreach (DataRow lineRow in lineTable.Rows)
{
SqlCreate sqlCreate = new SqlCreate("AS_SC_LINE", SqlType.INSERT);
sqlCreate.AppendQuote("LINECODE", lineRow["LINECODE"]);
sqlCreate.AppendQuote("ROUTECODE", lineRow["ROUTECODE"]);
sqlCreate.Append("QUANTITY", lineRow["QUANTITY"]);
sqlCreate.AppendQuote("BATCHNO", lineRow["BATCHNO"]);
sqlCreate.AppendQuote("ORDERDATE", lineRow["ORDERDATE"]);
string sql = sqlCreate.GetSQL();
ExecuteNonQuery(sql);
}
}
public void InsertEntity(DataSet dataSet)
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
if (dataRow.RowState == DataRowState.Added)
{
SqlCreate sqlCreate = new SqlCreate("SYS_USERLIST", SqlType.INSERT);
sqlCreate.AppendQuote("USERNAME", dataRow["USERNAME"]);
sqlCreate.AppendQuote("USERPASSWORD", dataRow["USERPASSWORD"]);
sqlCreate.AppendQuote("EMPLOYEECODE", dataRow["EMPLOYEECODE"]);
sqlCreate.AppendQuote("MEMO", dataRow["MEMO"].ToString().Replace("\'", "\''"));
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
}
public void InsertBatch(int batchNo, string channelCode, string cigaretteCode, string cigaretteName, int quantity, int StockRemainQuantity)
{
SqlCreate sqlCreate = new SqlCreate("AS_STOCK_IN_BATCH", SqlType.INSERT);
sqlCreate.Append("BATCHNO", batchNo);
sqlCreate.AppendQuote("CHANNELCODE", channelCode);
sqlCreate.AppendQuote("CIGARETTECODE", cigaretteCode);
sqlCreate.AppendQuote("CIGARETTENAME", cigaretteName);
sqlCreate.Append("QUANTITY", quantity);
sqlCreate.Append("INQUANTITY", StockRemainQuantity);
sqlCreate.AppendQuote("T1", DateTime.Now.ToString());
sqlCreate.AppendQuote("STATE", StockRemainQuantity == quantity ? "1" : "0");
string sql = sqlCreate.GetSQL();
ExecuteNonQuery(sql);
}
public void UpdateEntity(string lineCode, string status)
{
SqlCreate sqlCreate = new SqlCreate("AS_BI_LINEINFO", SqlType.UPDATE);
sqlCreate.AppendQuote("STATUS", status);
sqlCreate.AppendWhereQuote("LINECODE", lineCode);
ExecuteNonQuery(sqlCreate.GetSQL());
}
/// <summary>
/// 2010-11-21
/// </summary>
/// <param name="channelTable"></param>
public void UpdateQuantity(DataTable channelTable, bool isUseBalance)
{
foreach (DataRow channelRow in channelTable.Rows)
{
string sql = "UPDATE AS_SC_CHANNELUSED SET CIGARETTECODE = '{0}',CIGARETTENAME = '{1}',QUANTITY={2} " +
" WHERE ORDERDATE='{3}' AND BATCHNO='{4}' AND LINECODE='{5}' AND CHANNELCODE='{6}'";
ExecuteNonQuery(string.Format(sql, channelRow["CIGARETTECODE"], channelRow["CIGARETTENAME"], channelRow["QUANTITY"],
channelRow["ORDERDATE"], channelRow["BATCHNO"].ToString().Trim(), channelRow["LINECODE"], channelRow["CHANNELCODE"]));
if (isUseBalance && channelRow["CHANNELTYPE"].ToString() == "3" && channelRow["CIGARETTECODE"].ToString() == channelRow["D_CIGARETTECODE"].ToString())
{
SqlCreate sqlCreate = new SqlCreate("AS_SC_BALANCE", SqlType.INSERT);
sqlCreate.AppendQuote("ORDERDATE", channelRow["ORDERDATE"]);
sqlCreate.Append("BATCHNO", channelRow["BATCHNO"]);
sqlCreate.AppendQuote("CHANNELID", channelRow["CHANNELID"]);
sqlCreate.AppendQuote("LINECODE", channelRow["LINECODE"]);
sqlCreate.AppendQuote("CHANNELCODE", channelRow["CHANNELCODE"]);
sqlCreate.AppendQuote("CHANNELNAME", channelRow["CHANNELNAME"]);
sqlCreate.AppendQuote("CIGARETTECODE", channelRow["CIGARETTECODE"]);
sqlCreate.AppendQuote("CIGARETTENAME", channelRow["CIGARETTENAME"]);
int quantity = Convert.ToInt32(channelRow["QUANTITY"]) % 50;
int balance = Convert.ToInt32(channelRow["BALANCE"]);
sqlCreate.Append("QUANTITY", balance >= quantity ? 0 - quantity : 50 - quantity);
ExecuteNonQuery(sqlCreate.GetSQL());
}
else
{
ExecuteNonQuery(string.Format("DELETE FROM AS_SC_BALANCE WHERE CHANNELID = '{0}'", channelRow["CHANNELID"]));
}
}
}
/// <summary>
/// ¸üÐÂ
/// </summary>
/// <param name="dataSet"></param>
public void UpdateEntity(string sortID, string lineCode, string routeCode)
{
SqlCreate sqlCreate = new SqlCreate("AS_BI_ROUTE", SqlType.UPDATE);
sqlCreate.Append("SORTID", sortID);
sqlCreate.AppendQuote("LINECODE", lineCode);
sqlCreate.AppendWhereQuote("ROUTECODE", routeCode);
ExecuteNonQuery(sqlCreate.GetSQL());
}
public void UpdateEntity(Dictionary <string, string> parameters)
{
foreach (string key in parameters.Keys)
{
SqlCreate sqlCreate = new SqlCreate("AS_SYS_PARAMETER", SqlType.UPDATE);
sqlCreate.AppendQuote("PARAMETERVALUE", parameters[key]);
sqlCreate.AppendWhereQuote("PARAMETERNAME", key);
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
public void InsertEntity(DataSet dataSet)
{
try
{
foreach (DataRow dataRow in dataSet.Tables[0].Rows)
{
if (dataRow.RowState == DataRowState.Added)
{
SqlCreate sqlCreate = new SqlCreate("sys_GroupList", SqlType.INSERT);
sqlCreate.AppendQuote("GroupName", dataRow["GroupName"]);
sqlCreate.AppendQuote("Memo", dataRow["Memo"].ToString().Replace("\'", "\''"));
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
}
catch (Exception ex)
{
throw ex;
}
}
//zys_2011-10-06
public void UpdateStatus(DataTable table)
{
DataRow[] stockOutRows = table.Select(string.Format("STATE = '1'"), "STOCKOUTID");
foreach (DataRow row in stockOutRows)
{
SqlCreate sqlCreate = new SqlCreate("AS_STOCK_OUT", SqlType.UPDATE);
sqlCreate.AppendQuote("STATE", "1");
sqlCreate.AppendWhere("STOCKOUTID", row["STOCKOUTID"]);
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
//zys_2011-10-06
public void UpdateStockOutIdToStockIn(DataTable table)
{
DataRow[] stockInRows = table.Select(string.Format("STOCKINID IS NOT NULL AND STOCKOUTID <> 0 "), "STOCKINID");
foreach (DataRow row in stockInRows)
{
SqlCreate sqlCreate = new SqlCreate("AS_STOCK_IN", SqlType.UPDATE);
sqlCreate.AppendQuote("STOCKOUTID", row["STOCKOUTID"]);
sqlCreate.AppendWhere("STOCKINID", row["STOCKINID"]);
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
public void InsertEntity(DataSet dataSet)
{
foreach (DataRow dataRow in dataSet.Tables["AS_SYS_BARCODE"].Rows)
{
if (dataRow.RowState == DataRowState.Added)
{
SqlCreate sqlCreate = new SqlCreate("AS_SYS_BARCODE", SqlType.UPDATE);
sqlCreate.AppendQuote("CIGARETTECODE", dataRow["CIGARETTECODE"]);
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
}
public void UpdateEntity(DataSet dataSet)
{
foreach (DataRow dataRow in dataSet.Tables["AS_SYS_BARCODE"].Rows)
{
if (dataRow.RowState == DataRowState.Modified)
{
SqlCreate sqlCreate = new SqlCreate("AS_BI_CHANNEL", SqlType.UPDATE);
sqlCreate.AppendQuote("CIGARETTECODE", dataRow["CIGARETTECODE"]);
sqlCreate.AppendWhereQuote("CHANNELID", dataRow["CHANNELID", DataRowVersion.Original]);
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
}
internal void UpdateEntity(string channelID, string cigaretteCode, string cigaretteName, string status)
{
SqlCreate sqlCreate = new SqlCreate("AS_BI_CHANNEL", SqlType.UPDATE);
sqlCreate.AppendQuote("CIGARETTECODE", cigaretteCode.Trim());
sqlCreate.AppendQuote("CIGARETTENAME", cigaretteName.Trim());
sqlCreate.AppendQuote("STATUS", status);
sqlCreate.AppendWhereQuote("CHANNELID", channelID);
ExecuteNonQuery(sqlCreate.GetSQL());
string sql = "INSERT INTO AS_SC_BALANCE_OUT" +
" SELECT GETDATE(),LINECODE,CHANNELCODE,CHANNELNAME,CIGARETTECODE, CIGARETTENAME, " +
" SUM(QUANTITY) AS QUANTITY,0" +
" FROM AS_SC_BALANCE" +
" WHERE CHANNELID = '{0}'" +
" GROUP BY LINECODE,CHANNELCODE,CHANNELNAME,CIGARETTECODE, CIGARETTENAME" +
" HAVING ISNULL(SUM(QUANTITY),0) > 0 ";
ExecuteNonQuery(string.Format(sql, channelID));
sql = "DELETE FROM AS_SC_BALANCE WHERE CHANNELID = '{0}'";
ExecuteNonQuery(string.Format(sql, channelID));
}
//zys_2011-10-06
internal void InsertBatch(int batchNo, string lineCode, string channelGroup, string channelType, int sortNo, int quantity)
{
SqlCreate sqlCreate = new SqlCreate("AS_STOCK_OUT_BATCH", SqlType.INSERT);
sqlCreate.Append("BATCHNO", batchNo);
sqlCreate.AppendQuote("LINECODE", lineCode);
sqlCreate.Append("CHANNELGROUP", channelGroup);
sqlCreate.Append("CHANNELTYPE", channelType);
sqlCreate.Append("SORTNO", sortNo);
sqlCreate.Append("QUANTITY", quantity);
sqlCreate.Append("OUTQUANTITY", 0);
string sql = sqlCreate.GetSQL();
ExecuteNonQuery(sql);
}
public void InsertEntity(string orderDate, int batchNo)
{
DateTime SCDATE = DateTime.Parse(orderDate);
SqlCreate sqlCreate = new SqlCreate("AS_BI_BATCH", SqlType.INSERT);
sqlCreate.Append("BATCHNO", batchNo);
sqlCreate.AppendQuote("BATCHNAME", string.Format("{0}第{1}批次", orderDate, batchNo));
sqlCreate.AppendQuote("ORDERDATE", orderDate);
sqlCreate.AppendQuote("ISVALID", 0);
sqlCreate.AppendQuote("EXECUTEUSER", 0);
sqlCreate.AppendQuote("EXECUTEIP", 0);
sqlCreate.AppendQuote("ISUPTONOONEPRO", 0);
sqlCreate.AppendQuote("SCDATE", DateTime.Now.ToShortDateString());
ExecuteNonQuery(sqlCreate.GetSQL());
}
public void InsertEntity(string employeeCode, string employeeName, string departmentID, string status, string remark)
{
int maxID = Convert.ToInt32(ExecuteScalar("SELECT CASE WHEN MAX(EMPLOYEEID) IS NULL THEN 0 ELSE MAX(EMPLOYEEID) END FROM AS_BI_EMPLOYEE")) + 1;
SqlCreate sqlCreate = new SqlCreate("AS_BI_EMPLOYEE", SqlType.INSERT);
sqlCreate.AppendQuote("EMPLOYEEID", maxID.ToString().PadLeft(5, '0'));
sqlCreate.AppendQuote("EMPLOYEECODE", employeeCode);
sqlCreate.AppendQuote("EMPLOYEENAME", employeeName);
sqlCreate.AppendQuote("DEPARTMENTID", departmentID);
sqlCreate.AppendQuote("STATUS", status);
sqlCreate.AppendQuote("REMARK", remark);
ExecuteNonQuery(sqlCreate.GetSQL());
}
public void SaveOrderSchedule(DataTable orderTable)
{
foreach (DataRow orderRow in orderTable.Rows)
{
SqlCreate sql = new SqlCreate("AS_SC_ORDER", SqlType.INSERT);
sql.Append("SORTNO", orderRow["SORTNO"]);
sql.AppendQuote("LINECODE", orderRow["LINECODE"]);
sql.AppendQuote("BATCHNO", orderRow["BATCHNO"]);
sql.AppendQuote("ORDERID", orderRow["ORDERID"]);
sql.Append("ORDERNO", 1);
sql.AppendQuote("ORDERDATE", orderRow["ORDERDATE"]);
sql.AppendQuote("CIGARETTECODE", orderRow["CIGARETTECODE"]);
sql.AppendQuote("CIGARETTENAME", orderRow["CIGARETTENAME"]);
sql.AppendQuote("CHANNELCODE", orderRow["CHANNELCODE"]);
sql.Append("QUANTITY", orderRow["QUANTITY"]);
ExecuteNonQuery(sql.GetSQL());
}
}
//zys_2011-10-05
internal void Insert(int outID, DataTable supplyTable)
{
foreach (DataRow row in supplyTable.Rows)
{
SqlCreate sqlCreate = new SqlCreate("AS_STOCK_OUT", SqlType.INSERT);
sqlCreate.Append("STOCKOUTID", ++outID);
sqlCreate.AppendQuote("ORDERDATE", row["ORDERDATE"]);
sqlCreate.Append("BATCHNO", row["BATCHNO"]);
sqlCreate.AppendQuote("LINECODE", row["LINECODE"]);
sqlCreate.Append("SORTNO", row["SORTNO"]);
sqlCreate.Append("SERIALNO", row["SERIALNO"]);
sqlCreate.AppendQuote("CIGARETTECODE", row["CIGARETTECODE"]);
sqlCreate.AppendQuote("CIGARETTENAME", row["CIGARETTENAME"]);
sqlCreate.AppendQuote("BARCODE", row["BARCODE"]);
sqlCreate.AppendQuote("CHANNELCODE", row["CHANNELCODE"]);
ExecuteNonQuery(sqlCreate.GetSQL());
}
}
/// <summary>
/// 2010-11-19 todo
/// </summary>
/// <param name="channelTable"></param>
/// <param name="orderDate"></param>
/// <param name="batchNo"></param>
public void SaveChannelSchedule(DataTable channelTable, string orderDate, int batchNo)
{
foreach (DataRow channelRow in channelTable.Rows)
{
SqlCreate sql = new SqlCreate("AS_SC_CHANNELUSED", SqlType.INSERT);
sql.AppendQuote("ORDERDATE", orderDate);
sql.AppendQuote("BATCHNO", batchNo);
sql.AppendQuote("CHANNELID", channelRow["CHANNELID"]);
sql.AppendQuote("LINECODE", channelRow["LINECODE"]);
sql.AppendQuote("CHANNELCODE", channelRow["CHANNELCODE"]);
sql.AppendQuote("CHANNELNAME", channelRow["CHANNELNAME"]);
sql.AppendQuote("CHANNELTYPE", channelRow["CHANNELTYPE"]);
sql.AppendQuote("CIGARETTECODE", channelRow["CIGARETTECODE"]);
sql.AppendQuote("CIGARETTENAME", channelRow["CIGARETTENAME"]);
sql.Append("QUANTITY", channelRow["QUANTITY"]);
sql.AppendQuote("STATUS", channelRow["STATUS"]);
sql.AppendQuote("LEDGROUP", channelRow["LEDGROUP"]);
sql.AppendQuote("LEDNO", channelRow["LEDNO"]);
sql.AppendQuote("LED_X", channelRow["LED_X"]);
sql.AppendQuote("LED_Y", channelRow["LED_Y"]);
sql.AppendQuote("LED_WIDTH", channelRow["LED_WIDTH"]);
sql.AppendQuote("LED_HEIGHT", channelRow["LED_HEIGHT"]);
sql.AppendQuote("GROUPNO", channelRow["GROUPNO"]);
sql.AppendQuote("CHANNELGROUP", channelRow["CHANNELGROUP"]);
sql.AppendQuote("CHANNELORDER", channelRow["CHANNELORDER"]);
sql.AppendQuote("CHANNELADDRESS", channelRow["CHANNELADDRESS"]);
sql.AppendQuote("SUPPLYADDRESS", channelRow["SUPPLYADDRESS"]);
ExecuteNonQuery(sql.GetSQL());
}
}
//public void SaveOrder(DataTable orderTable)
//{
// foreach (DataRow orderRow in orderTable.Rows)
// {
// SqlCreate sql = new SqlCreate("AS_SC_ORDER", SqlType.INSERT);
// sql.AppendQuote("ORDERDATE", orderRow["ORDERDATE"]);
// sql.Append("BATCHNO", orderRow["BATCHNO"]);
// sql.AppendQuote("LINECODE", orderRow["LINECODE"]);
// sql.Append("SORTNO", orderRow["SORTNO"]);
// sql.Append("PALLETNO", orderRow["PALLETNO"]);
// sql.AppendQuote("CHANNELCODE", orderRow["CHANNELCODE"]);
// sql.AppendQuote("CIGARETTECODE", orderRow["CIGARETTECODE"]);
// sql.AppendQuote("CIGARETTENAME", orderRow["CIGARETTENAME"]);
// sql.Append("QUANTITY", orderRow["QUANTITY"]);
// ExecuteNonQuery(sql.GetSQL());
// }
//}
public void SaveOrderMaster(DataTable masterTable)
{
foreach (DataRow orderRow in masterTable.Rows)
{
SqlCreate sql = new SqlCreate("AS_SC_PALLETMASTER", SqlType.INSERT);
sql.AppendQuote("ORDERDATE", orderRow["ORDERDATE"]);
sql.Append("BATCHNO", orderRow["BATCHNO"]);
sql.AppendQuote("LINECODE", orderRow["LINECODE"]);
sql.Append("SORTNO", orderRow["SORTNO"]);
sql.AppendQuote("ORDERID", orderRow["ORDERID"]);
sql.AppendQuote("AREACODE", orderRow["AREACODE"]);
sql.AppendQuote("AREANAME", orderRow["AREANAME"]);
sql.AppendQuote("ROUTECODE", orderRow["ROUTECODE"]);
sql.AppendQuote("ROUTENAME", orderRow["ROUTENAME"]);
sql.AppendQuote("CUSTOMERCODE", orderRow["CUSTOMERCODE"]);
sql.AppendQuote("CUSTOMERNAME", orderRow["CUSTOMERNAME"]);
sql.AppendQuote("ADDRESS", orderRow["ADDRESS"]);
sql.AppendQuote("ORDERNO", orderRow["ORDERNO"]);
sql.Append("QUANTITY", orderRow["QUANTITY"]);
sql.Append("ABNORMITY_QUANTITY", orderRow["ABNORMITY_QUANTITY"]);
ExecuteNonQuery(sql.GetSQL());
}
}
/// <summary>
/// 2010-11-21 todo
/// </summary>
/// <param name="masterTable"></param>
/// <param name="tableName"></param>
public void SaveOrderMaster(DataTable masterTable, string tableName)
{
foreach (DataRow orderRow in masterTable.Rows)
{
SqlCreate sql = new SqlCreate(tableName, SqlType.INSERT);
sql.AppendQuote("ORDERDATE", orderRow["ORDERDATE"]);
sql.Append("BATCHNO", orderRow["BATCHNO"]);
sql.AppendQuote("LINECODE", orderRow["LINECODE"]);
sql.Append("SORTNO", orderRow["SORTNO"]);
sql.AppendQuote("ORDERID", orderRow["ORDERID"]);
sql.AppendQuote("AREACODE", orderRow["AREACODE"]);
sql.AppendQuote("AREANAME", orderRow["AREANAME"]);
sql.AppendQuote("ROUTECODE", orderRow["ROUTECODE"]);
sql.AppendQuote("ROUTENAME", orderRow["ROUTENAME"]);
sql.AppendQuote("CUSTOMERCODE", orderRow["CUSTOMERCODE"]);
sql.AppendQuote("CUSTOMERNAME", orderRow["CUSTOMERNAME"]);
sql.AppendQuote("LICENSENO", orderRow["LICENSENO"]);
sql.AppendQuote("ADDRESS", orderRow["ADDRESS"]);
sql.AppendQuote("CUSTOMERSORTNO", orderRow["CUSTOMERSORTNO"]);
sql.AppendQuote("ORDERNO", orderRow["ORDERNO"]);
sql.Append("QUANTITY", orderRow["QUANTITY"]);
sql.Append("QUANTITY1", orderRow["QUANTITY1"]);
sql.Append("ABNORMITY_QUANTITY", orderRow["ABNORMITY_QUANTITY"]);
sql.Append("EXPORTNO", orderRow["EXPORTNO"]);
sql.Append("EXPORTNO1", orderRow["EXPORTNO1"]);
sql.AppendQuote("PACKNO", orderRow["PACKNO"]);
sql.AppendQuote("PACKNO1", orderRow["PACKNO1"]);
ExecuteNonQuery(sql.GetSQL());
}
}