private void nodeImport(string catalogID, string nodeId)
{
MoneySends mss = ConnectionManager.Context.table("MoneySends").where ("MSID='" + nodeId + "' and CatalogID='" + catalogID + "'").select("*").getItem <MoneySends>(new MoneySends());
if (mss != null && string.IsNullOrEmpty(mss.MSID))
{
return;
}
OpenFileDialog sfd = new OpenFileDialog();
sfd.FileName = string.Empty;
sfd.Filter = "*.xlsx|*.xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
int subjectCount = 0;
List <string> errorSubjectList = new List <string>();
DataSet ds = ExcelHelper.ExcelToDataSet(sfd.FileName);
if (ds != null && ds.Tables.Count >= 2)
{
#region 清除数据
ConnectionManager.Context.table("Contact_Table1").where ("NodeID='" + nodeId + "'").delete();
ConnectionManager.Context.table("Contact_Table2").where ("NodeID='" + nodeId + "'").delete();
ConnectionManager.Context.table("Contact_Table3").where ("NodeID='" + nodeId + "'").delete();
ConnectionManager.Context.table("Contact_Table4").where ("NodeID='" + nodeId + "'").delete();
ConnectionManager.Context.table("Contact_Table5").where ("NodeID='" + nodeId + "'").delete();
#endregion
foreach (DataTable dt in ds.Tables)
{
switch (dt.TableName)
{
case "项目基本情况":
#region 项目基本情况
foreach (DataRow dr in dt.Rows)
{
string value1 = dr["项目名称"] != null ? dr["项目名称"].ToString() : string.Empty;
string value2 = dr["项目牵头单位"] != null ? dr["项目牵头单位"].ToString() : string.Empty;
string value3 = dr["项目总负责人"] != null ? dr["项目总负责人"].ToString() : string.Empty;
string value4 = dr["项目总经费"] != null ? dr["项目总经费"].ToString() : string.Empty;
string value5 = dr["项目到位经费"] != null ? dr["项目到位经费"].ToString() : string.Empty;
string value6 = dr["节点评估时间"] != null ? dr["节点评估时间"].ToString() : string.Empty;
string value7 = dr["节点评估意见"] != null ? dr["节点评估意见"].ToString() : string.Empty;
string value8 = dr["节点评估等级"] != null ? dr["节点评估等级"].ToString() : string.Empty;
string value9 = dr["节点评估专家"] != null ? dr["节点评估专家"].ToString() : string.Empty;
//添加节点-项目信息
Contact_Table1 ct1 = new Contact_Table1();
ct1.TID = Guid.NewGuid().ToString();
ct1.CatalogID = mss.CatalogID;
ct1.ProjectID = mss.ProjectID;
ct1.NodeID = mss.MSID;
ct1.ProjectName = value1;
ct1.WorkUnit = value2;
ct1.ProjectMaster = value3;
try
{
ct1.TotalMoney = decimal.Parse(value4);
}
catch (Exception ex)
{
MessageBox.Show("对不起，项目(" + value1 + ")的总经费错误！");
}
try
{
ct1.TotalMoneyNow = decimal.Parse(value5);
}
catch (Exception ex)
{
MessageBox.Show("对不起，项目(" + value1 + ")的到位错误！");
}
ct1.copyTo(ConnectionManager.Context.table(typeof(Contact_Table1).Name)).insert();
//更新节点信息
try
{
mss.NodeWillTime = DateTime.Parse(value6);
}
catch (Exception ex) { }
mss.WillContent = value7;
mss.WillLevel = value8;
mss.WillWorker = value9;
mss.copyTo(ConnectionManager.Context.table("MoneySends")).where ("MSID='" + nodeId + "' and CatalogID='" + catalogID + "'").update();
}
#endregion
break;
case "项目办公室组成":
#region 项目办公室组成
foreach (DataRow dr in dt.Rows)
{
string value1 = dr["项目办公室职务"] != null ? dr["项目办公室职务"].ToString() : string.Empty;
string value2 = dr["姓名"] != null ? dr["姓名"].ToString() : string.Empty;
string value3 = dr["单位"] != null ? dr["单位"].ToString() : string.Empty;
string value4 = dr["职务/职称"] != null ? dr["职务/职称"].ToString() : string.Empty;
string value5 = dr["联系电话"] != null ? dr["联系电话"].ToString() : string.Empty;
Contact_Table2 ct2 = new Contact_Table2();
ct2.TID = Guid.NewGuid().ToString();
ct2.CatalogID = mss.CatalogID;
ct2.ProjectID = mss.ProjectID;
ct2.NodeID = mss.MSID;
ct2.WorkDeskJob = value1;
ct2.PersonName = value2;
ct2.PersonUnit = value3;
ct2.PersonJob = value4;
ct2.PersonPhone = value5;
ct2.copyTo(ConnectionManager.Context.table(typeof(Contact_Table2).Name)).insert();
}
#endregion
break;
case "项目完成情况":
#region 项目完成情况
foreach (DataColumn dc in dt.Columns)
{
if (string.IsNullOrEmpty(dc.ColumnName))
{
continue;
}
int rowIndexxx = 0;
foreach (DataRow dr in dt.Rows)
{
rowIndexxx++;
string valueStr = dr[dc.ColumnName] != null ? dr[dc.ColumnName].ToString() : string.Empty;
Contact_Table3 ct3 = new Contact_Table3();
ct3.TID = Guid.NewGuid().ToString();
ct3.CatalogID = mss.CatalogID;
ct3.ProjectID = mss.ProjectID;
ct3.NodeID = mss.MSID;
ct3.ModuleName = dc.ColumnName + "xxxxx" + rowIndexxx;
ct3.ModuleValue = valueStr;
ct3.copyTo(ConnectionManager.Context.table(typeof(Contact_Table3).Name)).insert();
}
}
#endregion
break;
case "项目经费使用情况":
#region 导入 项目经费使用情况
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName == "科目名称")
{
continue;
}
//添数据
int rowIndexx = 0;
foreach (DataRow dr in dt.Rows)
{
rowIndexx++;
//取值
string valStr = dr[dc.ColumnName] != null ? dr[dc.ColumnName].ToString() : string.Empty;
Contact_Table4 ct4 = new Contact_Table4();
ct4.TID = Guid.NewGuid().ToString();
ct4.CatalogID = mss.CatalogID;
ct4.ProjectID = mss.ProjectID;
ct4.NodeID = mss.MSID;
ct4.ModuleName = dc.ColumnName + "xxxxx" + rowIndexx;
ct4.ModuleValue = valStr;
ct4.copyTo(ConnectionManager.Context.table("Contact_Table4")).insert();
}
}
#endregion
break;
case "课题经费拨付与支出情况":
#region 导入 课题经费拨付与支出情况
foreach (DataRow dr in dt.Rows)
{
subjectCount++;
string value1 = dr["课题名称"] != null ? dr["课题名称"].ToString() : string.Empty;
string value2 = dr["课题负责单位"] != null ? dr["课题负责单位"].ToString() : string.Empty;
string value3 = dr["课题合同总价款"] != null ? dr["课题合同总价款"].ToString() : string.Empty;
string value4 = dr["课题应拨经费"] != null ? dr["课题应拨经费"].ToString() : string.Empty;
string value5 = dr["课题经费拨付时间"] != null ? dr["课题经费拨付时间"].ToString() : string.Empty;
string value6 = dr["课题已拨经费"] != null ? dr["课题已拨经费"].ToString() : string.Empty;
string value7 = dr["课题支出经费"] != null ? dr["课题支出经费"].ToString() : string.Empty;
string value8 = dr["课题应拨未拨经费"] != null ? dr["课题应拨未拨经费"].ToString() : string.Empty;
if (string.IsNullOrEmpty(value1))
{
errorSubjectList.Add(value1);
continue;
}
Subject subjectObj = ConnectionManager.Context.table("Subject").where ("CatalogID='" + mss.CatalogID + "' and SubjectName='" + value1 + "'").select("*").getItem <Subject>(new Subject());
if (subjectObj != null && string.IsNullOrEmpty(subjectObj.SubjectID))
{
errorSubjectList.Add(value1);
continue;
}
Contact_Table5 ct5 = new Contact_Table5();
ct5.TID = Guid.NewGuid().ToString();
ct5.CatalogID = mss.CatalogID;
ct5.ProjectID = mss.ProjectID;
ct5.NodeID = mss.MSID;
ct5.SubjectID = subjectObj.SubjectID;
ct5.SubjectWorkUnit = value2;
ct5.SubjectTotalMoney = decimal.Parse(value3);
ct5.SubjectSendMoney = decimal.Parse(value4);
ct5.SubjectSendTime = DateTime.Parse(value5);
ct5.SubjectSendedMoney = decimal.Parse(value6);
ct5.SubjectUseMoney = decimal.Parse(value7);
ct5.SubjectNoSendMoney = decimal.Parse(value8);
ct5.copyTo(ConnectionManager.Context.table("Contact_Table5")).insert();
}
#endregion
break;
}
}
//组织文字
StringBuilder importResult = new StringBuilder();
importResult.Append("导入成功！").Append("共找到课题经费拨付支出情况").Append(subjectCount).Append("条,其中").Append(errorSubjectList.Count).Append("条导入错误！");
if (errorSubjectList.Count >= 1)
{
importResult.Append("分别是(");
foreach (string s in errorSubjectList)
{
importResult.Append("\"").Append(s).Append("\",");
}
importResult.Remove(importResult.Length - 1, 1);
importResult.Append(")");
}
MessageBox.Show(importResult.ToString());
}
srpSearch.search();
}
catch (Exception ex)
{
MessageBox.Show("对不起，Excel导入失败！Ex:" + ex.ToString());
}
}
}
private void tvProjectList_AfterSelect(object sender, TreeViewEventArgs e)
{
if (e.Node.Tag is Catalog)
{
Catalog catalogObj = (Catalog)e.Node.Tag;
DataTable dtCatalog = mlpMoneys.getTempMoneyTable("row", e.Node.Nodes.Count);
mlpMoneys.showOrHideTopPanel(true);
mlpMoneys.setNodeName("");
mlpMoneys.showOrHideColumn(dtCatalog.Columns.Count - 1, false);
mlpMoneys.showOrHideColumn(dtCatalog.Columns.Count - 2, true);
for (int yyy = 0; yyy < e.Node.Nodes.Count; yyy++)
{
mlpMoneys.showOrHideColumn(2 + yyy, true);
}
Application.DoEvents();
#region 显示总表
int nodeIndex = 0;
foreach (TreeNode sub in e.Node.Nodes)
{
MoneySends mss = (MoneySends)sub.Tag;
List <Contact_Table4> moneyss = ConnectionManager.Context.table("Contact_Table4").where ("NodeID='" + mss.MSID + "'").select("*").getList <Contact_Table4>(new Contact_Table4());
foreach (Contact_Table4 mObj in moneyss)
{
try
{
if (mObj.ModuleName != null)
{
if (mObj.ModuleName.StartsWith("项目合同"))
{
string[] tttt = mObj.ModuleName.Split(new string[] { "xxxxx" }, StringSplitOptions.None);
if (tttt != null && tttt.Length >= 2)
{
decimal val = 0;
try
{
val = decimal.Parse(mObj.ModuleValue);
}
catch (Exception ex) { }
decimal total = 0;
try
{
total = decimal.Parse(dtCatalog.Rows[(int.Parse(tttt[1]) - 1)][1].ToString());
}
catch (Exception ex) { }
dtCatalog.Rows[(int.Parse(tttt[1]) - 1)][1] = total + val;
}
}
else if (mObj.ModuleName.StartsWith("本阶段支出经费"))
{
string[] tttt = mObj.ModuleName.Split(new string[] { "xxxxx" }, StringSplitOptions.None);
if (tttt != null && tttt.Length >= 2)
{
decimal val = 0;
try
{
val = decimal.Parse(mObj.ModuleValue);
}
catch (Exception ex) { }
decimal total = 0;
try
{
total = decimal.Parse(dtCatalog.Rows[(int.Parse(tttt[1]) - 1)][dtCatalog.Columns.Count - 2].ToString());
}
catch (Exception ex) { }
mlpMoneys.showOrHideColumn(2 + nodeIndex, true);
dtCatalog.Rows[(int.Parse(tttt[1]) - 1)][2 + nodeIndex] = val;
total += val;
dtCatalog.Rows[(int.Parse(tttt[1]) - 1)][dtCatalog.Columns.Count - 2] = total;
}
}
//else if (mObj.ModuleName.StartsWith("备注"))
//{
// string[] tttt = mObj.ModuleName.Split(new string[] { "xxxxx" }, StringSplitOptions.None);
// if (tttt != null && tttt.Length >= 2)
// {
// dtData.Rows[(int.Parse(tttt[1]) - 1)][dtData.Columns.Count - 1] = mObj.ModuleValue;
// }
//}
}
}
catch (Exception ex) { }
}
nodeIndex++;
}
mlpMoneys.setTableDataSource(dtCatalog);
#endregion
#region 显示标题栏的值
//合同金额
Project proj = ConnectionManager.Context.table("Project").where ("ProjectID='" + catalogObj.CatalogID + "'").select("*").getItem <Project>(new Project());
mlpMoneys.setTag1Value(proj.TotalMoney != null ? proj.TotalMoney.ToString() : string.Empty);
//累计预算= 项目节点管理中的经费金额
decimal totalVal = 0;
foreach (TreeNode tn in e.Node.Nodes)
{
totalVal += ((MoneySends)tn.Tag).TotalMoney;
}
mlpMoneys.setTag2Value(totalVal + "");
//累计到位= 项目基本情况——项目到位经费
totalVal = 0;
foreach (TreeNode tn in e.Node.Nodes)
{
List <Contact_Table1> table1sss = ConnectionManager.Context.table("Contact_Table1").where ("NodeID='" + ((MoneySends)tn.Tag).MSID + "'").select("*").getList <Contact_Table1>(new Contact_Table1());
foreach (Contact_Table1 table1 in table1sss)
{
if (string.IsNullOrEmpty(table1.TID))
{
continue;
}
totalVal += table1.TotalMoneyNow;
}
}
mlpMoneys.setTag3Value(totalVal + "");
//累计支出= 项目经费使用情况——本阶段支出经费——合计
totalVal = 0;
foreach (TreeNode tn in e.Node.Nodes)
{
Contact_Table4 table1 = ConnectionManager.Context.table("Contact_Table4").where ("NodeID='" + ((MoneySends)tn.Tag).MSID + "' and ModuleName = '本阶段支出经费xxxxx16'").select("*").getItem <Contact_Table4>(new Contact_Table4());
if (string.IsNullOrEmpty(table1.TID))
{
continue;
}
decimal vall = 0;
try
{
vall = decimal.Parse(table1.ModuleValue);
}
catch (Exception ex) { }
totalVal += vall;
}
mlpMoneys.setTag4Value(totalVal + "");
#endregion
}
else if (e.Node.Tag is MoneySends)
{
MoneySends moneySendObj = (MoneySends)e.Node.Tag;
DataTable dtData = mlpMoneys.getTempMoneyTable("row", e.Node.Parent.Nodes.Count);
mlpMoneys.showOrHideTopPanel(false);
mlpMoneys.setNodeName(e.Node.Text);
int nodeIndex = e.Node.Parent.Nodes.IndexOf(e.Node);
mlpMoneys.showOrHideColumn(dtData.Columns.Count - 1, true);
mlpMoneys.showOrHideColumn(dtData.Columns.Count - 2, false);
for (int ttt = 0; ttt < e.Node.Parent.Nodes.Count; ttt++)
{
mlpMoneys.showOrHideColumn(2 + ttt, false);
}
#region 显示金额表
//显示节点经费
List <Contact_Table4> moneyss = ConnectionManager.Context.table("Contact_Table4").where ("NodeID='" + moneySendObj.MSID + "'").select("*").getList <Contact_Table4>(new Contact_Table4());
foreach (Contact_Table4 mObj in moneyss)
{
try
{
if (mObj.ModuleName != null)
{
if (mObj.ModuleName.StartsWith("项目合同"))
{
string[] tttt = mObj.ModuleName.Split(new string[] { "xxxxx" }, StringSplitOptions.None);
if (tttt != null && tttt.Length >= 2)
{
dtData.Rows[(int.Parse(tttt[1]) - 1)][1] = mObj.ModuleValue;
}
}
else if (mObj.ModuleName.StartsWith("本阶段支出经费"))
{
string[] tttt = mObj.ModuleName.Split(new string[] { "xxxxx" }, StringSplitOptions.None);
if (tttt != null && tttt.Length >= 2)
{
mlpMoneys.showOrHideColumn(2 + nodeIndex, true);
dtData.Rows[(int.Parse(tttt[1]) - 1)][2 + nodeIndex] = mObj.ModuleValue;
}
}
else if (mObj.ModuleName.StartsWith("备注"))
{
string[] tttt = mObj.ModuleName.Split(new string[] { "xxxxx" }, StringSplitOptions.None);
if (tttt != null && tttt.Length >= 2)
{
dtData.Rows[(int.Parse(tttt[1]) - 1)][dtData.Columns.Count - 1] = mObj.ModuleValue;
}
}
}
}
catch (Exception ex) { }
}
mlpMoneys.setTableDataSource(dtData);
#endregion
}
}