private void exportxmlbutton_Click(object sender, EventArgs e) { DateTime time1 = Convert.ToDateTime(this.dateTimePickerstart.Value.Date.ToString("yyyy/MM/dd")); DateTime time2 = Convert.ToDateTime(this.dateTimePickerend.Value.Date.ToString("yyyy/MM/dd")); if (DateTime.Compare(time1, time2) > 0) //判断日期大小 { MessageBox.Show("开始日期大于结束"); return; } string startTime = this.dateTimePickerstart.Value.ToString("yyyy/MM/dd"); string endTime = this.dateTimePickerend.Value.ToString("yyyy/MM/dd"); RealStockClass realstock = new RealStockClass(); List <StoreAmount> storeAmountList = new List <StoreAmount>(); string seq_no = DateTime.Now.ToString("yyyyMMdd") + "2006" + "02"; //日期+类型+序号01代表维修, 02代表整机 string boxtype = "2006"; //代码 string flowstateg = ""; string trade_code = ""; string ems_no = ""; trade_code = "3401560011"; ems_no = "H33138000002"; string status = "A"; try { SqlConnection mConn = new SqlConnection(Conlist.ConStr); mConn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = mConn; cmd.CommandType = CommandType.Text; Dictionary <string, string> nameDir = new Dictionary <string, string>(); cmd.CommandText = "select distinct SKU_LNO,SKU_NO from BOMCompare"; SqlDataReader querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { if (nameDir.ContainsKey(querySdr[0].ToString().Trim()) == false) { nameDir.Add(querySdr[0].ToString().Trim(), querySdr[1].ToString().Trim()); } } querySdr.Close(); //1 读取材料库房信息 cmd.CommandText = "select materialNo,number from materialhouse where materialNo!='' and number !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; init1.cop_g_no = querySdr[0].ToString(); init1.qty = querySdr[1].ToString(); init1.unit = "007"; init1.goods_nature = "I";//代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); //2 读取在维修的整机,还没有走到包装步骤 Dictionary <string, int> reparingNum = new Dictionary <string, int>(); cmd.CommandText = "select SKU from NBShouLiao where receiveDate !='' and PackDate is NULL"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { if (reparingNum.ContainsKey(querySdr[0].ToString().Trim())) { reparingNum[querySdr[0].ToString().Trim()] = reparingNum[querySdr[0].ToString().Trim()] + 1; } else { reparingNum[querySdr[0].ToString().Trim()] = 1; } } querySdr.Close(); foreach (string key in reparingNum.Keys) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; init1.cop_g_no = nameDir[key]; init1.qty = reparingNum[key] + ""; init1.unit = "001"; init1.goods_nature = "I";//代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } //2 整机良品库 cmd.CommandText = "select model,number from NBHouse where model!='' and number !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; init1.cop_g_no = nameDir[querySdr[0].ToString()]; init1.qty = querySdr[1].ToString(); init1.unit = "007"; init1.goods_nature = "E";//代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); //3 材料不良品库房 cmd.CommandText = "select materialNo,number from materialNgHouse where materialNo!='' and number !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; init1.cop_g_no = querySdr[0].ToString(); init1.qty = querySdr[1].ToString(); init1.unit = "007"; init1.goods_nature = "I";//代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); mConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } realstock.seq_no = seq_no; realstock.boxtype = boxtype; realstock.flowstateg = flowstateg; realstock.trade_code = trade_code; realstock.ems_no = ems_no; realstock.status = status; realstock.storeAmountList = storeAmountList; if (storeAmountList.Count > 0) { Untils.createRealStockXML(realstock, "D:\\STORE_AMOUNT" + seq_no + ".xml"); MessageBox.Show("海关实盘库存信息产生成功!"); } else { MessageBox.Show("没有实盘库存信息!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void exportxmlbutton_Click(object sender, EventArgs e) { DateTime time1 = Convert.ToDateTime(this.dateTimePickerstart.Value.Date.ToString("yyyy/MM/dd")); DateTime time2 = Convert.ToDateTime(this.dateTimePickerend.Value.Date.ToString("yyyy/MM/dd")); if (DateTime.Compare(time1, time2) > 0) //判断日期大小 { MessageBox.Show("开始日期大于结束"); return; } string startTime = this.dateTimePickerstart.Value.ToString("yyyy/MM/dd"); string endTime = this.dateTimePickerend.Value.ToString("yyyy/MM/dd"); RealStockClass realstock = new RealStockClass(); List <StoreAmount> storeAmountList = new List <StoreAmount>(); string seq_no = DateTime.Now.ToString("yyyyMMdd") + "2006" + "1"; //日期+类型+序号 string boxtype = "2006"; //代码 string flowstateg = ""; string trade_code = ""; string ems_no = ""; string status = "A"; try { SqlConnection mConn = new SqlConnection(Constlist.ConStr); mConn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = mConn; cmd.CommandType = CommandType.Text; //查询71bom Dictionary <string, string> _71bomDic = new Dictionary <string, string>(); cmd.CommandText = "select distinct material_mpn,material_vendor_pn from LCFC71BOM_table"; SqlDataReader querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { if (_71bomDic.ContainsKey(querySdr[0].ToString().Trim()) == false) { _71bomDic.Add(querySdr[0].ToString().Trim(), querySdr[1].ToString().Trim()); } } querySdr.Close(); //查询物料对照表 Dictionary <string, string> materialbomDic = new Dictionary <string, string>(); cmd.CommandText = "select distinct custommaterialNo,vendormaterialNo from MBMaterialCompare"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { if (materialbomDic.ContainsKey(querySdr[0].ToString().Trim()) == false) { materialbomDic.Add(querySdr[0].ToString().Trim(), querySdr[1].ToString().Trim()); } } querySdr.Close(); cmd.CommandText = "select indentifier, book_number from company_fixed_table"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { trade_code = querySdr[0].ToString(); ems_no = querySdr[1].ToString(); } querySdr.Close(); Dictionary <string, int> receiveOrderDic = new Dictionary <string, int>(); //1 从收货表中查询信息 cmd.CommandText = "select custom_materialNo, receivedNum,returnNum,cid_number from receiveOrder where _status !='return'"; querySdr = cmd.ExecuteReader(); int receiveNum = 0, returnNum = 0, cidNum = 0; while (querySdr.Read()) { receiveNum = Int32.Parse(querySdr[1].ToString()); try { returnNum = Int32.Parse(querySdr[2].ToString()); } catch (Exception ex) { returnNum = 0; } try { cidNum = Int32.Parse(querySdr[3].ToString()); } catch (Exception ex) { cidNum = 0; } if (receiveOrderDic.ContainsKey(querySdr[0].ToString())) { //加上原来的数量 receiveOrderDic[querySdr[0].ToString()] = receiveOrderDic[querySdr[0].ToString()] + receiveNum - returnNum - cidNum; } else { receiveOrderDic.Add(querySdr[0].ToString(), receiveNum - returnNum - cidNum); } } querySdr.Close(); foreach (KeyValuePair <string, int> kvp in receiveOrderDic) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; string temp = kvp.Key; if (temp.Length == 10 && temp.StartsWith("000")) { temp = temp.Substring(3); } init1.cop_g_no = temp; //正常使用客户料号 init1.qty = kvp.Value.ToString(); init1.unit = "007"; //固定单位 init1.goods_nature = "I"; //代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } //2 读取库房信息,良品 Dictionary <string, string> mpn_unit = new Dictionary <string, string>(); cmd.CommandText = "select distinct mpn,declare_unit from stock_in_sheet where mpn !=''"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { if (mpn_unit.ContainsKey(querySdr[0].ToString()) == false) { mpn_unit.Add(querySdr[0].ToString(), querySdr[1].ToString()); } } querySdr.Close(); cmd.CommandText = "select mpn, number from store_house where mpn !='' and number !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; string currentDeclear = ""; if (_71bomDic.ContainsKey(querySdr[0].ToString())) { currentDeclear = _71bomDic[querySdr[0].ToString()]; } else if (currentDeclear == "") { currentDeclear = querySdr[0].ToString(); if (currentDeclear.Length == 10 && currentDeclear.StartsWith("000")) { currentDeclear = currentDeclear.Substring(3); } } init1.cop_g_no = currentDeclear;//因为报关原因,需要改成71料号(联想料号)TODO init1.qty = querySdr[1].ToString(); try { init1.unit = Untils.getCustomCode(mpn_unit[querySdr[0].ToString()]); } catch (Exception ex) { init1.unit = "007"; } init1.goods_nature = "I";//代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); //3 读取MB/SMT/BGA不良品信息 cmd.CommandText = "select mpn, number from store_house_ng where mpn !='' and number !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; string currentDeclear = ""; bool isMB = false; if (_71bomDic.ContainsKey(querySdr[0].ToString())) { currentDeclear = _71bomDic[querySdr[0].ToString()] + "-1";//海关要求料号不一样,加-1 } else if (currentDeclear == "") { currentDeclear = querySdr[0].ToString(); if (currentDeclear.Length == 10 && currentDeclear.StartsWith("000")) { currentDeclear = currentDeclear.Substring(3); isMB = true; } } init1.cop_g_no = currentDeclear;//因为报关原因,需要改成71料号(联想料号)TODO init1.qty = querySdr[1].ToString(); try { init1.unit = Untils.getCustomCode(mpn_unit[querySdr[0].ToString()]); } catch (Exception ex) { init1.unit = "007"; } init1.goods_nature = isMB?"E": "I";//代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); //3-1 读取MB Buffer不良品信息,此处的MB是由良品库过来的,所以直接用原始料号71即可 cmd.CommandText = "select mpn, number from store_house_ng_buffer_mb where mpn !='' and number !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; string currentDeclear = querySdr[0].ToString(); init1.cop_g_no = currentDeclear;//因为报关原因,需要改成71料号(联想料号)TODO init1.qty = querySdr[1].ToString(); try { init1.unit = Untils.getCustomCode(mpn_unit[querySdr[0].ToString()]); } catch (Exception ex) { init1.unit = "007"; } init1.goods_nature = "E";//代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); //4 读取MB待维修库信息 cmd.CommandText = "select custom_materialNo,leftNumber from wait_repair_left_house_table where leftNumber !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; string temp = querySdr[0].ToString(); if (temp.Length == 10 && temp.StartsWith("000")) { temp = temp.Substring(3); } init1.cop_g_no = temp; //正常使用客户料号 init1.qty = querySdr[1].ToString(); init1.unit = "007"; //固定单位 init1.goods_nature = "I"; //代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); //5 读取MB良品库信息 cmd.CommandText = "select custom_materialNo, leftNumber from repaired_left_house_table where leftNumber !='0'"; querySdr = cmd.ExecuteReader(); while (querySdr.Read()) { StoreAmount init1 = new StoreAmount(); init1.ems_no = ems_no; string temp = querySdr[0].ToString(); if (temp.Length == 10 && temp.StartsWith("000")) { temp = temp.Substring(3); } init1.cop_g_no = temp; //正常使用客户料号 init1.qty = querySdr[1].ToString(); init1.unit = "007"; //固定单位 init1.goods_nature = "E"; //代码 init1.bom_version = ""; init1.stock_date = Untils.getCustomCurrentDate(); init1.date_type = "B";//代码 init1.whs_code = ""; init1.location_code = ""; init1.note = ""; storeAmountList.Add(init1); } querySdr.Close(); mConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } realstock.seq_no = seq_no; realstock.boxtype = boxtype; realstock.flowstateg = flowstateg; realstock.trade_code = trade_code; realstock.ems_no = ems_no; realstock.status = status; realstock.storeAmountList = storeAmountList; if (storeAmountList.Count > 0) { Untils.createRealStockXML(realstock, "D:\\STORE_AMOUNT" + seq_no + ".xml"); MessageBox.Show("海关实盘库存信息产生成功!"); } else { MessageBox.Show("没有实盘库存信息!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } }