public static void insert_pairs(string pair, string display_name, string pip, string max_unit)
{
string sql = "delete from oanda_pairs where pair='{0}'";
sql = string.Format(sql, pair);
SQLServerHelper.exe_sql(sql);
sql = "insert into oanda_pairs (pair,display_name,pip,max_unit) values ('{0}','{1}',{2},{3})";
sql = string.Format(sql, pair, display_name, pip, max_unit);
SQLServerHelper.exe_sql(sql);
}
public static void info(string description, string detail)
{
string time = DateTime.Now.ToString();
string type = "info";
string sql = "insert into log (time,type,description,detail) values('{0}','{1}','{2}','{3}')";
description = SQLServerHelper.format_sql_str(description);
detail = SQLServerHelper.format_sql_str(detail);
sql = string.Format(sql, time, type, description, detail);
SQLServerHelper.exe_sql(sql);
}
private void button3_Click(object sender, EventArgs e)
{
var config = loadConfig();
if (MessageBox.Show(this, $"将为当前指定的数据库服务器{config.DBServer}上移除备份加密密钥和证书，移除后，备份文件可以在任意服务器上还原，这可能导致数据泄露，是否继续？", "数据库配置", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.No)
{
return;
}
showResult(SQLServerHelper.Exec(string.Format(Resources.DropAutoBakCert, Path.Combine(Application.StartupPath, "sql")), config.DBServer),
(sender as Button).Text);
}
public DataTable getSumData(DateTime startTime, DateTime endTime)
{
string strTime = string.Format(" where t_xf_time>='{0}' and t_xf_time<='{1}'", startTime, endTime);
string strSql = string.Format(
" select SUM(srzm.f_sr_money) as f_sr_money" +
" from jt_sr_zm as srzm " +
strTime);
DataTable dataTable = SQLServerHelper.GetTable(strSql);
return(dataTable);
}
public DataSet GetData(string TJBH, string TJCS)
{
StringBuilder builder = new StringBuilder();
builder.Append(" SELECT A.TJXM,A.JG FROM view_tj_tjjlmxb_jk A INNER JOIN view_tj_tjjlb_jk B ");
builder.Append(" ON A.XH=B.XH ");
builder.Append(" WHERE B.TJBH=" + TJBH);
builder.Append(" AND B.TJCS=" + TJCS);
return(SQLServerHelper.Query(builder.ToString()));
}
/// <summary>
/// update the BookCount
/// </summary>
/// <param name="BarCode">BarCode</param>
/// <param name="bookcount"></param>
/// <returns></returns>
public int AddBookCount(string BarCode, int bookcount)
{
string sql = "update Books set BookCount = BookCount +@BookCount where BarCode = @BarCode";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@BarCode", BarCode),
new SqlParameter("@BookCount", bookcount)
};
return(SQLServerHelper.Update(sql, param));
}
public string GetAllMemberIDsByLeaderID(string leaderID, bool resQuery = false)
{
string deptID = "", sql = "";
deptIDs = "";
memberIDs = leaderID;
sql = "Select FID from t_Departments Where FIsDeleted =0 and FSupervisorID='" + leaderID + "'";
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
foreach (DataRow row in dt.Rows)
{
deptID = row["FID"].ToString();
if (resQuery)
{
GetResSubsID(deptID);
}
else
{
GetAllSbuDeptsByDeptID(deptID);
}
}
if (deptIDs.Length > 0)
{
deptIDs = deptIDs.Replace("|", "','");
sql = "Select FID from t_Employees Where FIsDeleted =0 and FDeptID in ('{0}') or FLeaderList like '%{1}%'";
sql = string.Format(sql, deptIDs, leaderID);
}
else
{
sql = "Select FID from t_Employees Where FIsDeleted =0 and FLeaderList like '%{0}%'";
sql = string.Format(sql, leaderID);
}
dt = runner.ExecuteSql(sql);
foreach (DataRow row in dt.Rows)
{
if (memberIDs.Length == 0)
{
memberIDs = row["FID"].ToString();
}
else
{
memberIDs = memberIDs + "|" + row["FID"].ToString();
}
}
return(memberIDs);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select pk,v_jrzm_no,v_jrzm_name,t_jr_time,v_jr_who,f_jr_money,f_accrual,v_jr_name,t_gh_time,i_gh_flag,f_gh_how_money,v_remark,v_jz_user_pk,v_jz_user_name,t_create_time,i_delete ");
strSql.Append(" FROM jt_jr_zm ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLServerHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select pk,v_zffs_no,v_zffs_name,t_create_time,i_delete,i_zffs_lx ");
strSql.Append(" FROM jt_zffs ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLServerHelper.Query(strSql.ToString()));
}
public static string get_all_pairs()
{
string sql = "select distinct pair from oanda_pairs";
DataTable dt = SQLServerHelper.get_table(sql);
string[] list = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
list[i] = dt.Rows[i]["pair"].ToString();
}
return(String.Join("%2C", list));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select pk,v_key,v_value,v_mac_address,t_create_time,i_delete ");
strSql.Append(" FROM jt_jc_sz ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLServerHelper.Query(strSql.ToString()));
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(HomeAccountingSystem.Model.jt_ys_zm model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update jt_ys_zm set ");
strSql.Append("v_yszm_no=@v_yszm_no,");
strSql.Append("v_yszm_name=@v_yszm_name,");
strSql.Append("t_date_start=@t_date_start,");
strSql.Append("t_date_end=@t_date_end,");
strSql.Append("f_ys_money=@f_ys_money,");
strSql.Append("v_remark=@v_remark,");
strSql.Append("t_create_time=@t_create_time,");
strSql.Append("i_delete=@i_delete,");
strSql.Append("v_jz_user_pk=@v_jz_user_pk,");
strSql.Append("v_jz_user_name=@v_jz_user_name");
strSql.Append(" where pk=@pk ");
SqlParameter[] parameters =
{
new SqlParameter("@v_yszm_no", SqlDbType.VarChar, 256),
new SqlParameter("@v_yszm_name", SqlDbType.VarChar, 256),
new SqlParameter("@t_date_start", SqlDbType.DateTime),
new SqlParameter("@t_date_end", SqlDbType.DateTime),
new SqlParameter("@f_ys_money", SqlDbType.Float, 8),
new SqlParameter("@v_remark", SqlDbType.VarChar, 256),
new SqlParameter("@t_create_time", SqlDbType.DateTime),
new SqlParameter("@i_delete", SqlDbType.Int, 4),
new SqlParameter("@v_jz_user_pk", SqlDbType.VarChar, 256),
new SqlParameter("@v_jz_user_name", SqlDbType.VarChar, 256),
new SqlParameter("@pk", SqlDbType.Int, 4)
};
parameters[0].Value = model.v_yszm_no;
parameters[1].Value = model.v_yszm_name;
parameters[2].Value = model.t_date_start;
parameters[3].Value = model.t_date_end;
parameters[4].Value = model.f_ys_money;
parameters[5].Value = model.v_remark;
parameters[6].Value = model.t_create_time;
parameters[7].Value = model.i_delete;
parameters[8].Value = model.v_jz_user_pk;
parameters[9].Value = model.v_jz_user_name;
parameters[10].Value = model.pk;
int rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select pk,v_yszm_no,v_yszm_name,t_date_start,t_date_end,f_ys_money,v_remark,t_create_time,i_delete,v_jz_user_pk,v_jz_user_name ");
strSql.Append(" FROM jt_ys_zm ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLServerHelper.Query(strSql.ToString()));
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select pk,v_yh_no,v_yh_name,v_yh_pwd,i_yh_type,v_phone,t_birthday_gregorian,t_birthday_lunar,t_create_time,i_delete,v_photo,v_age,v_photo_path ");
strSql.Append(" FROM jt_yh_zl ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLServerHelper.Query(strSql.ToString()));
}
/// <summary>
/// 得到一个代码集表实体
/// </summary>
/// <param name="code">代码编码</param>
/// <returns>代码集表实体</returns>
public CodeSetsEntity GetModel(string code)
{
SqlParameter[] parameters =
{
new SqlParameter("@code", SqlDbType.VarChar, 50)
};
parameters[0].Value = code;
DataSet ds = SQLServerHelper.RunProcedure("uspWip_GetSingleCodeSets", parameters, "pagetable");
CodeSetsEntity model = new CodeSetsEntity();
if (ds.Tables[0].Rows.Count > 0)
{
var dataRow = ds.Tables[0].Rows[0];
if (dataRow["id"].ToString() != "")
{
model.id = int.Parse(dataRow["id"].ToString());
}
model.code = dataRow["code"].ToString();
model.name = dataRow["name"].ToString();
model.note = dataRow["note"].ToString();
if (dataRow["delFlag"].ToString() != "")
{
if ((dataRow["delFlag"].ToString() == "1") || (dataRow["delFlag"].ToString().ToLower() == "true"))
{
model.delFlag = true;
}
else
{
model.delFlag = false;
}
}
model.creator = dataRow["creator"].ToString();
if (dataRow["createTime"].ToString() != "")
{
model.createTime = DateTime.Parse(dataRow["createTime"].ToString());
}
model.lastModifier = dataRow["lastModifier"].ToString();
if (dataRow["lastModifyTime"].ToString() != "")
{
model.lastModifyTime = DateTime.Parse(dataRow["lastModifyTime"].ToString());
}
return(model);
}
else
{
return(null);
}
}
private void btn_save_code_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.txt_code.Text))
{
MessageBox.Show("No code to save!!!");
return;
}
string sql = "insert into code (date,tag,code) values('{0}','{1}','{2}')";
sql = string.Format(sql, DateTime.Now.ToString(), this.txt_tag.Text, this.txt_code.Text);
SQLServerHelper.exe_sql(sql);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select pk,v_zczm_no,v_zczm_name,t_xf_time,f_zc_money,v_zclx_no,v_zclx_name,v_remark,v_jz_user_pk,v_jz_user_name,t_create_time,i_delete,v_zffs_no,v_zffs_name,v_who ");
strSql.Append(" FROM jt_zc_zm ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(SQLServerHelper.Query(strSql.ToString()));
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(HomeAccountingSystem.Model.jt_zc_zm model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into jt_zc_zm(");
strSql.Append("v_zczm_no,v_zczm_name,t_xf_time,f_zc_money,v_zclx_no,v_zclx_name,v_remark,v_jz_user_pk,v_jz_user_name,t_create_time,i_delete,v_zffs_no,v_zffs_name,v_who)");
strSql.Append(" values (");
strSql.Append("@v_zczm_no,@v_zczm_name,@t_xf_time,@f_zc_money,@v_zclx_no,@v_zclx_name,@v_remark,@v_jz_user_pk,@v_jz_user_name,@t_create_time,@i_delete,@v_zffs_no,@v_zffs_name,@_v_who)");
SqlParameter[] parameters =
{
new SqlParameter("@pk", SqlDbType.Int, 4),
new SqlParameter("@v_zczm_no", SqlDbType.VarChar, 256),
new SqlParameter("@v_zczm_name", SqlDbType.VarChar, 256),
new SqlParameter("@t_xf_time", SqlDbType.DateTime),
new SqlParameter("@f_zc_money", SqlDbType.Float, 8),
new SqlParameter("@v_zclx_no", SqlDbType.VarChar, 256),
new SqlParameter("@v_zclx_name", SqlDbType.VarChar, 256),
new SqlParameter("@v_remark", SqlDbType.VarChar, 256),
new SqlParameter("@v_jz_user_pk", SqlDbType.VarChar, 4000),
new SqlParameter("@v_jz_user_name", SqlDbType.VarChar, 256),
new SqlParameter("@t_create_time", SqlDbType.DateTime),
new SqlParameter("@i_delete", SqlDbType.Int, 4),
new SqlParameter("@v_zffs_no", SqlDbType.VarChar, 256),
new SqlParameter("@v_zffs_name", SqlDbType.VarChar, 256),
new SqlParameter("@_v_who", SqlDbType.VarChar, 256)
};
parameters[0].Value = model.pk;
parameters[1].Value = model.v_zczm_no;
parameters[2].Value = model.v_zczm_name;
parameters[3].Value = model.t_xf_time;
parameters[4].Value = model.f_zc_money;
parameters[5].Value = model.v_zclx_no;
parameters[6].Value = model.v_zclx_name;
parameters[7].Value = model.v_remark;
parameters[8].Value = model.v_jz_user_pk;
parameters[9].Value = model.v_jz_user_name;
parameters[10].Value = model.t_create_time;
parameters[11].Value = model.i_delete;
parameters[12].Value = model.v_zffs_no;
parameters[13].Value = model.v_zffs_name;
parameters[14].Value = model.v_who;
int rows = SQLServerHelper.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public string get_new_data()
{
string sql = "";
double rate = CurrencyHelper.get_rate("usd", "cny");
BsonArray data = new BsonArray();
string max_id = get_max_id();
sql = "select * from ticker_log where id >{0} and timespan>'{1}' and currency like '%btc%'";
sql = string.Format(sql, max_id, UnixTime.get_unix_time_from_local_long(DateTime.Now.AddSeconds(-10)));
DataTable dt = SQLServerHelper.get_table(sql);
if (dt.Rows.Count > 0)
{
Application["stock_dynamic_max_id"] = dt.Rows[dt.Rows.Count - 1]["id"].ToString();
}
//0 btctrade,1,btcchina
foreach (DataRow row in dt.Rows)
{
BsonArray data_item = new BsonArray();
bool is_has = false;
for (int i = 0; i < websites.Length; i++)
{
if (websites[i].ToString() == row["website"].ToString())
{
data_item.Add(i);
is_has = true;
}
}
if (is_has == false)
{
continue;
}
data_item.Add(row["timespan"].ToString());
if (row["currency"].ToString().Contains("usd"))
{
data_item.Add(Math.Round(Convert.ToDouble(row["last"].ToString()) * rate, 2));
}
else
{
data_item.Add(Convert.ToDouble(row["last"].ToString()));
}
data.Add(data_item);
}
return(data.ToString());
}
public string GetMyList(string xmlString)
{
string sql = "", employeeID = "";
string result = "";
result = "<GetData>" +
"<Result>False</Result>" +
"<Description></Description>" +
"<DataRows></DataRows>" +
"</GetData>";
try
{
SQLServerHelper runner = new SQLServerHelper();
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode vNode = doc.SelectSingleNode("GetData/EmployeeID");
if (vNode != null && vNode.InnerText.Trim().Length > 0)
{
employeeID = vNode.InnerText.Trim();
}
else
{
throw new Exception("EmployeeID不能为空");
}
sql = @"
Select t1.FID AS ID, t2.FName As FName,t3.FName AS FHosptalName,t4.FName As FDeptmentName,t5.FName As FTitleName,
t6.FName As FRankName
From t_Doctors t1
Left Join t_Items t2 On t1.FID=t2.FID
Left Join t_Items t3 On t3.FID=t1.FHospitalID
Left Join t_Items t4 On t4.FID=t1.FDeptID
Left Join t_Items t5 On t5.FID=t1.FTitleID
Left Join t_Items t6 On t6.FID=t1.FRankID
Where t1.FIsDeleted =0 and FHospitalID IN (Select FInstitutionID from AuthDatas Where FInstitutionType =1 and FAuthObjectID='{0}')
Order by t3.FName";
sql = string.Format(sql, employeeID);
runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
result = Common.DataTableToXml(dt, "GetData", "", "List");
}
catch (Exception err)
{
throw err;
}
return(result);
}
/// <summary>
/// 获取所有用户
/// </summary>
/// <returns></returns>
public List <UserInfoLogon> GetUserAccountView()
{
try
{
string sql = " SELECT LoginName as Name FROM UserAccountView ";
DataSet ds = SQLServerHelper.Query(sql);
return(DataTableToListForGetPersonnelGroupViewt(ds.Tables[0]));
}
catch
{
throw;
}
}
public DataTable getConsumerUseData(DateTime startTime, DateTime endTime)
{
string strTime = string.Format(" where t_xf_time>='{0}' and t_xf_time<='{1}'", startTime, endTime);
string strSql = string.Format(
" select zclx.pk, zclx.v_zclx_name, SUM(zczm.f_zc_money) as f_zc_money" +
" from jt_zc_lx as zclx left join jt_zc_zm as zczm on zczm.v_zclx_no = zclx.pk" +
strTime +
" group by zclx.pk,zclx.v_zclx_name"
);
DataTable dataTable = SQLServerHelper.GetTable(strSql);
return(dataTable);
}
/// <summary>
/// 事务执行
/// </summary>
/// <param name="sqlParamList"></param>
public void ExecuteSqlTranList(List <SqlParam> sqlParamList)
{
List <Dictionary <string, object> > ListDic = new List <Dictionary <string, object> >();
Dictionary <string, object> dicstr = null;
foreach (var item in sqlParamList)
{
dicstr = new Dictionary <string, object>();
dicstr.Add(item.StrSql, item.Parameters);
ListDic.Add(dicstr);
}
SQLServerHelper.ExecuteSqlTranList(ListDic);//事务执行
}
/// <summary>
/// 获取所有用户
/// </summary>
/// <returns></returns>
public List <MailBaseData> GetMailBaseData()
{
try
{
string sql = " SELECT mailAddress,mailDisplayName,mailHost,mailPort,mailPwd FROM udtWip_MailBaseData ";
DataSet ds = SQLServerHelper.Query(sql);
return(DataTableToListForGetMailBaseData(ds.Tables[0]));
}
catch
{
throw;
}
}
public static void insert_depth_log(string website, string currency, string type, string qty_type, string text, string time)
{
if (string.IsNullOrEmpty(time))
{
time = "0";
}
string sql = "";
sql = "insert into depth_log (timespan,website,currency,type,qty_type,text,time) values('{0}','{1}','{2}','{3}','{4}','{5}',{6})";
sql = string.Format(sql, UnixTime.unix_now_long.ToString(), website, currency, type, qty_type, text, time);
SQLServerHelper.exe_sql(sql);
}
public static void insert_events(string event_id, string sport, string country, string competition, string start_time, string home, string away)
{
string sql = "";
sql = "delete from s_mbook_events where event_id='{0}'";
sql = string.Format(sql, event_id);
SQLServerHelper.exe_sql(sql);
sql = "insert into s_mbook_events (event_id,sport,country,competition,start_time,home,away)" +
" values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')";
sql = string.Format(sql, event_id, sport, country, competition, start_time, home, away);
SQLServerHelper.exe_sql(sql);
}
public string Update(string supervisorID, string teamID)
{
string result = "-1", sql = "";
SQLServerHelper runner;
try
{
runner = new SQLServerHelper();
sql = "Select FID As FEmployeeID,FTypeID,FIsAgency From t_Employees Where FDeptID='" + teamID + "' And FIsDeleted=0";
DataTable dt = runner.ExecuteSql(sql);
foreach (DataRow row in dt.Rows)
{
sql = "Select * from t_Workships Where FEmployeeID='" + row["FEmployeeID"].ToString() + "' and FIsDeleted=0";
DataTable dtWorkship = runner.ExecuteSql(sql);
if (dtWorkship.Rows.Count == 0)//此员工尚未建立工作关系
{
sql = "INSERT INTO t_Workships(FTeamID,FEmployeeID,FTeamLeaderID,FBeginDate,FIsDeleted,FIsAgency)VALUES('" + teamID + "','" + row["FEmployeeID"].ToString() +
"','" + supervisorID + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "',0,'" + row["FIsAgency"].ToString() + "')";
runner.ExecuteSqlNone(sql);
}
else//workship表有此记录
{
foreach (DataRow wsRow in dtWorkship.Rows)
{
if (!wsRow["FTeamLeaderID"].ToString().Trim().Equals(supervisorID))//Workship表中的主管ID不同于当前LeaderID
{
sql = "Select FDeptID From t_Employees Where FID= '" + row["FEmployeeID"].ToString() + "' and FIsDeleted=0 And FDeptID !='" + teamID + "'";
DataTable dt2 = runner.ExecuteSql(sql);
if (dt2.Rows.Count == 0)//同部门，Leader变化
{
sql = "Udpdate t_Workships Set FIsDeleted=1,FEndDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
sql = sql + " Where FID=" + wsRow["FID"].ToString();
runner.ExecuteSqlNone(sql);
}
else//该员工在多个部门任职
{
sql = "INSERT INTO t_Workships(FTeamID,FEmployeeID,FTeamLeaderID,FBeginDate,FIsDeleted,FIsAgency)VALUES('" + teamID + "','" + row["FEmployeeID"].ToString() +
"','" + supervisorID + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "',0," + row["FIsAgency"].ToString() + ")";
runner.ExecuteSqlNone(sql);//插入此主管的汇报关系记录
}
}
}
}
}
}
catch (Exception err)
{
throw err;
}
return(result);
}
public List <PrintTag> GetPrintTagFromDBByAutoID(string autoID)
{
try
{
List <PrintTag> printTagList = new List <PrintTag>();
List <SqlWrapper> lstSqlWrapper = new List <SqlWrapper>();
SqlWrapper sqlWrapper = new SqlWrapper();
string strSQL = string.Format("Select * from T_PrintTag where AutoID = '{0}' order by ImageUID", autoID);
List <SqlParameter> lstParas = new List <SqlParameter>();
lstParas.Add(new SqlParameter("@AutoID", autoID));
sqlWrapper.SqlString = strSQL;
sqlWrapper.Parameter = lstParas.ToArray();
DataSet ds = SQLServerHelper.ExecuteQuery(sqlWrapper, QCConnectionType.PrintSystem);
foreach (DataRow row in ds.Tables[0].Rows)
{
//DataRow row = ds.Tables[0].Rows[0];
PrintTag printTag = new PrintTag();
printTag.AutoUID = row["AutoID"].ToString().Trim();
printTag.BorderDensity = row["BorderDensity"].ToString().Trim();
printTag.CalledAE = row["CalledAE"].ToString().Trim();
printTag.CallingAE = row["CallingAE"].ToString().Trim();
printTag.CallingIPAddress = IPAddress.Parse(row["CallingIP"].ToString().Trim());
printTag.DicomFilePath = row["FilePath"].ToString().Trim();
printTag.EmptyImageDensity = row["EmptyImageDensity"].ToString().Trim();
printTag.FilmOrienation = row["FilmOrientation"].ToString().Trim();
printTag.FilmSizeID = row["FilmSize"].ToString().Trim();
printTag.ImageBoxPosition = Convert.ToInt32(row["ImageUID"].ToString().Trim());
printTag.ImageDisplayFormat = row["DisplayFormat"].ToString().Trim();
printTag.MagnificationType = row["MagnificationType"].ToString().Trim();
printTag.MaxDensity = Convert.ToUInt16(row["MaxDensity"].ToString().Trim());
printTag.MinDensity = Convert.ToUInt16(row["MinDensity"].ToString().Trim());
printTag.NumberOfCopies = Convert.ToInt32(row["Copies"].ToString().Trim());
printTag.PresentationLut = row["PresentationLUT"].ToString().Trim();
printTag.printUID = row["PrintUID"].ToString().Trim();
//printTag.SamplesPerPixel = row["BorderDensity"].ToString().Trim();
printTag.SmoothingType = row["SmoothingType"].ToString().Trim();
printTag.Trim = row["Trim"].ToString().Trim();
printTagList.Add(printTag);
}
return(printTagList);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// 读取指定人员某时段的拜访汇总报表数据
/// </summary>
/// <param name="xmlString"></param>
/// <returns></returns>
public string GetCallRepotr2(string xmlString)
{
string result = "", sql = "", date1 = "", date2 = "", employeeIds = "";
result = "<GetData>" +
"<Result>False</Result>" +
"<Description></Description>" +
"<DataRows></DataRows>" +
"</GetData>";
date1 = DateTime.Now.ToString("yyyy-MM") + "-01";
date2 = DateTime.Now.ToString("yyyy-MM-dd");
XmlDocument doc = new XmlDocument();
doc.LoadXml(xmlString);
XmlNode node = doc.SelectSingleNode("GetData/BeginDate");
if (node != null && node.InnerText.Trim().Length > 0)
{
date1 = node.InnerText.Trim();
}
node = doc.SelectSingleNode("GetData/EndDate");
if (node != null && node.InnerText.Trim().Length > 0)
{
date2 = node.InnerText.Trim();
}
if (employeeIds.Length == 0)
{
node = doc.SelectSingleNode("GetData/EmployeeID");//若为团队负责人，要读取其及直接下属的数据
if (node != null && node.InnerText.Trim().Length > 0)
{
employeeIds = node.InnerText.Trim();
}
}
sql = @" Select t3.FName As EmployeeName, t1.FEmployeeID As EmployeeID,t2.FName As InstitutionName ,
sum(Case FScheduleID When '4484030a-28d1-4e5e-ba72-6655f1cb2898' Then 1 Else 0 End) AS UnplanedCallCount,
Sum(1) AS CallCount,SUM(ISNULL(DATEDIFF(mi, t1.FStartTime, t1.FEndTime), 0)) AS TimeSpan
From CallActivity t1
Left Join t_Items t2 On t1.FInstitutionID = t2.FID
Left Join t_Items t3 On t1.FEmployeeID = t3.FID
Where FDate between '{0}' and '{1}' and FEmployeeID In('{2}')
Group by t3.FName,t2.FName,t1.FEmployeeID
Order by CallCount Desc,TimeSpan Desc";
sql = string.Format(sql, date1, date2, employeeIds);
SQLServerHelper runner = new SQLServerHelper();
DataTable dt = runner.ExecuteSql(sql);
result = Common.DataTableToXml(dt, "GetData", "", "List");
return(result);
}
/// <summary>
/// 连接测试
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void Test_Button_Click(object sender, EventArgs e)
{
await Task.Run(() =>
{
if (!(this.Invoke(new Func <LinkModel>(() =>
{
this.Test_Button.Enabled = this.OK_Button.Enabled = this.Cancel_Button.Enabled = false;
this.Test_Button.Text = "测试中...";
return(this.CheckInfo());
})) is LinkModel result))
{
this.Invoke(new Action(() =>
{
this.Test_Button.Enabled = this.OK_Button.Enabled = this.Cancel_Button.Enabled = true;
this.Test_Button.Text = "连接测试";
}));
return;
}
try
{
if (SQLServerHelper.TestLink(result.LinkString))
{
this.Invoke(new Action(() =>
{
this.Test_Button.Enabled = this.OK_Button.Enabled = this.Cancel_Button.Enabled = true;
this.Test_Button.Text = "连接测试";
}));
this.IsTestSuccess = true;
MessageBox.Show("测试连接成功", "测试连接", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
this.Invoke(new Action(() =>
{
this.Test_Button.Enabled = this.OK_Button.Enabled = this.Cancel_Button.Enabled = true;
this.Test_Button.Text = "连接测试";
}));
MessageBox.Show("测试连接失败", "测试连接", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
catch (Exception ex)
{
this.Invoke(new Action(() =>
{
this.Test_Button.Enabled = this.OK_Button.Enabled = this.Cancel_Button.Enabled = true;
this.Test_Button.Text = "连接测试";
}));
MessageBox.Show($"测试连接失败, 失败信息为: {ex.Message}", "测试连接", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
});
}
public SQLServerStore(string connectionString)
{
dbh = new SQLServerHelper(connectionString);
}
