private void button1_Click(object sender, EventArgs e)
{
if (dgv1.RowCount < 1)
{
return;
}
Site = cmb_site.SelectedValue.ToString();
if (Site == string.Empty)
{
MessageBox.Show("请选择ERP域！");
return;
}
#region 开始循环比对Excel中的每行的材料号是否都对。
for (int i = 0; i < dgv1.RowCount - 1; i++)
{
string partno = dgv1.Rows[i].Cells[4].Value.ToString();
if (string.IsNullOrEmpty(partno))
{
//MessageBox.Show("第" + (i + 1) + "行此材料编码不存在");
//return;
}
else
{
InventoryPart invpartnew = InventoryPart.FindInvInfor(partno, Site);
if (invpartnew != null)
{
}
else
{
MessageBox.Show("第" + (i + 1) + "行ERP中无此材料编码");
return;
}
}
}
#endregion
string appdir = System.AppDomain.CurrentDomain.BaseDirectory;
InitializeWorkbook(@appdir + "Template\\项目MSS明细表.xls");
ISheet sheet1 = hssfworkbook.GetSheet("MSS明细表");
//create cell on rows, since rows do already exist,it's not necessary to create rows again.
//sheet1.GetRow(1).GetCell(1).SetCellValue(200200);
//sheet1.GetRow(2).GetCell(1).SetCellValue(300);
//sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
//sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
int j = 0, l = 0;
#region 开始循环比对Excel中的每行的库存以及申请数量
for (int i = 0; i < dgv1.RowCount; i++)
{
string partno = dgv1.Rows[i].Cells[4].Value.ToString();
string Project_id = string.Empty;
Project_id = dgv1.Rows[i].Cells["Column1"].Value.ToString();
ProjectId = project.FindERPID(Project_id);
if (!string.IsNullOrEmpty(partno))
{
if (partno.Length < 7)
{
partno = "0" + partno;
}
int tt = j + 1;
string partname, unitname;
InventoryPart invpartnew = InventoryPart.FindInvInfor(partno, Site);
if (invpartnew != null)
{
partname = invpartnew.description;
unitname = invpartnew.unit_meas;
}
else
{
MessageBox.Show("第" + i + "行ERP中无此材料编码");
return;
}
//DateTime reqdatestr = Convert.ToDateTime(dgv1.Rows[i].Cells["需求日期"].Value);
//string reqreason = dgv1.Rows[i].Cells[4].Value.ToString();
string reqreason = "1001";
string preQty = dgv1.Rows[i].Cells[6].Value.ToString();
string purpose = dgv1.Rows[i].Cells["Column9"].Value.ToString();
string remark = dgv1.Rows[i].Cells["图纸号"].Value.ToString();
Str_Discipline = dgv1.Rows[i].Cells[3].Value.ToString();
string replaceno = "";
InventoryPart invpart = InventoryPart.GetRequiredqty(Site, partno, ProjectId);
decimal left_reqqty = 0, reserved_qty = 0;
if (invpart != null)
{
left_reqqty = Convert.ToDecimal(invpart.qty_reserved);
reserved_qty = Convert.ToDecimal(invpart.qty_onhand) - Convert.ToDecimal(invpart.qty_issued);
}
if (!string.IsNullOrEmpty(purpose))
{
Str_Area = ProjectBlock.GetAreaByBlock(purpose, ProjectId, Site);
}
decimal needqty = Convert.ToDecimal(preQty);
#region 如果数量不够则考虑替代码的库存以及申请情况
if (Convert.ToDecimal(preQty) > left_reqqty + reserved_qty)
{
#region 先将标准码数量写入
if (left_reqqty + reserved_qty > 0)
{
string meo_no = GetPartMEONO(partno, preQty);
sheet1.GetRow(j + 5).GetCell(0).SetCellValue(tt.ToString());
sheet1.GetRow(j + 5).GetCell(1).SetCellValue(ProjectId);
sheet1.GetRow(j + 5).GetCell(2).SetCellValue(Str_Area);
sheet1.GetRow(j + 5).GetCell(3).SetCellValue(Str_FX);
sheet1.GetRow(j + 5).GetCell(4).SetCellValue(Str_Discipline);
sheet1.GetRow(j + 5).GetCell(5).SetCellValue(ActivityName);
sheet1.GetRow(j + 5).GetCell(6).SetCellValue(partno);
sheet1.GetRow(j + 5).GetCell(7).SetCellValue(partname);
sheet1.GetRow(j + 5).GetCell(9).SetCellValue((left_reqqty + reserved_qty).ToString());
sheet1.GetRow(j + 5).GetCell(10).SetCellValue(unitname);
sheet1.GetRow(j + 5).GetCell(8).SetCellValue(meo_no);
sheet1.GetRow(j + 5).GetCell(12).SetCellValue(reqreason);
sheet1.GetRow(j + 5).GetCell(17).SetCellValue(replaceno);
sheet1.GetRow(j + 5).GetCell(15).SetCellValue(remark);
sheet1.GetRow(j + 5).GetCell(16).SetCellValue(purpose);
j++;
}
#endregion
needqty = Convert.ToDecimal(preQty) - left_reqqty - reserved_qty;
#region 替代码的库存数量以及需求数量比对
DataSet ReplaceDS = Ration.QueryPartRationList("select replace_no,part_description from mm_part_standard_tab where part_no ='" + partno + "'");
if (ReplaceDS != null)
{
int p = ReplaceDS.Tables[0].Rows.Count;
for (int k = 0; k < p; k++)
{
string ReplaceNo = ReplaceDS.Tables[0].Rows[k][0].ToString();
string ReplaceDesc = ReplaceDS.Tables[0].Rows[k][1].ToString();
InventoryPart invparttemp = InventoryPart.GetRequiredqty(Site, ReplaceNo, ProjectId);
decimal temp_left_reqqty = 0, temp_reserved_qty = 0;
if (invparttemp != null)
{
temp_left_reqqty = Convert.ToDecimal(invparttemp.qty_reserved);
temp_reserved_qty = Convert.ToDecimal(invparttemp.qty_onhand) - Convert.ToDecimal(invparttemp.qty_issued);
if (temp_left_reqqty + temp_reserved_qty > 0)
{
#region 此替代码数量仍然不够
if (Convert.ToDecimal(needqty) > temp_left_reqqty + temp_reserved_qty)
{
replaceno = replaceno + ReplaceNo + ";";
needqty = needqty - temp_left_reqqty - temp_reserved_qty;
string meo_no = GetPartMEONO(ReplaceNo, (temp_left_reqqty + temp_reserved_qty).ToString());
sheet1.GetRow(j + 5).GetCell(0).SetCellValue(tt.ToString());
sheet1.GetRow(j + 5).GetCell(1).SetCellValue(ProjectId);
sheet1.GetRow(j + 5).GetCell(2).SetCellValue(Str_Area);
sheet1.GetRow(j + 5).GetCell(3).SetCellValue(Str_FX);
sheet1.GetRow(j + 5).GetCell(4).SetCellValue(Str_Discipline);
sheet1.GetRow(j + 5).GetCell(5).SetCellValue(ActivityName);
sheet1.GetRow(j + 5).GetCell(6).SetCellValue(ReplaceNo);
sheet1.GetRow(j + 5).GetCell(7).SetCellValue(ReplaceDesc);
sheet1.GetRow(j + 5).GetCell(9).SetCellValue((temp_left_reqqty + temp_reserved_qty).ToString());
sheet1.GetRow(j + 5).GetCell(10).SetCellValue(unitname);
sheet1.GetRow(j + 5).GetCell(8).SetCellValue(meo_no);
sheet1.GetRow(j + 5).GetCell(12).SetCellValue(reqreason);
sheet1.GetRow(j + 5).GetCell(17).SetCellValue(partno);
sheet1.GetRow(j + 5).GetCell(15).SetCellValue(remark);
sheet1.GetRow(j + 5).GetCell(16).SetCellValue(purpose);
j++;
}
#endregion
#region 替代码数量可以满足则跳出循环
else
{
replaceno = replaceno + ReplaceNo + ";";
string meo_no = GetPartMEONO(ReplaceNo, needqty.ToString());
sheet1.GetRow(j + 5).GetCell(0).SetCellValue(tt.ToString());
sheet1.GetRow(j + 5).GetCell(1).SetCellValue(ProjectId);
sheet1.GetRow(j + 5).GetCell(2).SetCellValue(Str_Area);
sheet1.GetRow(j + 5).GetCell(3).SetCellValue(Str_FX);
sheet1.GetRow(j + 5).GetCell(4).SetCellValue(Str_Discipline);
sheet1.GetRow(j + 5).GetCell(5).SetCellValue(ActivityName);
sheet1.GetRow(j + 5).GetCell(6).SetCellValue(ReplaceNo);
sheet1.GetRow(j + 5).GetCell(7).SetCellValue(ReplaceDesc);
sheet1.GetRow(j + 5).GetCell(9).SetCellValue(needqty.ToString());
sheet1.GetRow(j + 5).GetCell(10).SetCellValue(unitname);
sheet1.GetRow(j + 5).GetCell(8).SetCellValue(meo_no);
sheet1.GetRow(j + 5).GetCell(12).SetCellValue(reqreason);
sheet1.GetRow(j + 5).GetCell(17).SetCellValue(partno);
sheet1.GetRow(j + 5).GetCell(15).SetCellValue(remark);
sheet1.GetRow(j + 5).GetCell(16).SetCellValue(purpose);
j++;
needqty = 0;
break;
}
#endregion
}
}
}
}
#endregion
}
#endregion
#region 如果本身库存以及申请数量足够 直接写入MEO号以及申请数量
else
{
needqty = 0;
string meo_no = GetPartMEONO(partno, preQty);
sheet1.GetRow(j + 5).GetCell(0).SetCellValue(tt.ToString());
sheet1.GetRow(j + 5).GetCell(1).SetCellValue(ProjectId);
sheet1.GetRow(j + 5).GetCell(2).SetCellValue(Str_Area);
sheet1.GetRow(j + 5).GetCell(3).SetCellValue(Str_FX);
sheet1.GetRow(j + 5).GetCell(4).SetCellValue(Str_Discipline);
sheet1.GetRow(j + 5).GetCell(5).SetCellValue(ActivityName);
sheet1.GetRow(j + 5).GetCell(6).SetCellValue(partno);
sheet1.GetRow(j + 5).GetCell(7).SetCellValue(partname);
sheet1.GetRow(j + 5).GetCell(9).SetCellValue(preQty);
sheet1.GetRow(j + 5).GetCell(10).SetCellValue(unitname);
sheet1.GetRow(j + 5).GetCell(8).SetCellValue(meo_no);
sheet1.GetRow(j + 5).GetCell(12).SetCellValue(reqreason);
sheet1.GetRow(j + 5).GetCell(17).SetCellValue(replaceno);
sheet1.GetRow(j + 5).GetCell(15).SetCellValue(remark);
sheet1.GetRow(j + 5).GetCell(16).SetCellValue(purpose);
j++;
}
#endregion
}
}
#endregion
//Force excel to recalculate all the formula while open
sheet1.ForceFormulaRecalculation = true;
//create cell on rows, since rows do already exist,it's not necessary to create rows again.
//sheet1.GetRow(1).GetCell(1).SetCellValue(200200);
//sheet1.GetRow(2).GetCell(1).SetCellValue(300);
//sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
//sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
try
{
WriteToFile(appdir + "\\导出的标准MSS格式\\" + "标准MSSList.xls");
//WriteToFile(appdir + "\\导出的标准MSS格式\\" + "不足材料列表.xls");
//MessageBox.Show("导出的标准MSS格式MSSList.xls和不足材料列表" + "成功!", "提示消息");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
#region 足MSS的材料信息
InitializeWorkbook(@appdir + "Template\\不足项目MSS明细表.xls");
ISheet sheet2 = hssfworkbook.GetSheet("MSS明细表");
for (int i = 0; i < dgv1.RowCount; i++)
{
string partno = dgv1.Rows[i].Cells[4].Value.ToString();
if (!string.IsNullOrEmpty(partno))
{
string partname, unitname;
if (partno.Length < 7)
{
partno = "0" + partno;
}
InventoryPart invpartnew = InventoryPart.FindInvInfor(partno, Site);
if (invpartnew != null)
{
partname = invpartnew.description;
unitname = invpartnew.unit_meas;
}
else
{
MessageBox.Show("第" + i + "行ERP中无此材料编码");
return;
}
//DateTime reqdatestr = Convert.ToDateTime(dgv1.Rows[i].Cells["需求日期"].Value);
string reqreason = "1001";
string preQty = dgv1.Rows[i].Cells[6].Value.ToString();
string purpose = dgv1.Rows[i].Cells["Column9"].Value.ToString();
string remark = dgv1.Rows[i].Cells["图纸号"].Value.ToString();
Str_Discipline = dgv1.Rows[i].Cells[3].Value.ToString();
string replaceno = "";
InventoryPart invpart = InventoryPart.GetRequiredqty(Site, partno, ProjectId);
decimal left_reqqty = 0, reserved_qty = 0;
if (invpart != null)
{
left_reqqty = Convert.ToDecimal(invpart.qty_reserved);
reserved_qty = Convert.ToDecimal(invpart.qty_onhand) - Convert.ToDecimal(invpart.qty_issued);
}
decimal needqty = Convert.ToDecimal(preQty);
if (Convert.ToDecimal(preQty) > left_reqqty + reserved_qty)
{
needqty = Convert.ToDecimal(preQty) - left_reqqty - reserved_qty;
//decimal replaceqty = 0;
DataSet ReplaceDS = Ration.QueryPartRationList("select replace_no from mm_part_standard_tab where part_no ='" + partno + "'");
if (ReplaceDS != null)
{
int p = ReplaceDS.Tables[0].Rows.Count;
for (int k = 0; k < p; k++)
{
string ReplaceNo = ReplaceDS.Tables[0].Rows[k][0].ToString();
InventoryPart invparttemp = InventoryPart.GetRequiredqty(Site, ReplaceNo, ProjectId);
decimal temp_left_reqqty = 0, temp_reserved_qty = 0;
if (invparttemp != null)
{
temp_left_reqqty = Convert.ToDecimal(invparttemp.qty_reserved);
temp_reserved_qty = Convert.ToDecimal(invparttemp.qty_onhand) - Convert.ToDecimal(invparttemp.qty_issued);
if (temp_left_reqqty + temp_reserved_qty > 0)
{
if (Convert.ToDecimal(needqty) > temp_left_reqqty + temp_reserved_qty)
{
replaceno = replaceno + ReplaceNo + ";";
needqty = needqty - temp_left_reqqty - temp_reserved_qty;
}
else
{
replaceno = replaceno + ReplaceNo + ";";
needqty = 0;
break;
}
}
}
}
}
//if (i + 5 > 10)
// sheet1.CreateRow(i + 5);
}
else
{
needqty = 0;
}
if (needqty == 0)
{
}
else
{
int tt = l + 1;
sheet2.GetRow(l + 5).GetCell(0).SetCellValue(tt.ToString());
sheet2.GetRow(l + 5).GetCell(1).SetCellValue(ProjectId);
sheet2.GetRow(l + 5).GetCell(2).SetCellValue(Str_Area);
sheet2.GetRow(l + 5).GetCell(3).SetCellValue(Str_FX);
sheet2.GetRow(l + 5).GetCell(4).SetCellValue(Str_Discipline);
sheet2.GetRow(l + 5).GetCell(5).SetCellValue(ActivityName);
sheet2.GetRow(l + 5).GetCell(6).SetCellValue(partno);
sheet2.GetRow(l + 5).GetCell(7).SetCellValue(partname);
sheet2.GetRow(l + 5).GetCell(9).SetCellValue(needqty.ToString());
sheet2.GetRow(l + 5).GetCell(10).SetCellValue(unitname);
//sheet2.GetRow(j + 5).GetCell(11).SetCellValue(reqdate);
sheet2.GetRow(l + 5).GetCell(12).SetCellValue(reqreason);
//sheet2.GetRow(j + 5).GetCell(13).SetCellValue(reqreason);
sheet2.GetRow(l + 5).GetCell(15).SetCellValue(remark);
sheet2.GetRow(l + 5).GetCell(16).SetCellValue(purpose);
//sheet1.GetRow(i + 5).GetCell(17).SetCellValue(replaceno);
//if (i + 5 > 14)
// sheet2.CreateRow(j + 6);
l++;
}
}
}
sheet2.ForceFormulaRecalculation = true;
#endregion
try
{
//WriteToFile(appdir + "\\导出的标准MSS格式\\" + "标准MSSList.xls");
WriteToFile(appdir + "\\导出的标准MSS格式\\" + "不足材料列表.xls");
MessageBox.Show("导出的标准MSS格式MSSList.xls和不足材料列表" + "成功!", "提示消息");
System.Diagnostics.Process.Start("explorer.exe", appdir + "导出的标准MSS格式");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 控制申请数量只能填写数字
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dgv1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex >= 0)
{
int i = e.ColumnIndex;
DataGridViewColumn column = dgv1.Columns[e.ColumnIndex];
if (column.Name == "申请数量")
{
if (dgv1.Rows[e.RowIndex].Cells[i].Value != null)
{
string rqty = dgv1.Rows[e.RowIndex].Cells[i].Value.ToString();
if (BaseClass.validateNum(rqty) == false)
{
MessageBox.Show("请输入数字!!!", "提示");
dgv1.Rows[e.RowIndex].Cells[i].Value = "";
}
}
}
else if (column.Name == "零件号")
{
if (dgv1.Rows[e.RowIndex].Cells[i].Value != null)
{
string rqty = dgv1.Rows[e.RowIndex].Cells[i].Value.ToString();
string Site = cmb_site.SelectedValue.ToString();
if (ProjectId == string.Empty)
{
MessageBox.Show("请选择项目");
return;
}
if (Str_Area == string.Empty)
{
MessageBox.Show("请选择区域");
return;
}
if (ActivityName == string.Empty)
{
MessageBox.Show("请选择分项");
return;
}
InventoryPart invpartnew = InventoryPart.FindInvInfor(rqty, Site);
if (invpartnew != null)
{
dgv1.Rows[e.RowIndex].Cells[i].Value = invpartnew.PART_NO;
dgv1.Rows[e.RowIndex].Cells[i + 1].Value = invpartnew.description;
dgv1.Rows[e.RowIndex].Cells["单位"].Value = invpartnew.unit_meas;
}
else
{
MessageBox.Show("无此材料编码");
return;
}
InventoryPart invpart = InventoryPart.GetRequiredqty(Site, rqty, ProjectId);
if (invpart != null)
{
dgv1.Rows[e.RowIndex].Cells["reserved_qty"].Value = invpart.qty_reserved;
dgv1.Rows[e.RowIndex].Cells["total_qty"].Value = invpart.qty_onhand;
dgv1.Rows[e.RowIndex].Cells["meo_qty"].Value = invpart.qty_issued;
dgv1.Rows[e.RowIndex].Cells["left_qty"].Value = Convert.ToDecimal(invpart.qty_onhand) - Convert.ToDecimal(invpart.qty_issued);
}
}
}
}
}