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);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
int intAdminId = HelperUtility.hasPurviewPage("InventoryContract_add");
ViewState["AdminId"] = intAdminId;
// 得到上次盘点的结束时间
ModelInventoryContract model = BllInventoryContract.getLatestContract();
lblTimeLast.Text = model.time_end.ToString("yyyy-MM-dd");
tbTimeStart.Value = model.time_end.ToString("yyyy-MM-dd");
}
}
public static ModelInventoryContract getLatestContract()
{
ModelInventoryContract model;
int intId = DalInventoryContract.getLatestContractId();
if (intId > 0)
{
model = getById(intId);
}
else
{
model = new ModelInventoryContract();
model.time_end = Convert.ToDateTime("2019-01-01 00:00:00");
}
return(model);
}
public static string[] setExcel(int intContractId)
{
// 根据盘点单ID得到此盘点单下所有的盘点记录，转成DataTable作为Excel文件的数据源
DataTable objDT = BllInventoryRecord.getAll(intContractId);
objDT.TableName = "TableInventory";
DataRow objDR;
objDT.Columns.Add(new DataColumn("id_row", typeof(int)));
objDT.Columns.Add(new DataColumn("temp1", typeof(string)));
objDT.Columns.Add(new DataColumn("temp2", typeof(string)));
for (int i = 0; i < objDT.Rows.Count; i++)
{
objDR = objDT.Rows[i];
objDR["id_row"] = i + 1;
objDR["temp1"] = Convert.ToDateTime(objDR["time_sign"]).ToString("yyyy-MM-dd");
objDR["temp2"] = Convert.ToDateTime(objDR["validity_period"]).ToString("yyyy-MM-dd");
}
objDT.Columns.Remove(objDT.Columns["time_sign"]);
objDT.Columns.Remove(objDT.Columns["validity_period"]);
objDT.Columns["temp1"].ColumnName = "time_sign";
objDT.Columns["temp2"].ColumnName = "validity_period";
// 得到此盘点单的信息，设置要输出的Excel文件的文件名
ModelInventoryContract model = getById(intContractId);
string strDateShow = "盘点单 " +
model.time_start.ToString("yyyy年MM月dd日") +
" 至 " +
model.time_end.ToString("yyyy年MM月dd日");
string strFileName = "盘点单[" +
model.time_start.ToString("yyMMdd") +
"-" +
model.time_end.ToString("yyMMdd") +
"]-1.xlsx";
string strExcelTemplateFileName = "/Excel/Template/03盘点单.xlsx";
string strExcelOutFileName = "/Excel/Export/" + strFileName;
// 根据以上参数生成excel文件，并输出生成的excel文件路径
WorkbookDesigner objDesigner = new WorkbookDesigner();
objDesigner.SetDataSource("DateShow", strDateShow);
objDesigner.SetDataSource(objDT);
HelperExcel.ExportExcelByTemplate(objDesigner, strExcelTemplateFileName, strExcelOutFileName);
string[] aryReturn = new string[1];
aryReturn[0] = strExcelOutFileName;
return(aryReturn);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
int intAdminId = HelperUtility.hasPurviewPage("InventoryContract_update");
ViewState["AdminId"] = intAdminId;
// 本页只能从list.aspx的编辑页转过来
// 因此要得到要修改的id值和页面的page值用于修改成功后返回
int intId = HelperUtility.getQueryInt("id");
ViewState["id"] = intId;
int intPage = HelperUtility.getQueryInt("page");
ViewState["page"] = intPage;
// 根据入库单id查询得到入库单model
ModelInventoryContract model = BllInventoryContract.getById(intId);
tbNameSign.Text = model.name_sign;
tbComment.Text = model.comment;
tbTimeStart.Value = model.time_start.ToString("yyyy-MM-dd");
tbTimeEnd.Value = model.time_end.ToString("yyyy-MM-dd");
}
}
public static int add(ModelInventoryContract model)
{
string strSQL = @"
INSERT INTO inventory_contract (
id_admin,
name_sign,
time_create,
photo_urls,
comment,
time_start,
time_end
) VALUES (
@id_admin,
@name_sign,
@time_create,
@photo_urls,
@comment,
@time_start,
@time_end
)";
MySqlParameter[] aryParams = new MySqlParameter[7];
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);
if (HelperMySql.ExecuteNonQuery(strSQL, aryParams) > 0)
{
strSQL = "SELECT MAX(id) FROM inventory_contract";
object objReturn = HelperMySql.ExecuteScalar(strSQL);
return(objReturn == null ? 0 : Convert.ToInt32(objReturn));
}
else
{
return(0);
}
}
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);
}
protected void btnEdit_Click(object sender, EventArgs e)
{
if (!HelperUtility.hasPurviewOP("InventoryContract_update"))
{
HelperUtility.showAlert("没有操作权限", "/BackManager/home.aspx");
}
int intAdminId = Convert.ToInt32(ViewState["AdminId"]);
int intId = Convert.ToInt32(ViewState["id"]);
int intPage = Convert.ToInt32(ViewState["page"]);
string strThisPageUrl = "edit.aspx?id=" + intId + "&page=" + intPage;
string strMsgError = "";
// 验证输入
string strTimeStart = tbTimeStart.Value.Trim();
if ("".Equals(strTimeStart))
{
strMsgError += "开始时间不能为空！";
}
if (!HelperUtility.isDateType(strTimeStart))
{
strMsgError += "开始时间格式不正确！";
}
string strTimeEnd = tbTimeEnd.Value.Trim();
if ("".Equals(strTimeEnd))
{
strMsgError += "结束时间不能为空！";
}
if (!HelperUtility.isDateType(strTimeEnd))
{
strMsgError += "结束时间格式不正确！";
}
string strNameSign = tbNameSign.Text.Trim();
if ("".Equals(strNameSign))
{
strMsgError += "公司名不能为空！";
}
string strComment = tbComment.Text.Trim();
if (strComment.Length > 500)
{
strMsgError += "备注信息不能超过500个字数！";
}
if (!"".Equals(strMsgError))
{
HelperUtility.showAlert(strMsgError, strThisPageUrl);
return;
}
// 验证完毕，提交数据
ModelInventoryContract model = BllInventoryContract.getById(intId);
model.name_sign = strNameSign;
model.comment = strComment;
model.time_start = Convert.ToDateTime(strTimeStart);
model.time_end = Convert.ToDateTime(strTimeEnd);
// 更新数据库记录
BllInventoryContract.update(model);
// 跳转回列表页
Response.Redirect("/BackManager/inventory_contract/list.aspx?page=" + intPage);
}
public static void update(ModelInventoryContract model)
{
DalInventoryContract.update(model);
}
public static int add(ModelInventoryContract model)
{
return(DalInventoryContract.add(model));
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (!HelperUtility.hasPurviewOP("InventoryContract_add"))
{
string strUrl = "/BackManager/home.aspx";
HelperUtility.showAlert("没有操作权限", strUrl);
}
string strMsgError = "";
string strTimeStart = tbTimeStart.Value.Trim();
if ("".Equals(strTimeStart))
{
strMsgError += "开始时间不能为空！";
}
if (!HelperUtility.isDateType(strTimeStart))
{
strMsgError += "开始时间格式不正确！";
}
string strTimeEnd = tbTimeEnd.Value.Trim();
if ("".Equals(strTimeEnd))
{
strMsgError += "结束时间不能为空！";
}
if (!HelperUtility.isDateType(strTimeEnd))
{
strMsgError += "结束时间格式不正确！";
}
if (Convert.ToDateTime(strTimeEnd) <= Convert.ToDateTime(strTimeStart))
{
strMsgError += "结束时间不能早于开始时间！";
}
string strNameSign = tbNameSign.Text.Trim();
if ("".Equals(strNameSign))
{
strMsgError += "盘点人员签名不能为空！";
}
string strComment = tbComment.Text.Trim();
if (strComment.Length > 500)
{
strMsgError += "备注信息不能超过500个字数！";
}
if (!"".Equals(strMsgError))
{
HelperUtility.showAlert(strMsgError, "add.aspx");
return;
}
string strPhotoUrls = "";
// 验证完毕，提交数据，盘点单表添加一条记录
int intAdminId = Convert.ToInt32(ViewState["AdminId"]);
ModelInventoryContract model = new ModelInventoryContract();
model.id_admin = intAdminId;
model.name_sign = strNameSign;
model.photo_urls = strPhotoUrls;
model.comment = strComment;
model.time_start = Convert.ToDateTime(strTimeStart);
model.time_end = Convert.ToDateTime(strTimeEnd);
int intContractId = BllInventoryContract.add(model);
if (intContractId <= 0)
{
HelperUtility.showAlert("添加失败，请联系管理员！", "add.aspx");
}
// 盘点单表添加完成后，在盘点物品表里添加所有库存量大于0的货品记录
BllInventoryRecord.setInventoryRecord(intContractId);
HelperUtility.showAlert("添加成功！", "list.aspx");
}
