// 得到某个出库单下所有的出库货品的记录
public static DataTable getAll(int intContractId)
{
string strSQL = @"
SELECT
record.id,
record.id_goods,
record.amount,
contract.time_sign,
goods.name_product,
goods.name_factory,
goods.unit,
goods.price_unit,
goods.batch_number,
goods.validity_period,
goods.type
FROM checkout_record record
INNER JOIN sales_goods goods
ON record.id_goods = goods.id
INNER JOIN sales_contract contract
ON goods.id_contract = contract.id
WHERE
record.id_contract = @id_contract
";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
return(HelperMySql.GetDataTable(strSQL, aryParams));
}
public static ModelCheckoutContract getById(int intId)
{
string strSQL = @"SELECT * FROM checkout_contract WHERE id = @id";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT != null && objDT.Rows.Count > 0)
{
ModelCheckoutContract model = new ModelCheckoutContract();
model.id = Convert.ToInt32(objDT.Rows[0]["id"]);
model.id_admin = Convert.ToInt32(objDT.Rows[0]["id_admin"]);
model.time_create = Convert.ToDateTime(objDT.Rows[0]["time_create"]);
model.name_unit = Convert.ToString(objDT.Rows[0]["name_unit"]);
model.name_department = Convert.ToString(objDT.Rows[0]["name_department"]);
model.name_sign = Convert.ToString(objDT.Rows[0]["name_sign"]);
model.photo_urls = Convert.ToString(objDT.Rows[0]["photo_urls"]);
model.comment = Convert.ToString(objDT.Rows[0]["comment"]);
model.flag = Convert.ToInt16(objDT.Rows[0]["flag"]);
return(model);
}
else
{
return(null);
}
}
public static int add(ModelCheckoutRecord model)
{
string strSQL = @"
INSERT INTO checkout_record (
id_contract,
id_goods,
amount
) VALUES (
@id_contract,
@id_goods,
@amount
)";
MySqlParameter[] aryParams = new MySqlParameter[3];
aryParams[0] = new MySqlParameter("@id_contract", model.id_contract);
aryParams[1] = new MySqlParameter("@id_goods", model.id_goods);
aryParams[2] = new MySqlParameter("@amount", model.amount);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM checkout_record";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
public static int update(ModelCheckoutContract model)
{
string strSQL = @"
UPDATE checkout_contract
SET
id_admin = @id_admin,
time_create = @time_create,
name_unit = @name_unit,
name_department = @name_department,
name_sign = @name_sign,
photo_urls = @photo_urls,
comment = @comment,
flag = @flag
WHERE
id = @id
";
MySqlParameter[] aryParams = new MySqlParameter[9];
aryParams[0] = new MySqlParameter("@id_admin", model.id_admin);
aryParams[1] = new MySqlParameter("@time_create", model.time_create);
aryParams[2] = new MySqlParameter("@name_unit", model.name_unit);
aryParams[3] = new MySqlParameter("@name_department", model.name_department);
aryParams[4] = new MySqlParameter("@name_sign", model.name_sign);
aryParams[5] = new MySqlParameter("@photo_urls", model.photo_urls);
aryParams[6] = new MySqlParameter("@comment", model.comment);
aryParams[7] = new MySqlParameter("@flag", model.flag);
aryParams[8] = new MySqlParameter("@id", model.id);
return((int)HelperMySql.ExecuteNonQuery(strSQL, aryParams));
}
public static int add(ModelSalesCompany model)
{
string strSQL = @"
INSERT INTO sales_company (
name,
id_admin,
time_create,
is_deleted
) VALUES (
@name,
@id_admin,
@time_create,
@is_deleted
)";
MySqlParameter[] aryParams = new MySqlParameter[4];
aryParams[0] = new MySqlParameter("@name", model.name);
aryParams[1] = new MySqlParameter("@id_admin", model.id_admin);
aryParams[2] = new MySqlParameter("@time_create", model.time_create);
aryParams[3] = new MySqlParameter("@is_deleted", model.is_deleted);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM sales_company";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
/// <summary>
/// 将某盘点单中所有货品的库存量全部清0
/// </summary>
/// <param name="intContractId">盘点单id</param>
public static void clearZero(int intContractId)
{
int intGoodsId;
// 找出所有在此盘点单中的货品
string strSQL = @"
SELECT goods.id, goods.amount_stock
FROM sales_goods goods
LEFT JOIN inventory_record inventory
ON inventory.id_goods = goods.id
WHERE inventory.id_contract = @id_contract
";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT == null || objDT.Rows.Count <= 0)
{
return;
}
for (int i = 0; i < objDT.Rows.Count; i++)
{
intGoodsId = Convert.ToInt32(objDT.Rows[i]["id"]);
// 执行清零操作
DalSalesGoods.clearAmountStock(intGoodsId);
}
}
/// <summary>
/// 得到某个出库单下所有货品的总价
/// </summary>
/// <param name="intContractId">出库单id</param>
/// <returns>某个出库单下所有货品的总价</returns>
public static decimal getPriceTotal(int intContractId)
{
string strSQL = @"
SELECT id_goods, amount
FROM checkout_record
WHERE id_contract = @id_contract";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT == null || objDT.Rows.Count <= 0)
{
return(0);
}
int intGoodsId = 0;
decimal dcmAmount = 0;
decimal dcmPriceUnit = 0;
decimal dcmPriceTotal = 0;
ModelSalesGoods modelSalesGoods;
for (int i = 0; i < objDT.Rows.Count; i++)
{
intGoodsId = Convert.ToInt32(objDT.Rows[i]["id_goods"]);
dcmAmount = Convert.ToDecimal(objDT.Rows[i]["amount"]);
modelSalesGoods = DalSalesGoods.getById(intGoodsId);
dcmPriceUnit = modelSalesGoods != null ? modelSalesGoods.price_unit : 0;
dcmPriceTotal += dcmPriceUnit * dcmAmount;
}
return(dcmPriceTotal);
}
public static int update(ModelSalesContract model)
{
string strSQL = @"
UPDATE sales_contract
SET
id_company = @id_company,
id_admin = @id_admin,
time_sign = @time_sign,
time_create = @time_create,
photo_urls = @photo_urls,
comment = @comment,
is_deleted = @is_deleted
WHERE
id = @id
";
MySqlParameter[] aryParams = new MySqlParameter[8];
aryParams[0] = new MySqlParameter("@id_company", model.id_company);
aryParams[1] = new MySqlParameter("@id_admin", model.id_admin);
aryParams[2] = new MySqlParameter("@time_sign", model.time_sign);
aryParams[3] = new MySqlParameter("@time_create", model.time_create);
aryParams[4] = new MySqlParameter("@photo_urls", model.photo_urls);
aryParams[5] = new MySqlParameter("@comment", model.comment);
aryParams[6] = new MySqlParameter("@is_deleted", model.is_deleted);
aryParams[7] = new MySqlParameter("@id", model.id);
return(HelperMySql.ExecuteNonQuery(strSQL, aryParams));
}
public static int add(ModelInventoryRecord model)
{
string strSQL = @"
INSERT INTO inventory_record (
id_contract,
id_goods,
amount_real,
amount_stock,
amount_fill
) VALUES (
@id_contract,
@id_goods,
@amount_real,
@amount_stock,
@amount_fill
)";
MySqlParameter[] aryParams = new MySqlParameter[5];
aryParams[0] = new MySqlParameter("@id_contract", model.id_contract);
aryParams[1] = new MySqlParameter("@id_goods", model.id_goods);
aryParams[2] = new MySqlParameter("@amount_real", model.amount_real);
aryParams[3] = new MySqlParameter("@amount_stock", model.amount_stock);
aryParams[4] = new MySqlParameter("@amount_fill", model.amount_fill);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM inventory_record";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
public static ModelAdmin getById(int intId)
{
string strSQL = @"SELECT * FROM sys_admin WHERE id = @id";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT == null || objDT.Rows.Count <= 0)
{
return(null);
}
ModelAdmin model = new ModelAdmin();
model.id = Convert.ToInt32(objDT.Rows[0]["id"]);
model.username = Convert.ToString(objDT.Rows[0]["username"]);
model.password = Convert.ToString(objDT.Rows[0]["password"]);
model.salt = Convert.ToString(objDT.Rows[0]["salt"]);
model.real_name = Convert.ToString(objDT.Rows[0]["real_name"]);
model.id_card = Convert.ToString(objDT.Rows[0]["id_card"]);
model.mobile_phone = Convert.ToString(objDT.Rows[0]["mobile_phone"]);
model.avatar_url = Convert.ToString(objDT.Rows[0]["avatar_url"]);
model.time_add = Convert.ToDateTime(objDT.Rows[0]["time_add"]);
model.time_last_login = Convert.ToDateTime(objDT.Rows[0]["time_last_login"]);
model.enabled = Convert.ToInt32(objDT.Rows[0]["enabled"]);
model.purviews = Convert.ToString(objDT.Rows[0]["purviews"]);
return(model);
}
public static void login(string strUsername, string strPassword,
out int intId, out string strPurviews, out int intEnabled, out int intIsDeleted)
{
string strSQL = @"
SELECT
id,
purviews,
enabled,
is_deleted
FROM sys_admin
WHERE
username = @username AND
password = @password
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@username", strUsername);
aryParams[1] = new MySqlParameter("@password", strPassword);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT != null && objDT.Rows.Count > 0)
{
intId = Convert.ToInt32(objDT.Rows[0]["id"]);
strPurviews = Convert.ToString(objDT.Rows[0]["purviews"]);
intEnabled = Convert.ToInt32(objDT.Rows[0]["enabled"]);
intIsDeleted = Convert.ToInt32(objDT.Rows[0]["is_deleted"]);
}
else
{
intId = 0;
strPurviews = "";
intEnabled = 0;
intIsDeleted = 1;
}
}
public static ModelMedicalRecord getById(int intId)
{
string strSQL = @"SELECT * FROM medical_record WHERE id = @id";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT == null || objDT.Rows.Count <= 0)
{
return(null);
}
ModelMedicalRecord model = new ModelMedicalRecord();
model.id = Convert.ToInt32(objDT.Rows[0]["id"]);
model.sickbed_number = Convert.ToString(objDT.Rows[0]["sickbed_number"]);
model.name_real = Convert.ToString(objDT.Rows[0]["name_real"]);
model.sex = Convert.ToString(objDT.Rows[0]["sex"]);
model.birthday = Convert.ToDateTime(objDT.Rows[0]["birthday"]);
model.department = Convert.ToString(objDT.Rows[0]["department"]);
model.name_disease = Convert.ToString(objDT.Rows[0]["name_disease"]);
model.time_in = Convert.ToDateTime(objDT.Rows[0]["time_in"]);
model.time_out = Convert.ToDateTime(objDT.Rows[0]["time_out"]);
model.situation_out = Convert.ToString(objDT.Rows[0]["situation_out"]);
model.situation_in = Convert.ToString(objDT.Rows[0]["situation_in"]);
model.photo_urls = Convert.ToString(objDT.Rows[0]["photo_urls"]);
model.comment = Convert.ToString(objDT.Rows[0]["comment"]);
model.time_create = Convert.ToDateTime(objDT.Rows[0]["time_create"]);
model.id_admin = Convert.ToInt32(objDT.Rows[0]["id_admin"]);
model.is_deleted = Convert.ToInt16(objDT.Rows[0]["is_deleted"]);
return(model);
}
public static ModelSalesGoods getById(int intId)
{
string strSQL = @"SELECT * FROM sales_goods WHERE id = @id";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT == null || objDT.Rows.Count <= 0)
{
return(null);
}
ModelSalesGoods model = new ModelSalesGoods();
model.id = Convert.ToInt32(objDT.Rows[0]["id"]);
model.id_contract = Convert.ToInt32(objDT.Rows[0]["id_contract"]);
model.name_product = Convert.ToString(objDT.Rows[0]["name_product"]);
model.type = Convert.ToString(objDT.Rows[0]["type"]);
model.name_factory = Convert.ToString(objDT.Rows[0]["name_factory"]);
model.unit = Convert.ToString(objDT.Rows[0]["unit"]);
model.amount = Convert.ToDecimal(objDT.Rows[0]["amount"]);
model.price_unit = Convert.ToDecimal(objDT.Rows[0]["price_unit"]);
model.price_total = Convert.ToDecimal(objDT.Rows[0]["price_total"]);
model.batch_number = Convert.ToString(objDT.Rows[0]["batch_number"]);
model.validity_period = Convert.ToDateTime(objDT.Rows[0]["validity_period"]);
model.approval_number = Convert.ToString(objDT.Rows[0]["approval_number"]);
model.comment = Convert.ToString(objDT.Rows[0]["comment"]);
model.photo_urls = Convert.ToString(objDT.Rows[0]["photo_urls"]);
model.id_admin = Convert.ToInt32(objDT.Rows[0]["id_admin"]);
model.time_add = Convert.ToDateTime(objDT.Rows[0]["time_add"]);
model.amount_stock = Convert.ToDecimal(objDT.Rows[0]["amount_stock"]);
return(model);
}
public static void update(ModelInventoryContract model)
{
string strSQL = @"
UPDATE inventory_contract
SET
id_admin = @id_admin,
name_sign = @name_sign,
time_create = @time_create,
photo_urls = @photo_urls,
comment = @comment,
time_start = @time_start,
time_end = @time_end
WHERE
id = @id
";
MySqlParameter[] aryParams = new MySqlParameter[8];
aryParams[0] = new MySqlParameter("@id_admin", model.id_admin);
aryParams[1] = new MySqlParameter("@name_sign", model.name_sign);
aryParams[2] = new MySqlParameter("@time_create", model.time_create);
aryParams[3] = new MySqlParameter("@photo_urls", model.photo_urls);
aryParams[4] = new MySqlParameter("@comment", model.comment);
aryParams[5] = new MySqlParameter("@time_start", model.time_start);
aryParams[6] = new MySqlParameter("@time_end", model.time_end);
aryParams[7] = new MySqlParameter("@id", model.id);
HelperMySql.ExecuteNonQuery(strSQL, aryParams);
}
// 根据查询条件得到盘点记录
public static DataTable getByQuery(int intContractId, string strProductName)
{
string strSQL = @"
SELECT
record.id,
record.id_goods,
record.amount_real,
record.amount_stock,
record.amount_fill,
contract.time_sign,
goods.name_product,
goods.name_factory,
goods.price_unit,
goods.batch_number,
goods.validity_period,
goods.type,
goods.amount_stock
FROM inventory_record record
INNER JOIN sales_goods goods
ON record.id_goods = goods.id
INNER JOIN sales_contract contract
ON goods.id_contract = contract.id
WHERE
record.id_contract = @id_contract AND
goods.name_product LIKE CONCAT('%', @name_product, '%')
ORDER BY contract.time_sign ASC, goods.name_product ASC
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
aryParams[1] = new MySqlParameter("@name_product", strProductName);
return(HelperMySql.GetDataTable(strSQL, aryParams));
}
/// <summary>
/// 得到记录总数
/// </summary>
public static int getRecordsAmount()
{
string strSQL = @"SELECT COUNT(*) FROM inventory_contract";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
/// <summary>
/// 得到记录总数
/// </summary>
public static int getRecordsAmount()
{
// 注意选的是所有删除标记是0的记录
string strSQL = @"SELECT COUNT(*) FROM sales_contract WHERE is_deleted = 0";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
public static DataTable getAll(int intContractId)
{
string strSQL = @"SELECT * FROM sales_goods WHERE id_contract = @id_contract";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
return(HelperMySql.GetDataTable(strSQL, aryParams));
}
public static void deleteById(int intId)
{
string strSQL = @"DELETE FROM sales_contract WHERE id=@id";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
HelperMySql.ExecuteNonQuery(strSQL, aryParams);
}
public static string getRealNameById(int intId)
{
string strSQL = @"SELECT real_name FROM sys_admin WHERE id = @id";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
object objReturn = HelperMySql.ExecuteScalar(strSQL, aryParams);
return(objReturn == null ? "" : Convert.ToString(objReturn));
}
/// <summary>
/// 得到属于某个入库单下的所有货品记录总数
/// </summary>
public static int getRecordsAmount(int intContractId)
{
string strSQL = @"SELECT COUNT(id) FROM sales_goods WHERE id_contract = @id_contract";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
object objReturn = HelperMySql.ExecuteScalar(strSQL, aryParams);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
public static int add(ModelAdmin model)
{
string strSQL = @"
INSERT INTO sys_admin (
username,
password,
salt,
real_name,
id_card,
mobile_phone,
avatar_url,
time_add,
time_last_login,
enabled,
purviews,
is_deleted
) VALUES (
@username,
@password,
@salt,
@real_name,
@id_card,
@mobile_phone,
@avatar_url,
@time_add,
@time_last_login,
@enabled,
@purviews,
@is_deleted
)";
MySqlParameter[] aryParams = new MySqlParameter[12];
aryParams[0] = new MySqlParameter("@username", model.username);
aryParams[1] = new MySqlParameter("@password", model.password);
aryParams[2] = new MySqlParameter("@salt", model.salt);
aryParams[3] = new MySqlParameter("@real_name", model.real_name);
aryParams[4] = new MySqlParameter("@id_card", model.id_card);
aryParams[5] = new MySqlParameter("@mobile_phone", model.mobile_phone);
aryParams[6] = new MySqlParameter("@avatar_url", model.avatar_url);
aryParams[7] = new MySqlParameter("@time_add", model.time_add);
aryParams[8] = new MySqlParameter("@time_last_login", model.time_last_login);
aryParams[9] = new MySqlParameter("@enabled", model.enabled);
aryParams[10] = new MySqlParameter("@purviews", model.purviews);
aryParams[11] = new MySqlParameter("@is_deleted", model.is_deleted);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM sys_admin";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
/// <summary>
/// 将某货品的库存数清零
/// </summary>
/// <param name="intId">货品id</param>
/// <returns></returns>
public static int clearAmountStock(int intId)
{
string strSQL = @"
UPDATE sales_goods
SET amount_stock = 0
WHERE id = @id
";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
return(HelperMySql.ExecuteNonQuery(strSQL, aryParams));
}
/// <summary>
/// 得到某个入库单下所有货品的总价
/// </summary>
/// <param name="intContractId">入库单id</param>
/// <returns>某个入库单下所有货品的总价</returns>
public static decimal getPriceTotal(int intContractId)
{
string strSQL = @"
SELECT SUM(price_total)
FROM sales_goods
WHERE id_contract = @id_contract";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
object objTotal = HelperMySql.ExecuteScalar(strSQL, aryParams);
return(objTotal == null ? 0 : Convert.ToDecimal(objTotal));
}
/// <summary>
/// 得到某个货品的总出库数
/// </summary>
/// <param name="intGoodsId">出库货品id</param>
/// <returns>出库记录表中某个货品的总出库数</returns>
public static decimal getAmountByGoodsId(int intGoodsId)
{
string strSQL = @"
SELECT SUM(amount)
FROM checkout_record
WHERE id_goods = @id_goods";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_goods", intGoodsId);
object objTotal = HelperMySql.ExecuteScalar(strSQL, aryParams);
return(objTotal == null ? 0 : Convert.ToDecimal(objTotal));
}
/// <summary>
/// 某出库单添加出库货品或者某盘点单单独添加某货品时，按名称搜索库存中货品
/// </summary>
/// <param name="strProductName">货品名称关键字词</param>
/// <param name="strFactoryName">货品厂商关键字词</param>
/// <returns>库存中货品列表返回给前台的DataList显示用</returns>
public static DataTable getDTByName(string strProductName, string strFactoryName)
{
string strSql, strSqlHead, strSqlWhere;
strSqlHead = @"
SELECT
goods.id,
goods.id_contract,
goods.name_product,
goods.name_factory,
goods.type,
goods.price_unit,
goods.validity_period,
goods.amount,
goods.amount_stock,
contract.time_sign
FROM sales_goods goods
INNER JOIN sales_contract contract
ON
goods.id_contract = contract.id
WHERE
goods.amount_stock > 0
";
MySqlParameter[] aryParams;
if (!"".Equals(strProductName) && "".Equals(strFactoryName))
{
strSqlWhere = " AND name_product LIKE CONCAT('%', @ProductName, '%')";
aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@ProductName", strProductName);
}
else if ("".Equals(strProductName) && !"".Equals(strFactoryName))
{
strSqlWhere = " AND name_factory LIKE CONCAT('%', @FactoryName, '%')";
aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@FactoryName", strFactoryName);
}
else if (!"".Equals(strProductName) && !"".Equals(strFactoryName))
{
strSqlWhere = " AND name_product LIKE CONCAT('%', @ProductName, '%') AND " +
"name_factory LIKE CONCAT('%', @FactoryName, '%')";
aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@ProductName", strProductName);
aryParams[1] = new MySqlParameter("@FactoryName", strFactoryName);
}
else
{
return(null);
}
strSql = strSqlHead + strSqlWhere;
return(HelperMySql.GetDataTable(strSql, aryParams));
}
public static void deleteById(int intId)
{
// 删除某盘点单要先删除此盘点单下所有盘点记录
string strSQL = @"DELETE FROM inventory_record WHERE id_contract = @id_contract";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id_contract", intId);
HelperMySql.ExecuteNonQuery(strSQL, aryParams);
// 删除某盘点单
strSQL = @"DELETE FROM inventory_contract WHERE id = @id";
aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
HelperMySql.ExecuteNonQuery(strSQL, aryParams);
}
public static void deleteById(int intId)
{
string strSQL = @"
DELETE FROM sys_admin
WHERE
id = @id AND
username <> 'rush2112' OR
username <> 'wumin'
";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intId);
HelperMySql.ExecuteNonQuery(strSQL, aryParams);
}
/// <summary>
/// 盘点时，将某货品的库存数设置为盘点数
/// 本来货品的库存数是实时计算的，但是由于各种原因，库存数和盘点数对不上
/// 因此根据老敏要求，盘点时直接将库存数修改为盘点数
/// </summary>
/// <param name="dcmAmountInventoryShow"></param>
/// <param name="intId"></param>
/// <returns></returns>
public static int updateAmountStockByInventory(decimal dcmAmountInventoryShow, int intId)
{
string strSQL = @"
UPDATE sales_goods
SET
amount_stock = @AmountInventoryShow
WHERE
id = @id
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@AmountInventoryShow", dcmAmountInventoryShow);
aryParams[1] = new MySqlParameter("@id", intId);
return(HelperMySql.ExecuteNonQuery(strSQL, aryParams));
}
// 当删除一条出货记录时，要将这条货品的出货数量加回到库存里
public static int addAmountStock(int intGoodsId, decimal dcmAmountOut)
{
string strSQL = @"
UPDATE sales_goods
SET
amount_stock = amount_stock + @amountOut
WHERE
id = @id
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@amountOut", dcmAmountOut);
aryParams[1] = new MySqlParameter("@id", intGoodsId);
return(HelperMySql.ExecuteNonQuery(strSQL, aryParams));
}
