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); } }