protected List <string> getSQLList(GridView dgv)
{
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Rows[i].Cells.Count; j++)
{
if (dgv.Rows[i].Cells[j].Text == " ")
{
dgv.Rows[i].Cells[j].Text = "";
}
}
}
List <string> sList = new List <string>();
InsertCommandBuilder ins = new InsertCommandBuilder("Client_FC");
for (int i = 0; i < dgv.Rows.Count; i++)
{
ins.InsertColumn("NO_id", "1");
ins.InsertColumn("goods_name", dgv.Rows[i].Cells[0].Text.ToString());
//ins.InsertColumn("lb1_id", dgv.Rows[i].Cells[1].Text.ToString());
ins.InsertColumn("client_id", dgv.Rows[i].Cells[2].Text.ToString());
ins.InsertColumn("FC_qty", dgv.Rows[i].Cells[3].Text.ToString());
ins.InsertColumn("delivery_date", Convert.ToDateTime(dgv.Rows[i].Cells[4].Text).ToString("yyyy-MM-dd HH:mm:ss"));
ins.InsertColumn("delivery_no", dgv.Rows[i].Cells[5].Text.ToString());
ins.InsertColumn("remark", txtRemark.Text.Trim());
ins.InsertColumn("version_no", txtVer_No.Text.Trim());
ins.InsertColumn("status", "N");
ins.InsertColumn("operation_date", "getDate()");
ins.InsertColumn("operator_id", HttpContext.Current.Request.Cookies["cookie"].Values["id"]);
sList.Add(ins.getInsertCommand());
ins.CommandClear();
}
return(sList);
}
protected List <string> getSQLList(GridView dgv)
{
List <string> sList = new List <string>();
InsertCommandBuilder insert = new InsertCommandBuilder("Goods_Up");
for (int i = 0; i < dgv.Rows.Count; i++)
{
string goods_name = dgv.Rows[i].Cells[0].Text.ToString();
string prapareId = txtPrapareID.Text.Trim().ToUpper();
if (!checkCustomer(goods_name))
{
int j = i + 1;
dgv.Rows[i].BackColor = Color.Pink;
Response.Write("<script>alert('第" + j + "行客户代码错误')</script>");
btnUpload.Text = Resources.Resource.yl;
sList.Clear();
break;
}
else if (checkIsUpload(goods_name, prapareId))
{
int j = i + 1;
dgv.Rows[i].BackColor = Color.Pink;
Response.Write("<script>alert('第" + j + "行已上传至数据库，请检查数据是否重复!')</script>");
btnUpload.Text = Resources.Resource.yl;
sList.Clear();
break;
}
else
{
insert.InsertColumn("goods_name", goods_name);
insert.InsertColumn("qty", dgv.Rows[i].Cells[1].Text.ToString());
insert.InsertColumn("khdm", ddlCustomers.SelectedItem.Value);
insert.InsertColumn("Prepare_goods_Id", prapareId);
insert.InsertColumn("delivery_date", txtdelivery_date.Text);
string sql = insert.getInsertCommand();
insert.CommandClear();
sList.Add(sql);
}
}
return(sList);
}
public void stockin(string id, string type_id, string goods_id, int qty, string hwh)
{
List <string> sList = new List <string>();
InsertCommandBuilder insert = new InsertCommandBuilder("pre_str_in_bill");
insert.InsertColumn("str_in_bill_id", id);
insert.InsertColumn("str_in_type_id", type_id);
insert.InsertColumn("operator_date", "getdate()");
insert.InsertColumn("str_in_date", "getdate");
insert.InsertColumn("store_id", "12");
insert.InsertColumn("create_user", ViewState["UserId"]);
insert.InsertColumn("come_from", "生产入库");
insert.InsertColumn("is_local", "y");
insert.InsertColumn("is_state", "N");
sList.Add(insert.getInsertCommand());
insert.CommandClear();
insert = new InsertCommandBuilder("pre_str_in_bill_detail");
insert.InsertColumn("batch_id", id);
insert.InsertColumn("str_in_bill_id", id);
insert.InsertColumn("goods_id", goods_id);
insert.InsertColumn("qty", qty);
insert.InsertColumn("hwh", hwh);
sList.Add(insert.getInsertCommand());
}
protected void btnSave_Click(object sender, EventArgs e)
{
txtAlert.Text = string.Empty;
try
{
List <string> sqlList = new List <string>();
InsertCommandBuilder ins = new InsertCommandBuilder("tmp_pk_detail");
//InsertCommandBuilder insert = new InsertCommandBuilder("pre_prd_pk_detail");
string Bill_id = CommadMethod.getNextId("PB", "0101");
for (int i = 0; i < iList.Count; i++)
{
List <string> sList = getBatch_id(iList[i].pm, iList[i].pch);
string detail_id = Bill_id + (i + 1).ToString().PadLeft(4, '0');
//string sql = "INSERT INTO tmp_pk_detail ( Bill_id, Batch_id, Goods_id, Qty, Pch, Detail_id, Price, is_can_sale, hwh ) VALUES ( '" + Bill_id + "', '盘盈入库', '3034', 500.000000, '无库存', 'PB2012072109381211401010938257800002', 1.225000, 'Y', '' )";
ins.CommandClear();
ins.InsertColumn("Bill_id", Bill_id);
ins.InsertColumn("Batch_id", sList[1].Trim());
ins.InsertColumn("Goods_id", sList[0].Trim());
ins.InsertColumn("Qty", iList[i].pdsl);
ins.InsertColumn("Pch", iList[i].pch);
ins.InsertColumn("Detail_id", detail_id);
ins.InsertColumn("is_can_sale", "Y");
ins.InsertColumn("hwh", iList[i].hwh);
sqlList.Add(ins.getInsertCommand());
//insert.CommandClear();
//insert.InsertColumn("prd_pk_id", ViewState["bill_id"]);
//insert.InsertColumn("stock_remain_id", ViewState["bill_no"]);
//insert.InsertColumn("materials_id", sList[0].Trim());
//insert.InsertColumn("pdsl", iList[i].pdsl);
//insert.InsertColumn("zmsl", 0);
//insert.InsertColumn("prd_batch_id", sList[1].Trim());
//insert.InsertColumn("zmsl1", 0);
//insert.InsertColumn("pdsl1", iList[i].pdsl);
//insert.InsertColumn("is_can_sale", "Y");
//insert.InsertColumn("detail_id", detail_id);
//sqlList.Add(insert.getInsertCommand());
}
ins.CommandClear();
ins = new InsertCommandBuilder("tmp_pk_bill");
ins.InsertColumn("Bill_Id", Bill_id);
ins.InsertColumn("Bill_no", ViewState["bill_no"]);
ins.InsertColumn("Pk_id", ViewState["bill_id"]);
ins.InsertColumn("store_id", ViewState["s"]);
ins.InsertColumn("Crt_emp", HttpContext.Current.Request.Cookies["cookie"].Values["id"]);
ins.InsertColumn("Crt_Date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
ins.InsertColumn("Status", "N");
ins.InsertColumn("isAdd", "Y");
//ins.InsertColumn("Remark", txtRemark.Text.Trim().ToUpper());
sqlList.Add(ins.getInsertCommand());
int c = ins.ExcutTransaction(sqlList);
if (c != 0)
{
//Response.Write("<script>alert('" + Resources.Resource.alterOk + "')</script>");
txtAlert.Text = Resources.Resource.alterOk;
gvAddData.DataSource = null;
gvAddData.DataBind();
iList.Clear();
}
else
{
//Response.Write("<script>alert('" + Resources.Resource.alterfiald + "')</script>");
txtAlert.Text = Resources.Resource.alterfiald;
}
}
catch (Exception ex)
{
txtAlert.Text = ex.Message;
//Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (gvAddData.Rows.Count == 0)
{
return;
}
List <string> sqlList = new List <string>();
InsertCommandBuilder insert = new InsertCommandBuilder();
if (gvAddData.Columns[11].Visible == false)
{
for (int i = 0; i < aList.Count; i++)
{
UpdateCommandBuilder up = new UpdateCommandBuilder("pd_detail");
up.UpdateColumn("Qty", aList[i].pdsl);
up.UpdateColumn("pch", aList[i].pch);
up.UpdateColumn("hwh", aList[i].hwh);
up.ConditionsColumn("Detail_id", aList[i].id);
sqlList.Add(up.getUpdateCommand());
//UpdateCommandBuilder up1 = new UpdateCommandBuilder("pre_prd_pk_detail");
//up1.UpdateColumn("pdsl", aList[i].pdsl);
//up1.ConditionsColumn("Detail_id", aList[i].id);
//sqlList.Add(up1.getUpdateCommand());
}
}
else
{
InsertCommandBuilder ins = new InsertCommandBuilder("pd_detail");
string Bill_id = "PD" + DateTime.Now.ToString("ddHHmmssfff");
for (int i = 0; i < lList.Count; i++)
{
List <string> sList = getBatch_id(lList[i].pm, lList[i].pch);
string detail_id = Bill_id + (i + 1).ToString().PadLeft(4, '0');
//string sql = "INSERT INTO tmp_pk_detail ( Bill_id, Batch_id, Goods_id, Qty, Pch, Detail_id, Price, is_can_sale, hwh ) VALUES ( '" + Bill_id + "', '盘盈入库', '3034', 500.000000, '无库存', 'PB2012072109381211401010938257800002', 1.225000, 'Y', '' )";
ins.CommandClear();
ins.InsertColumn("Bill_id", Bill_id);
ins.InsertColumn("prd_Batch_id", sList[1].Trim());
ins.InsertColumn("materials_id", sList[0].Trim());
ins.InsertColumn("Qty", lList[i].pdsl);
ins.InsertColumn("Pch", lList[i].pch);
ins.InsertColumn("Detail_id", detail_id);
ins.InsertColumn("Price", lList[i].Price);
ins.InsertColumn("is_can_sale", "Y");
ins.InsertColumn("hwh", lList[i].hwh);
ins.InsertColumn("is_new", "Y");
sqlList.Add(ins.getInsertCommand());
int countPD = Convert.ToInt32(new SelectCommandBuilder().ExecuteScalar("select count(*) from pre_prd_pk_detail WHERE (prd_pk_id = '" + txtPK_Id.Text.Trim().ToUpper() + "') AND (prd_batch_id = '" + lList[i].b_id + "') AND (stock_remain_id = '" + lList[i].s_id + "') "));
if (countPD != 0)
{
string updateSQL = "update pre_prd_pk_detail set pdsl =" + lList[i].pdsl + ",detail_id = '" + detail_id + "' WHERE (prd_pk_id = '" + txtPK_Id.Text.Trim().ToUpper() + "') AND (prd_batch_id = '" + lList[i].b_id + "') AND (stock_remain_id = '" + lList[i].s_id + "') ";
sqlList.Add(updateSQL);
}
}
ins.CommandClear();
ins = new InsertCommandBuilder("pd");
ins.InsertColumn("Bill_Id", Bill_id);
ins.InsertColumn("Bill_no", txtPK_No.Text.Trim());
ins.InsertColumn("Pk_id", txtPK_Id.Text.Trim().ToUpper());
ins.InsertColumn("store_id", ddlMaterialStock.SelectedItem.Value.Trim());
ins.InsertColumn("Crt_emp", HttpContext.Current.Request.Cookies["cookie"].Values["id"]);
ins.InsertColumn("Crt_Date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
ins.InsertColumn("Status", "N");
ins.InsertColumn("Remark", txtRemark.Text.Trim().ToUpper());
sqlList.Add(ins.getInsertCommand());
}
int count = insert.ExcutTransaction(sqlList);
if (count != 0)
{
Response.Write("<script>alert('" + Resources.Resource.alterOk + "')</script>");
lList.Clear();
aList.Clear();
gvAddData.DataSource = lList;
gvAddData.DataBind();
ClearTextBox();
ddlMaterialStock.Enabled = true;
ddlMaterialStock.SelectedIndex = 0;
gvDetailData.DataSource = null;
gvDetailData.DataBind();
}
}
protected void btnConfirm_Click(object sender, EventArgs e)
{
if (ddlVendor.SelectedIndex == 0)
{
ClientScript.RegisterClientScriptBlock(ClientScript.GetType(), "alert", "<script>alert('请选择供应商')</script>", false);
return;
}
if (string.IsNullOrEmpty(txtDate.Text))
{
ClientScript.RegisterClientScriptBlock(ClientScript.GetType(), "alert", "<script>alert('请选择采购日期')</script>", false);
return;
}
List <Details> DList = new List <Details>();
List <string> SList = new List <string>();
for (int i = 0; i < gvDetailData.Rows.Count; i++)
{
if ((gvDetailData.Rows[i].Cells[0].FindControl("checkbox1") as CheckBox).Checked == true)
{
if (string.IsNullOrEmpty((gvDetailData.Rows[i].Cells[6].FindControl("txtPrice") as TextBox).Text.Trim()))
{
ClientScript.RegisterClientScriptBlock(ClientScript.GetType(), "alert", "<script>alert('请输入价格')</script>", false);
(gvDetailData.Rows[i].Cells[6].FindControl("txtPrice") as TextBox).Focus();
return;
}
Details dItem = new Details()
{
Apply_No = Apply_No,
MNo = Mno,
Name = gvDetailData.Rows[i].Cells[2].Text.Trim(),
Texture = gvDetailData.Rows[i].Cells[3].Text.Trim(),
Spec = gvDetailData.Rows[i].Cells[4].Text.Trim(),
Qty = Convert.ToInt32(gvDetailData.Rows[i].Cells[5].Text.Trim()),
Price = Convert.ToDouble((gvDetailData.Rows[i].Cells[6].FindControl("txtPrice") as TextBox).Text.Trim()),
Id = Convert.ToInt64(gvDetailData.Rows[i].Cells[7].Text.Trim())
};
DList.Add(dItem);
}
}
if (DList.Count == 0)
{
ClientScript.RegisterClientScriptBlock(ClientScript.GetType(), "alert", "<script>alert('无选择数据')</script>", false);
return;
}
string Id = CommadMethod.getNextId("PCH" + DateTime.Now.ToString("yyyyMMdd"), "").Trim();
InsertCommandBuilder insert = new InsertCommandBuilder("m_materails_pch_order_detail");
for (int i = 0; i < DList.Count; i++)
{
UpdateCommandBuilder up = new UpdateCommandBuilder("m_materails_apply_detail");
up.UpdateColumn("is_check", "Y");
up.ConditionsColumn("Id", DList[i].Id);
insert.InsertColumn("Order_No", Id);
insert.InsertColumn("name", DList[i].Name);
insert.InsertColumn("texture", DList[i].Texture);
insert.InsertColumn("spec", DList[i].Spec);
insert.InsertColumn("qty", DList[i].Qty);
insert.InsertColumn("price", DList[i].Price);
insert.InsertColumn("is_end", "N");
insert.InsertColumn("wb_id", ddlwb.SelectedItem.Value);
SList.Add(insert.getInsertCommand());
SList.Add(up.getUpdateCommand());
insert.CommandClear();
}
InsertCommandBuilder ins = new InsertCommandBuilder("m_materails_pch_order");
ins.InsertColumn("Order_date", txtDate.Text);
ins.InsertColumn("Order_No", Id);
if (!string.IsNullOrEmpty(Mno))
{
ins.InsertColumn("mode_no", Mno);
}
ins.InsertColumn("Vendor_id", ddlVendor.SelectedItem.Value);
ins.InsertColumn("Remark", txtRemark.Text.Trim());
ins.InsertColumn("Operator_id", "0000");
ins.InsertColumn("Operator_Date", "getDate()");
ins.InsertColumn("isCheck", "N");
SList.Add(ins.getInsertCommand());
int count = ins.ExcutTransaction(SList);
if (count != 0)
{
ClientScript.RegisterClientScriptBlock(ClientScript.GetType(), "alert", "<script>alert('保存成功')</script>", false);
gvDetailData.DataSource = null;
gvDetailData.DataBind();
txtDate.Text = string.Empty;
txtRemark.Text = string.Empty;
ddlVendor.SelectedIndex = 0;
ddlwb.SelectedIndex = 0;
string updateSql = "";
int countY = 0;
if (string.IsNullOrEmpty(Mno))
{
updateSql = "update m_materails_apply set is_confirm = 'E' where apply_no='" + Apply_No.Trim() + "'";
countY = Convert.ToInt32(new SelectCommandBuilder().ExecuteScalar("select count(*) from m_materails_apply_detail where apply_no='" + Id.Trim() + "' and is_check = 'N' "));
}
else
{
updateSql = "update m_materails_apply set is_confirm = 'E' where mode_no = '" + Mno.Trim() + "'";
countY = Convert.ToInt32(new SelectCommandBuilder().ExecuteScalar("select count(*) from m_materails_apply_detail where apply_no='" + Id.Trim() + "' and is_check = 'N' "));
}
if (countY == 0)
{
new UpdateCommandBuilder().ExecuteNonQuery(updateSql);
}
}
else
{
ClientScript.RegisterClientScriptBlock(ClientScript.GetType(), "alert", "<script>alert('保存失败')</script>", false);
}
}