/// <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 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;
}
}
/// <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);
}
}
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 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));
}
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 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 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 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 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));
}
/// <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 DataTable getForDDL()
{
string strSQL = @"
SELECT
contract.id,
contract.time_create,
company.name,
concat(DATE_FORMAT(contract.time_create, '%Y-%m-%d') , '-' , company.name) AS text_show
FROM sales_contract contract
INNER JOIN sales_company company ON company.id = contract.id_company
WHERE id_company > 0
ORDER BY contract.id DESC
";
return(HelperMySql.GetDataTable(strSQL));
}
// 手动添加盘点货品时，根据货品名称或者货品生产厂家查找货品时，要先判断此货品是否已经在盘点单中
public static bool isRecordAdded(int intContractId, int intGoodsId)
{
string strSQL = @"
SELECT id
FROM inventory_record
WHERE id_contract = @id_contract
AND id_goods = @id_goods
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
aryParams[1] = new MySqlParameter("@id_goods", intGoodsId);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
return(objDT != null && objDT.Rows.Count > 0);
}
public static bool hasUsername(string strUsername)
{
string strSQL = @"SELECT id FROM sys_admin WHERE username = @username";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@username", strUsername);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT != null && objDT.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public static int getIdByName(string strCompanyName)
{
string strSQL = @"SELECT id FROM sales_company WHERE name = @name";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@name", strCompanyName);
DataTable objDT = HelperMySql.GetDataTable(strSQL, aryParams);
if (objDT != null && objDT.Rows.Count > 0)
{
return(Convert.ToInt32(objDT.Rows[0]["id"]));
}
else
{
return(0);
}
}
/// <summary>
/// 查询盘点单表里是否有记录，如果没有则返回0，有则返回最近一次盘点单的id
/// 添加盘点单的时候，会将所有库存量大于0的货品存到盘点货品表里
/// 这个时候要判断，如果是第一次加就把所有货品加过去，如果不是第一次，是第2，3。。。次
/// 就把前一次的盘点单里的货品记录再判断一次库存量是否大于0
/// 这个函数的作用是用在添加盘点单的时候看看是不是第一次
/// </summary>
/// <returns></returns>
public static int getLatestContractId()
{
string strSQL = @"
SELECT id
FROM inventory_contract
ORDER BY id DESC
LIMIT 1
";
DataTable objDT = HelperMySql.GetDataTable(strSQL);
if (objDT == null || objDT.Rows.Count <= 0)
{
return(0);
}
else
{
return(Convert.ToInt32(objDT.Rows[0]["id"]));
}
}
/// <summary>
/// 分页查询
/// </summary>
public static DataTable getPage(int intPage, int intPageSize)
{
string strSQL = @"
SELECT *
FROM inventory_contract
WHERE id <=
(
SELECT id
FROM inventory_contract
ORDER BY id DESC
LIMIT " + (intPage - 1) * intPageSize + @" , 1
)
ORDER BY id DESC
LIMIT @PageSize
";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@PageSize", intPageSize);
return(HelperMySql.GetDataTable(strSQL, aryParams));
}
/// <summary>
/// 新建某盘点单时，计算某货品的真实库存量，然后将所有真实计算库存量大于0的货品加到盘点货品表里
/// </summary>
/// <param name="intContractId">盘点单id</param>
public static void setInventoryRecord(int intContractId)
{
int intGoodsId;
decimal dcmAmount, dcmAmountStock, dcmAmountStockReal;
ModelInventoryRecord model;
// 查询所有货品
string strSQL = @"
SELECT id, amount, amount_stock
FROM sales_goods
ORDER BY time_add DESC
";
DataTable objDT = HelperMySql.GetDataTable(strSQL);
if (objDT == null || objDT.Rows.Count <= 0)
{
return;
}
// 循环遍历所有货品
for (int i = 0; i < objDT.Rows.Count; i++)
{
intGoodsId = Convert.ToInt32(objDT.Rows[i]["id"]);
dcmAmount = Convert.ToDecimal(objDT.Rows[i]["amount"]);
dcmAmountStock = Convert.ToDecimal(objDT.Rows[i]["amount_stock"]);
// 真实库存量
dcmAmountStockReal = dcmAmount - DalCheckoutRecord.getAmountByGoodsId(intGoodsId);
// 如果记录的库存量或者计算的真实库存量大于0，就将该货品加到盘点表里
// if (dcmAmountStock > 0 || dcmAmountStockReal > 0)
if (dcmAmountStock > 0)
{
model = new ModelInventoryRecord();
model.id_contract = intContractId;
model.id_goods = intGoodsId;
model.amount_real = dcmAmountStockReal;
model.amount_stock = dcmAmountStock;
model.amount_fill = 0;
add(model);
// 将某货品的库存清零
// DalSalesGoods.clearAmountStock(intGoodsId);
}
}
}
public static ModelSalesCompany getById(int intId)
{
string strSQL = @"SELECT * FROM sales_company 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);
}
ModelSalesCompany model = new ModelSalesCompany();
model.id = Convert.ToInt32(objDT.Rows[0]["id"]);
model.name = Convert.ToString(objDT.Rows[0]["name"]);
model.id_admin = Convert.ToInt32(objDT.Rows[0]["id_admin"]);
model.time_create = Convert.ToDateTime(objDT.Rows[0]["time_create"]);
model.is_deleted = Convert.ToInt32(objDT.Rows[0]["is_deleted"]);
return(model);
}
/// <summary>
/// 分页查询
/// </summary>
public static DataTable getPage(int intContractId, int intPage, int intPageSize)
{
string strSQL = @"
SELECT *
FROM sales_goods
WHERE id <=
(
SELECT id
FROM sales_goods
WHERE id_contract = @id_contract
ORDER BY id DESC
LIMIT " + (intPage - 1) * intPageSize + @" , 1
) AND id_contract = @id_contract
ORDER BY id DESC
LIMIT @PageSize
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
aryParams[1] = new MySqlParameter("@PageSize", intPageSize);
return(HelperMySql.GetDataTable(strSQL, aryParams));
}
public static ModelCheckoutRecord getById(int intId)
{
string strSQL = @"SELECT * FROM checkout_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)
{
ModelCheckoutRecord model = new ModelCheckoutRecord();
model.id = Convert.ToInt32(objDT.Rows[0]["id"]);
model.id_contract = Convert.ToInt32(objDT.Rows[0]["id_contract"]);
model.id_goods = Convert.ToInt32(objDT.Rows[0]["id_goods"]);
model.amount = Convert.ToDecimal(objDT.Rows[0]["amount"]);
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 分页查询
/// </summary>
public static DataTable getPage(int intPage, int intPageSize)
{
// 注意选的是所有删除标记是0的记录
string strSQL = @"
SELECT *
FROM sales_contract
WHERE id <=
(
SELECT id
FROM sales_contract
WHERE is_deleted = 0
ORDER BY id DESC
LIMIT " + (intPage - 1) * intPageSize + @" , 1
) AND is_deleted = 0
ORDER BY id DESC
LIMIT @PageSize
";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@PageSize", intPageSize);
return(HelperMySql.GetDataTable(strSQL, aryParams));
}
/// <summary>
/// 分页查询某个盘点单下所有盘点记录
/// </summary>
public static DataTable getPage(int intContractId, int intPage, int intPageSize)
{
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 <=
(
SELECT id
FROM inventory_record
WHERE id_contract = @id_contract
ORDER BY id DESC
LIMIT " + (intPage - 1) * intPageSize + @" , 1
) AND record.id_contract = @id_contract
ORDER BY id DESC
LIMIT @PageSize
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@id_contract", intContractId);
aryParams[1] = new MySqlParameter("@PageSize", intPageSize);
return(HelperMySql.GetDataTable(strSQL, aryParams));
}
public static ModelInventoryContract getById(int intId)
{
string strSQL = @"SELECT * FROM inventory_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)
{
return(null);
}
ModelInventoryContract model = new ModelInventoryContract();
model.id = Convert.ToInt32(objDT.Rows[0]["id"]);
model.id_admin = Convert.ToInt32(objDT.Rows[0]["id_admin"]);
model.name_sign = Convert.ToString(objDT.Rows[0]["name_sign"]);
model.time_create = Convert.ToDateTime(objDT.Rows[0]["time_create"]);
model.photo_urls = Convert.ToString(objDT.Rows[0]["photo_urls"]);
model.comment = Convert.ToString(objDT.Rows[0]["comment"]);
model.time_start = Convert.ToDateTime(objDT.Rows[0]["time_start"]);
model.time_end = Convert.ToDateTime(objDT.Rows[0]["time_end"]);
return(model);
}
/// <summary>
/// 得到某个盘点单下所有货品的盘点总价数组，第一个数字是真实库存数，第二个数字是实时库存数，第三个数字是盘点库存数
/// </summary>
/// <param name="intContractId">盘点单id</param>
/// <returns>某个盘点单下所有货品的盘点总价</returns>
public static decimal[] getPriceTotalInventory(int intContractId)
{
decimal[] aryReturn = { 0, 0, 0 };
string strSQL = @"
SELECT
record.amount_real,
record.amount_stock,
record.amount_fill,
goods.price_unit
FROM inventory_record record
INNER JOIN sales_goods goods
ON record.id_goods = goods.id
WHERE record.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(aryReturn);
}
decimal dcmAmountReal, dcmAmountStock, dcmAmountFill;
decimal dcmPriceUnit;
decimal dcmPriceTotalReal = 0;
decimal dcmPriceTotalStock = 0;
decimal dcmPriceTotalFill = 0;
for (int i = 0; i < objDT.Rows.Count; i++)
{
if (objDT.Rows[i]["amount_real"] is DBNull)
{
dcmAmountReal = 0;
}
else
{
dcmAmountReal = Convert.ToDecimal(objDT.Rows[i]["amount_real"]);
}
if (objDT.Rows[i]["amount_stock"] is DBNull)
{
dcmAmountStock = 0;
}
else
{
dcmAmountStock = Convert.ToDecimal(objDT.Rows[i]["amount_stock"]);
}
if (objDT.Rows[i]["amount_fill"] is DBNull)
{
dcmAmountFill = 0;
}
else
{
dcmAmountFill = Convert.ToDecimal(objDT.Rows[i]["amount_fill"]);
}
dcmPriceUnit = Convert.ToDecimal(objDT.Rows[i]["price_unit"]);
dcmPriceTotalReal += dcmPriceUnit * dcmAmountReal;
dcmPriceTotalStock += dcmPriceUnit * dcmAmountStock;
dcmPriceTotalFill += dcmPriceUnit * dcmAmountFill;
}
aryReturn[0] = dcmPriceTotalReal;
aryReturn[1] = dcmPriceTotalStock;
aryReturn[2] = dcmPriceTotalFill;
return(aryReturn);
}
public static DataTable getAll()
{
string strSQL = @"SELECT * FROM sales_contract";
return(HelperMySql.GetDataTable(strSQL));
}
public static DataTable getAll()
{
string strSQL = @"SELECT * FROM sales_company ORDER BY name";
return(HelperMySql.GetDataTable(strSQL));
}
public static DataTable getAll()
{
string strSQL = @"SELECT * FROM medical_record";
return(HelperMySql.GetDataTable(strSQL));
}
public static DataTable getAll()
{
string strSQL = @"SELECT * FROM sys_admin";
return(HelperMySql.GetDataTable(strSQL));
}
