public static void createOpeningStockXML(OpeningStockClass openingStockClass, string fileName)
{
StringBuilder xmlResult = new StringBuilder("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n");
xmlResult.AppendFormat("<HCHX_DATA xmlns=\"http://HCHX.Schemas.STORE_INIT\">\n");
xmlResult.Append("<TRANSMIT>\n");
xmlResult.AppendFormat("<SEQ_NO>{0}</SEQ_NO>\n", openingStockClass.seq_no);
xmlResult.AppendFormat("<BOXTYPE>{0}</BOXTYPE>\n", openingStockClass.boxtype);
xmlResult.AppendFormat("<FLOWSTATEG>{0}</FLOWSTATEG>\n", openingStockClass.flowstateg);
xmlResult.AppendFormat("<TRADE_CODE>{0}</TRADE_CODE>\n", openingStockClass.trade_code);
xmlResult.AppendFormat("<EMS_NO>{0}</EMS_NO>\n", openingStockClass.ems_no);
xmlResult.AppendFormat("<STATUS>{0}</STATUS>\n", openingStockClass.status);
xmlResult.Append("</TRANSMIT>");
foreach (StoreInit storeInit in openingStockClass.storeInitList)
{
xmlResult.Append("<STORE_INIT>\n");
xmlResult.AppendFormat("<EMS_NO>{0}</EMS_NO>\n", storeInit.ems_no);
xmlResult.AppendFormat("<COP_G_NO>{0}</COP_G_NO>\n", storeInit.cop_g_no);
xmlResult.AppendFormat("<QTY>{0}</QTY>\n", storeInit.qty);
xmlResult.AppendFormat("<UNIT>{0}</UNIT>\n", storeInit.unit);
xmlResult.AppendFormat("<GOODS_NATURE>{0}</GOODS_NATURE>\n", storeInit.goods_nature);
xmlResult.AppendFormat("<BOM_VERSION>{0}</BOM_VERSION>\n", storeInit.bom_version);
xmlResult.AppendFormat("<CHECK_DATE>{0}</CHECK_DATE>\n", storeInit.check_date);
xmlResult.AppendFormat("<DATA_TYPE>{0}</DATA_TYPE>\n", storeInit.date_type);
xmlResult.AppendFormat("<WHS_CODE>{0}</WHS_CODE>\n", storeInit.whs_code);
xmlResult.AppendFormat("<LOCATION_CODE>{0}</LOCATION_CODE>\n", storeInit.location_code);
xmlResult.AppendFormat("<NOTE>{0}</NOTE>\n", storeInit.note);
xmlResult.Append("</STORE_INIT>\n");
}
xmlResult.Append("</HCHX_DATA>\n");
//写入文件
try
{
//1.创建文件流
FileStream fileStream = new FileStream(fileName, FileMode.Create);
//2.创建写入器
StreamWriter streamWriter = new StreamWriter(fileStream);
//3.将内容写入文件
streamWriter.WriteLine(xmlResult);
//4.关闭写入器
streamWriter.Close();
//5.关闭文件流
fileStream.Close();
}
catch (Exception e)
{ }
}
//Microsoft.Office.Interop.Excel.Application app = null;// new Microsoft.Office.Interop.Excel.Application();
//Microsoft.Office.Interop.Excel.Workbooks wbs = null;// app.Workbooks;
private void uploadExcelButton_Click(object sender, EventArgs e)
{
//Dictionary<string, string> realMaterialNum = new Dictionary<string, string>();
//解析xml，并把所以的料号与数量对上，其他类似之前的做法
//try
//{
// app = new Microsoft.Office.Interop.Excel.Application();
// wbs = app.Workbooks;
// Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(pathTextBox.Text, 0, false, 5, string.Empty, string.Empty,
// false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
// string.Empty, true, false, 0, true, 1, 0);
// app.DisplayAlerts = false;
// Microsoft.Office.Interop.Excel.Worksheet ws = wb.Worksheets["Sheet1"];
// int rowLength = ws.UsedRange.Rows.Count;
// int columnLength = ws.UsedRange.Columns.Count;
// for (int i = 2; i <= rowLength; i++)
// {
// string mpn = "", number = "";
// //有可能有空值
// mpn = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[i, 1]).Value2.ToString();
// number = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[i, 6]).Value2.ToString();
// if (mpn.Trim() == "" || number.Trim() == "")
// {
// MessageBox.Show("数量或料号有空值");
// break;
// }
// realMaterialNum.Add(mpn.Trim(), number.Trim());
// }
//}catch(Exception ex)
//{
// MessageBox.Show(ex.ToString());
//}
//finally
//{
// closeAndKillApp();
//}
//下面把内容按之前的方式生成
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");
OpeningStockClass openingstock = new OpeningStockClass();
List <StoreInit> storeInitList = new List <StoreInit>();
List <StockCheck> StockCheckList = new List <StockCheck>();
string seq_no = DateTime.Now.ToString("yyyyMMdd") + "2005" + "1"; //日期+类型+序号
string boxtype = "2005"; //代码
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>();
Dictionary <string, string> _71bomDescribeDic = new Dictionary <string, string>();//料号与描述对应
cmd.CommandText = "select distinct material_mpn,material_vendor_pn,_description 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());
}
if (_71bomDescribeDic.ContainsKey(querySdr[0].ToString().Trim()) == false)
{
_71bomDescribeDic.Add(querySdr[0].ToString().Trim(), querySdr[2].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)
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.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,house,place from store_house where mpn !='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
init1.ems_no = ems_no;
string currentDeclear = "";
bool isMB = false;
if (_71bomDic.ContainsKey(querySdr[0].ToString()))
{
currentDeclear = _71bomDic[querySdr[0].ToString()];
}
else if (currentDeclear == "")
{
currentDeclear = querySdr[0].ToString();//buffer主板直接用71料号存储的
if (currentDeclear.Length == 10 && currentDeclear.StartsWith("000"))
{
currentDeclear = currentDeclear.Substring(3);
isMB = true;
}
}
init1.cop_g_no = currentDeclear;//因为报关原因，需要改成71料号（联想料号）TODO，包括材料与买的MB，物料对照表与71bom
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
}
querySdr.Close();
//3 读取MB/SMT/BGA不良品信息，此处的MB是由CID过来的，所以直接用原始料号即可
cmd.CommandText = "select mpn, number,house,place from store_house_ng where mpn !='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
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();//buffer主板直接用71料号存储的
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 = "I";//代码
init1.bom_version = "";
init1.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
}
querySdr.Close();
//3-1 读取MB Buffer不良品信息，此处的MB是由良品库过来的，所以直接用原始料号71即可
cmd.CommandText = "select mpn, number,house,place from store_house_ng_buffer_mb where mpn !='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
init1.ems_no = ems_no;
string currentDeclear = querySdr[0].ToString().Trim();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.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())
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.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())
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
}
querySdr.Close();
mConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
openingstock.seq_no = seq_no;
openingstock.boxtype = boxtype;
openingstock.flowstateg = flowstateg;
openingstock.trade_code = trade_code;
openingstock.ems_no = ems_no;
openingstock.status = status;
openingstock.storeInitList = storeInitList;
if (storeInitList.Count > 0)
{
Untils.createOpeningStockXML(openingstock, "D:\\STORE_INIT" + 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");
OpeningStockClass openingstock = new OpeningStockClass();
List <StoreInit> storeInitList = new List <StoreInit>();
List <StockCheck> StockCheckList = new List <StockCheck>();
string seq_no = DateTime.Now.ToString("yyyyMMdd") + "2005" + "1"; //日期+类型+序号
string boxtype = "2005"; //代码
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>();
Dictionary <string, string> _71bomDescribeDic = new Dictionary <string, string>();//料号与描述对应
cmd.CommandText = "select distinct material_mpn,material_vendor_pn,_description 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());
}
if (_71bomDescribeDic.ContainsKey(querySdr[0].ToString().Trim()) == false)
{
_71bomDescribeDic.Add(querySdr[0].ToString().Trim(), querySdr[2].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)
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
StockCheck stockcheck = new StockCheck();
stockcheck.material_no = temp;
stockcheck.num = kvp.Value.ToString();
stockcheck.house = "成品之前";
stockcheck.place = "";
stockcheck.describe = "维修中主板";
if (stockcheck.num != "0")
{
StockCheckList.Add(stockcheck);
}
}
//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,house,place from store_house where mpn !='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
init1.ems_no = ems_no;
string currentDeclear = "";
bool isMB = false;
if (_71bomDic.ContainsKey(querySdr[0].ToString()))
{
currentDeclear = _71bomDic[querySdr[0].ToString()];
}
else if (currentDeclear == "")
{
currentDeclear = querySdr[0].ToString();//buffer主板直接用71料号存储的
if (currentDeclear.Length == 10 && currentDeclear.StartsWith("000"))
{
currentDeclear = currentDeclear.Substring(3);
isMB = true;
}
}
init1.cop_g_no = currentDeclear;//因为报关原因，需要改成71料号（联想料号）TODO，包括材料与买的MB，物料对照表与71bom
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
StockCheck stockcheck = new StockCheck();
stockcheck.material_no = isMB ? currentDeclear:querySdr[0].ToString();
stockcheck.num = querySdr[1].ToString();
stockcheck.house = querySdr[2].ToString();
stockcheck.place = querySdr[3].ToString();
if (_71bomDescribeDic.ContainsKey(stockcheck.material_no))
{
stockcheck.describe = _71bomDescribeDic[stockcheck.material_no];
}
if (stockcheck.num != "0")
{
StockCheckList.Add(stockcheck);
}
}
querySdr.Close();
//3 读取MB/SMT/BGA不良品信息，此处的MB是由CID过来的，所以直接用原始料号即可
cmd.CommandText = "select mpn, number,house,place from store_house_ng where mpn !='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
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();//buffer主板直接用71料号存储的
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
StockCheck stockcheck = new StockCheck();
stockcheck.material_no = isMB ? currentDeclear : querySdr[0].ToString();
stockcheck.num = querySdr[1].ToString();
stockcheck.house = querySdr[2].ToString();
stockcheck.place = querySdr[3].ToString();
if (_71bomDescribeDic.ContainsKey(stockcheck.material_no))
{
stockcheck.describe = _71bomDescribeDic[stockcheck.material_no];
}
else
{
stockcheck.describe = isMB ? "不良品主板" : "";
}
stockcheck.material_no += "_1";//区分良品与不良品信息
if (stockcheck.num != "0")
{
StockCheckList.Add(stockcheck);
}
}
querySdr.Close();
//3-1 读取MB Buffer不良品信息，此处的MB是由良品库过来的，所以直接用原始料号71即可
cmd.CommandText = "select mpn, number,house,place from store_house_ng_buffer_mb where mpn !='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
init1.ems_no = ems_no;
string currentDeclear = querySdr[0].ToString().Trim();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
StockCheck stockcheck = new StockCheck();
stockcheck.material_no = currentDeclear;
stockcheck.num = querySdr[1].ToString();
stockcheck.house = querySdr[2].ToString();
stockcheck.place = querySdr[3].ToString();
if (_71bomDescribeDic.ContainsKey(stockcheck.material_no))
{
stockcheck.describe = _71bomDescribeDic[stockcheck.material_no];
}
stockcheck.material_no += "_1";//区分良品与不良品信息
if (stockcheck.num != "0")
{
StockCheckList.Add(stockcheck);
}
}
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())
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
StockCheck stockcheck = new StockCheck();
stockcheck.material_no = temp;
stockcheck.num = querySdr[1].ToString();
stockcheck.house = "待维修库";
stockcheck.place = "";
stockcheck.describe = "待维修主板";
if (stockcheck.num != "0")
{
StockCheckList.Add(stockcheck);
}
}
querySdr.Close();
//5 读取MB良品库信息
cmd.CommandText = "select custom_materialNo, leftNumber from repaired_left_house_table where leftNumber !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
StockCheck stockcheck = new StockCheck();
stockcheck.material_no = temp;
stockcheck.num = querySdr[1].ToString();
stockcheck.house = "良品库";
stockcheck.place = "";
stockcheck.describe = "维修后良品主板";
if (stockcheck.num != "0")
{
StockCheckList.Add(stockcheck);
}
}
querySdr.Close();
mConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
generateExcelToCheck(StockCheckList);
openingstock.seq_no = seq_no;
openingstock.boxtype = boxtype;
openingstock.flowstateg = flowstateg;
openingstock.trade_code = trade_code;
openingstock.ems_no = ems_no;
openingstock.status = status;
openingstock.storeInitList = storeInitList;
//导出xml的逻辑变成excel上传了
if (storeInitList.Count > 0)
{
Untils.createOpeningStockXML(openingstock, "D:\\STORE_INIT" + seq_no + "test.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");
OpeningStockClass openingstock = new OpeningStockClass();
List <StoreInit> storeInitList = new List <StoreInit>();
string seq_no = DateTime.Now.ToString("yyyyMMdd") + "2005" + "02"; //日期+类型+序号 01代表维修， 02代表整机
string boxtype = "2005"; //代码
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())
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.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)
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
}
//2 整机良品库
cmd.CommandText = "select model,number from NBHouse where model!='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
init1.ems_no = ems_no;
init1.cop_g_no = nameDir[querySdr[0].ToString()];
init1.qty = querySdr[1].ToString();
init1.unit = "001";
init1.goods_nature = "E";//代码
init1.bom_version = "";
init1.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
}
querySdr.Close();
//3 材料不良品库房
cmd.CommandText = "select materialNo,number from materialNgHouse where materialNo!='' and number !='0'";
querySdr = cmd.ExecuteReader();
while (querySdr.Read())
{
StoreInit init1 = new StoreInit();
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.check_date = Untils.getCustomCurrentDate();
init1.date_type = "C";//代码
init1.whs_code = "";
init1.location_code = "";
init1.note = "";
storeInitList.Add(init1);
}
querySdr.Close();
mConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
openingstock.seq_no = seq_no;
openingstock.boxtype = boxtype;
openingstock.flowstateg = flowstateg;
openingstock.trade_code = trade_code;
openingstock.ems_no = ems_no;
openingstock.status = status;
openingstock.storeInitList = storeInitList;
if (storeInitList.Count > 0)
{
Untils.createOpeningStockXML(openingstock, "D:\\STORE_INIT" + seq_no + ".xml");
MessageBox.Show("海关期初库存信息产生成功！");
}
else
{
MessageBox.Show("没有期初库存信息！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
