private void MenuItem35_Click(System.Object sender, System.EventArgs e)
{
string InvntDate;
InvntDate = DateTime.Now.Year.ToString() + string.Format("{00}",DateTime.Now.Month); //结转本月及以前的仓库数据
MessageBox.Show(InvntDate);
frmSelStorageDept frmSelStrgDept = new frmSelStorageDept();
frmSelStrgDept.ShowDialog();
if (frmSelStrgDept.DialogResult != DialogResult.OK)
{
return;
}
string strgdeptname;
strgdeptname = frmSelStrgDept.ComboBox1.Text;
//检测 Storage_Inventory ，再根据入库、出库计算各物品数量
System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection();
conn1.ConnectionString = rms_var.ConnStr;
try
{
SqlCommand selectCMD1 = new SqlCommand();
selectCMD1.Connection = conn1;
//获取仓库前期期末结转时的日期
selectCMD1.CommandText = "SELECT yearmonth FROM Storage_Inventory group by yearmonth order by yearmonth";
selectCMD1.CommandTimeout = 30;
SqlDataAdapter dbDA1 = new SqlDataAdapter();
dbDA1.SelectCommand = selectCMD1;
conn1.Open();
DataSet dbDS1 = new DataSet();
dbDA1.Fill(dbDS1, "prioryearmonth");
if (dbDS1.Tables["prioryearmonth"].Rows.Count == 0)
{
//先删除原有数据
//selectCMD1.CommandText = "delete from storage_inventory where yearmonth='" + InvntDate + "'"
//selectCMD1.ExecuteNonQuery()
//没有前期结转数据，统计全部出入库数据，并保存结转后的数据
selectCMD1.CommandText = "SELECT materialcode,sum(quantity) as quantity,sum(totalprice) as totalprice FROM view_InStorageMaterials " + "where depcode='" + rms_var.GetDeptCode(strgdeptname) + "' and (storagebilltypecode='H' or storagebilltypecode='I'or storagebilltypecode='M') and " + "convert(char(4),indate,102)+convert(char(2),indate,100)<='" + InvntDate + "'" + "group by materialcode";
dbDA1.Fill(dbDS1, "instrg");
//将所有入库数据添加进 storage_inventory
for (int i = 0; i <= dbDS1.Tables["instrg"].Rows.Count - 1; i++)
{
selectCMD1.CommandText = "insert into storage_inventory (yearmonth,materialcode,quantity,price,totalprice) " + "values (" + "'" + InvntDate + "'" + "," + "'" + dbDS1.Tables["instrg"].Rows[i][0].ToString() + "'" + "," + dbDS1.Tables["instrg"].Rows[i][1].ToString() + "," + (System.Convert.ToString((double.Parse(dbDS1.Tables["instrg"].Rows[i][2].ToString())) / double.Parse(dbDS1.Tables["instrg"].Rows[i][1].ToString()))) + "," + dbDS1.Tables["instrg"].Rows[i][2].ToString() + ")";
selectCMD1.ExecuteNonQuery();
}
//将出库数据 更新 storage_inventory
selectCMD1.CommandText = "SELECT materialcode,sum(quantity) as quantity,sum(totalprice) as totalprice FROM view_OutStorageMaterials " + "where outdepcode='" + rms_var.GetDeptCode(strgdeptname) + "' and (storagebilltypecode='A' or storagebilltypecode='B'or storagebilltypecode='D' or storagebilltypecode='G') and " + "convert(char(4),outdate,102)+convert(char(2),outdate,100)<='" + InvntDate + "'" + "group by materialcode";
dbDA1.Fill(dbDS1, "outstrg");
//将所有出库数据更新进 storage_inventory
for (int i = 0; i <= dbDS1.Tables["outstrg"].Rows.Count - 1; i++)
{
selectCMD1.CommandText = "update storage_inventory set " + "quantity=quantity-" + dbDS1.Tables["outstrg"].Rows[i][1].ToString() + "," + "price=totalprice/quantity" + "," + "totalprice=totalprice-" + dbDS1.Tables["outstrg"].Rows[i][2].ToString() + " where yearmonth='" + InvntDate + "' and materialcode='" + dbDS1.Tables["outstrg"].Rows[i][0].ToString() + "'";
selectCMD1.ExecuteNonQuery();
}
MessageBox.Show("结转成功");
}
else //存在前期结转数据，获取最后一次结转日期，根据该日期返回所有结转的物品数量
{
string LastInvntDate;
LastInvntDate = dbDS1.Tables["prioryearmonth"].Rows[dbDS1.Tables["prioryearmonth"].Rows.Count - 1][0].ToString();
if (LastInvntDate == InvntDate) //当月已结转
{
MessageBox.Show("当月已结转");
return;
}
//根据 LastInvntDate 和 InvntDate 日期区间结转所有入库物品
selectCMD1.CommandText = "SELECT materialcode,sum(quantity) as quantity,sum(totalprice) as totalprice FROM view_InStorageMaterials " + "where (storagebilltypecode='H' or storagebilltypecode='I'or storagebilltypecode='M') and " + "convert(char(4),indate,102)+convert(char(2),indate,100)>='" + LastInvntDate + "'" + " and convert(char(4),indate,102)+convert(char(2),indate,100)<='" + InvntDate + "'" + "group by materialcode";
dbDA1.Fill(dbDS1, "instrg");
//将所有入库数据与前期数据更新进 storage_inventory
double priorquantity = 0;
double priortotalprice = 0;
double currentquantity = 0;
double currenttotalprice = 0;
for (int i = 0; i <= dbDS1.Tables["instrg"].Rows.Count - 1; i++)
{
currentquantity = System.Convert.ToDouble(dbDS1.Tables["instrg"].Rows[i][1]);
currenttotalprice = System.Convert.ToDouble(dbDS1.Tables["instrg"].Rows[i][2]);
//返回前期物品数量
selectCMD1.CommandText = "SELECT materialcode,quantity,totalprice FROM storage_inventory where yearmonth='" + LastInvntDate + "' and materialcode='" + dbDS1.Tables["instrg"].Rows[i][0] + "'";
dbDA1.Fill(dbDS1, "priormaterial");
//在原有数量之上加上入库数量
if (dbDS1.Tables["priormaterial"].Rows.Count > 0) //上期存在该物品，更新数量后再添加 = 前期数量+本期入库数量
{
priorquantity = System.Convert.ToDouble(dbDS1.Tables["priormaterial"].Rows[0][1]);
priortotalprice = System.Convert.ToDouble(dbDS1.Tables["priormaterial"].Rows[0][2]);
//selectCMD1.CommandText = "update storage_inventory set " + _
// "quantity=" + CStr(priorquantity + currentquantity) + "," + _
// "price=" + CStr((priortotalprice + currenttotalprice) / (priorquantity + currentquantity)) + "," + _
// "totalprice=" + CStr(priortotalprice + currenttotalprice) + _
// " where yearmonth='" + LastInvntDate + "' and materialcode='" + dbDS1.Tables("instrg").Rows(i).Item(0).ToString + "'"
//selectCMD1.ExecuteNonQuery()
//Else
}
selectCMD1.CommandText = "insert into storage_inventory (yearmonth,materialcode,quantity,price,totalprice) " + "values (" + "'" + InvntDate + "'" + "," + "'" + dbDS1.Tables["instrg"].Rows[i][0].ToString() + "'" + "," + (System.Convert.ToString(priorquantity + currentquantity)) + "," + (Convert.ToString(((priortotalprice + currenttotalprice) / (priorquantity + currentquantity)))) + "," + (System.Convert.ToString(priortotalprice + currenttotalprice)) + ")";
selectCMD1.ExecuteNonQuery();
dbDS1.Tables["priormaterial"].Rows.Clear();
}
//根据 LastInvntDate 和 InvntDate 日期区间结转所有出库物品
selectCMD1.CommandText = "SELECT materialcode,sum(quantity) as quantity,sum(totalprice) as totalprice FROM view_OutStorageMaterials " + "where outdepcode='" + rms_var.GetDeptCode(strgdeptname) + "' and (storagebilltypecode='A' or storagebilltypecode='B'or storagebilltypecode='D' or storagebilltypecode='G') and " + "convert(char(4),outdate,102)+convert(char(2),outdate,100)<='" + InvntDate + "'" + "group by materialcode";
dbDA1.Fill(dbDS1, "outstrg");
for (int i = 0; i <= dbDS1.Tables["outstrg"].Rows.Count - 1; i++)
{
currentquantity = System.Convert.ToDouble(dbDS1.Tables["outstrg"].Rows[i][1]);
currenttotalprice = System.Convert.ToDouble(dbDS1.Tables["outstrg"].Rows[i][2]);
//返回前期物品数量
selectCMD1.CommandText = "SELECT materialcode,quantity,totalprice FROM storage_inventory where yearmonth='" + InvntDate + "' and materialcode='" + dbDS1.Tables["outstrg"].Rows[i][0] + "'";
dbDA1.Fill(dbDS1, "priormaterial");
//在原有数量之上减去出库数量
if (dbDS1.Tables["priormaterial"].Rows.Count > 0) //上期存在该物品，更新数量
{
//priorquantity = dbDS1.Tables("priormaterial").Rows(i).Item(1)
//priortotalprice = dbDS1.Tables("priormaterial").Rows(i).Item(2)
selectCMD1.CommandText = "update storage_inventory set " + "quantity=quantity-" + currentquantity.ToString() + "," + "price=totalprice/quantity" + "," + "totalprice=totalprice-" + currenttotalprice.ToString() + " where yearmonth='" + InvntDate + "' and materialcode='" + dbDS1.Tables["outstrg"].Rows[i][0].ToString() + "'";
selectCMD1.ExecuteNonQuery();
}
dbDS1.Tables["priormaterial"].Rows.Clear();
}
MessageBox.Show("结转成功");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
conn1.Close();
}
}
private void MenuItem32_Click(System.Object sender, System.EventArgs e)
{
frmSelStorageDept frmssd = new frmSelStorageDept();
frmssd.ShowDialog();
if (frmssd.DialogResult == DialogResult.OK)
{
ShowStorageInventory(frmssd.ComboBox1.Text);
}
}