protected void btnFind_Click(object sender, EventArgs e)
{
dgv.Disabled = false;
SelectCommandBuilder select = new SelectCommandBuilder();
string sql = "SELECT name, spec, texture FROM m_materials";
if (txtM_name.Text != string.Empty)
{
sql += " where name like '" + txtM_name.Text.Trim() + "%'";
}
SqlDataReader dr = select.ExecuteReader(sql);
List <AddGoodsInfo> addinfo = new List <AddGoodsInfo>();
if (dr.HasRows)
{
while (dr.Read())
{
AddGoodsInfo info = new AddGoodsInfo()
{
name = dr.GetString(0),
spec = dr.IsDBNull(1) ? "" : dr.GetString(1),
cz = dr.IsDBNull(2) ? "" : dr.GetString(2)
};
addinfo.Add(info);
}
}
gvShowData.DataSource = addinfo;
gvShowData.DataBind();
}
public List <material> getIndexPageData()
{
List <material> mList = new List <material>();
SelectCommandBuilder select = new SelectCommandBuilder("materials");
select.SelectColumn("name");
select.SelectColumn("sccj_id");
select.SelectColumn("new_price");
select.SelectColumn("wb_name");
select.ConditionsColumn("1", 1);
select.getSelectCommand();
SqlDataReader dr = select.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
material m = new material()
{
name = dr.IsDBNull(0) ? "NULL" : dr.GetString(0),
sccj = dr.IsDBNull(1) ? "NULL" : getVendorById(dr.GetString(1)),
price = dr.IsDBNull(2) ? 0.0 : dr.GetDouble(2),
currency = dr.IsDBNull(3) ? "N" : dr.GetString(3),
exchangeRate = getexchangeRate(dr.IsDBNull(3) ? "N" : dr.GetString(3))
};
mList.Add(m);
}
}
ViewState["DataTable_GridView_ReferedDataDetail"] = ListToDataaTable(mList);
return(mList);
}
public List <CrushedMaterialDetail> getAllCrushedMaterial(string area, string status)
{
List <CrushedMaterialDetail> cList = new List <CrushedMaterialDetail>();
SelectCommandBuilder s = new SelectCommandBuilder(ConnectionFactory.ConnectionString_hudsonwwwroot, "trymolde");
string sql = "SELECT id, 品番, 材料, 材料编号, 模具担当, addtime, area1style, area2style, area3style, area4style FROM trymolde WHERE (PO处理 = '粉碎') AND (isApprove = 'OK') AND (area" + area + "style = '" + status + "') ORDER BY addtime";
SqlDataReader dr = s.ExecuteReader(sql);
if (dr.HasRows)
{
while (dr.Read())
{
CrushedMaterialDetail c = new CrushedMaterialDetail()
{
id = dr.GetInt32(0),
Name = dr.GetString(1),
Material = dr.GetString(2),
MaterialNo = dr.GetString(3),
JigLeader = dr.GetString(4),
addtime = dr.GetString(5),
area1style = dr.IsDBNull(6) ? "待粉碎" : getStatusdeng(dr.GetString(6)),
area2style = dr.IsDBNull(7) ? "待粉碎" : getStatusdeng(dr.GetString(7)),
area3style = dr.IsDBNull(8) ? "待粉碎" : getStatusdeng(dr.GetString(8)),
area4style = dr.IsDBNull(9) ? "待粉碎" : getStatusdeng(dr.GetString(9))
};
cList.Add(c);
}
}
return(cList);
}
public List <Vendor> getAllVendor(string VendorName)
{
List <Vendor> vList = new List <Vendor>();
string sql = "select vendor_id,vendor_name ,vendor_aname from Vendor where CheckFirst = 'Y'";
if (!string.IsNullOrEmpty(VendorName))
{
sql += " and vendor_name like '%" + VendorName + "%'";
}
SelectCommandBuilder select = new SelectCommandBuilder();
SqlDataReader reader = select.ExecuteReader(sql);
if (reader.HasRows)
{
while (reader.Read())
{
Vendor v = new Vendor()
{
VendorID = reader.GetString(0), VenderName = reader.GetString(1), VenderShortName = reader.IsDBNull(2) ? "null" : reader.GetString(2)
};
vList.Add(v);
}
}
return(vList);
}
public List <CrushedMaterial> getAllCrushedMaterial()
{
List <CrushedMaterial> cList = new List <CrushedMaterial>();
SelectCommandBuilder s = new SelectCommandBuilder(ConnectionFactory.ConnectionString_hudsonwwwroot, "trymolde");
string sql = "SELECT id, 品番, 材料, 材料编号, 模具担当, addtime, PO处理 FROM trymolde WHERE (PO处理 = '粉碎') AND(isApprove is null) ORDER BY addtime";
SqlDataReader dr = s.ExecuteReader(sql);
if (dr.HasRows)
{
while (dr.Read())
{
CrushedMaterial c = new CrushedMaterial()
{
id = dr.GetInt32(0),
Name = dr.GetString(1),
Material = dr.GetString(2),
MaterialNo = dr.GetString(3),
JigLeader = dr.GetString(4),
addtime = dr.GetString(5)
};
cList.Add(c);
}
}
return(cList);
}
protected void ButtonOk_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(Request.QueryString["id"]);
string goodsName = (TextgoodsName.Text).Trim();
string count = Textcount.Text;
string badcontent = Textbadcontent.Text;
string produceTime = string.IsNullOrWhiteSpace(TextproduceTime.Text.Trim()) ? "null" : "'" + TextproduceTime.Text.Trim() + "'";
string employeeName = TextemployeeName.Text;
string produceArea = DropDownListproduceArea.Value;
UpdateCommandBuilder ucd = new UpdateCommandBuilder();
string sqlgetPrice = @" select new_price from goods where goods_name='" + goodsName + "'";
//string sqlspec = @" select spec from goods where goods_name='" + goodsName + "'";
Debug.WriteLine(sqlgetPrice);
double price = 0;
using (SqlDataReader dr = cmd.ExecuteReader(sqlgetPrice))
{
while (dr.Read())
{
price = double.Parse(dr[0].ToString());
}
dr.Close();
}
string sqlExchange = @"select wb_hl from prd_dictate_wb inner join goods
on prd_dictate_wb.wb_name = goods.wb_name
where goods.new_price=" + price + "";
SqlDataReader dhl = new SelectCommandBuilder().ExecuteReader(sqlExchange);
double exchang = 1;
while (dhl.Read())
{
exchang = double.Parse(dhl[0].ToString());
}
dhl.Close();
price = price * exchang;
double moneySum = price * double.Parse(count);
//string spec = (string)cmd.ExecuteScalar(sqlspec);
string sql = @"update shatter_Parts set
goodsName='" + goodsName + "',Shattercount='" + count + "',price='" +
price + "',moneySum='" + moneySum + "',badContent='"
+ badcontent + "',produceTime=" + produceTime + ",employeeName='" +
employeeName + "',produceArea='" +
produceArea + "' where id=" + id + "";
Debug.WriteLine(sql);
ucd.ExecuteNonQuery(sql);
Response.Redirect("index.aspx");
}
public string getStockData(string Pid, string khdm, string goodsname, int SumQty, int pqty)
{
string str = "";
List <string> slist = new List <string>();
SelectCommandBuilder s = new SelectCommandBuilder();
string sql = @"SELECT d.hwh, SUM(u.qty) AS p_qty,sum(d.qty) as s_Qty, right(rtrim( d.pch),6) as pch
FROM goods_up u INNER JOIN
(SELECT goods.goods_id, goods.goods_name, c.batch_id, c.hwh, c.qty,
c.str_in_date, c.pch
FROM goods INNER JOIN
(SELECT a.goods_id, a.qty, b.str_in_date, a.batch_id, b.hwh, b.pch
FROM stock_remain a INNER JOIN
batch b ON a.batch_id = b.batch_id
WHERE a.store_id = '03') c ON goods.goods_id = c.goods_id) d ON
u.goods_name = d.goods_name
WHERE (u.prepare_goods_id = '" + Pid + "') AND (u.khdm = '" + khdm + @"')
GROUP BY d.hwh, d.pch, u.goods_name,d.str_in_date
HAVING (u.goods_name = '" + goodsname + "') ORDER BY u.goods_name,right(rtrim( d.pch),6),d.str_in_date";
SqlDataReader dr = s.ExecuteReader(sql);
if (dr.HasRows)
{
int sum = 0;
while (dr.Read())
{
//str += dr.GetString(0) + "(" + dr.GetInt32(1) + "),";
sum += int.Parse(dr.GetSqlDecimal(2).ToDouble().ToString());
slist.Add(dr.GetString(0) + "[" + dr.GetString(3) + "]" + "(" + int.Parse(dr.GetSqlDecimal(2).ToDouble().ToString()) + ")" + "(" + "_____" + ")");
if (SumQty <= sum || pqty <= sum)
{
break;
}
}
}
if (slist.Count != 0)
{
for (int i = 0; i < slist.Count; i++)
{
if (i != slist.Count - 1)
{
str += slist[i] + ",";
}
else
{
str += slist[i];
}
}
}
return(str);
}
private List <m> getAllData(string con)
{
List <m> mList = new List <m>();
SelectCommandBuilder s = new SelectCommandBuilder("m_materials");
s.SelectColumn("id");
s.SelectColumn("name");
s.SelectColumn("spec");
s.SelectColumn("sccj_id");
s.SelectColumn("new_price");
s.SelectColumn("wb_name");
s.SelectColumn("lb1_id");
s.SelectColumn("texture");
s.SelectColumn("color");
s.SelectColumn("dm");
s.ConditionsColumn("1", 1);
string sql = s.getSelectCommand();
if (txtName.Value != string.Empty)
{
sql += " and name like '" + txtName.Value + "%'";
}
SqlDataReader dr = s.ExecuteReader(sql);
if (dr.HasRows)
{
while (dr.Read())
{
m m = new m()
{
id = dr.GetInt64(0),
mc = dr.IsDBNull(1) ? "" : dr.GetString(1),
gg = dr.IsDBNull(2) ? "" : dr.GetString(2),
gys = dr.IsDBNull(3) ? "" : dr.GetString(3),
dj = dr.IsDBNull(4) ? "0.00" : dr.GetDouble(4).ToString("0.00#"),
bz = dr.IsDBNull(5) ? "" : dr.GetString(5),
lx = dr.IsDBNull(6) ? "" : dr.GetString(6),
cz = dr.IsDBNull(7) ? "" : dr.GetString(7),
ys = dr.IsDBNull(8) ? "" : dr.GetString(8),
jc = dr.IsDBNull(9) ? "" : dr.GetString(9)
};
mList.Add(m);
}
}
return(mList);
}
public List <material> getIndexPageData(string name, string sccj)
{
List <material> mList = new List <material>();
SelectCommandBuilder select = new SelectCommandBuilder("materials");
select.SelectColumn("name");
select.SelectColumn("sccj_id");
select.SelectColumn("new_price");
select.SelectColumn("wb_name");
select.ConditionsColumn("1", 1);
string sql = select.getSelectCommand();
if (!string.IsNullOrEmpty(name))
{
sql += "and name like '%" + name + "%'";
}
if (!string.IsNullOrEmpty(sccj))
{
select.ConditionsColumn("sccj_id", sccj);
sql += "and sccj_id = '" + sccj + "'";
}
SqlDataReader dr = select.ExecuteReader(sql);
if (dr.HasRows)
{
while (dr.Read())
{
material m = new material()
{
name = dr.GetString(0),
sccj = getVendorById(dr.GetString(1)),
price = dr.IsDBNull(2) ? 0.00 : dr.GetDouble(2),
currency = dr.IsDBNull(3) ? "RMB" : dr.GetString(3),
exchangeRate = getexchangeRate(dr.IsDBNull(3) ? "RMB" : dr.GetString(3))
};
mList.Add(m);
}
}
ViewState["DataTable_GridView_ReferedDataDetail"] = ListToDataaTable(mList);
return(mList);
}
protected void btnCommit_Click(object sender, EventArgs e)
{
if (ddlCustomers.SelectedItem.Value.ToString() != "0" || !string.IsNullOrEmpty(txtPrapareID.Text))
{
List <Stock> sList = new List <Stock>();
SelectCommandBuilder s = new SelectCommandBuilder();
gvData.DataSource = null;
gvData.DataBind();
string sql = "select DISTINCT Prepare_goods_Id ,khdm,delivery_date from Goods_Up where 1=1";
if (ddlCustomers.SelectedItem.Value.ToString() != "0")
{
sql += " and khdm = '" + ddlCustomers.SelectedItem.Value.Trim() + "'";
}
if (!string.IsNullOrEmpty(txtPrapareID.Text))
{
sql += " and Prepare_goods_Id ='" + txtPrapareID.Text.Trim().ToUpper() + "'";
}
SqlDataReader dr = s.ExecuteReader(sql + "order by delivery_date desc");
if (dr.HasRows)
{
while (dr.Read())
{
Stock stock = new Stock()
{
pId = dr.GetString(0),
khdm = dr.GetString(1),
Date = dr.GetDateTime(2).ToString("yyyy-MM-dd"),
Visible = getVisible(dr.GetString(0), dr.GetString(1)) == true ? "是/Y" : "否/N"
};
sList.Add(stock);
}
}
gvExcel.DataSource = sList;
gvExcel.DataBind();
}
else
{
Response.Write("<script>alert('请输入查询条件!')</script>");
ddlCustomers.Focus();
}
}
public List <StockDetail> getStockData(string Pid, string khdm)
{
List <StockDetail> sdList = new List <StockDetail>();
SelectCommandBuilder s = new SelectCommandBuilder();
string sql = @"SELECT goods.goods_name, goods.bzxx, goods.jzl, goods.classify_area, goods_up.qty,
ISNULL(delivery_plan.plan_qty, 0) AS plan_qty, SUM(goods_up.qty)
AS xsht_detailqty, CEILING(goods_up.qty / goods.jzl) AS jzlQty,
ISNULL(stock_remain.sumQty, 0) AS sumQty, goods_up.khdm,
goods_up.prepare_goods_id, ISNULL(stock_remain.sumQty2, 0) AS sumQty2,goods.分类区域
FROM goods_up LEFT OUTER JOIN
goods ON goods.goods_name = goods_up.goods_name LEFT OUTER JOIN
xsht_detail ON goods.goods_id = xsht_detail.goods_id LEFT OUTER JOIN
xsht ON xsht_detail.xsht_id = xsht.xsht_id LEFT OUTER JOIN
(SELECT delivery_plan.goods_id,
SUM(delivery_plan.plan_qty - ISNULL(delivery_plan.delivery_qty, 0))
AS plan_qty
FROM delivery_plan
WHERE is_end = 'N'
GROUP BY delivery_plan.goods_id) delivery_plan ON
goods.goods_id = delivery_plan.goods_id LEFT OUTER JOIN
(SELECT a.goods_id, a.sumQty, isnull(b.sumQty2, 0) sumQty2
FROM (SELECT goods_id, SUM(qty) sumQty
FROM stock_remain
WHERE store_id = '03'
GROUP BY goods_id) a LEFT JOIN
(SELECT goods_id, SUM(qty) sumQty2
FROM stock_remain
WHERE store_id = '12'
GROUP BY goods_id) b ON a.goods_id = b.goods_id) stock_remain ON
goods.goods_id = stock_remain.goods_id
WHERE (Goods_Up.Prepare_goods_Id ='" + Pid + @"') AND (Goods_Up.khdm ='" + khdm + @"') AND (xsht.is_end = 'M')
GROUP BY goods.goods_name, goods.bzxx, goods.jzl, goods.classify_area,
goods_up.qty, ISNULL(delivery_plan.plan_qty, 0), CEILING(goods_up.qty / goods.jzl),
stock_remain.sumQty,stock_remain.sumQty2,goods_up.khdm, goods_up.prepare_goods_id,goods.分类区域 ORDER BY goods.分类区域 DESC, goods.goods_name";
//s.SelectColumn("goods_name");
//s.SelectColumn("qty");
//s.ConditionsColumn("khdm", khdm);
//s.ConditionsColumn("Prepare_goods_Id", Pid);
//s.getSelectCommand();
SqlDataReader dr = s.ExecuteReader(sql);
if (dr.HasRows)
{
while (dr.Read())
{
StockDetail sd = new StockDetail();
sd.goodsName = dr.GetString(0);
sd.bzxx = dr.IsDBNull(1) ? "" : dr.GetString(1);
sd.jzl = Convert.ToInt32(dr.GetDecimal(2));
sd.area = dr.IsDBNull(3) ? "" : dr.GetString(3);
sd.Qty = dr.GetDecimal(4);
sd.plan_qty = int.Parse(dr.GetSqlDecimal(5).ToDouble().ToString());
sd.xsht_detailqty = Convert.ToInt32(dr.GetDecimal(6));
sd.jzlqty = dr.GetDecimal(7);
sd.stockSumQty = int.Parse(dr.GetSqlDecimal(8).ToDouble().ToString());
sd.isOk = getIsOK(Convert.ToInt32(dr.GetDecimal(4)), int.Parse(dr.GetSqlDecimal(5).ToDouble().ToString()), Convert.ToInt32(dr.GetDecimal(6)), int.Parse(dr.GetSqlDecimal(8).ToDouble().ToString()));
sd.stockQty = getStockData(Pid, khdm, dr.GetString(0), Convert.ToInt32(dr.GetDecimal(6)), Convert.ToInt32(dr.GetDecimal(4)));
sd.area = dr.GetString(12);
sd.NgQty = int.Parse(dr.GetSqlDecimal(11).ToDouble().ToString());
sdList.Add(sd);
}
}
return(sdList);
}