/// <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));
}
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 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);
}
}
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);
}
}
/// <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 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="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));
}
public static int add(ModelCheckoutContract model)
{
string strSQL = @"
INSERT INTO checkout_contract (
id_admin,
time_create,
name_unit,
name_department,
name_sign,
photo_urls,
comment,
flag
) VALUES (
@id_admin,
@time_create,
@name_unit,
@name_department,
@name_sign,
@photo_urls,
@comment,
@flag
)";
MySqlParameter[] aryParams = new MySqlParameter[8];
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);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM checkout_contract";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
/// <summary>
/// 得到某个货品的某个时间点之后的总出库数
/// </summary>
/// <param name="intGoodsId">出库货品id</param>
/// <param name="timeStart">某个时间点之后</param>
/// <returns>出库记录表中某个货品某个时间点之后的总出库数</returns>
public static decimal getAmountByGoodsIdAndTime(int intGoodsId, DateTime timeStart)
{
string strSQL = @"
SELECT SUM(amount)
FROM checkout_record record
INNER JOIN checkout_contract contract
ON
record.id_contract = contract.id
WHERE
record.id_goods = @id_goods AND
contract.time_create > @time_create
";
MySqlParameter[] aryParams = new MySqlParameter[2];
aryParams[0] = new MySqlParameter("@id_goods", intGoodsId);
aryParams[1] = new MySqlParameter("@time_create", timeStart);
object objTotal = HelperMySql.ExecuteScalar(strSQL, aryParams);
return(objTotal == null ? 0 : Convert.ToDecimal(objTotal));
}
public static int add(ModelSalesContract model)
{
string strSQL = @"
INSERT INTO sales_contract (
id_company,
id_admin,
time_sign,
time_create,
photo_urls,
comment,
is_deleted
) VALUES (
@id_company,
@id_admin,
@time_sign,
@time_create,
@photo_urls,
@comment,
@is_deleted
)";
MySqlParameter[] aryParams = new MySqlParameter[7];
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);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM sales_contract";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
public static void changeIsDeleted(int intAdminID)
{
if (intAdminID <= 0)
{
return;
}
int intIsDeleted = 0;
string strSQL = "SELECT is_deleted FROM sys_admin WHERE id = @id";
MySqlParameter[] aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intAdminID);
intIsDeleted = Convert.ToInt16(HelperMySql.ExecuteScalar(strSQL, aryParams));
if (intIsDeleted == 1)
{
strSQL = @"UPDATE sys_admin SET is_deleted = 0 WHERE id = @id";
}
else
{
strSQL = @"UPDATE sys_admin SET is_deleted = 1 WHERE id = @id";
}
aryParams = new MySqlParameter[1];
aryParams[0] = new MySqlParameter("@id", intAdminID);
HelperMySql.ExecuteNonQuery(strSQL, aryParams);
}
public static int add(ModelMedicalRecord model)
{
string strSQL = @"
INSERT INTO medical_record (
sickbed_number,
name_real,
sex,
birthday,
department,
name_disease,
time_in,
time_out,
situation_out,
situation_in,
photo_urls,
comment,
time_create,
id_admin,
is_deleted
) VALUES (
@sickbed_number,
@name_real,
@sex,
@birthday,
@department,
@name_disease,
@time_in,
@time_out,
@situation_out,
@situation_in,
@photo_urls,
@comment,
@time_create,
@id_admin,
@is_deleted
)";
MySqlParameter[] aryParams = new MySqlParameter[15];
aryParams[0] = new MySqlParameter("@sickbed_number", model.sickbed_number);
aryParams[1] = new MySqlParameter("@name_real", model.name_real);
aryParams[2] = new MySqlParameter("@sex", model.sex);
aryParams[3] = new MySqlParameter("@birthday", model.birthday);
aryParams[4] = new MySqlParameter("@department", model.department);
aryParams[5] = new MySqlParameter("@name_disease", model.name_disease);
aryParams[6] = new MySqlParameter("@time_in", model.time_in);
aryParams[7] = new MySqlParameter("@time_out", model.time_out);
aryParams[8] = new MySqlParameter("@situation_out", model.situation_out);
aryParams[9] = new MySqlParameter("@situation_in", model.situation_in);
aryParams[10] = new MySqlParameter("@photo_urls", model.photo_urls);
aryParams[11] = new MySqlParameter("@comment", model.comment);
aryParams[12] = new MySqlParameter("@time_create", model.time_create);
aryParams[13] = new MySqlParameter("@id_admin", model.id_admin);
aryParams[14] = new MySqlParameter("@is_deleted", model.is_deleted);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM medical_record";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
public static int add(ModelSalesGoods model)
{
string strSQL = @"
INSERT INTO sales_goods (
id_contract,
name_product,
type,
name_factory,
unit,
amount,
price_unit,
price_total,
batch_number,
validity_period,
approval_number,
comment,
photo_urls,
id_admin,
time_add,
amount_stock
) VALUES (
@id_contract,
@name_product,
@type,
@name_factory,
@unit,
@amount,
@price_unit,
@price_total,
@batch_number,
@validity_period,
@approval_number,
@comment,
@photo_urls,
@id_admin,
@time_add,
@amount_stock
)";
MySqlParameter[] aryParams = new MySqlParameter[16];
aryParams[0] = new MySqlParameter("@id_contract", model.id_contract);
aryParams[1] = new MySqlParameter("@name_product", model.name_product);
aryParams[2] = new MySqlParameter("@type", model.type);
aryParams[3] = new MySqlParameter("@name_factory", model.name_factory);
aryParams[4] = new MySqlParameter("@unit", model.unit);
aryParams[5] = new MySqlParameter("@amount", model.amount);
aryParams[6] = new MySqlParameter("@price_unit", model.price_unit);
aryParams[7] = new MySqlParameter("@price_total", model.price_total);
aryParams[8] = new MySqlParameter("@batch_number", model.batch_number);
aryParams[9] = new MySqlParameter("@validity_period", model.validity_period);
aryParams[10] = new MySqlParameter("@approval_number", model.approval_number);
aryParams[11] = new MySqlParameter("@comment", model.comment);
aryParams[12] = new MySqlParameter("@photo_urls", model.photo_urls);
aryParams[13] = new MySqlParameter("@id_admin", model.id_admin);
aryParams[14] = new MySqlParameter("@time_add", model.time_add);
aryParams[15] = new MySqlParameter("@amount_stock", model.amount_stock);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM sales_goods";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}