/// <summary>
/// Update the dataset object by fetching from the database.
/// </summary>
/// <returns>Dataset object.</returns>
public override DataSet GetData()
{
int CustomVocationalJobPlacementId = -1;
using (SHS.UserBusinessServices.DetailPages objectCpt = new SHS.UserBusinessServices.DetailPages())
{
if (base.GetRequestParameterValue("CustomVocationalJobPlacementId").Length > 0)
{
int.TryParse(base.GetRequestParameterValue("CustomVocationalJobPlacementId"), out CustomVocationalJobPlacementId);
}
else // Get AuthorizationcodeId after save
{
if (BaseCommonFunctions.ScreenInfo.CurrentDocument.DocumentDataSet != null)
{
if (BaseCommonFunctions.ScreenInfo.CurrentDocument.DocumentDataSet.Tables["CustomVocationalJobPlacements"].Rows[0]["CustomVocationalJobPlacementId"] != null)
{
int.TryParse(BaseCommonFunctions.ScreenInfo.CurrentDocument.DocumentDataSet.Tables["CustomVocationalJobPlacements"].Rows[0]["CustomVocationalJobPlacementId"].ToString(), out CustomVocationalJobPlacementId);
}
}
}
System.Data.DataSet dataSetCustomVocationalJobPlacement = new System.Data.DataSet();
dataSetCustomVocationalJobPlacement = (SHS.BaseLayer.BaseCommonFunctions.GetPageDataSet(PageDataSetName));
DataSet dataSetObject = null;
if (CustomVocationalJobPlacementId >= 0)
{
dataSetObject = objectCpt.GetJobPlacementDetails(CustomVocationalJobPlacementId);
HiddenField_CustomVocationalJobPlacements_CustomVocationalJobPlacementId.Value = dataSetObject.Tables["CustomVocationalJobPlacements"].Rows[0]["CustomVocationalJobPlacementId"].ToString();
//if (dataSetObject.Tables["ClientPrograms"].Rows[0]["PrimaryAssignment"].ToString() == "Y")
// CheckBox_ClientPrograms_PrimaryAssignment.Checked = true;
//else
// CheckBox_ClientPrograms_PrimaryAssignment.Checked = false;
}
if (dataSetObject != null)
{
if (!dataSetObject.IsRowExists("CustomVocationalJobPlacements"))
{
dataSetObject = BaseCommonFunctions.ScreenInfo.CurrentDocument.DocumentDataSet = base.DocumentInitializeDocument();
}
try
{
dataSetCustomVocationalJobPlacement.Merge(dataSetObject);
}
catch
{
dataSetCustomVocationalJobPlacement.Merge(dataSetObject);
}
}
SHS.BaseLayer.BaseCommonFunctions.UnsavedProcessDetailPage(this.ParentDetailPageObject.UnSavedChangeId, 0, this.ParentDetailPageObject.ScreenId, SHS.BaseLayer.SharedTables.StaffSharedTables.UnsavedChanges, this.ParentDetailPageObject, dataSetObject, dataSetCustomVocationalJobPlacement, PageDataSetName);
return(dataSetCustomVocationalJobPlacement);
}
}
/// <summary>
///
/// </summary>
/// <param name="Comb_Item"></param>
public static bool FindMember(string where, ComboBox comb, System.Data.DataTable dt_All, string SortName)
{
//string displayMember = comb.DisplayMember;
//string valueMember = comb.ValueMember;
if (dt_All == null || dt_All.Rows.Count == 0)
{
return(false);
}
System.Data.DataRow[] drs = dt_All.Select(where);
System.Data.DataSet ds = new System.Data.DataSet();
ds.Merge(drs);
if (ds.Tables.Count > 0)
{
DataView dw = ds.Tables[0].DefaultView;
dw.Sort = SortName + " asc";
comb.DataSource = ds.Tables[0];
comb.SelectedIndex = -1;
return(true);
}
else
{
return(false);
}
//comb.DisplayMember = displayMember;
//comb.ValueMember = valueMember;
}
public override int Fill(DataSet dataSet)
{
int filled = 0;
foreach (DataTable table in dataSet.Tables)
{
DateTime lastModified;
if (lastModifiedDict.ContainsKey(table.TableName))
{
lastModified = lastModifiedDict[table.TableName];
}
else
{
lastModified = DateTime.Now - TimeSpan.FromDays(90); // TODO: 90 days is arbitrary
}
int modifiedTime = dateTimeConverter.ToUnixTime(lastModified);
JToken token = webServiceClient.DoSync(modifiedTime.ToString(), table.TableName);
if (token != null)
{
DataTable dataTable = ToDataTable(token["updated"]);
DataTable mergeTable = dataSet.Tables[table.TableName].Clone();
foreach (DataRow row in dataTable.Rows)
{
if (null != BeforeImport)
{
BeforeImport(mergeTable, row);
}
mergeTable.ImportRow(row);
}
dataSet.Merge(mergeTable);
}
}
return filled;
}
private DataSet importExcelFile(string fileName)
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
System.Data.DataSet ds = new System.Data.DataSet();
Workbook wkBook = excelApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
ds.DataSetName = wkBook.Name;
foreach (Worksheet ws in wkBook.Worksheets)
{
ds.Merge(importExcelSheet(ws));
ds.AcceptChanges();
}
System.Windows.Forms.DataGridTableStyle tstyle = new System.Windows.Forms.DataGridTableStyle();
tstyle.AllowSorting = true;
tstyle.RowHeaderWidth = 0;
DG.TableStyles.Add(tstyle);
DG.NavigateBack();
DG.NavigateBack();
DG.DataSource = ds;
excelApp.Quit();
releaseObject(excelApp);
releaseObject(wkBook);
return(ds);
}
private void button1_Click(object sender, EventArgs e)
{
string s = Application.StartupPath;
try
{
XmlTextReader xmlreader1 = new XmlTextReader("D:\\Dropboxdata\\Rms\\RMS\\RMS\\bin\\Debug\\MyXmlFile.xml");
XmlTextReader xmlreader2 = new XmlTextReader("D:\\Dropboxdata\\Rms\\RMS\\RMS\\bin\\Debug\\XML2.xml");
DataSet ds = new DataSet();
ds.ReadXml(xmlreader1);
DataSet ds2 = new DataSet();
ds2.ReadXml(xmlreader2);
ds.Merge(ds2);
// ds2.Merge(ds);
ds.WriteXml("D:\\Dropboxdata\\Rms\\RMS\\RMS\\bin\\Debug\\Books.xml");
MessageBox.Show("Completed merging XML documents");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public DataSet LookupZipCode(string firmName,string address1,string address2,string city,string state) {
//
DataSet response = new DataSet();
try {
DataSet ds = new USPSGateway().LookupZipCode(firmName,address1,address2,city,state);
if (ds != null) response.Merge(ds);
}
catch (Exception ex) { throw new FaultException<USPSFault>(new USPSFault(ex.Message),"Service Error"); }
return response;
}
public DataSet LookupCityState(string zip5) {
//
DataSet response = new DataSet();
try {
DataSet ds = new USPSGateway().LookupCityState(zip5);
if (ds != null) response.Merge(ds);
}
catch(Exception ex) { throw new FaultException<USPSFault>(new USPSFault(ex.Message), "Service Error"); }
return response;
}
public void ThreadProc() {
//The thread procedure performs the data access, and then invokes the callback
//delegate with the filled dataset
Debug.Write("ThreadWithState::ThreadProc()\n");
Debug.Write("Thread= " + Thread.CurrentThread.Name + "\n");
DataSet ds = new DataSet();
ds.Merge(this.m_oViewer.GetReportData(this.m_oDataMgr));
if(this.m_oCallBack != null)
this.m_oCallBack(ds);
}
public override int Fill(DataSet dataSet)
{
if (Configuration.ConfigurationHelper.Instance.TestMode)
{
dataSet.Merge(((MockDriver)Conf.Driver).ds);
return 1;
}
return base.Fill(dataSet);
}
/// <summary>
/// 查询所有数据
/// </summary>
/// <param name="shopID"></param>
/// <param name="year"></param>
/// <param name="statTypeID"></param>
/// <returns></returns>
public static DataSet GetDBAccess(int shopID, int year)
{
List<int> statTypeList = GetAllStatType(shopID);
DataSet ds = new DataSet();
foreach (int item in statTypeList)
{
ds.Merge(GetSpecialData(shopID, year, item, 1), true, MissingSchemaAction.Add);
}
return ds;
}
private void button1_Click(object sender, EventArgs e)
{
robj = RemoteInterface.RemoteBuilder.GetRemoteObj(typeof(RemoteInterface.I_ProcessManager),
RemoteInterface.RemoteBuilder.getRemoteUri(txtIp.Text, (int)RemoteInterface.RemotingPortEnum.ProcessManager, RemoteInterface.RemotingPortEnum.ProcessManager.ToString())) as RemoteInterface.I_ProcessManager;
System.Data.DataSet ds=new DataSet();
ds.Merge(robj.getProcessStatus());
this.dataGridView1.SuspendLayout();
this.dataGridView1.DataSource = ds;
this.dataGridView1.DataMember = "tblProcessInfo";
this.dataGridView1.ResumeLayout();
}
public DataSet GetDBSchemas()
{
DataSet set = new DataSet();
object[] restrictions = new object[4];
restrictions[3] = "TABLE";
DataTable oleDbSchemaTable = this._connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, restrictions);
set.Merge(oleDbSchemaTable);
restrictions = new object[4];
restrictions[3] = "VIEW";
DataTable table = this._connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, restrictions);
set.Merge(table);
foreach (DataRow row in set.Tables[0].Rows)
{
restrictions = new object[3];
restrictions[2] = row["TABLE_NAME"];
DataTable table3 = this._connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions);
set.Merge(table3, true, MissingSchemaAction.Add);
}
set.Relations.Add(this._rel, set.Tables["Tables"].Columns["TABLE_NAME"], set.Tables["Columns"].Columns["TABLE_NAME"]);
return set;
}
/// <summary>
/// 根据DataGridView当前排列顺序和宽度记录在DataTable中
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public System.Data.DataTable GetViewColumns()
{
System.Data.DataSet ds = new System.Data.DataSet();
ds.ReadXml(this.xmlFile);
if (ds.Tables == null || ds.Tables.Count == 0)
{
return(null);
}
DataTable dt = ds.Tables[0];
List <DataRow> drsTemp = new List <DataRow>();
for (int i = 0; i < this.Columns.Count; i++)
{
bool isHave = false;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (dt.Rows[j]["ColumnName"].ToString() == this.Columns[i].Name)
{
dt.Rows[j]["DisplayIndex"] = this.Columns[i].DisplayIndex.ToString("000");
dt.Rows[j]["Width"] = this.Columns[i].Width;
isHave = true;
break;
}
}
if (!isHave)
{
DataRow dr = dt.NewRow();
dr["ColumnName"] = this.Columns[i].Name;
dr["DisplayIndex"] = this.Columns[i].DisplayIndex;
dr["Text"] = this.Columns[i].HeaderText;
dr["DataName"] = this.Columns[i].DataPropertyName;
dr["Visible"] = this.Columns[i].Visible == true ? 1 : 0;
dr["Width"] = this.Columns[i].Width;
dr["AutoSizeMode"] = this.Columns[i].AutoSizeMode;
drsTemp.Add(dr);
}
}
if (drsTemp.Count > 0)
{
foreach (DataRow dr in drsTemp)
{
dt.Rows.Add(dr);
}
}
System.Data.DataRow[] drs = dt.Select("1=1", " DisplayIndex Asc");
ds = new System.Data.DataSet();
ds.Merge(drs);
return(ds.Tables[0]);
}
public DataSet GetListDataSet(string listName)
{
try
{
DataSet dataSet = new DataSet();
SPList list = GetListItems(listName);
SPView view = list.Views["All Items"];
if (view != null && view.ViewFields.ToStringCollection().Contains("Title"))
{
dataSet.Merge(list.GetItems(view).GetDataTable());
}
else
{
dataSet.Merge(list.Items.GetDataTable());
}
return dataSet;
}
catch (Exception ex)
{
throw ex;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet dep = new DataSet();
dep = pxy.GetDepartments();
DataSet dep2 = new DataSet();
dep2 = EGpxy.GetDepartments();
dep.Merge(dep2);
ddlDepartments.DataSource = dep;
ddlDepartments.DataTextField = "departmentName";
ddlDepartments.DataValueField = "departmentNumber";
ddlDepartments.DataBind();
ddlDepartments.Items.Insert(0, "-------Choose-------");
}
}
/*
How to Use:
dtJ = new ConvertDatatableToJson();
JsonString = dtJ.DataTableToJson((DataTable)ViewState["dt"]);
*/
/// <summary>
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string DataTableToJson(DataTable dt)
{
var ds = new DataSet();
ds.Merge(dt);
var jsonStr = new StringBuilder();
if (ds.Tables[0].Rows.Count > 0)
{
jsonStr.Append("[");
for (int i = 0;
i < ds.Tables[0].Rows.Count;
i++)
{
jsonStr.Append("{");
for (int j = 0;
j < ds.Tables[0].Columns.Count;
j++)
{
if (j < ds.Tables[0].Columns.Count - 1)
{
jsonStr.Append
(
"\"" + ds.Tables[0].Columns[j].ColumnName + "\":" + "\"" + ds.Tables[0].Rows[i][j]
+ "\",");
}
else if (j == ds.Tables[0].Columns.Count - 1)
{
jsonStr.Append
(
"\"" + ds.Tables[0].Columns[j].ColumnName + "\":" + "\"" + ds.Tables[0].Rows[i][j]
+ "\"");
}
}
if (i == ds.Tables[0].Rows.Count - 1)
{
jsonStr.Append("}");
}
else
{
jsonStr.Append("},");
}
}
jsonStr.Append("]");
return jsonStr.ToString();
}
return null;
}
private void btnMerge_Click(object sender, EventArgs e)
{
Con = new SqlConnection();
Con.ConnectionString = "Server=(local);database=ADOTest;" +
"Integrated Security=true";
Adpt = new SqlDataAdapter("SELECT * FROM tblPeople", Con);
DbADOTest = new DataSet("ADOTest");
Adpt.Fill(DbADOTest, "tblPeople");
tblPeople2 = MakePeopleTable();
//DbADOTest.Merge(tblPeople2);
//DbADOTest.Merge(tblPeople2, true, MissingSchemaAction.Add);
//DbADOTest.Merge(tblPeople2, false, MissingSchemaAction.Add);
DbADOTest.Merge(tblPeople2, false, MissingSchemaAction.Ignore);
dataGridView1.DataSource = DbADOTest.Tables["tblPeople"];
}
private void ButtonProcessDocument_Click(object sender, RoutedEventArgs e)
{
if (FileNameTextBox.Text == "")
{
return;
}
var path = Path.GetDirectoryName(FileNameTextBox.Text);
var fileName = Path.GetFileNameWithoutExtension(FileNameTextBox.Text);
var newFileName = string.Format("{0}\\{1}_cleaned.xlsx", path, fileName);
var dataSet = new DataSet("ExcelData");
using (var spreadsheetDocument = SpreadsheetDocument.Open(FileNameTextBox.Text, false))
{
var workbookPart = spreadsheetDocument.WorkbookPart;
var allSheets = workbookPart.Workbook.Descendants<Sheet>();
foreach (var sheet in allSheets)
{
var worksheetPart = Processor.GetWorksheetFromSheetName(workbookPart, sheet.Name);
using (var reader = OpenXmlReader.Create(worksheetPart))
{
var processor = ProcessorFactory.GetProcessor(sheet.Name, fileName);
if (processor == null)
{
continue;
}
var excelData = processor.GetDataSetFromExcelTab(reader, workbookPart);
if (excelData != null)
{
dataSet.Merge(excelData, false);
}
}
}
}
if (dataSet.Tables.Count != 0)
{
Processor.CreateExcelDocument(dataSet, newFileName);
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
BindGridView(GridView1);
if (GridView1.Rows.Count > 0)
{
// string reportName = DbHelper.GetInstance().GetWorkflow_ReportMainEntityByReportID(DNTRequest.GetQueryString("ReportID")).ReportName;
// string fileName = reportName + "_ToExcel.xls";
string fileName = "Report_ToExcel.xls";
DataSet ds = new DataSet();
ds.Merge((DataTable)(ViewState["dtValue"]));
DataTable dtColumn = GetColoumns(ds.Tables[0]);
CreateExcel(dtColumn, ds, "1", fileName, "True");
}
System.Web.UI.ScriptManager.RegisterStartupScript(btnExcel, this.GetType(), "ButtonHideScript", strButtonHideScript, false);
}
public string DataTableToJsonObj(DataTable dt)
{
DataSet ds = new DataSet();
ds.Merge(dt);
StringBuilder JsonString = new StringBuilder();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
JsonString.Append("{");
JsonString.Append("\"data\"");
JsonString.Append(":");
JsonString.Append("[");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
JsonString.Append("{");
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
if (j < ds.Tables[0].Columns.Count - 1)
{
JsonString.Append("\"" + ds.Tables[0].Columns[j].ColumnName.ToString() + "\":" + "\"" + ds.Tables[0].Rows[i][j].ToString() + "\",");
}
else if (j == ds.Tables[0].Columns.Count - 1)
{
JsonString.Append("\"" + ds.Tables[0].Columns[j].ColumnName.ToString() + "\":" + "\"" + ds.Tables[0].Rows[i][j].ToString() + "\"");
}
}
if (i == ds.Tables[0].Rows.Count - 1)
{
JsonString.Append("}");
}
else
{
JsonString.Append("},");
}
}
JsonString.Append("]");
JsonString.Append("}");
return JsonString.ToString();
}
else
{
return null;
}
}
public static string ConvertDataTableToXML(DataTable dtBuildSQL)
{
DataSet dsBuildSQL = new DataSet();
StringBuilder sbSQL;
StringWriter swSQL;
string XMLformat;
sbSQL = new StringBuilder();
swSQL = new StringWriter(sbSQL);
dsBuildSQL.Merge(dtBuildSQL, true, MissingSchemaAction.AddWithKey);
dsBuildSQL.Tables[0].TableName = "Table";
foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
{
col.ColumnMapping = MappingType.Element;//.Attribute;
}
dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);
XMLformat = sbSQL.ToString();
return XMLformat;
}
public DataSet GetCountry(string TimeZoneId)
{
try
{
DataSet ds1 = ExecuteDataSet("Select distinct(CountryId) from Location where TimeZoneID=" + TimeZoneId, CommandType.Text, null);
DataSet ds = new DataSet();
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
DataSet dsTemp = new DataSet();
dsTemp = ExecuteDataSet("Select Name from Country where ID=" + ds1.Tables[0].Rows[i][0].ToString(), CommandType.Text, null);
ds.Merge(dsTemp);
}
return ds;
}
catch (Exception ex)
{
VMuktiAPI.VMuktiHelper.ExceptionHandler(ex, "GetCountry(string TimeZoneId)", "ClsImportLocationDataService.cs");
return null;
}
}
public void InsertWithDataSet()
{
var ds = new DataSet();
var da = new NpgsqlDataAdapter("SELECT * FROM data", Conn);
da.InsertCommand = new NpgsqlCommand("INSERT INTO data (field_int2, field_timestamp, field_numeric) VALUES (:a, :b, :c)", Conn);
da.InsertCommand.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("b", DbType.DateTime));
da.InsertCommand.Parameters.Add(new NpgsqlParameter("c", DbType.Decimal));
da.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[2].Direction = ParameterDirection.Input;
da.InsertCommand.Parameters[0].SourceColumn = "field_int2";
da.InsertCommand.Parameters[1].SourceColumn = "field_timestamp";
da.InsertCommand.Parameters[2].SourceColumn = "field_numeric";
da.Fill(ds);
var dt = ds.Tables[0];
var dr = dt.NewRow();
dr["field_int2"] = 4;
dr["field_timestamp"] = new DateTime(2003, 01, 30, 14, 0, 0);
dr["field_numeric"] = 7.3M;
dt.Rows.Add(dr);
var ds2 = ds.GetChanges();
da.Update(ds2);
ds.Merge(ds2);
ds.AcceptChanges();
var dr2 = new NpgsqlCommand("SELECT field_int2, field_numeric, field_timestamp FROM data", Conn).ExecuteReader();
dr2.Read();
Assert.AreEqual(4, dr2[0]);
Assert.AreEqual(7.3000000M, dr2[1]);
dr2.Close();
}
private void btnExportar_Click(object sender, EventArgs e)
{
if (dgvHorasExtras.RowCount > 0)
{
savefileDexcel.Filter = "EXCEL XLS (*.xls)|*.xls";
savefileDexcel.FileName = "";
//if (savefileDexcel.ShowDialog() == DialogResult.OK)
//{
Cursor.Current = Cursors.WaitCursor;
//empleado.Foto = openFileDialogFoto.FileName;
DataSet ds = new DataSet();
ds.Merge((DataTable)this.dgvHorasExtras.DataSource);
Model.DataSetTo.XLS(ds,"HorasExtras.xls");
//exportarAexcel.exportar(ds, savefileDexcel.FileName);
Cursor.Current = Cursors.Default;
//}
}
MessageBox.Show("La exportación finalizó con éxito.");
}
public Ext.Net.TreeNode BindTreePanel(DataTable allData)
{
Ext.Net.TreeNode topNode = new Ext.Net.TreeNode();
topNode.NodeID = "0";
topNode.Text = "Root";
if (allData != null && allData.Rows.Count > 0)
{
DataRow[] items = allData.Select("puserid='0'");
if (items != null && items.Length > 0)
{
DataSet ds = new DataSet();
ds.Merge(items);
DataTable SourseTable = ds.Tables[0];
for (int i = 0; i < SourseTable.Rows.Count; i++)
{
Ext.Net.TreeNode Tvn = new Ext.Net.TreeNode();
Tvn.Expandable = ThreeStateBool.True;
Tvn.NodeID = SourseTable.Rows[i]["userid"].ToString();
Tvn.Text = SourseTable.Rows[i]["username"].ToString();
DataRow[] isChildNode = allData.Select("puserid=" + SourseTable.Rows[i]["userid"].ToString());
if (isChildNode.Length <= 0)
{
Tvn.Leaf = true;
Tvn.Checked = Ext.Net.ThreeStateBool.False;
}
if (isChildNode.Length > 0) Tvn.Icon = Icon.FolderGo;
else Tvn.Icon = Icon.Table;
BindSubTreePanel(Tvn, allData, SourseTable.Rows[i]["userid"].ToString());
topNode.Nodes.Add(Tvn);
}
}
}
return topNode;
}
protected void BtnExportLog_Click(object sender, EventArgs e)
{
//获取查询数据
int status = -1; //状态
string licenseno = ""; //需要查询的车牌号
string starttime = txtStarttime.Text.ToString();
string endtime = txtEndtime.Text.ToString();
int searchtype = 0; //查询类型
DateTime startdt;
DateTime enddt;
DateTime.TryParse(starttime, out startdt);
if (DateTime.TryParse(endtime, out enddt))
enddt = enddt.AddDays(1);
/* 选择查询的情况（
* 0：licenseno/starttime/endtime are null，
* 1：licenseno is not null, starttime and endtime are null,
* 2：licenseno and starttime are not null, endtime is null,
* 3：licenseno and endtime are not null, starttime is null,
* 4：licenseno/starttime/endtime are not null,
* 5：starttime is not null, licenseno and endtime are null,
* 6：endtime is not null, licenseno and starttime are null,
* 7：starttime and endtime are not null, licenseno is null
* 。）
*
*/
if (licenseno == "" && starttime == "" && endtime == "") searchtype = 0;
else if (licenseno != "" && starttime == "" && endtime == "") searchtype = 1;
else if (licenseno != "" && starttime != "" && endtime == "") searchtype = 2;
else if (licenseno != "" && starttime == "" && endtime != "") searchtype = 3;
else if (licenseno != "" && starttime != "" && endtime != "") searchtype = 4;
else if (licenseno == "" && starttime != "" && endtime == "") searchtype = 5;
else if (licenseno == "" && starttime == "" && endtime != "") searchtype = 6;
else if (licenseno == "" && starttime != "" && endtime != "") searchtype = 7;
DataSet ds = new DataSet();
ds = operation.SelectBillbyInserttime(status, licenseno, startdt, enddt, searchtype);
DataSet dslog = new DataSet();
//遍历DateSet，并获取每条数据的日志列表
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataSet temp_ds = operation.SelectLog(ds.Tables[0].Rows[i]["pk_id"].ToString());
temp_ds.Tables[0].Columns.Add("订单号", typeof(System.String));
temp_ds.Tables[0].Columns.Add("动作", typeof(System.String));
for (int j = 0; j < temp_ds.Tables[0].Rows.Count; j++)
{
temp_ds.Tables[0].Rows[j]["订单号"] = ds.Tables[0].Rows[i]["billno"].ToString();
if (temp_ds.Tables[0].Rows[j]["status"].ToString() == "0" ) temp_ds.Tables[0].Rows[j]["动作"] = "导入系统";
else if (temp_ds.Tables[0].Rows[j]["status"].ToString() == "1") temp_ds.Tables[0].Rows[j]["动作"] = "被认领";
else if (temp_ds.Tables[0].Rows[j]["status"].ToString() == "2") temp_ds.Tables[0].Rows[j]["动作"] = "下单成功";
else if (temp_ds.Tables[0].Rows[j]["status"].ToString() == "3") temp_ds.Tables[0].Rows[j]["动作"] = "下单失败";
else if (temp_ds.Tables[0].Rows[j]["status"].ToString() == "4") temp_ds.Tables[0].Rows[j]["动作"] = "改期";
else if (temp_ds.Tables[0].Rows[j]["status"].ToString() == "5") temp_ds.Tables[0].Rows[j]["动作"] = "催单";
}
dslog.Merge(temp_ds);
}
dslog.Tables[0].Columns.Remove("status");
dslog.Tables[0].Columns.Remove("inserttimeforhis");
dslog.Tables[0].Columns.Remove("operatetimeforhis");
dslog.Tables[0].Columns[0].ColumnName = "日志编号";
dslog.Tables[0].Columns[1].ColumnName = "订单编号";
dslog.Tables[0].Columns[2].ColumnName = "操作人";
dslog.Tables[0].Columns[3].ColumnName = "操作日期";
DateTime dt = DateTime.Now;
string dts = dt.Year.ToString() + dt.Month.ToString() + dt.Day.ToString() + dt.Hour.ToString() + dt.Minute.ToString() + dt.Second.ToString() + ".xls";
ExportResult(dslog, dts);
}
public static DataSet SearchJobs(string skills,int countryid,int stateid,string city)
{
string[] arr = skills.Split(' ');
DataSet ds = new DataSet();
DataSet dsTemp = new DataSet();
bool flag = false;
DBAccess db = new DBAccess();
foreach (string s in arr)
{
db.AddParameter("@sSkill", s);
if (countryid == -1)
{
db.AddParameter("@iCountryID", DBNull.Value);
}
else
{
db.AddParameter("@iCountryID", countryid);
}
if (stateid == -1)
{
db.AddParameter("@iStateID", DBNull.Value);
}
else
{
db.AddParameter("@iStateID", stateid);
}
if (city=="")
{
db.AddParameter("@sCity", DBNull.Value);
}
else
{
db.AddParameter("@sCity", city);
}
dsTemp = db.ExecuteDataSet("JobsDb_JobPostings_SelectForMatchingSkills");
db.Parameters.Clear();
ds.Merge(dsTemp);
if (flag == false)
{
DataColumn[] pk = new DataColumn[1];
pk[0] = ds.Tables[0].Columns["postingid"];
ds.Tables[0].PrimaryKey = pk;
flag = true;
}
}
return ds;
}
/// <summary>
/// Indexes a MyLo datastore using PostgreSQL function 'SetupIndexCursorsIntervalAndTimePoints'
/// </summary>
public int ExecuteIndexerOnDataStore()
{
try
{
DataSet indexDS = new DataSet();
NpgsqlTransaction t = _conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand("SetupIndexCursorsOrdered", _conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new NpgsqlParameter());
command.Parameters[0].DbType = DbType.Int64;
command.Parameters[0].Value = _userId;
NpgsqlCommand updateCommandforPhotos = new NpgsqlCommand("UpdatePhotoActivityId", _conn);
updateCommandforPhotos.CommandType = CommandType.StoredProcedure;
updateCommandforPhotos.Parameters.Add(new NpgsqlParameter());
updateCommandforPhotos.Parameters.Add(new NpgsqlParameter());
updateCommandforPhotos.Parameters.Add(new NpgsqlParameter());
updateCommandforPhotos.Parameters[0].DbType = DbType.Int64;
updateCommandforPhotos.Parameters[1].DbType = DbType.Guid;
updateCommandforPhotos.Parameters[2].DbType = DbType.Int64;
updateCommandforPhotos.Parameters[0].Value = _userId;
updateCommandforPhotos.Parameters[1].SourceColumn = "uniqueid";
updateCommandforPhotos.Parameters[2].SourceColumn = "activityid";
NpgsqlDataAdapter postgresqlAdapterForPhotos = new NpgsqlDataAdapter(command);
postgresqlAdapterForPhotos.UpdateCommand = updateCommandforPhotos;
NpgsqlCommand commandForActivities = new NpgsqlCommand("SetupIndexCursorsIntervalAndTimePoints", _conn);
commandForActivities.CommandType = CommandType.StoredProcedure;
commandForActivities.Parameters.Add(new NpgsqlParameter());
commandForActivities.Parameters.Add(new NpgsqlParameter());
commandForActivities.Parameters.Add(new NpgsqlParameter());
commandForActivities.Parameters[0].DbType = DbType.Int64;
commandForActivities.Parameters[1].DbType = DbType.DateTime;
commandForActivities.Parameters[2].DbType = DbType.Int32;
commandForActivities.Parameters[0].Value = _userId;
NpgsqlDataAdapter postgresqlAdapterForActivities = new NpgsqlDataAdapter(commandForActivities);
postgresqlAdapterForPhotos.Fill(indexDS);
DataTable photos = indexDS.Tables[0];
_countIndexed = 0;
foreach (DataRow photo in photos.Rows)
{
DataSet activitiesDS = new DataSet();
// TODO - remove the Fill when datetaken time is still within the last retrieved activity interval
commandForActivities.Parameters[1].Value = photo["datetaken"];
// TODO - make this hours radius a variable set in UI
commandForActivities.Parameters[2].Value = 4;
postgresqlAdapterForActivities.Fill(activitiesDS);
DataTable activities = activitiesDS.Tables[0];
// TODO - the function returns the data sorted by duration, but this order is not preserved by ADO.Net!! Need to investigate
// and avoid the use of the ADO.Net sorted view.
DataView activitiesView = activities.DefaultView;
activitiesView.Sort = "duration ASC";
DataTable activitiesSorted = activitiesView.ToTable();
//foreach (DataRow activity in activities.Rows)
foreach (DataRow activity in activitiesSorted.Rows)
{
if ((Double)photo["gpsLat"] != 0.0)
{
if (IsSameLocation(photo, activity, 2.0))
{
photo["activityid"] = activity["activityid"];
Debug.WriteLine("Indexed Photoid Location and Time: {0} to ActivityId: {1}", photo["uniqueid"], activity["activityid"]);
Debug.WriteLine("Photo time: {0} Activity time: {1} ", photo["datetaken"], activity["startdatetime"]);
Debug.WriteLine("Photo loc: {0}, {1} Activity loc: {2}, {3} ", photo["gpslat"], photo["gpslong"], activity["latitude"], activity["longitude"]);
Debug.WriteLine("");
_countIndexed++;
break;
}
}
else
{
photo["activityid"] = activity["activityid"];
_countIndexed++;
Debug.WriteLine("Indexed Photoid Time only: {0} to ActivityId: {1}", photo["uniqueid"], activity["activityid"]);
Debug.WriteLine("Photo time: {0} Activity time: {1} ", photo["datetaken"], activity["startdatetime"]);
Debug.WriteLine("");
break;
}
}
}
t.Commit();
// now write changes back to the database
DataSet changeDS = indexDS.GetChanges(DataRowState.Modified);
if (changeDS != null)
{
postgresqlAdapterForPhotos.Update(changeDS);
indexDS.Merge(changeDS);
indexDS.AcceptChanges();
}
_conn.Close();
return _countIndexed;
}
catch (System.Data.DBConcurrencyException daex)
{
_conn.Close();
Debug.WriteLine("Exception Data {0}", daex.Data);
throw new MyLoDataStoreException(daex.Message, daex);
}
catch (NpgsqlException npex)
{
_conn.Close();
throw new MyLoDataStoreException(npex.Message, npex);
}
catch (Exception ex)
{
_conn.Close();
throw new MyLoDataStoreException(ex.Message);
}
}
public void DifferentDatabase()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");
conn.ChangeDatabase(database1);
MySqlDataAdapter da = new MySqlDataAdapter(
String.Format("SELECT id, name FROM `{0}`.Test", database0), conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].Rows[0]["id"] = 4;
DataSet changes = ds.GetChanges();
da.Update(changes);
ds.Merge(changes);
ds.AcceptChanges();
cb.Dispose();
conn.ChangeDatabase(database0);
}
public DataSet SelectNFCE(DateTime d1, DateTime d2)
{
try
{
DataSet ds = new DataSet();
DataTable dt = null;
dt = DB.SendSelectMultiResultQueries(
String.Format("select id, numero, serie, check_id, ws_id, chave, isnull(recibo,'') recibo , data from nfce_dados where convert(char(20),data,'105') between '{0}' and '{1}' order by 8 desc"
, d1.ToString("dd-MM-yyyy")
, d2.ToString("dd-MM-yyy")
), "dados");
ds.Merge(dt);
dt = DB.SendSelectMultiResultQueries(String.Format("select id_dados, nfce_status, nfce_status_desc, nfce_data, isnull(nfce_info,'') nfce_info from nfce_status where convert(char(20),nfce_data,'105') between '{0}' and '{1}' order by 1,4,2 desc"
, d1.ToString("dd-MM-yyyy")
, d2.ToString("dd-MM-yyy")
), "status");
ds.Merge(dt);
return ds;
}
catch (Exception ex)
{
throw ex;
}
}
public static string ConvertDataTableToXML(DataTable dtData)
{
DataSet dsData = new DataSet();
StringBuilder sbOleDb;
StringWriter swOleDb;
string XMLformat;
try
{
sbOleDb = new StringBuilder();
swOleDb = new StringWriter(sbOleDb);
dsData.Merge(dtData, true, MissingSchemaAction.AddWithKey);
dsData.Tables[0].TableName = "SampleDataTable";
foreach (DataColumn col in dsData.Tables[0].Columns)
{
col.ColumnMapping = MappingType.Attribute;
}
dsData.WriteXml(swOleDb, XmlWriteMode.WriteSchema);
XMLformat = sbOleDb.ToString();
return XMLformat;
}
catch (Exception sysException)
{
throw sysException;
}
}
///<summary>
///<para>Gắn Header vào Entity để truyền lên BusinessLogic xử lý</para>
///</summary>
/// <param name="Entity"><para>Entity chứa thông tin nghiệp vụ</para></param>
/// <param name="Header"><para>Header chứa gói tin lưu vết và loại nghiệp vụ cần thao tác</para></param>
///<returns></returns>
public static void MergeHeader(ref DataSet Entity, DataTable Header)
{
try
{
if (Entity.Equals(null))
{
Utility.ShowMsg("Bạn phải khởi tạo Entity trước khi thực hiện hàm này");
return;
}
if (Header.Equals(null))
{
Utility.ShowMsg("Bạn phải khởi tạo Header trước khi thực hiện hàm này");
return;
}
if (Entity.Tables.Contains(Header.TableName))
{
Entity.Tables.Remove(Header.TableName);
Entity.AcceptChanges();
Entity.Merge(Header);
Entity.AcceptChanges();
}
else
{
Entity.Merge(Header);
Entity.AcceptChanges();
}
}
catch (Exception ex)
{
ShowMsg("Lỗi khi gắn Header vào Entity:\n" + ex.Message);
}
}
public DataSet GetPatientsList(string DoctorId, string ModuleType, int Plan, int Compliance, int Goal)
{
int patientTotalCount = 0; //某个模块下的患者总数
int planCount = 0; //已有计划的患者数
int complianceCount = 0; //依从的患者数
int goalCount = 0; //达标的患者数
double planRate = 0;
double complianceRateTotal = 0;
double goalRate = 0;
DataTable DT_Rates = new DataTable();
DT_Rates.TableName = "RateTable";
DT_Rates.Columns.Add(new DataColumn("PlanRate", typeof(double)));
DT_Rates.Columns.Add(new DataColumn("ComplianceRate", typeof(double)));
DT_Rates.Columns.Add(new DataColumn("GoalRate", typeof(double)));
DataTable DT_PatientList = new DataTable();
DT_PatientList.TableName = "PatientListTable";
DT_PatientList.Columns.Add(new DataColumn("PatientId", typeof(string)));
DT_PatientList.Columns.Add(new DataColumn("PatientName", typeof(string)));
DT_PatientList.Columns.Add(new DataColumn("photoAddress", typeof(string)));
DT_PatientList.Columns.Add(new DataColumn("PlanNo", typeof(string)));
DT_PatientList.Columns.Add(new DataColumn("StartDate", typeof(string)));
DT_PatientList.Columns.Add(new DataColumn("Process", typeof(double)));
DT_PatientList.Columns.Add(new DataColumn("RemainingDays", typeof(string)));
DT_PatientList.Columns.Add(new DataColumn("VitalSign", typeof(List<string>)));
DT_PatientList.Columns.Add(new DataColumn("ComplianceRate", typeof(double)));
DT_PatientList.Columns.Add(new DataColumn("TotalDays", typeof(string)));
DT_PatientList.Columns.Add(new DataColumn("Status", typeof(string)));
DataTable DT_Patients = new DataTable();
DT_Patients.Columns.Add(new DataColumn("PatientId", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("PlanNo", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("StartDate", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("EndDate", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("TotalDays", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("RemainingDays", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("Status", typeof(string)));
DataSet DS_Patients = new DataSet();
try
{
int nowDate = GetServerDate();
DT_Patients = PsPlan.GetPatientsPlanByDoctorId(_cnCache, DoctorId, ModuleType);
if (DT_Patients != null)
patientTotalCount = DT_Patients.Rows.Count;
else
return DS_Patients;
foreach (DataRow item in DT_Patients.Rows)
{
string patientId = item["PatientId"].ToString();
string planNo = item["PlanNo"].ToString();
if (planNo != "")
{
planCount++;
}
//HavePlan 0 1 2
if ((Plan == 1 && planNo == "") || (Plan == 2 && planNo != ""))
{
continue;
}
string startDate = item["StartDate"].ToString();
string totalDays = item["TotalDays"].ToString();
string remainingDays = item["RemainingDays"].ToString();
string status = item["Status"].ToString();
double process = 0.0;
double complianceRate = 0.0;
//VitalSign
List<string> vitalsigns = new List<string>();
if (planNo != "")
{
complianceRate = PsCompliance.GetComplianceByDay(_cnCache, patientId, nowDate, planNo);
if (complianceRate > 0)
{
complianceCount++;
}
if (complianceRate < 0)
{
complianceRate = 0;
}
//Compliance
if (Compliance == 1 && complianceRate <= 0)
{
continue;
}
if (Compliance == 2 && complianceRate > 0)
{
continue;
}
//Vitalsign
string itemType = "Bloodpressure";
string itemCode = "Bloodpressure_1";
int recordDate = nowDate; //nowDate
//recordDate = 20150422;
bool goalFlag = false;
CacheSysList list = PsVitalSigns.GetSignByDay(_cnCache, patientId, itemType, itemCode, recordDate);
if (list != null)
{
vitalsigns.Add(list[2]);
}
else
{
// vitalsigns.Add("115");
//ZAM 2015-6-17
vitalsigns.Add("");
}
CacheSysList targetlist = PsTarget.GetTargetByCode(_cnCache, planNo, itemType, itemCode);
if (targetlist != null)
{
vitalsigns.Add(targetlist[4]); //index 4 for Origin value
vitalsigns.Add(targetlist[3]); //index 3 for target value
}
else
{
//vitalsigns.Add("200");
//vitalsigns.Add("120");
//ZAM 2015-6-17
vitalsigns.Add("");
vitalsigns.Add("");
}
//非法数据判断 zam 2015-5-18
if (list != null && targetlist != null)
{
double m, n;
bool misNumeric = double.TryParse(list[2].ToString(), out m);
bool nisNumeric = double.TryParse(targetlist[3].ToString(), out n);
if (misNumeric && nisNumeric)
{
//if (Convert.ToInt32(list[2]) <= Convert.ToInt32(targetlist[3])) //已达标
if (m <= n)
{
goalCount++;
goalFlag = true;
}
}
}
//Goal
if (Goal == 1 && goalFlag == false)
{
continue;
}
if (Goal == 2 && goalFlag == true)
{
continue;
}
//非法数据判断 zam 2015-5-18
if (startDate != "" && totalDays != "" && remainingDays != "")
{
double m, n;
bool misNumeric = double.TryParse(totalDays, out m);
bool nisNumeric = double.TryParse(remainingDays, out n);
if (misNumeric && nisNumeric)
{
//process = (Convert.ToDouble(totalDays) - Convert.ToDouble(remainingDays)) / Convert.ToDouble(totalDays);
process = m != 0.0 ? (m - n) / m : 0;
}
}
}
//PhotoAddress
string photoAddress = "";
CacheSysList patientInfolist = PsBasicInfoDetail.GetPatientDetailInfo(_cnCache, patientId);
if (patientInfolist != null || patientInfolist.Count != 0)
{
photoAddress = patientInfolist[7];
}
string patientName = "";
patientName = CmMstUser.GetNameByUserId(_cnCache, patientId);
DT_PatientList.Rows.Add(patientId, patientName, photoAddress, planNo, startDate, process, remainingDays, vitalsigns, complianceRate, totalDays, status);
}
DS_Patients.Merge(DT_PatientList);
//The main rates for Plan, Compliance , Goal
planRate = patientTotalCount != 0 ? (double)planCount / patientTotalCount : 0;
complianceRateTotal = planCount != 0 ? (double)complianceCount / planCount : 0;
goalRate = planCount != 0 ? (double)goalCount / planCount : 0;
DT_Rates.Rows.Add(planRate, complianceRateTotal, goalRate);
DS_Patients.Merge(DT_Rates);
return DS_Patients;
}
catch (Exception ex)
{
HygeiaComUtility.WriteClientLog(HygeiaEnum.LogType.ErrorLog, "GetPatientsByDoctorId", "WebService调用异常！ error information : " + ex.Message + Environment.NewLine + ex.StackTrace);
return null;
throw (ex);
}
}
public DataSet GetPatientsByDoctorId(string DoctorId, string VisitId)
{
DataTable DT_Patients = new DataTable();
DT_Patients.Columns.Add(new DataColumn("PatientId", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("PatientName", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("Gender", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("Age", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("Diagnosis", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("Module", typeof(string)));
DT_Patients.Columns.Add(new DataColumn("AlertNumber", typeof(int)));
DT_Patients.Columns.Add(new DataColumn("CareLevel", typeof(int)));
DT_Patients.Columns.Add(new DataColumn("ModuleType", typeof(string)));
DataSet DS_Patients = new DataSet();
DataTable DT_PatientList = new DataTable();
string CategoryCode = string.Empty;
int GenderType = 0;
int AlertStatus = 0;
int CareLevel = 0;
try
{
//GetDoctorModuleList: CategoryCode
DataTable DT_DoctorModule = PsDoctorInfo.GetDoctorModuleList(_cnCache, DoctorId);
//DataCheck
if (DT_DoctorModule == null)
{
return DS_Patients;
}
foreach (DataRow dr_DoctorModule in DT_DoctorModule.Rows)
{
CategoryCode = string.Empty;
CategoryCode = dr_DoctorModule["CategoryCode"].ToString();
DT_PatientList = (PsDoctorInfoDetail.GetPatientsByDoctorId(_cnCache, DoctorId, CategoryCode));
string genderName = "";
//DataCheck
if (DT_PatientList == null)
{
return DS_Patients;
}
foreach (DataRow dr_patient in DT_PatientList.Rows)
{
DT_Patients.Clear();
DT_Patients.TableName = CategoryCode;
GetPatientsInfo(ref DT_Patients, dr_patient, CategoryCode, GenderType, genderName, AlertStatus, CareLevel, VisitId);
DS_Patients.Merge(DT_Patients);
}
}
return DS_Patients;
}
catch (Exception ex)
{
HygeiaComUtility.WriteClientLog(HygeiaEnum.LogType.ErrorLog, "GetPatientsByDoctorId", "WebService调用异常！ error information : " + ex.Message + Environment.NewLine + ex.StackTrace);
return null;
throw (ex);
}
}
public void RefreshView(bool initialize)
{
try
{
if (currentConnection == null)
{
return;
}
if (!currentConnection.Connected)
{
return;
}
RefreshTimer.Enabled = false;
String s = currentConnection.Ps();
DataSet ds = JobRuntimeDS.Clone();
ds.Clear();
ds.ReadXml(new StringReader(s), XmlReadMode.Auto);
JobGrid.SuspendLayout();
if (initialize)
{
JobRuntimeDS.Clear();
}
JobRuntimeDS.Merge(ds.Tables["JobRuntimeType"]);
ServerConfigRefreshRate r = UIConfigFileHandler.Instance().Config.RefreshRate;
switch (r)
{
case ServerConfigRefreshRate.High:
RefreshTimer.Interval = 1000;
RefreshTimer.Enabled = true;
break;
case ServerConfigRefreshRate.Low:
RefreshTimer.Interval = 30000;
RefreshTimer.Enabled = true;
break;
case ServerConfigRefreshRate.Normal:
RefreshTimer.Interval = 10000;
RefreshTimer.Enabled = true;
break;
case ServerConfigRefreshRate.Paused:
RefreshTimer.Enabled = false;
break;
}
}
catch (Exception ex)
{
currentConnection.Disconnect();
MessageBox.Show(ex.Message);
}
finally
{
JobGrid.ResumeLayout(true);
}
}