public static string SaveData(string data)
{
data = data.Replace("&", "&");
MemoryStream mem = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(data));
XPathDocument document;
try
{
document = new XPathDocument(mem);
}
catch (Exception)
{
return "error";
}
BenefitAdminDataContext bfAdmin = new BenefitAdminDataContext();
var allInSvr = from c in bfAdmin.Services select c;
bfAdmin.Services.DeleteAllOnSubmit(allInSvr);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
var allInAct = from c in bfAdmin.Activities select c;
bfAdmin.Activities.DeleteAllOnSubmit(allInAct);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
var allInSAct = from c in bfAdmin.SubActivities select c;
bfAdmin.SubActivities.DeleteAllOnSubmit(allInSAct);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
var allInMat = from c in bfAdmin.Materials select c;
bfAdmin.Materials.DeleteAllOnSubmit(allInMat);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
XPathNavigator navigator = document.CreateNavigator();
XPathNodeIterator allDocs = navigator.Select("DOCUMENTS/DOCUMENT");
while (allDocs.MoveNext())
{
string title = allDocs.Current.GetAttribute("title", "");
if (title.Equals("Service"))
{
#region
string col0 = "", col1 = "", col2 = "", col3 = "", col4 = "", col5 = "";
string col6 = "", col7 = "", col8 = "", col9 = "", col10 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3");
getCol3.MoveNext();
col3 = getCol3.Current.Value;
XPathNodeIterator getCol4 = rows.Current.Select("R" + i + "/C4");
getCol4.MoveNext();
col4 = getCol4.Current.Value;
XPathNodeIterator getCol5 = rows.Current.Select("R" + i + "/C5");
getCol5.MoveNext();
col5 = getCol5.Current.Value;
XPathNodeIterator getCol6 = rows.Current.Select("R" + i + "/C6");
getCol6.MoveNext();
col6 = getCol6.Current.Value;
XPathNodeIterator getCol7 = rows.Current.Select("R" + i + "/C7");
getCol7.MoveNext();
col7 = getCol7.Current.Value;
XPathNodeIterator getCol8 = rows.Current.Select("R" + i + "/C8");
getCol8.MoveNext();
col8 = getCol8.Current.Value;
XPathNodeIterator getCol9 = rows.Current.Select("R" + i + "/C9");
getCol9.MoveNext();
col9 = getCol9.Current.Value;
XPathNodeIterator getCol10 = rows.Current.Select("R" + i + "/C10");
getCol10.MoveNext();
col10 = getCol10.Current.Value;
//insert to DB
if (!col0.Equals(""))
{
Service svr = new Service();
svr.SVCCode = col0;
svr.SVCName = col1;
svr.SVCDesc = col2;
svr.HostCode = col3;
svr.StaffRole = col4;
svr.SVCType = col5;
svr.SVCObjective = col6;
svr.SVCSupport = col7;
svr.SVCCoverage = col8;
svr.SVCStart = col9;
svr.SVCEnd = col10;
bfAdmin.Services.InsertOnSubmit(svr);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
else if (title.Equals("Activity"))
{
#region
string col0 = "", col1 = "", col2 = "", col3 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3");
getCol3.MoveNext();
col3 = getCol3.Current.Value;
//insert to DB
if (!col0.Equals("") && !col2.Equals(""))
{
Activity act = new Activity();
act.ACTCode = col0;
act.ACTDesc = col1;
act.SVCCode = col2;
act.ICF_Code = col3;
bfAdmin.Activities.InsertOnSubmit(act);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
else if (title.Equals("SubActivity"))
{
#region
string col0 = "", col1 = "", col2 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
//insert to DB
if (!col0.Equals("") && !col2.Equals(""))
{
SubActivity sact = new SubActivity();
sact.SACTCode = col0;
sact.SACTDesc = col1;
sact.ACTCode = col2;
bfAdmin.SubActivities.InsertOnSubmit(sact);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
else if (title.Equals("Material"))
{
#region
string col0 = "", col1 = "", col2 = "", col3 = "", col4 = "", col5 = "", col6 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3");
getCol3.MoveNext();
col3 = getCol3.Current.Value;
XPathNodeIterator getCol4 = rows.Current.Select("R" + i + "/C4");
getCol4.MoveNext();
col4 = getCol4.Current.Value;
XPathNodeIterator getCol5 = rows.Current.Select("R" + i + "/C5");
getCol5.MoveNext();
col5 = getCol5.Current.Value;
XPathNodeIterator getCol6 = rows.Current.Select("R" + i + "/C6");
getCol6.MoveNext();
col6 = getCol6.Current.Value;
//insert to DB
if (!col0.Equals("") && !col5.Equals(""))
{
Material mt = new Material();
mt.MaterialCode = col0;
mt.MaterialDesc = col1;
mt.Unit = col2;
mt.EstimatedPrice = col3;
mt.RealPrice = col4;
mt.SVCCode = col5;
mt.Note = col6;
bfAdmin.Materials.InsertOnSubmit(mt);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
}
return null;
}
public static string SaveData(string data)
{
data = data.Replace("&", "&");
MemoryStream mem = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(data));
XPathDocument document;
try
{
document = new XPathDocument(mem);
}
catch (Exception)
{
return "error";
}
BenefitAdminDataContext bfAdmin = new BenefitAdminDataContext();
var allInMis = from c in bfAdmin.Ministries select c;
bfAdmin.Ministries.DeleteAllOnSubmit(allInMis);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
var allInOr = from c in bfAdmin.Organizations select c;
bfAdmin.Organizations.DeleteAllOnSubmit(allInOr);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
var allInPro = from c in bfAdmin.Providers select c;
bfAdmin.Providers.DeleteAllOnSubmit(allInPro);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
XPathNavigator navigator = document.CreateNavigator();
XPathNodeIterator allDocs = navigator.Select("DOCUMENTS/DOCUMENT");
while (allDocs.MoveNext())
{
string title = allDocs.Current.GetAttribute("title", "");
if (title.Equals("Ministry"))
{
#region
string col0 = "", col1 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
if (!col0.Equals(""))
{
Ministry min = new Ministry();
min.MinistryCode = col0;
min.MinistryName = col1;
bfAdmin.Ministries.InsertOnSubmit(min);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
else if(title.Equals("Organization"))
{
#region
string col0 = "", col1 = "", col2 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
if (!col0.Equals(""))
{
Organization org = new Organization();
org.OrgCode = col0;
org.OrgName = col1;
org.MinistryCode = col2;
bfAdmin.Organizations.InsertOnSubmit(org);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
else if(title.Equals("Provider"))
{
#region
string col0 = "", col1 = "", col2 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
if (!col0.Equals(""))
{
Provider pro = new Provider();
pro.ProviderCode = col0;
pro.ProviderName = col1;
pro.OrgCode = col2;
bfAdmin.Providers.InsertOnSubmit(pro);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
}
return null;
}
private void excelToDB(DataTable data)
{
//clean DB
#region
if (RadioButtonList_type.SelectedIndex == 1) //selecte create new sheet
{
if (DropDownListTo.SelectedIndex == 0)
{
var allInSvr = from c in bfAdmin.Services select c;
bfAdmin.Services.DeleteAllOnSubmit(allInSvr);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
else if (DropDownListTo.SelectedIndex == 1)
{
var allInAct = from c in bfAdmin.Activities select c;
bfAdmin.Activities.DeleteAllOnSubmit(allInAct);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
else if (DropDownListTo.SelectedIndex == 2)
{
var allInSAct = from c in bfAdmin.SubActivities select c;
bfAdmin.SubActivities.DeleteAllOnSubmit(allInSAct);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
else
{
var allInMat = from c in bfAdmin.Materials select c;
bfAdmin.Materials.DeleteAllOnSubmit(allInMat);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
#endregion
if (DropDownListTo.SelectedIndex == 0)
{
int skip = 0;
foreach (DataRow row in data.Rows)
{
if (row[0].ToString() != null && skip > 0)
{
Service svr = new Service();
try
{
svr.SVCCode = row[0].ToString();
svr.SVCName = row[1].ToString() == null ? "" : row[1].ToString();
svr.SVCDesc = row[2].ToString() == null ? "" : row[2].ToString();
svr.HostCode = row[3].ToString() == null ? "" : row[3].ToString();
svr.StaffRole = row[4].ToString() == null ? "" : row[4].ToString();
svr.SVCType = row[5].ToString() == null ? "" : row[5].ToString();
svr.SVCObjective = row[6].ToString() == null ? "" : row[6].ToString();
svr.SVCSupport = row[7].ToString() == null ? "" : row[7].ToString();
svr.SVCCoverage = row[8].ToString() == null ? "" : row[8].ToString();
svr.SVCStart = row[9].ToString() == null ? "" : row[9].ToString();
svr.SVCEnd = row[10].ToString() == null ? "" : row[10].ToString();
}
catch { }
var Svr = from c in bfAdmin.Services where c.SVCCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c;
if (Svr != null)
{
bfAdmin.Services.DeleteAllOnSubmit(Svr);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
bfAdmin.Services.InsertOnSubmit(svr);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
skip = 1;
}
}
else if (DropDownListTo.SelectedIndex == 1)
{
int skip = 0;
foreach (DataRow row in data.Rows)
{
if (row[0].ToString() != null && skip > 0)
{
Activity act = new Activity();
try
{
act.ACTCode = row[0].ToString();
act.ACTDesc = row[1].ToString() == null ? "" : row[1].ToString();
act.SVCCode = row[2].ToString() == null ? "" : row[2].ToString();
}
catch { }
var Act = from c in bfAdmin.Activities where c.ACTCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c;
bfAdmin.Activities.DeleteAllOnSubmit(Act);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
bfAdmin.Activities.InsertOnSubmit(act);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
skip = 1;
}
}
else if (DropDownListTo.SelectedIndex == 2)
{
int skip = 0;
foreach (DataRow row in data.Rows)
{
if (row[0].ToString() != null && skip > 0)
{
SubActivity sact = new SubActivity();
try
{
sact.SACTCode = row[0].ToString();
sact.SACTDesc = row[1].ToString() == null ? "" : row[1].ToString();
sact.ACTCode = row[2].ToString() == null ? "" : row[2].ToString();
}
catch { }
var Sact = from c in bfAdmin.SubActivities where c.SACTCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c;
bfAdmin.SubActivities.DeleteAllOnSubmit(Sact);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
bfAdmin.SubActivities.InsertOnSubmit(sact);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
skip = 1;
}
}
else
{
int skip = 0;
foreach (DataRow row in data.Rows)
{
if (row[0].ToString() != null && skip > 0)
{
Material mat = new Material();
try
{
mat.MaterialCode = row[0].ToString();
mat.MaterialDesc = row[1].ToString() == null ? "" : row[1].ToString();
mat.Unit = row[2].ToString() == null ? "" : row[2].ToString();
mat.EstimatedPrice = row[3].ToString() == null ? "" : row[3].ToString();
mat.RealPrice = row[4].ToString() == null ? "" : row[4].ToString();
mat.SVCCode = row[5].ToString() == null ? "" : row[5].ToString();
}
catch { }
var Mat = from c in bfAdmin.Materials where c.MaterialCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c;
bfAdmin.Materials.DeleteAllOnSubmit(Mat);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
bfAdmin.Materials.InsertOnSubmit(mat);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
skip = 1;
}
}
//refresh page
Response.Redirect(Request.RawUrl);
}
public static string SaveData(string data)
{
data = data.Replace("&", "&");
MemoryStream mem = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(data));
XPathDocument document;
try
{
document = new XPathDocument(mem);
}
catch (Exception)
{
return "error";
}
BenefitAdminDataContext bfAdmin = new BenefitAdminDataContext();
var allInAcost = from c in bfAdmin.ActivityCosts select c;
bfAdmin.ActivityCosts.DeleteAllOnSubmit(allInAcost);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
var allInAnno = from c in bfAdmin.Annotations select c;
bfAdmin.Annotations.DeleteAllOnSubmit(allInAnno);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
XPathNavigator navigator = document.CreateNavigator();
XPathNodeIterator allDocs = navigator.Select("DOCUMENTS/DOCUMENT");
while (allDocs.MoveNext())
{
string title = allDocs.Current.GetAttribute("title", "");
if (title.Equals("Annotation"))
{
#region
string col0 = "", col1 = "", col2 = "", col3 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3");
getCol3.MoveNext();
col3 = getCol3.Current.Value;
//insert to DB
if (!col0.Equals(""))
{
Annotation anno = new Annotation();
anno.AID = col0;
anno.AText = col1;
anno.AnnotationID = col2;
anno.Reference = col3;
bfAdmin.Annotations.InsertOnSubmit(anno);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
#endregion
}
else if (title.Equals("ActivityCost"))
{
string col0 = "", col1 = "", col2 = "", col3 = "", col4 = "", col5 = "", col6 = "", col7 = "", col8 = "", col9 = "";
XPathNodeIterator countRowString = allDocs.Current.Select("METADATA/ROWS");
countRowString.MoveNext();
int countRow = Convert.ToInt32(countRowString.Current.Value);
XPathNodeIterator rows = allDocs.Current.Select("DATA");
while (rows.MoveNext())
{
for (int i = 1; i < countRow; i++)
{
XPathNodeIterator getCol0 = rows.Current.Select("R" + i + "/C0");
getCol0.MoveNext();
col0 = getCol0.Current.Value;
XPathNodeIterator getCol1 = rows.Current.Select("R" + i + "/C1");
getCol1.MoveNext();
col1 = getCol1.Current.Value;
XPathNodeIterator getCol2 = rows.Current.Select("R" + i + "/C2");
getCol2.MoveNext();
col2 = getCol2.Current.Value;
XPathNodeIterator getCol3 = rows.Current.Select("R" + i + "/C3");
getCol3.MoveNext();
col3 = getCol3.Current.Value;
XPathNodeIterator getCol4 = rows.Current.Select("R" + i + "/C4");
getCol4.MoveNext();
col4 = getCol4.Current.Value;
XPathNodeIterator getCol5 = rows.Current.Select("R" + i + "/C5");
getCol5.MoveNext();
col5 = getCol5.Current.Value;
XPathNodeIterator getCol6 = rows.Current.Select("R" + i + "/C6");
getCol6.MoveNext();
col6 = getCol6.Current.Value;
XPathNodeIterator getCol7 = rows.Current.Select("R" + i + "/C7");
getCol7.MoveNext();
col7 = getCol7.Current.Value;
XPathNodeIterator getCol8 = rows.Current.Select("R" + i + "/C8");
getCol8.MoveNext();
col8 = getCol8.Current.Value;
XPathNodeIterator getCol9 = rows.Current.Select("R" + i + "/C9");
getCol9.MoveNext();
col9 = getCol9.Current.Value;
//insert to DB
if (!col0.Equals(""))
{
ActivityCost acost = new ActivityCost();
acost.ACTCode = col0;
acost.Unit = col1;
acost.LabourCost = col2;
acost.MaterialCost = col3;
acost.CC_Equipment = col4;
acost.CC_Building = col5;
acost.IndirectCost = col6;
acost.ProposedCost = col7;
acost.CurrentCost = col8;
acost.ReferencedCostOrg = col9;
bfAdmin.ActivityCosts.InsertOnSubmit(acost);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
}
}
}
return null;
}
private void excelToDB(DataTable data)
{
//remove data if radio selected 1
if (RadioButtonList_type.SelectedIndex == 1) //selecte create new sheet
{
if (DropDownListTo.SelectedIndex == 0)
{
var allInAnno = from c in bfAdmin.Annotations select c;
bfAdmin.Annotations.DeleteAllOnSubmit(allInAnno);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
else if (DropDownListTo.SelectedIndex == 1)
{
var allInAcost = from c in bfAdmin.ActivityCosts select c;
bfAdmin.ActivityCosts.DeleteAllOnSubmit(allInAcost);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
}
if (DropDownListTo.SelectedIndex == 0)
{
int skip = 0;
foreach (DataRow row in data.Rows)
{
if (row[0].ToString() != null && skip > 0)
{
Annotation annot = new Annotation();
try
{
annot.AID = row[0].ToString();
annot.AText = row[1].ToString() == null ? "" : row[1].ToString();
annot.AnnotationID = row[2].ToString() == null ? "" : row[2].ToString();
annot.Reference = row[3].ToString() == null ? "" : row[3].ToString();
}
catch { }
var Annot = from c in bfAdmin.Annotations where c.AID.ToString().Trim().Equals(row[0].ToString().Trim()) select c;
bfAdmin.Annotations.DeleteAllOnSubmit(Annot);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
bfAdmin.Annotations.InsertOnSubmit(annot);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
skip = 1;
}
}
else if (DropDownListTo.SelectedIndex == 1)
{
int skip = 0;
foreach (DataRow row in data.Rows)
{
if (row[0].ToString() != null && skip > 0)
{
ActivityCost act = new ActivityCost();
try
{
act.ACTCode = row[0].ToString();
act.Unit = row[1].ToString() == null ? "" : row[1].ToString();
act.LabourCost = row[2].ToString() == null ? "" : row[2].ToString();
act.MaterialCost = row[3].ToString() == null ? "" : row[3].ToString();
act.CC_Equipment = row[4].ToString() == null ? "" : row[4].ToString();
act.CC_Building = row[5].ToString() == null ? "" : row[5].ToString();
act.IndirectCost = row[6].ToString() == null ? "" : row[6].ToString();
act.ProposedCost = row[7].ToString() == null ? "" : row[7].ToString();
act.CurrentCost = row[8].ToString() == null ? "" : row[8].ToString();
act.ReferencedCostOrg = row[9].ToString();
}
catch { }
var Acost = from c in bfAdmin.ActivityCosts where c.ACTCode.ToString().Trim().Equals(row[0].ToString().Trim()) select c;
bfAdmin.ActivityCosts.DeleteAllOnSubmit(Acost);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
bfAdmin.ActivityCosts.InsertOnSubmit(act);
try
{
bfAdmin.SubmitChanges();
}
catch (Exception) { bfAdmin = new BenefitAdminDataContext(); }
}
skip = 1;
}
}
//refresh page
Response.Redirect(Request.RawUrl);
}
public static void Clean()
{
bfAdmin = new BenefitAdminDataContext();
}
public XmlElement getXMLSheet(string select, string pk, string fk)
{
bfAdmin = new BenefitAdminDataContext();
XmlDocument xmlDoc = new XmlDocument();
XmlElement dataElm = xmlDoc.CreateElement("DATA");
xmlDoc.AppendChild(dataElm);
List<string> dataPK = this.queryPK(pk);
if (!select.Equals("All"))
{
string tmp = "";
foreach (string a in dataPK)
{
if (a.Trim().Equals(select))
{
tmp = a.Trim();
}
}
dataPK.Clear();
dataPK.Add(tmp);
}
foreach (string stringPK in dataPK)
{
XmlElement root = xmlDoc.CreateElement(pk);
root.SetAttribute("code", stringPK.Trim());
dataElm.AppendChild(root);
if (fk.Equals("Activity") && pk.Equals("Service"))
#region
{
var data = from m in bfAdmin.Activities where stringPK.Trim().Equals(m.SVCCode.Trim()) select m;
foreach (var s in data)
{
XmlElement act = xmlDoc.CreateElement("Activity");
root.AppendChild(act);
XmlElement ACTCode = xmlDoc.CreateElement("ACTCode");
ACTCode.InnerXml = s.ACTCode.Trim();
act.AppendChild(ACTCode);
XmlElement ACTDesc = xmlDoc.CreateElement("ACTDesc");
ACTDesc.InnerXml = s.ACTDesc.Trim();
act.AppendChild(ACTDesc);
XmlElement SVCCode = xmlDoc.CreateElement("SVCCode");
SVCCode.InnerXml = s.SVCCode.Trim();
act.AppendChild(SVCCode);
XmlElement ICF_Code = xmlDoc.CreateElement("ICF_Code");
ICF_Code.InnerXml = s.ICF_Code.Trim();
act.AppendChild(ICF_Code);
}
}
#endregion
else if (fk.Equals("SubActivity") && pk.Equals("Activity"))
#region
{
var data = from m in bfAdmin.SubActivities where stringPK.Trim().Equals(m.ACTCode.Trim()) select m;
foreach (var s in data)
{
XmlElement sact = xmlDoc.CreateElement("SubActivity");
root.AppendChild(sact);
XmlElement SACTCode = xmlDoc.CreateElement("SACTCode");
SACTCode.InnerXml = s.SACTCode.Trim();
sact.AppendChild(SACTCode);
XmlElement SACTDesc = xmlDoc.CreateElement("SACTDesc");
SACTDesc.InnerXml = s.SACTDesc.Trim();
sact.AppendChild(SACTDesc);
XmlElement ACTCode = xmlDoc.CreateElement("ACTCode");
ACTCode.InnerXml = s.ACTCode.Trim();
sact.AppendChild(ACTCode);
}
}
#endregion
else if (fk.Equals("Material") && pk.Equals("Service"))
#region
{
var data = from m in bfAdmin.Materials where stringPK.Trim().Equals(m.SVCCode.Trim()) select m;
foreach (var s in data)
{
XmlElement mat = xmlDoc.CreateElement("Material");
root.AppendChild(mat);
XmlElement MaterialCode = xmlDoc.CreateElement("MaterialCode");
MaterialCode.InnerXml = s.MaterialCode.Trim();
mat.AppendChild(MaterialCode);
XmlElement MaterialDesc = xmlDoc.CreateElement("MaterialDesc");
MaterialDesc.InnerXml = s.MaterialDesc.Trim();
mat.AppendChild(MaterialDesc);
XmlElement Unit = xmlDoc.CreateElement("Unit");
Unit.InnerXml = s.Unit.Trim();
mat.AppendChild(Unit);
XmlElement EstimatedPrice = xmlDoc.CreateElement("EstimatedPrice");
EstimatedPrice.InnerXml = s.EstimatedPrice.Trim();
mat.AppendChild(EstimatedPrice);
XmlElement RealPrice = xmlDoc.CreateElement("RealPrice");
RealPrice.InnerXml = s.RealPrice.Trim();
mat.AppendChild(RealPrice);
XmlElement SVCCode = xmlDoc.CreateElement("SVCCode");
SVCCode.InnerXml = s.SVCCode.Trim();
mat.AppendChild(SVCCode);
XmlElement Note = xmlDoc.CreateElement("Note");
Note.InnerXml = s.Note.Trim();
mat.AppendChild(Note);
}
}
#endregion
}
return xmlDoc.DocumentElement;
}
/* getXMLSheet : returen specific XmlElement of sheet
* input : name of organization and name of sheet
* : name of organization and string "All"
* : name of organization and string "List"
* output : XmlElement specific by input
* contract string string -> XmlElement
* */
public XmlElement getXMLSheet(string group, string sheet)
{
bfAdmin = new BenefitAdminDataContext();
XmlDocument xmlDoc = new XmlDocument();
//init filepath and read xml document
#region
if (group.ToUpper().Equals("CODE"))
{
if (sheet.ToUpper().Equals("ALL"))
{
return getXmlDocumentFromDB(xmlCode).DocumentElement;
}
else
{
xmlDoc = getXmlDocumentFromDB(xmlCode);
}
}
else if (group.ToUpper().Equals("COST"))
{
if (sheet.ToUpper().Equals("ALL"))
{
return getXmlDocumentFromDB(xmlCost).DocumentElement;
}
else
{
xmlDoc = getXmlDocumentFromDB(xmlCost);
}
}
#endregion
XmlNodeList nodes = xmlDoc.GetElementsByTagName(group.ToUpper());
if (!sheet.ToUpper().Equals("LIST"))
{
foreach (XmlNode node in nodes.Item(0).ChildNodes)
{
if (node.Name.Equals(sheet))
{
return (XmlElement)node;
}
}
return null;
}
else
{
if (group.ToUpper().Equals("COST"))
{
XmlDocument doc = new XmlDocument();
XmlElement root = doc.CreateElement("Lists");
doc.AppendChild(root);
XmlElement list = doc.CreateElement("SheetName");
list.InnerText = "Annotation";
root.AppendChild(list);
list = doc.CreateElement("SheetName");
list.InnerText = "ActivityCost";
root.AppendChild(list);
return doc.DocumentElement;
}
else if (group.ToUpper().Equals("CODE"))
{
XmlDocument doc = new XmlDocument();
XmlElement root = doc.CreateElement("Lists");
doc.AppendChild(root);
XmlElement list = doc.CreateElement("SheetName");
list.InnerText = "Service";
root.AppendChild(list);
list = doc.CreateElement("SheetName");
list.InnerText = "Activity";
root.AppendChild(list);
list = doc.CreateElement("SheetName");
list.InnerText = "SubActivity";
root.AppendChild(list);
list = doc.CreateElement("SheetName");
list.InnerText = "Material";
root.AppendChild(list);
return doc.DocumentElement;
}
}
return null;
}
