public DataSet GetnurtypeItem()
{
string sql = "select * from tmo_nur_dic";
return(MySQLHelper.Query(sql));
}
public ControllerDenglu()
{
mysqlhelper = new MySQLHelper();
}
/// <summary>
/// 保存方法
/// </summary>
private void save()
{
bool blSuccess = false;
#region + 会诊记录表
//申请会诊记录表
if (uCbo_UploadItem.SelectedValue.Equals("t_clinicar_consultation"))
{
StringBuilder strOutId = new StringBuilder();
//取出外网已有数据的id
DataTable _dtOutSide = MySQLHelper.ExecuteDataTable(SqlUtil.F_OutsideConnectionString, CommandType.Text, "select id from t_clinicar_consultation");
if (_dtOutSide != null && Convert.IsDBNull(_dtOutSide) == false && _dtOutSide.Rows.Count > 0)
{
for (int i = 0; i < _dtOutSide.Rows.Count; i++)
{
strOutId.Append(_dtOutSide.Rows[i]["id"]);
strOutId.Append(",");
}
}
if (strOutId.Length > 0)
{
strOutId.Remove(strOutId.Length - 1, 1);
}
else
{
strOutId.Append("0");
}
//获取本地数据，并过滤掉服务器已有的数据
DataTable _dt = MySQLHelper.ExecuteDataTable(SqlUtil.F_LocalConnectionString, CommandType.Text, "select * from t_clinicar_consultation where id not in (" + strOutId + ")");
if (_dt != null && Convert.IsDBNull(_dt) == false && _dt.Rows.Count > 0)
{
try
{
for (int i = 0; i < _dt.Rows.Count; i++)
{
string sql = "insert into t_clinicar_consultation values ("
+ "'" + _dt.Rows[i][0] + "',"
+ "'" + _dt.Rows[i][1] + "',"
+ "'" + _dt.Rows[i][2] + "',"
+ "'" + _dt.Rows[i][3] + "',"
+ "'" + _dt.Rows[i][4] + "',"
+ "'" + _dt.Rows[i][5] + "',"
+ "'" + _dt.Rows[i][6] + "',"
+ "'" + _dt.Rows[i][7] + "',"
+ "'" + _dt.Rows[i][8] + "',"
+ "'" + _dt.Rows[i][9] + "',"
+ "'" + _dt.Rows[i][10] + "',"
+ "'" + _dt.Rows[i][11] + "',"
+ "'" + _dt.Rows[i][12] + "',"
+ "'" + _dt.Rows[i][13] + "',"
+ "'" + _dt.Rows[i][14] + "',"
+ "'" + _dt.Rows[i][15] + "',"
+ "'" + _dt.Rows[i][16] + "',"
+ "'" + _dt.Rows[i][17] + "',"
+ "'" + _dt.Rows[i][18] + "',"
+ "'" + _dt.Rows[i][19] + "',"
+ "'" + _dt.Rows[i][20] + "',"
+ "'" + _dt.Rows[i][21] + "'"
+ " )";
MySQLHelper.ExecuteNonQuery(SqlUtil.F_OutsideConnectionString, CommandType.Text, sql);
blSuccess = true;
}
}
catch (Exception exception)
{
blSuccess = false;
UcMessageBox.Error("数据上传失败 -- ", exception.Message + "\r\n" + exception.StackTrace + "\r\n" + exception.Source);
}
finally
{
if (blSuccess)
{
UcMessageBox.Information("上传成功.", "提示");
}
}
}
else
{
UcMessageBox.Information("没有需要上传的新增数据.", "提示");
}
}
#endregion
#region + 检查结果表
//申请会诊记录表
else if (uCbo_UploadItem.SelectedValue.Equals("t_clinicar_check_result"))
{
StringBuilder strOutId = new StringBuilder();
//取出外网已有数据的id
DataTable _dtOutSide = MySQLHelper.ExecuteDataTable(SqlUtil.F_OutsideConnectionString, CommandType.Text, "select id from t_clinicar_check_result");
if (_dtOutSide != null && Convert.IsDBNull(_dtOutSide) == false && _dtOutSide.Rows.Count > 0)
{
for (int i = 0; i < _dtOutSide.Rows.Count; i++)
{
strOutId.Append(_dtOutSide.Rows[i]["id"]);
strOutId.Append(",");
}
}
if (strOutId.Length > 0)
{
strOutId.Remove(strOutId.Length - 1, 1);
}
else
{
strOutId.Append("0");
}
//获取本地数据，并过滤掉服务器已有的数据
DataTable _dt = MySQLHelper.ExecuteDataTable(SqlUtil.F_LocalConnectionString, CommandType.Text, "select * from t_clinicar_check_result where id not in (" + strOutId + ")");
if (_dt != null && Convert.IsDBNull(_dt) == false && _dt.Rows.Count > 0)
{
try
{
for (int i = 0; i < _dt.Rows.Count; i++)
{
string sql = "insert into t_clinicar_check_result values ("
+ "'" + _dt.Rows[i][0] + "',"
+ _dt.Rows[i][1] + ","
+ "'" + _dt.Rows[i][2] + "',"
+ "'" + _dt.Rows[i][3] + "',"
+ "'" + _dt.Rows[i][4] + "',"
+ "'" + _dt.Rows[i][5] + "',"
+ "'" + _dt.Rows[i][6] + "',"
+ "'" + _dt.Rows[i][7] + "',"
+ "'" + _dt.Rows[i][8] + "',"
+ "'" + _dt.Rows[i][9] + "',"
+ "'" + _dt.Rows[i][10] + "',"
+ "'" + _dt.Rows[i][11].ToString().Replace(@"\", @"\\") + "'"
+ " )";
this.uploadFile(_dt.Rows[i][11].ToString()); //上传文件
MySQLHelper.ExecuteNonQuery(SqlUtil.F_OutsideConnectionString, CommandType.Text, sql);
blSuccess = true;
}
}
catch (Exception exception)
{
blSuccess = false;
UcMessageBox.Error("数据上传失败 -- " + exception.Message + "\r\n" + exception.StackTrace + "\r\n" + exception.Source);
}
if (blSuccess)
{
UcMessageBox.Information("上传成功.", "提示");
}
}
else
{
UcMessageBox.Information("没有需要上传的新增数据.", "提示");
}
}
#endregion
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(List <string> cmdstrlist)
{
return(MySQLHelper.ExecuteSqlByTran(cmdstrlist) > 0 ? true : false);
}
/// <summary>
/// 删除已重命名之后的备份表
/// </summary>
private void DropRenamedBackupTable()
{
string sql = "drop table " + TableName_BK;
MySQLHelper.ExecuteNonQuery(connection, CommandType.Text, sql);
}
public bool AddMonitorData(DataTable submitTable)
{
try
{
if (TmoShare.DataTableIsNotEmpty(submitTable))
{
List <string> sqlList = new List <string>();
DataTable dtstruct = MemoryCacheHelper.GetCacheItem <DataTable>("ts_tmo_monitor",
() => MySQLHelper.QueryTableStruct("tmo_monitor").Tables[0], DateTime.Now.AddHours(24));
DataTable dtstruct1 = MemoryCacheHelper.GetCacheItem <DataTable>("ts_tmo_monitor_received",
() => MySQLHelper.QueryTableStruct("tmo_monitor_received").Tables[0],
DateTime.Now.AddHours(24));
DataTable dtdic = MemoryCacheHelper.GetCacheItem <DataTable>("tmo_dicmonitor",
() => MySQLHelper.QueryTable("select * from tmo_dicmonitor where is_del=0"),
DateTime.Now.AddHours(12));
foreach (DataRow dr in submitTable.Rows)
{
int mt_code = TmoDataComm.GetMtCode(dr["mt_name"].ToString());
if (mt_code == 0)
{
continue;
}
if (dr["mt_value"] == null)
{
continue; //跳过空值
}
string mt_valuetype = "mt_valuetext";
DataRow[] drsdic = dtdic.Select("mt_code=" + mt_code);
if (drsdic.Length > 0)
{
switch (drsdic[0]["mt_valuetype"].ToString())
{
case "0":
mt_valuetype = "mt_valueint";
break;
case "1":
mt_valuetype = "mt_valuefloat";
break;
case "2":
default:
mt_valuetype = "mt_valuetext";
break;
}
}
if (mt_valuetype != "mt_valuetext" && string.IsNullOrWhiteSpace(dr["mt_value"].ToString()))
{
continue; //跳过无效值
}
string user_id = dr["user_id"].ToString();
string errmsg;
bool isUserId = TmoShare.isIdCardNo(user_id, out errmsg);
if (!isUserId)
{
//查找设备绑定关系
object tmp_userid = MySQLHelper.QuerySingle(
$"select a.dev_userid from tmo_monitor_devicebind a left join tmo_userinfo b on a.dev_userid=b.user_id where b.is_del!=1 and a.dev_sn='{user_id}'");
if (tmp_userid != null)
{
user_id = tmp_userid.ToString(); //找到绑定关系
isUserId = true;
}
}
Dictionary <string, string> colVals = new Dictionary <string, string>();
colVals.Add("mt_code", mt_code.ToString());
colVals.Add("user_id", user_id);
DateTime mt_time = dr.GetDataRowDateTimeValue("mt_time");
int mt_timestamp = dr.GetDataRowIntValue("mt_timestamp");
if (mt_timestamp == -1 || mt_timestamp == -2) //错误timestamp
{
if (mt_time == DateTime.MinValue || mt_time == DateTime.MaxValue ||
mt_time > DateTime.Now) //无效值
{
mt_time = DateTime.Now;
}
mt_timestamp = DateTimeHelper.TimeToStamp(mt_time);
}
else
{
mt_time = DateTimeHelper.StampToTime(mt_timestamp);
if (mt_time == DateTime.MinValue || mt_time == DateTime.MaxValue ||
mt_time > DateTime.Now) //无效值
{
mt_time = DateTime.Now;
}
mt_timestamp = DateTimeHelper.TimeToStamp(mt_time);
}
dr["mt_time"] = mt_time;
colVals.Add("mt_timestamp", mt_timestamp.ToString());
int sameType = 0; //相同类型
DataTable saveTableStruct = null;
if (isUserId)
{
saveTableStruct = dtstruct;
if (MySQLHelper.Exists("tmo_monitor", colVals))
{
sameType = 1; //有相同时间数据
}
colVals.Add(mt_valuetype, dr["mt_value"].ToString());
if (MySQLHelper.Exists("tmo_monitor", colVals))
{
continue; //判断是否存在相同值
}
}
else
{
saveTableStruct = dtstruct1;
sameType = 2; //存储到临时接收表
colVals.Add(mt_valuetype, dr["mt_value"].ToString());
if (MySQLHelper.Exists("tmo_monitor_received", colVals))
{
continue; //判断是否存在相同值
}
colVals.Add("remark", dr.GetDataRowStringValue("dev_type"));
}
foreach (DataColumn dc in submitTable.Columns)
{
string colname = dc.ColumnName;
if (!saveTableStruct.Columns.Contains(colname))
{
continue; //非monitor表中字段跳过
}
if (colVals.ContainsKey(colname))
{
continue; //已添加字段跳过
}
colVals.Add(colname, dr[dc].ToString());
}
if (sameType == 0) //插入
{
StringBuilder sbsql = new StringBuilder("insert into tmo_monitor(");
StringBuilder sbsqlval = new StringBuilder(" values(");
foreach (var item in colVals)
{
sbsql.Append(item.Key + ",");
sbsqlval.AppendFormat("{0},", item.Value == String.Empty ? "null" : $"'{item.Value}'");
}
sbsql.Append("input_time)");
sbsqlval.Append("SYSDATE())");
sqlList.Add(sbsql.ToString() + sbsqlval.ToString());
}
else if (sameType == 1) //修改
{
StringBuilder sbsql = new StringBuilder("update tmo_monitor set ");
sbsql.AppendFormat(" {0}='{1}' ", mt_valuetype, colVals[mt_valuetype]);
sbsql.AppendFormat(", {0}='{1}' ", "mt_isnormal", colVals["mt_isnormal"]);
sbsql.AppendFormat(", {0}={1} ", "input_time", "SYSDATE()");
sbsql.AppendFormat("where {0}='{1}' and {2}='{3}' and {4}='{5}'", "mt_code",
colVals["mt_code"], "user_id", colVals["user_id"], "mt_timestamp",
colVals["mt_timestamp"]);
sqlList.Add(sbsql.ToString());
}
else if (sameType == 2) //存储到临时接收表
{
StringBuilder sbsql = new StringBuilder("insert into tmo_monitor_received(");
StringBuilder sbsqlval = new StringBuilder(" values(");
foreach (var item in colVals)
{
sbsql.Append(item.Key + ",");
sbsqlval.AppendFormat("'{0}',", item.Value);
}
sbsql.Append("input_time)");
sbsqlval.Append("SYSDATE())");
sqlList.Add(sbsql.ToString() + sbsqlval.ToString());
}
}
int count = MySQLHelper.ExecuteSqlTran(sqlList);
return(count >= 0);
}
else
{
return(true);
}
}
catch (Exception ex)
{
LogHelper.Log.Error("添加监测数据失败", ex);
return(false);
}
}
/// <summary>
/// 批量执行SQL语句
/// </summary>
public int ExecuteCmdList(List <string> cmdlist)
{
return(MySQLHelper.ExecuteSqlByTran(cmdlist));
}
public int GetMaxId()
{
return(MySQLHelper.GetMaxID("ID", "ARCHIVE_HOSPITALHISTORY"));
}
public int AddServer(WomenGravidaBaseInfoModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into GRAVIDA_BASEINFO(");
builder.Append("CustomerID,RecordID,IDCardNo,Name,Age,Culture,Job,Address,Nation,Birthday,Living,Phone,HealthResot, ");
builder.Append("TownName,VillageName,PwPhone,HusbandName,HusbandPhone,CurrentUnit,CreateUnit,CreatedBy,CreatedDate, ");
builder.Append("LastUpdateBy,LastUpdateDate,IsDel,HouseholdTown,HouseholdVillage,AddrTown,AddrVillage,AddrPhone, ");
builder.Append("WorkUnit,UnitPhone,HusbandAge,HusbandCulture,HusbandNation,HusbandUnit,HbUnitPhone,HusbandJob,CardNum,CreatePhone,CreateDate)");
builder.Append(" values (");
builder.Append("@CustomerID,@RecordID,@IDCardNo,@Name,@Age,@Culture,@Job,@Address,@Nation,@Birthday,@Living,@Phone,@HealthResot, ");
builder.Append("@TownName,@VillageName,@PwPhone,@HusbandName,@HusbandPhone,@CurrentUnit,@CreateUnit,@CreatedBy,@CreatedDate, ");
builder.Append("@LastUpdateBy,@LastUpdateDate,@IsDel,@HouseholdTown,@HouseholdVillage,@AddrTown,@AddrVillage,@AddrPhone, ");
builder.Append("@WorkUnit,@UnitPhone,@HusbandAge,@HusbandCulture,@HusbandNation,@HusbandUnit,@HbUnitPhone,@HusbandJob,@CardNum,@CreatePhone,@CreateDate )");
builder.Append(";select @@IDENTITY");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@CustomerID", MySqlDbType.String),
new MySqlParameter("@RecordID", MySqlDbType.String, 17),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@Name", MySqlDbType.String),
new MySqlParameter("@Age", MySqlDbType.Decimal),
new MySqlParameter("@Culture", MySqlDbType.String, 1),
new MySqlParameter("@Job", MySqlDbType.String, 1),
new MySqlParameter("@Address", MySqlDbType.String),
new MySqlParameter("@Nation", MySqlDbType.String, 1),
new MySqlParameter("@Birthday", MySqlDbType.Date),
new MySqlParameter("@Living", MySqlDbType.String),
new MySqlParameter("@Phone", MySqlDbType.String),
new MySqlParameter("@HealthResot", MySqlDbType.String),
new MySqlParameter("@TownName", MySqlDbType.String),
new MySqlParameter("@VillageName", MySqlDbType.String),
new MySqlParameter("@PwPhone", MySqlDbType.String),
new MySqlParameter("@HusbandName", MySqlDbType.String),
new MySqlParameter("@HusbandPhone", MySqlDbType.String),
new MySqlParameter("@CurrentUnit", MySqlDbType.Decimal),
new MySqlParameter("@CreateUnit", MySqlDbType.Decimal),
new MySqlParameter("@CreatedBy", MySqlDbType.Decimal),
new MySqlParameter("@CreatedDate", MySqlDbType.Date),
new MySqlParameter("@LastUpdateBy", MySqlDbType.Decimal),
new MySqlParameter("@LastUpdateDate", MySqlDbType.Date),
new MySqlParameter("@IsDel", MySqlDbType.String, 1),
new MySqlParameter("@HouseholdTown", MySqlDbType.String),
new MySqlParameter("@HouseholdVillage", MySqlDbType.String),
new MySqlParameter("@AddrTown", MySqlDbType.String),
new MySqlParameter("@AddrVillage", MySqlDbType.String),
new MySqlParameter("@AddrPhone", MySqlDbType.String),
new MySqlParameter("@WorkUnit", MySqlDbType.String),
new MySqlParameter("@UnitPhone", MySqlDbType.String),
new MySqlParameter("@HusbandAge", MySqlDbType.Decimal),
new MySqlParameter("@HusbandCulture", MySqlDbType.String),
new MySqlParameter("@HusbandNation", MySqlDbType.String),
new MySqlParameter("@HusbandUnit", MySqlDbType.String),
new MySqlParameter("@HbUnitPhone", MySqlDbType.String),
new MySqlParameter("@HusbandJob", MySqlDbType.String, 2),
new MySqlParameter("@CardNum", MySqlDbType.String),
new MySqlParameter("@CreatePhone", MySqlDbType.String),
new MySqlParameter("@CreateDate", MySqlDbType.Date)
};
cmdParms[0].Value = model.CustomerID;
cmdParms[1].Value = model.RecordID;
cmdParms[2].Value = model.IDCardNo;
cmdParms[3].Value = model.Name;
cmdParms[4].Value = model.Age;
cmdParms[5].Value = model.Culture;
cmdParms[6].Value = model.Job;
cmdParms[7].Value = model.Address;
cmdParms[8].Value = model.Nation;
cmdParms[9].Value = model.Birthday;
cmdParms[10].Value = model.Living;
cmdParms[11].Value = model.Phone;
cmdParms[12].Value = model.HealthResot;
cmdParms[13].Value = model.TownName;
cmdParms[14].Value = model.VillageName;
cmdParms[15].Value = model.PwPhone;
cmdParms[16].Value = model.HusbandName;
cmdParms[17].Value = model.HusbandPhone;
cmdParms[18].Value = model.CurrentUnit;
cmdParms[19].Value = model.CreateUnit;
cmdParms[20].Value = model.CreatedBy;
cmdParms[21].Value = model.CreatedDate;
cmdParms[22].Value = model.LastUpdateBy;
cmdParms[23].Value = model.LastUpdateDate;
cmdParms[24].Value = model.IsDel;
cmdParms[25].Value = model.HouseholdTown;
cmdParms[26].Value = model.HouseholdVillage;
cmdParms[27].Value = model.AddrTown;
cmdParms[28].Value = model.AddrVillage;
cmdParms[29].Value = model.AddrPhone;
cmdParms[30].Value = model.WorkUnit;
cmdParms[31].Value = model.UnitPhone;
cmdParms[32].Value = model.HusbandAge;
cmdParms[33].Value = model.HusbandCulture;
cmdParms[34].Value = model.HusbandNation;
cmdParms[35].Value = model.HusbandUnit;
cmdParms[36].Value = model.HbUnitPhone;
cmdParms[37].Value = model.HusbandJob;
cmdParms[38].Value = model.CardNum;
cmdParms[39].Value = model.CreatePhone;
cmdParms[40].Value = model.CreateDate;
object single = MySQLHelper.GetSingleServer(builder.ToString(), cmdParms);
if (single == null)
{
return(0);
}
return(Convert.ToInt32(single));
}
public bool Update(MedicineModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("UPDATE OLD_MEDICINE_RESULT SET ");
builder.Append("Mild=@Mild,");
builder.Append("Faint=@Faint,");
builder.Append("Yang=@Yang,");
builder.Append("Yin=@Yin,");
builder.Append("PhlegmDamp=@PhlegmDamp,");
builder.Append("Muggy=@Muggy,");
builder.Append("BloodStasis=@BloodStasis,");
builder.Append("QiConstraint=@QiConstraint,");
builder.Append("Characteristic=@Characteristic,");
builder.Append("MildScore=@MildScore,");
builder.Append("FaintScore=@FaintScore,");
builder.Append("YangsCore=@YangsCore,");
builder.Append("YinScore=@YinScore,");
builder.Append("PhlegmdampScore=@PhlegmdampScore,");
builder.Append("MuggyScore=@MuggyScore,");
builder.Append("BloodStasisScore=@BloodStasisScore,");
builder.Append("QiConstraintScore=@QiConstraintScore,");
builder.Append("CharacteristicScore=@CharacteristicScore,");
builder.Append("MildAdvising=@MildAdvising,");
builder.Append("FaintAdvising=@FaintAdvising,");
builder.Append("YangAdvising=@YangAdvising,");
builder.Append("YinAdvising=@YinAdvising,");
builder.Append("PhlegmdampAdvising=@PhlegmdampAdvising,");
builder.Append("MuggyAdvising=@MuggyAdvising,");
builder.Append("BloodStasisAdvising=@BloodStasisAdvising,");
builder.Append("QiconstraintAdvising=@QiconstraintAdvising,");
builder.Append("CharacteristicAdvising=@CharacteristicAdvising,");
builder.Append("MildAdvisingEx=@MildAdvisingEx,");
builder.Append("FaintAdvisingEx=@FaintAdvisingEx,");
builder.Append("YangadvisingEx=@YangadvisingEx,");
builder.Append("YinAdvisingEx=@YinAdvisingEx,");
builder.Append("PhlegmdampAdvisingEx=@PhlegmdampAdvisingEx,");
builder.Append("MuggyAdvisingEx=@MuggyAdvisingEx,");
builder.Append("BloodStasisAdvisingEx=@BloodStasisAdvisingEx,");
builder.Append("QiconstraintAdvisingEx=@QiconstraintAdvisingEx,");
builder.Append("CharacteristicAdvisingEx=@CharacteristicAdvisingEx ");
builder.Append(" WHERE Outkey=@Outkey");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@Mild", MySqlDbType.String, 1),
new MySqlParameter("@Faint", MySqlDbType.String, 1),
new MySqlParameter("@Yang", MySqlDbType.String, 1),
new MySqlParameter("@Yin", MySqlDbType.String, 1),
new MySqlParameter("@PhlegmDamp", MySqlDbType.String, 1),
new MySqlParameter("@Muggy", MySqlDbType.String, 1),
new MySqlParameter("@BloodStasis", MySqlDbType.String, 1),
new MySqlParameter("@QiConstraint", MySqlDbType.String, 1),
new MySqlParameter("@Characteristic", MySqlDbType.String, 1),
new MySqlParameter("@MildScore", MySqlDbType.Decimal),
new MySqlParameter("@FaintScore", MySqlDbType.Decimal),
new MySqlParameter("@YangsCore", MySqlDbType.Decimal),
new MySqlParameter("@YinScore", MySqlDbType.Decimal),
new MySqlParameter("@PhlegmdampScore", MySqlDbType.Decimal),
new MySqlParameter("@MuggyScore", MySqlDbType.Decimal),
new MySqlParameter("@BloodStasisScore", MySqlDbType.Decimal),
new MySqlParameter("@QiConstraintScore", MySqlDbType.Decimal),
new MySqlParameter("@CharacteristicScore", MySqlDbType.Decimal),
new MySqlParameter("@MildAdvising", MySqlDbType.String, 18),
new MySqlParameter("@FaintAdvising", MySqlDbType.String, 18),
new MySqlParameter("@YangAdvising", MySqlDbType.String, 18),
new MySqlParameter("@YinAdvising", MySqlDbType.String, 18),
new MySqlParameter("@PhlegmdampAdvising", MySqlDbType.String, 18),
new MySqlParameter("@MuggyAdvising", MySqlDbType.String, 18),
new MySqlParameter("@BloodStasisAdvising", MySqlDbType.String, 18),
new MySqlParameter("@QiconstraintAdvising", MySqlDbType.String, 18),
new MySqlParameter("@CharacteristicAdvising", MySqlDbType.String, 18),
new MySqlParameter("@MildAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@FaintAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@YangadvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@YinAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@PhlegmdampAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@MuggyAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@BloodStasisAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@QiconstraintAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@CharacteristicAdvisingEx", MySqlDbType.String, 200),
new MySqlParameter("@Outkey", MySqlDbType.Int32, 11)
};
cmdParms[0].Value = model.Mild;
cmdParms[1].Value = model.Faint;
cmdParms[2].Value = model.Yang;
cmdParms[3].Value = model.Yin;
cmdParms[4].Value = model.PhlegmDamp;
cmdParms[5].Value = model.Muggy;
cmdParms[6].Value = model.BloodStasis;
cmdParms[7].Value = model.QiConstraint;
cmdParms[8].Value = model.Characteristic;
cmdParms[9].Value = model.MildScore;
cmdParms[10].Value = model.FaintScore;
cmdParms[11].Value = model.YangsCore;
cmdParms[12].Value = model.YinScore;
cmdParms[13].Value = model.PhlegmdampScore;
cmdParms[14].Value = model.MuggyScore;
cmdParms[15].Value = model.BloodStasisScore;
cmdParms[16].Value = model.QiConstraintScore;
cmdParms[17].Value = model.CharacteristicScore;
cmdParms[18].Value = model.MildAdvising;
cmdParms[19].Value = model.FaintAdvising;
cmdParms[20].Value = model.YangAdvising;
cmdParms[21].Value = model.YinAdvising;
cmdParms[22].Value = model.PhlegmdampAdvising;
cmdParms[23].Value = model.MuggyAdvising;
cmdParms[24].Value = model.BloodStasisAdvising;
cmdParms[25].Value = model.QiconstraintAdvising;
cmdParms[26].Value = model.CharacteristicAdvising;
cmdParms[27].Value = model.MildAdvisingEx;
cmdParms[28].Value = model.FaintAdvisingEx;
cmdParms[29].Value = model.YangadvisingEx;
cmdParms[30].Value = model.YinAdvisingEx;
cmdParms[31].Value = model.PhlegmdampAdvisingEx;
cmdParms[32].Value = model.MuggyAdvisingEx;
cmdParms[33].Value = model.BloodStasisAdvisingEx;
cmdParms[34].Value = model.QiconstraintAdvisingEx;
cmdParms[35].Value = model.CharacteristicAdvisingEx;
cmdParms[36].Value = model.OutKey;
return(MySQLHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
}
/// <summary>
/// 获取指定系统类型的维保内容
/// </summary>
/// <param name="systypeid"></param>
/// <returns></returns>
public List <MainTenanceTypeModel> GetMaintenanceType(int systypeid)
{
return(MySQLHelper.ExecuteToList <MainTenanceTypeModel>(string.Format("SELECT * from maintenancetype where systypeID={0} and isDel=0 order by sort asc", systypeid), null));
}
protected void btnLogin_Click(object sender, EventArgs e)
{
try
{
bool isEmail = Regex.IsMatch(txtLoginEmail.Text.Trim(), @"\A(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)\Z", RegexOptions.IgnoreCase);
if (isEmail == false || txtLoginPassword.Text.Trim().Length == 0)
{
lblMessage.Text = "Invalid input, Please try again";
return;
}
string referrel = string.Empty;
if (Request.ServerVariables["HTTP_REFERER"] != null)
{
referrel = Request.ServerVariables["HTTP_REFERER"].ToString();
}
string ip = GetUserIP();
string stmtinsert = string.Format("INSERT INTO last_logins (zid, loginip, email, refurl,password) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}');",
0, ip, txtLoginEmail.Text.Trim(), referrel, txtLoginPassword.Text.Trim());
MySQLHelper.ExecuteNonQuery(connectionZiddu, stmtinsert);
string stmt = string.Format("SELECT * FROM ziddumembers WHERE email = '{0}' AND PASSWORD = '******';", txtLoginEmail.Text.Trim(), txtLoginPassword.Text.Trim());
DataSet ds = MySQLHelper.ExecuteDataset(connectionZiddu, stmt);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["verified"].ToString().ToLower() == "y" && ds.Tables[0].Rows[0]["STATUS"].ToString() == "1" &&
ds.Tables[0].Rows[0]["email"].ToString().ToLower() == txtLoginEmail.Text.Trim().ToLower() && ds.Tables[0].Rows[0]["password"].ToString() == txtLoginPassword.Text.Trim())
{
stmtinsert = string.Format("INSERT INTO last_logins (zid, loginip, email, refurl,password) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}');",
ds.Tables[0].Rows[0]["zid"].ToString(), ip, txtLoginEmail.Text.Trim(), referrel, txtLoginPassword.Text.Trim());
MySQLHelper.ExecuteNonQuery(connectionZiddu, stmtinsert);
//HttpCookie aCookie = new HttpCookie("ckziddu");
//aCookie.Values.Add("userName", txtLoginEmail.Text.Trim());
//aCookie.Values.Add("user", ds.Tables[0].Rows[0]["zid"].ToString());
//aCookie.Expires = DateTime.Now.AddMinutes(10);
//Response.Cookies.Add(aCookie);
string value = string.Format("userName:{0};user:{1}", txtLoginEmail.Text.Trim(), ds.Tables[0].Rows[0]["zid"].ToString());
HttpCookie aCookie = new HttpCookie("ckziddu");
aCookie.Values.Add("ziddu", Ziddu.Utility.GetEncryptValue(value));
aCookie.Expires = DateTime.Now.AddMinutes(10);
Response.Cookies.Add(aCookie);
Response.Redirect("~/user/receive.aspx");
}
else if (ds.Tables[0].Rows[0]["verified"].ToString().ToLower() == "n" && ds.Tables[0].Rows[0]["STATUS"].ToString() == "1")
{
lblMessage.Text = "Thanks for signing up, To access our platform,<br/>Please verify your email address from your email";
}
else if (ds.Tables[0].Rows[0]["STATUS"].ToString() == "2")
{
lblMessage.Text = "Your account has been restricted or temporarily suspended";
}
else
{
lblMessage.Text = "Please register/login";
}
}
else
{
lblMessage.Text = "Authentication Failed";
}
}
catch (Exception ex)
{
lblMessage.Text = "Invalid input, Please try again";
}
}
public List <DevHouseSysTypeModel> GetHouseSystype(string strWhere)
{
return(MySQLHelper.ExecuteToList <DevHouseSysTypeModel>(string.Format("select * from devhousesystype {0}", strWhere), null));
}
public DataSet GetHotDic()
{
string sql = "select * from tmo_hotdic";
return(MySQLHelper.Query(sql));
}
/// <summary>
/// 更新中医体质结果
/// </summary>
/// <param name="model"></param>
/// <param name="customerID"></param>
/// <returns></returns>
public bool UpdateByMiniPad(MedicineModel model, string customerID)
{
StringBuilder builder = new StringBuilder();
builder.Append(@"
UPDATE ARCHIVE_MEDICINE_RESULT D SET
Mild=@Mild,Faint=@Faint,Yang=@Yang,
Yin=@Yin, PhlegmDamp=@PhlegmDamp,
Muggy=@Muggy,BloodStasis=@BloodStasis,
QiConstraint=@QiConstraint,
Characteristic=@Characteristic,
MildScore=@MildScore,
FaintScore=@FaintScore,
YangsCore=@YangsCore,
YinScore=@YinScore,
PhlegmdampScore=@PhlegmdampScore,
MuggyScore=@MuggyScore,
BloodStasisScore=@BloodStasisScore,
QiConstraintScore=@QiConstraintScore,
CharacteristicScore=@CharacteristicScore,
MildAdvising=@MildAdvising,
FaintAdvising=@FaintAdvising,
YangAdvising=@YangAdvising,
YinAdvising=@YinAdvising,
PhlegmdampAdvising=@PhlegmdampAdvising,
MuggyAdvising=@MuggyAdvising,
BloodStasisAdvising=@BloodStasisAdvising,
QiconstraintAdvising=@QiconstraintAdvising,
CharacteristicAdvising=@CharacteristicAdvising,
MildAdvisingEx=@MildAdvisingEx,
FaintAdvisingEx=@FaintAdvisingEx,
YangadvisingEx=@YangadvisingEx,
YinAdvisingEx=@YinAdvisingEx,
PhlegmdampAdvisingEx=@PhlegmdampAdvisingEx,
MuggyAdvisingEx=@MuggyAdvisingEx,
BloodStasisAdvisingEx=@BloodStasisAdvisingEx,
QiconstraintAdvisingEx=@QiconstraintAdvisingEx,
CharacteristicAdvisingEx=@CharacteristicAdvisingEx
WHERE
EXISTS
(
SELECT
D.ID
FROM
ARCHIVE_MEDI_PHYS_DIST med
WHERE D.ID= med.MedicineResultID
AND med.OutKey=@OutKey
) ");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@Mild", model.Mild),
new MySqlParameter("@Faint", model.Faint),
new MySqlParameter("@Yang", model.Yang),
new MySqlParameter("@Yin", model.Yin),
new MySqlParameter("@PhlegmDamp", model.PhlegmDamp),
new MySqlParameter("@Muggy", model.Muggy),
new MySqlParameter("@BloodStasis", model.BloodStasis),
new MySqlParameter("@QiConstraint", model.QiConstraint),
new MySqlParameter("@Characteristic", model.Characteristic),
new MySqlParameter("@MildScore", model.MildScore),
new MySqlParameter("@FaintScore", model.FaintScore),
new MySqlParameter("@YangsCore", model.YangsCore),
new MySqlParameter("@YinScore", model.YinScore),
new MySqlParameter("@PhlegmdampScore", model.PhlegmdampScore),
new MySqlParameter("@MuggyScore", model.MuggyScore),
new MySqlParameter("@BloodStasisScore", model.BloodStasisScore),
new MySqlParameter("@QiConstraintScore", model.QiConstraintScore),
new MySqlParameter("@CharacteristicScore", model.CharacteristicScore),
new MySqlParameter("@MildAdvising", model.MildAdvising),
new MySqlParameter("@FaintAdvising", model.FaintAdvising),
new MySqlParameter("@YangAdvising", model.YangAdvising),
new MySqlParameter("@YinAdvising", model.YinAdvising),
new MySqlParameter("@PhlegmdampAdvising", model.PhlegmdampAdvising),
new MySqlParameter("@MuggyAdvising", model.MuggyAdvising),
new MySqlParameter("@BloodStasisAdvising", model.BloodStasisAdvising),
new MySqlParameter("@QiconstraintAdvising", model.QiconstraintAdvising),
new MySqlParameter("@CharacteristicAdvising", model.QiconstraintAdvising),
new MySqlParameter("@MildAdvisingEx", model.MildAdvisingEx),
new MySqlParameter("@FaintAdvisingEx", model.FaintAdvisingEx),
new MySqlParameter("@YangadvisingEx", model.YangadvisingEx),
new MySqlParameter("@YinAdvisingEx", model.YinAdvisingEx),
new MySqlParameter("@PhlegmdampAdvisingEx", model.PhlegmdampAdvisingEx),
new MySqlParameter("@MuggyAdvisingEx", model.MuggyAdvisingEx),
new MySqlParameter("@BloodStasisAdvisingEx", model.BloodStasisAdvisingEx),
new MySqlParameter("@QiconstraintAdvisingEx", model.QiconstraintAdvisingEx),
new MySqlParameter("@CharacteristicAdvisingEx", model.CharacteristicAdvisingEx),
new MySqlParameter("@OutKey", customerID)
};
return(MySQLHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
}
public int GetMaxId()
{
return(MySQLHelper.GetMaxID("ID", "GRAVIDA_BASEINFO"));
}
/// <summary>
/// 获取检测数据
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public DataSet GetMonitorData(DataTable dtQuery, DataSet combine = null)
{
DataTable tb_combine = new DataTable("combine")
{
Columns = { { "mt_code", typeof(string) } }
};
if (combine != null && combine.Tables.Contains("combine"))
{
tb_combine = combine.Tables["combine"];
}
DataRow dr = dtQuery.Rows[0];
int NowPage = dr.GetDataRowIntValue("now_page", 1);
int PageSize = dr.GetDataRowIntValue("page_size");
string userID = dr["user_id"].ToString();
string mt_code = dr["mt_code"].ToString();
string mt_time = dr["mt_time"].ToString();
if (mt_code == "99")
{
if (tb_combine.Rows.Count == 0)
{
mt_code = "100";
tb_combine.Rows.Add(tb_combine.NewRow());
tb_combine.Rows[0]["mt_code"] = "101";
}
else
{
mt_code = tb_combine.Rows[0]["mt_code"].ToString();
tb_combine.Rows.RemoveAt(0);
}
}
string wherestr = " and a.mt_code='" + mt_code + "' ";
string sql = "select MAX(mt_time) as max_time from tmo_monitor where user_id='" + userID + "' and mt_code='" + mt_code + "'";
if (mt_time != string.Empty && mt_time != "0")
{
DateTime maxTime = DateTime.Now;
object ot = MySQLHelper.QuerySingle(sql);
if (ot != null)
{
maxTime = Convert.ToDateTime(ot);
}
string time = "";
if (mt_time == "1")
{
time = maxTime.AddDays(-3).ToString();
}
if (mt_time == "2")
{
time = maxTime.AddMonths(-1).ToString();
}
if (mt_time == "3")
{
time = maxTime.AddMonths(-3).ToString();
}
if (mt_time == "4")
{
time = maxTime.AddYears(-1).ToString();
}
if (time != "")
{
wherestr += "and a.mt_time>='" + time + "' ";
}
}
StringBuilder strSql = new StringBuilder();
StringBuilder strWhere = new StringBuilder();
StringBuilder groupStr = new StringBuilder();
strSql.Append(
"select id,a.mt_code,user_id,mt_valueint,mt_normalrange, mt_valuefloat,mt_valuetext,mt_isnormal,mt_time, mt_timestamp,a.input_time,mt_unit from");
strWhere.Append(" tmo_monitor as a LEFT JOIN tmo_dicmonitor as b on a.mt_code=b.mt_code where user_id='" + userID + "'" + wherestr);
groupStr.Append(" order by mt_time desc ");
DataSet dsSel = tmoCommonDal.GetPagingData(strSql, strWhere, groupStr.ToString(), PageSize, NowPage);
if (dsSel != null && combine != null)
{
if (combine.Tables.Contains("dt"))
{
foreach (DataRow row in combine.Tables["dt"].Rows)
{
dsSel.Tables["dt"].Rows.Add(row.ItemArray);
}
}
if (combine.Tables.Contains("Count"))
{
dsSel.Tables["Count"].Rows[0]["totalRowCount"] = dsSel.Tables["Count"].Rows[0].GetDataRowIntValue("totalRowCount") +
combine.Tables["Count"].Rows[0].GetDataRowIntValue("totalRowCount");
}
}
if (tb_combine.Rows.Count > 0)
{
if (dsSel == null)
{
dsSel = new DataSet("tmodata");
}
dsSel.Tables.Add(tb_combine);
dsSel = GetMonitorData(dtQuery, dsSel);
}
return(dsSel);
}
public bool UpdateServer(WomenGravidaBaseInfoModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("update GRAVIDA_BASEINFO set ");
builder.Append("CustomerID=@CustomerID,");
builder.Append("RecordID=@RecordID,");
builder.Append("IDCardNo=@IDCardNo,");
builder.Append("Name=@Name,");
builder.Append("Age=@Age,");
builder.Append("Culture=@Culture,");
builder.Append("Job=@Job,");
builder.Append("Address=@Address,");
builder.Append("Nation=@Nation,");
builder.Append("Birthday=@Birthday,");
builder.Append("Living=@Living,");
builder.Append("Phone=@Phone,");
builder.Append("HealthResot=@HealthResot,");
builder.Append("TownName=@TownName,");
builder.Append("VillageName=@VillageName,");
builder.Append("PwPhone=@PwPhone,");
builder.Append("HusbandName=@HusbandName,");
builder.Append("HusbandPhone=@HusbandPhone,");
builder.Append("CurrentUnit=@CurrentUnit,");
builder.Append("CreateUnit=@CreateUnit,");
builder.Append("CreatedBy=@CreatedBy,");
builder.Append("CreatedDate=@CreatedDate,");
builder.Append("LastUpdateBy=@LastUpdateBy,");
builder.Append("LastUpdateDate=@LastUpdateDate,");
builder.Append("IsDel=@IsDel,");
builder.Append("HouseholdTown=@HouseholdTown,");
builder.Append("HouseholdVillage=@HouseholdVillage,");
builder.Append("AddrTown=@AddrTown,");
builder.Append("AddrVillage=@AddrVillage,");
builder.Append("AddrPhone=@AddrPhone,");
builder.Append("WorkUnit=@WorkUnit,");
builder.Append("UnitPhone=@UnitPhone,");
builder.Append("HusbandAge=@HusbandAge,");
builder.Append("HusbandCulture=@HusbandCulture,");
builder.Append("HusbandNation=@HusbandNation,");
builder.Append("HusbandUnit=@HusbandUnit,");
builder.Append("HbUnitPhone=@HbUnitPhone,");
builder.Append("HusbandJob=@HusbandJob,");
builder.Append("CardNum=@CardNum,");
builder.Append("CreatePhone=@CreatePhone,");
builder.Append("CreateDate=@CreateDate");
builder.Append(" where IDCardNo=@IDCardNo");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@CustomerID", MySqlDbType.String),
new MySqlParameter("@RecordID", MySqlDbType.String, 17),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@Name", MySqlDbType.String),
new MySqlParameter("@Age", MySqlDbType.Decimal),
new MySqlParameter("@Culture", MySqlDbType.String, 1),
new MySqlParameter("@Job", MySqlDbType.String, 1),
new MySqlParameter("@Address", MySqlDbType.String),
new MySqlParameter("@Nation", MySqlDbType.String, 1),
new MySqlParameter("@Birthday", MySqlDbType.Date),
new MySqlParameter("@Living", MySqlDbType.String),
new MySqlParameter("@Phone", MySqlDbType.String),
new MySqlParameter("@HealthResot", MySqlDbType.String),
new MySqlParameter("@TownName", MySqlDbType.String),
new MySqlParameter("@VillageName", MySqlDbType.String),
new MySqlParameter("@PwPhone", MySqlDbType.String),
new MySqlParameter("@HusbandName", MySqlDbType.String),
new MySqlParameter("@HusbandPhone", MySqlDbType.String),
new MySqlParameter("@CurrentUnit", MySqlDbType.Decimal),
new MySqlParameter("@CreateUnit", MySqlDbType.Decimal),
new MySqlParameter("@CreatedBy", MySqlDbType.Decimal),
new MySqlParameter("@CreatedDate", MySqlDbType.Date),
new MySqlParameter("@LastUpdateBy", MySqlDbType.Decimal),
new MySqlParameter("@LastUpdateDate", MySqlDbType.Date),
new MySqlParameter("@IsDel", MySqlDbType.String, 1),
new MySqlParameter("@HouseholdTown", MySqlDbType.String),
new MySqlParameter("@HouseholdVillage", MySqlDbType.String),
new MySqlParameter("@AddrTown", MySqlDbType.String),
new MySqlParameter("@AddrVillage", MySqlDbType.String),
new MySqlParameter("@AddrPhone", MySqlDbType.String),
new MySqlParameter("@WorkUnit", MySqlDbType.String),
new MySqlParameter("@UnitPhone", MySqlDbType.String),
new MySqlParameter("@HusbandAge", MySqlDbType.Decimal),
new MySqlParameter("@HusbandCulture", MySqlDbType.String),
new MySqlParameter("@HusbandNation", MySqlDbType.String),
new MySqlParameter("@HusbandUnit", MySqlDbType.String),
new MySqlParameter("@HbUnitPhone", MySqlDbType.String),
new MySqlParameter("@HusbandJob", MySqlDbType.String, 2),
new MySqlParameter("@CardNum", MySqlDbType.String),
new MySqlParameter("@CreatePhone", MySqlDbType.String),
new MySqlParameter("@CreateDate", MySqlDbType.Date),
// new MySqlParameter("@ID", MySqlDbType.Int32, 8)
};
cmdParms[0].Value = model.CustomerID;
cmdParms[1].Value = model.RecordID;
cmdParms[2].Value = model.IDCardNo;
cmdParms[3].Value = model.Name;
cmdParms[4].Value = model.Age;
cmdParms[5].Value = model.Culture;
cmdParms[6].Value = model.Job;
cmdParms[7].Value = model.Address;
cmdParms[8].Value = model.Nation;
cmdParms[9].Value = model.Birthday;
cmdParms[10].Value = model.Living;
cmdParms[11].Value = model.Phone;
cmdParms[12].Value = model.HealthResot;
cmdParms[13].Value = model.TownName;
cmdParms[14].Value = model.VillageName;
cmdParms[15].Value = model.PwPhone;
cmdParms[16].Value = model.HusbandName;
cmdParms[17].Value = model.HusbandPhone;
cmdParms[18].Value = model.CurrentUnit;
cmdParms[19].Value = model.CreateUnit;
cmdParms[20].Value = model.CreatedBy;
cmdParms[21].Value = model.CreatedDate;
cmdParms[22].Value = model.LastUpdateBy;
cmdParms[23].Value = model.LastUpdateDate;
cmdParms[24].Value = model.IsDel;
cmdParms[25].Value = model.HouseholdTown;
cmdParms[26].Value = model.HouseholdVillage;
cmdParms[27].Value = model.AddrTown;
cmdParms[28].Value = model.AddrVillage;
cmdParms[29].Value = model.AddrPhone;
cmdParms[30].Value = model.WorkUnit;
cmdParms[31].Value = model.UnitPhone;
cmdParms[32].Value = model.HusbandAge;
cmdParms[33].Value = model.HusbandCulture;
cmdParms[34].Value = model.HusbandNation;
cmdParms[35].Value = model.HusbandUnit;
cmdParms[36].Value = model.HbUnitPhone;
cmdParms[37].Value = model.HusbandJob;
cmdParms[38].Value = model.CardNum;
cmdParms[39].Value = model.CreatePhone;
cmdParms[40].Value = model.CreateDate;
// cmdParms[41].Value = model.ID;
return(MySQLHelper.ExecuteSqlServer(builder.ToString(), cmdParms) > 0);
}
static void ReMoMain()
{
List <TempZoneID> temp4 = DtToList <TempZoneID> .ConvertToModel(MySQLHelper.Query("select id as zoneid from Temp where type=4").Tables[0]);
DataTable dtid = MySQLHelper.Query("select min(id),max(id) from zones").Tables[0];
long min = Convert.ToInt32(dtid.Rows[0][0]);
long max = Convert.ToInt32(dtid.Rows[0][1]);
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();//开始计时
long index = max;
do
{
DataTable dt = MySQLHelper.Query("SELECT z.id,z.zone,z.userid,CASE when d.maxfensheng<1 then 0 else 1 end as level,z.nsstate from zones as z left join domainlevel as d on z.DomainLevel=d.levelvalue where z.userid<>348672 and z.Active='Y' and z.ForceStop='N' and z.id between " + (index - 1000) + " and " + index + "").Tables[0];
List <zones> zonesList = DtToList <zones> .ConvertToModel(dt);
List <zones> zl = new List <zones>();
foreach (zones z in zonesList)
{
if (temp4.FindAll(tz => tz.zoneid == z.id).Count == 0)
{
zl.Add(z);
}
}
Console.WriteLine("Data Filter;Use time={0};", watch.ElapsedMilliseconds);
List <ZonesSimple> zslist = new List <ZonesSimple>();
List <AuthoritiesSimple>[] ala = new List <AuthoritiesSimple>[16] {
new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>(), new List <AuthoritiesSimple>()
};
List <DnsRecordsSimple>[] dla = new List <DnsRecordsSimple>[16] {
new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>(), new List <DnsRecordsSimple>()
};
foreach (zones z in zl)
{
ZonesSimple zs = Row2ZoneSimple(z);
zslist.Add(zs);
string rrcol = StringHelper.CalculateMD5Hash(zs.domain).ToLower().Substring(0, 1);
//
DataSet ds = MySQLHelper.Query("select id,zone,host,data,type,ttl,mbox,serial,refresh,retry,expire,minimum ," + z.userid + " as userid from authorities where zoneid=" + z.id + " order by type;" +
"select id,zoneid,zone,host,type,data,ttl,view,mx_priority,userid from dnsrecords where active='Y'and type<>'PTR' and zoneid= " + z.id + ";");
DataTable adt = ds.Tables[0];
DataTable rdt = ds.Tables[1];
//
List <authorities> alist = new List <authorities>();
List <AuthoritiesSimple> aslist = new List <AuthoritiesSimple>();
if (adt.Rows.Count > 0)
{
aslist = Row2Authorities(DtToList <authorities> .ConvertToModel(adt));
}
else
{
break;
}
//
List <dnsrecords> dlist = DtToList <dnsrecords> .ConvertToModel(rdt);
List <DnsRecordsSimple> dslist = new List <DnsRecordsSimple>();
List <dnsrecords> wrongList = new List <dnsrecords>();
foreach (dnsrecords d in dlist)
{
if (CheckRecordData(d.data, d.type, d.view, d.host) && CheckRecordHost(d.host, d.type))
{
dslist.Add(Row2DnsRecords(d));
}
else
{
//记录违法的records
wrongList.Add(d);
}
}
}
index = index - 1001;
Console.WriteLine("Mongo Insert Success; Use time={0};", watch.ElapsedMilliseconds);
DnsUpdateInsert(zl);
Console.WriteLine("UpdateQueue Insert Success; Use time={0};", watch.ElapsedMilliseconds);
Console.WriteLine("min={0};max={1};index={2};", min, max, index);
Console.WriteLine("==============================================");
} while (index < max);
Console.WriteLine("End min={0};max={1};index={2};use time {3}", min, max, index, watch.ElapsedMilliseconds);
watch.Stop();//停止计时
}
/// <summary>
/// 得到最大ID
/// </summary>
public int GetMaxId()
{
return(MySQLHelper.GetMaxID("standard_id", "eccm_standard"));
}
/// <summary>
/// 增加一个新用户
/// </summary>
public bool Add(string maincmdstr, List <string> subcmdlist)
{
return(MySQLHelper.ExecuteMasterslaveByTran(maincmdstr, subcmdlist) > 0 ? true : false);
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(EccmInspectionOrderModel model, string equCodes)
{
List <string> cmdlist = new List <string>();
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into eccm_inspection_order(");
strSql.Append("order_sn,order_type,order_time,term_time,order_finish_time,community_id,order_stats,uid_dispatch,ext1,ext2,ext3,uid)");
strSql.Append(" values (");
strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')", model.order_sn, model.order_type, model.order_time, model.term_time, model.order_finish_time, model.community_id, model.order_stats, model.uid_dispatch, model.ext1, model.ext2, model.ext3, model.uid);
//MySqlParameter[] parameters = {
// new MySqlParameter("@order_sn", MySqlDbType.VarChar,32),
// new MySqlParameter("@order_type", MySqlDbType.Int32,11),
// new MySqlParameter("@order_time", MySqlDbType.DateTime),
// new MySqlParameter("@term_time", MySqlDbType.DateTime),
// new MySqlParameter("@order_finish_time", MySqlDbType.DateTime),
// new MySqlParameter("@community_id", MySqlDbType.Int32,11),
// new MySqlParameter("@order_stats", MySqlDbType.Int32,11),
// new MySqlParameter("@uid_dispatch", MySqlDbType.Int32,11),
// new MySqlParameter("@ext1", MySqlDbType.VarChar,50),
// new MySqlParameter("@ext2", MySqlDbType.VarChar,50),
// new MySqlParameter("@ext3", MySqlDbType.VarChar,50),
// new MySqlParameter("@uid", MySqlDbType.Int32)};
//parameters[0].Value = model.order_sn;
//parameters[1].Value = model.order_type;
//parameters[2].Value = model.order_time;
//parameters[3].Value = model.term_time;
//parameters[4].Value = model.order_finish_time;
//parameters[5].Value = model.community_id;
//parameters[6].Value = model.order_stats;
//parameters[7].Value = model.uid_dispatch;
//parameters[8].Value = model.ext1;
//parameters[9].Value = model.ext2;
//parameters[10].Value = model.ext3;
//parameters[11].Value = model.uid;
cmdlist.Add(strSql.ToString());
StringBuilder strSql2 = new StringBuilder();
strSql2.Append(" insert into eccm_order_device_standard(order_sn, equCode, device_standard, order_device_standard_type) ");
strSql2.AppendFormat(" select '{0}',ei.equCode,s.inspection_standard,1 FROM equipmentinfo ei ", model.order_sn);
strSql2.Append(" left join eccm_device_relation_standard drs ON ei.device_type_code = drs.device_type_code ");
strSql2.Append(" left join eccm_standard s ON drs.standard_id = s.standard_id and s.standard_type = 1 ");
StringBuilder codes = new StringBuilder();
foreach (string d in equCodes.Split(','))
{
codes.AppendFormat("'{0}',", d);
}
codes.Remove(codes.Length - 1, 1);
strSql2.AppendFormat(" where ei.equCode in ({0}) ", codes);
cmdlist.Add(strSql2.ToString());
int rows = MySQLHelper.ExecuteSqlByTran(cmdlist);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 根据查询条件返回结果
/// </summary>
public List <UserInfoModel> GetList(string strWhere)
{
return(MySQLHelper.ExecuteToList <UserInfoModel>("select * from userinfo where " + strWhere, null));
}
/// <summary>
/// 得到最大ID
/// </summary>
public int GetMaxId()
{
return(MySQLHelper.GetMaxID("order_id", "eccm_inspection_order"));
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/json";
var result = new ApiResult();
if (context.Request.HttpMethod != "POST")
{
result.code = 0x01;
result.message = "需要使用POST请求";
context.Response.Write(JsonConvert.SerializeObject(result));
return;
}
var phone = context.Request.Form["phone"];
var code = context.Request.Form["code"];
if (phone == null || code == null)
{
result.code = 0x03;
result.message = "参数错误";
context.Response.Write(JsonConvert.SerializeObject(result));
return;
}
var returnStruct = new ReturnStruct();
var sql = "select count(*) from user where phone = ?p;";
var ret = int.Parse(MySQLHelper.ExecuteScalar(sql, new MySqlParameter("?p", phone)).ToString());
// 存在于user表中
if (ret == 1)
{
returnStruct.firstLogin = false;
sql = "select id, name from user where phone = ?p;";
var ds = MySQLHelper.ExecuteDataSet(sql, new MySqlParameter("?p", phone));
returnStruct.userId = int.Parse(ds.Tables[0].Rows[0].ItemArray[0].ToString());
returnStruct.name = ds.Tables[0].Rows[0].ItemArray[1].ToString();
context.Session["uid"] = returnStruct.userId;
result.message = "成功";
result.data = returnStruct;
}
else
{
sql = "select count(*) from owner where phone = ?p;";
ret = int.Parse(MySQLHelper.ExecuteScalar(sql, new MySqlParameter("?p", phone)).ToString());
// 存在于owner表中
if (ret == 1)
{
returnStruct.firstLogin = true;
returnStruct.userId = 0;
sql = "select name, id from owner where phone = ?p;";
var ds = MySQLHelper.ExecuteDataSet(sql, new MySqlParameter("?p", phone));
returnStruct.name = ds.Tables[0].Rows[0].ItemArray[0].ToString();
context.Session["oid"] = ds.Tables[0].Rows[0].ItemArray[1].ToString();
result.message = "成功";
result.data = returnStruct;
}
else
{
result.message = "成功";
result.data = null;
}
}
context.Response.Write(JsonConvert.SerializeObject(result));
}
public int Add(RecordsMedicineResultModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("insert into ARCHIVE_MEDICINE_RESULT(");
builder.Append("PhysicalID,MedicineID,Mild,Faint,Yang,Yin,PhlegmDamp,Muggy,BloodStasis,QIconStraint,Characteristic,");
builder.Append("MildScore,FaintScore,YangsCore,YinScore,PhlegmdampScore,MuggyScore,BloodStasisScore,QiConstraintScore,");
builder.Append("CharacteristicScore,MildAdvising,FaintAdvising,YangAdvising,YinAdvising,PhlegmdampAdvising,MuggyAdvising,");
builder.Append("BloodStasisAdvising,QiconstraintAdvising,CharacteristicAdvising,MildAdvisingEx,FaintAdvisingEx,YangadvisingEx,");
builder.Append("YinAdvisingEx,PhlegmdampAdvisingEx,MuggyAdvisingEx,BloodStasisAdvisingEx,QiconstraintAdvisingEx,");
builder.Append("CharacteristicAdvisingEx,IsDel,IDCardNo,EffectAssess,Satisfy)");
builder.Append(" values (");
builder.Append("@PhysicalID,@MedicineID,@Mild,@Faint,@Yang,@Yin,@PhlegmDamp,@Muggy,@BloodStasis,@QIconStraint,@Characteristic,");
builder.Append("@MildScore,@FaintScore,@YangsCore,@YinScore,@PhlegmdampScore,@MuggyScore,@BloodStasisScore,@QiConstraintScore,");
builder.Append("@CharacteristicScore,@MildAdvising,@FaintAdvising,@YangAdvising,@YinAdvising,@PhlegmdampAdvising,@MuggyAdvising,");
builder.Append("@BloodStasisAdvising,@QiconstraintAdvising,@CharacteristicAdvising,@MildAdvisingEx,@FaintAdvisingEx,@YangadvisingEx,");
builder.Append("@YinAdvisingEx,@PhlegmdampAdvisingEx,@MuggyAdvisingEx,@BloodStasisAdvisingEx,@QiconstraintAdvisingEx,");
builder.Append("@CharacteristicAdvisingEx,@IsDel,@IDCardNo,EffectAssess,Satisfy)");
builder.Append(" ;select @@IDENTITY");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@PhysicalID", MySqlDbType.String, 100),
new MySqlParameter("@MedicineID", MySqlDbType.Decimal),
new MySqlParameter("@Mild", MySqlDbType.String, 1),
new MySqlParameter("@Faint", MySqlDbType.String, 1),
new MySqlParameter("@Yang", MySqlDbType.String, 1),
new MySqlParameter("@Yin", MySqlDbType.String, 1),
new MySqlParameter("@PhlegmDamp", MySqlDbType.String, 1),
new MySqlParameter("@Muggy", MySqlDbType.String, 1),
new MySqlParameter("@BloodStasis", MySqlDbType.String, 1),
new MySqlParameter("@QIconStraint", MySqlDbType.String, 1),
new MySqlParameter("@Characteristic", MySqlDbType.String, 1),
new MySqlParameter("@MildScore", MySqlDbType.Decimal),
new MySqlParameter("@FaintScore", MySqlDbType.Decimal),
new MySqlParameter("@YangsCore", MySqlDbType.Decimal),
new MySqlParameter("@YinScore", MySqlDbType.Decimal),
new MySqlParameter("@PhlegmdampScore", MySqlDbType.Decimal),
new MySqlParameter("@MuggyScore", MySqlDbType.Decimal),
new MySqlParameter("@BloodStasisScore", MySqlDbType.Decimal),
new MySqlParameter("@QiConstraintScore", MySqlDbType.Decimal),
new MySqlParameter("@CharacteristicScore", MySqlDbType.Decimal),
new MySqlParameter("@MildAdvising", MySqlDbType.String, 100),
new MySqlParameter("@FaintAdvising", MySqlDbType.String, 100),
new MySqlParameter("@YangAdvising", MySqlDbType.String, 100),
new MySqlParameter("@YinAdvising", MySqlDbType.String, 100),
new MySqlParameter("@PhlegmdampAdvising", MySqlDbType.String, 100),
new MySqlParameter("@MuggyAdvising", MySqlDbType.String, 100),
new MySqlParameter("@BloodStasisAdvising", MySqlDbType.String, 100),
new MySqlParameter("@QiconstraintAdvising", MySqlDbType.String, 100),
new MySqlParameter("@CharacteristicAdvising", MySqlDbType.String, 100),
new MySqlParameter("@MildAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@FaintAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@YangadvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@YinAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@PhlegmdampAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@MuggyAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@BloodStasisAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@QiconstraintAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@CharacteristicAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@IsDel", MySqlDbType.String, 1),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@EffectAssess", MySqlDbType.String, 10),
new MySqlParameter("@Satisfy", MySqlDbType.String, 10)
};
cmdParms[0].Value = model.PhysicalID;
cmdParms[1].Value = model.MedicineID;
cmdParms[2].Value = model.Mild;
cmdParms[3].Value = model.Faint;
cmdParms[4].Value = model.Yang;
cmdParms[5].Value = model.Yin;
cmdParms[6].Value = model.PhlegmDamp;
cmdParms[7].Value = model.Muggy;
cmdParms[8].Value = model.BloodStasis;
cmdParms[9].Value = model.QiConstraint;
cmdParms[10].Value = model.Characteristic;
cmdParms[11].Value = model.MildScore;
cmdParms[12].Value = model.FaintScore;
cmdParms[13].Value = model.YangsCore;
cmdParms[14].Value = model.YinScore;
cmdParms[15].Value = model.PhlegmdampScore;
cmdParms[16].Value = model.MuggyScore;
cmdParms[17].Value = model.BloodStasisScore;
cmdParms[18].Value = model.QiConstraintScore;
cmdParms[19].Value = model.CharacteristicScore;
cmdParms[20].Value = model.MildAdvising;
cmdParms[21].Value = model.FaintAdvising;
cmdParms[22].Value = model.YangAdvising;
cmdParms[23].Value = model.YinAdvising;
cmdParms[24].Value = model.PhlegmdampAdvising;
cmdParms[25].Value = model.MuggyAdvising;
cmdParms[26].Value = model.BloodStasisAdvising;
cmdParms[27].Value = model.QiconstraintAdvising;
cmdParms[28].Value = model.CharacteristicAdvising;
cmdParms[29].Value = model.MildAdvisingEx;
cmdParms[30].Value = model.FaintAdvisingEx;
cmdParms[31].Value = model.YangadvisingEx;
cmdParms[32].Value = model.YinAdvisingEx;
cmdParms[33].Value = model.PhlegmdampAdvisingEx;
cmdParms[34].Value = model.MuggyAdvisingEx;
cmdParms[35].Value = model.BloodStasisAdvisingEx;
cmdParms[36].Value = model.QiconstraintAdvisingEx;
cmdParms[37].Value = model.CharacteristicAdvisingEx;
cmdParms[38].Value = model.IsDel;
cmdParms[39].Value = model.IDCardNo;
cmdParms[40].Value = model.EffectAssess;
cmdParms[41].Value = model.Satisfy;
object single = MySQLHelper.GetSingle(builder.ToString(), cmdParms);
if (single == null)
{
return(0);
}
return(Convert.ToInt32(single));
}
/// <summary>
/// 获取所有激活的城市信息
/// </summary>
/// <returns></returns>
public List <CityInfoModel> GetAllCity()
{
return(MySQLHelper.ExecuteToList <CityInfoModel>("select * from eccm_city_info where isDel=0", null));
}
public bool Update(RecordsMedicineResultModel model)
{
StringBuilder builder = new StringBuilder();
builder.Append("update ARCHIVE_MEDICINE_RESULT set ");
builder.Append("PhysicalID=@PhysicalID,");
builder.Append("MedicineID=@MedicineID,");
builder.Append("Mild=@Mild,");
builder.Append("Faint=@Faint,");
builder.Append("Yang=@Yang,");
builder.Append("Yin=@Yin,");
builder.Append("PhlegmDamp=@PhlegmDamp,");
builder.Append("Muggy=@Muggy,");
builder.Append("BloodStasis=@BloodStasis,");
builder.Append("QiConstraint=@QiConstraint,");
builder.Append("Characteristic=@Characteristic,");
builder.Append("MildScore=@MildScore,");
builder.Append("FaintScore=@FaintScore,");
builder.Append("YangsCore=@YangsCore,");
builder.Append("YinScore=@YinScore,");
builder.Append("PhlegmdampScore=@PhlegmdampScore,");
builder.Append("MuggyScore=@MuggyScore,");
builder.Append("BloodStasisScore=@BloodStasisScore,");
builder.Append("QiConstraintScore=@QiConstraintScore,");
builder.Append("CharacteristicScore=@CharacteristicScore,");
builder.Append("MildAdvising=@MildAdvising,");
builder.Append("FaintAdvising=@FaintAdvising,");
builder.Append("YangAdvising=@YangAdvising,");
builder.Append("YinAdvising=@YinAdvising,");
builder.Append("PhlegmdampAdvising=@PhlegmdampAdvising,");
builder.Append("MuggyAdvising=@MuggyAdvising,");
builder.Append("BloodStasisAdvising=@BloodStasisAdvising,");
builder.Append("QiconstraintAdvising=@QiconstraintAdvising,");
builder.Append("CharacteristicAdvising=@CharacteristicAdvising,");
builder.Append("MildAdvisingEx=@MildAdvisingEx,");
builder.Append("FaintAdvisingEx=@FaintAdvisingEx,");
builder.Append("YangadvisingEx=@YangadvisingEx,");
builder.Append("YinAdvisingEx=@YinAdvisingEx,");
builder.Append("PhlegmdampAdvisingEx=@PhlegmdampAdvisingEx,");
builder.Append("MuggyAdvisingEx=@MuggyAdvisingEx,");
builder.Append("BloodStasisAdvisingEx=@BloodStasisAdvisingEx,");
builder.Append("QiconstraintAdvisingEx=@QiconstraintAdvisingEx,");
builder.Append("CharacteristicAdvisingEx=@CharacteristicAdvisingEx ,");
builder.Append("IsDel=@IsDel,");
builder.Append("IDCardNo=@IDCardNo, ");
builder.Append("EffectAssess=@EffectAssess, ");
builder.Append("Satisfy=@Satisfy ");
builder.Append(" where ID=@ID");
MySqlParameter[] cmdParms = new MySqlParameter[] {
new MySqlParameter("@PhysicalID", MySqlDbType.String, 100),
new MySqlParameter("@MedicineID", MySqlDbType.Decimal),
new MySqlParameter("@Mild", MySqlDbType.String, 1),
new MySqlParameter("@Faint", MySqlDbType.String, 1),
new MySqlParameter("@Yang", MySqlDbType.String, 1),
new MySqlParameter("@Yin", MySqlDbType.String, 1),
new MySqlParameter("@PhlegmDamp", MySqlDbType.String, 1),
new MySqlParameter("@Muggy", MySqlDbType.String, 1),
new MySqlParameter("@BloodStasis", MySqlDbType.String, 1),
new MySqlParameter("@QIconStraint", MySqlDbType.String, 1),
new MySqlParameter("@Characteristic", MySqlDbType.String, 1),
new MySqlParameter("@MildScore", MySqlDbType.Decimal),
new MySqlParameter("@FaintScore", MySqlDbType.Decimal),
new MySqlParameter("@YangsCore", MySqlDbType.Decimal),
new MySqlParameter("@YinScore", MySqlDbType.Decimal),
new MySqlParameter("@PhlegmdampScore", MySqlDbType.Decimal),
new MySqlParameter("@MuggyScore", MySqlDbType.Decimal),
new MySqlParameter("@BloodStasisScore", MySqlDbType.Decimal),
new MySqlParameter("@QiConstraintScore", MySqlDbType.Decimal),
new MySqlParameter("@CharacteristicScore", MySqlDbType.Decimal),
new MySqlParameter("@MildAdvising", MySqlDbType.String, 100),
new MySqlParameter("@FaintAdvising", MySqlDbType.String, 100),
new MySqlParameter("@YangAdvising", MySqlDbType.String, 100),
new MySqlParameter("@YinAdvising", MySqlDbType.String, 100),
new MySqlParameter("@PhlegmdampAdvising", MySqlDbType.String, 100),
new MySqlParameter("@MuggyAdvising", MySqlDbType.String, 100),
new MySqlParameter("@BloodStasisAdvising", MySqlDbType.String, 100),
new MySqlParameter("@QiconstraintAdvising", MySqlDbType.String, 100),
new MySqlParameter("@CharacteristicAdvising", MySqlDbType.String, 100),
new MySqlParameter("@MildAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@FaintAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@YangadvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@YinAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@PhlegmdampAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@MuggyAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@BloodStasisAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@QiconstraintAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@CharacteristicAdvisingEx", MySqlDbType.String, 100),
new MySqlParameter("@IsDel", MySqlDbType.String, 1),
new MySqlParameter("@IDCardNo", MySqlDbType.String, 21),
new MySqlParameter("@EffectAssess", MySqlDbType.String, 10),
new MySqlParameter("@Satisfy", MySqlDbType.String, 10),
new MySqlParameter("@ID", MySqlDbType.Int32, 8)
};
cmdParms[0].Value = model.PhysicalID;
cmdParms[1].Value = model.MedicineID;
cmdParms[2].Value = model.Mild;
cmdParms[3].Value = model.Faint;
cmdParms[4].Value = model.Yang;
cmdParms[5].Value = model.Yin;
cmdParms[6].Value = model.PhlegmDamp;
cmdParms[7].Value = model.Muggy;
cmdParms[8].Value = model.BloodStasis;
cmdParms[9].Value = model.QiConstraint;
cmdParms[10].Value = model.Characteristic;
cmdParms[11].Value = model.MildScore;
cmdParms[12].Value = model.FaintScore;
cmdParms[13].Value = model.YangsCore;
cmdParms[14].Value = model.YinScore;
cmdParms[15].Value = model.PhlegmdampScore;
cmdParms[16].Value = model.MuggyScore;
cmdParms[17].Value = model.BloodStasisScore;
cmdParms[18].Value = model.QiConstraintScore;
cmdParms[19].Value = model.CharacteristicScore;
cmdParms[20].Value = model.MildAdvising;
cmdParms[21].Value = model.FaintAdvising;
cmdParms[22].Value = model.YangAdvising;
cmdParms[23].Value = model.YinAdvising;
cmdParms[24].Value = model.PhlegmdampAdvising;
cmdParms[25].Value = model.MuggyAdvising;
cmdParms[26].Value = model.BloodStasisAdvising;
cmdParms[27].Value = model.QiconstraintAdvising;
cmdParms[28].Value = model.CharacteristicAdvising;
cmdParms[29].Value = model.MildAdvisingEx;
cmdParms[30].Value = model.FaintAdvisingEx;
cmdParms[31].Value = model.YangadvisingEx;
cmdParms[32].Value = model.YinAdvisingEx;
cmdParms[33].Value = model.PhlegmdampAdvisingEx;
cmdParms[34].Value = model.MuggyAdvisingEx;
cmdParms[35].Value = model.BloodStasisAdvisingEx;
cmdParms[36].Value = model.QiconstraintAdvisingEx;
cmdParms[37].Value = model.CharacteristicAdvisingEx;
cmdParms[38].Value = model.IsDel;
cmdParms[39].Value = model.IDCardNo;
cmdParms[40].Value = model.EffectAssess;
cmdParms[41].Value = model.Satisfy;
cmdParms[42].Value = model.ID;
return(MySQLHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
}
/// <summary>
/// 加载所有表
/// </summary>
/// <param name="node"></param>
private void LoadingTables(TreeNode node)
{
var type = (DataBaseType)node.Tag;
switch (type)
{
case DataBaseType.SQLServer:
var sql = @"select * from
(SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end
FROM
syscolumns a
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0) t
where t.表名!='' order by t.表名" ;
var dt = SQLServerHelper.QueryDataTable(node.Name, sql);
if (dt?.Rows.Count > 0)
{
node.Nodes.Clear();
foreach (DataRow item in dt.Rows)
{
var nodeItem = new TreeNode
{
Text = item["表名"].ToString(),
Name = item["表说明"].ToString(),
Tag = type
};
node.Nodes.Add(nodeItem);
}
dt.Rows.Clear();
dt.Clear();
dt.Dispose();
dt = null;
GC.Collect();
}
break;
case DataBaseType.MySQL:
var database = node.Name.Substring(node.Name.IndexOf("Database=") + 9, node.Name.IndexOf(";port=") - node.Name.IndexOf("Database=") - 9);
var sql1 = $"SELECT TABLE_NAME as 表名, Table_Comment as 表说明 FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '{database}'";
var dt1 = MySQLHelper.QueryDataTable(node.Name, sql1);
if (dt1?.Rows.Count > 0)
{
node.Nodes.Clear();
foreach (DataRow item in dt1.Rows)
{
var nodeItem = new TreeNode
{
Text = item["表名"].ToString(),
Name = item["表说明"].ToString(),
Tag = type
};
node.Nodes.Add(nodeItem);
}
dt1.Rows.Clear();
dt1.Clear();
dt1.Dispose();
dt1 = null;
GC.Collect();
}
break;
case DataBaseType.Oracler:
break;
case DataBaseType.SQLite:
break;
case DataBaseType.PostgreSQL:
break;
default:
break;
}
}
/// <summary>
///
/// </summary>
/// <param name="query">Stored procedure e.g. ARStored_1</param>
/// <param name="date">Transaction date e.g. '2020-12-12'</param>
/// <param name="transTypeRC">Transtype under RC</param>
/// <param name="branchCodeNumber">Branch code number</param>
public void Extract(List <string> query, string date, List <string> transTypeRC, string branchCodeNumber)
{
try
{
reference = db.GetReferenceNumbers(date, date);
foreach (var item in query)
{
var parameter = item.Split('_');
string[] scripts = storedProcedures[parameter[0]];
string[] byBatch = byBatchGeneration.Split(',');
if (byBatchGeneration.Contains(item.Substring(0, 2)))
{
//if trans type generation is by batch.
//if (item == "IP_SI")
//{
// for (int i = 0; i <= scripts.Length - 1; i++)
// {
// string querys = string.Empty;
// if (i == 4)
// break;
// else
// querys = $"CALL IPStored_{i + 1}('{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}')";
// db = new MySQLHelper();
// db.GetExtract(querys);
// }
// GetZip(date);
//}
//else if(item == "IP_OR")
//{
// for (int i = 0; i <= scripts.Length - 1; i++)
// {
// string querys = string.Empty;
// if (i == 3)
// {
// querys = $"CALL IPStored_{i + 1}('{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}')";
// db = new MySQLHelper();
// db.GetExtract(querys);
// }
// }
// GetZip(date);
//}
//Paiwi - service type
if (item == "AR_WS")
{
string[] paiwi = storedProcedures["WS"];
for (int i = 0; i < reference.Count; i++)
{
if (parameter[1] == reference[i].Substring(0, 2))
{
foreach (var querys in paiwi)
{
string queryString = string.Empty;
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating WS - {querys} - {reference[i]} ... ");
queryString = $"CALL {querys} ('WS', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{reference[i]}', '{branchCodeNumber}');";
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating WS - {querys} - {reference[i]} ... ");
}
GetZip(date);
}
}
}
else if (item == "AR_SI" || item == "AR_OL")
{
string[] arIPMM = storedProcedures["AR3"];
string[] arIPNM = storedProcedures["AR4"];
string[] arKNP = storedProcedures["KNP"];
#region AR Member
foreach (var _query in scripts)
{
string queryString = string.Empty;
db = new MySQLHelper();
//if (parameter[1] != "SI" && _query == "ARStored_5")
//{
// break;
//}
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {_query} ... ");
if (_query != "ARStored_4")
{
queryString = $"CALL {_query}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}','{branchCodeNumber}');";
}
//else if (_query == "ARStored_4")
//{
// foreach (var qKnp in arKNP)
// {
// queryString = $"CALL {qKnp}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}','{branchCodeNumber}');";
// }
//}
else
{
break;
}
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {_query} ... ");
}
////*************************************************************************Kanego -> Member*****************************************************************************
foreach (var kng in arKNP)
{
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {kng} ... ");
db.GetExtract($"CALL {kng}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}','{branchCodeNumber}');");
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {kng} ... ");
}
////**************************************************************************End Kanego -> Member*************************************************************************
////*************************************************************************IP -> Member*****************************************************************************
foreach (var qq in arIPMM)
{
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {qq} ... ");
db.GetExtract($"CALL {qq}('{parameter[1]}','{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');");
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {qq} ... ");
}
////**************************************************************************End IP -> Member*************************************************************************
#endregion
GetZip(date);
#region AR Non-Member
string[] nonmember = storedProcedures["AR2"];
foreach (var _query in nonmember)
{
string queryString = string.Empty;
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {_query} ... ");
queryString = $"CALL {_query}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');";
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {_query} ... ");
}
////**************************************************************************End Kanego -> Non Member*************************************************************************
//*************************************************************************IP -> Non-member*****************************************************************************
foreach (var qq in arIPNM)
{
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {qq} ... ");
db.GetExtract($"CALL {qq}('{parameter[1]}','{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');");
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {qq} ... ");
}
//**************************************************************************End IP -> Non-member*************************************************************************
#endregion
GetZip(date);
}
else if (item == "AR_CI" || item == "AR_CE")
{
string[] arKNP = storedProcedures["KNP"];
foreach (var _query in scripts)
{
string queryString = string.Empty;
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {_query} ... ");
if (_query != "ARStored_4")
{
queryString = $"CALL {_query}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');";
}
//else if (_query == "ARStored_4")
//{
// foreach (var qKnp in arKNP)
// {
// queryString = $"CALL {qKnp}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');";
// }
//}
else
{
break;
}
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {_query} ... ");
}
////*************************************************************************Kanego -> Member*****************************************************************************
foreach (var kng in arKNP)
{
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {kng} ... ");
db.GetExtract($"CALL {kng}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}','{branchCodeNumber}');");
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {kng} ... ");
}
////**************************************************************************End Kanego -> Member*************************************************************************
GetZip(date);
}
else if (item == "RC_RC")
{
foreach (var transType in transTypeRC)
{
//foreach (var querries in scripts)
//{
// string queryString = string.Empty;
// db = new MySQLHelper();
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {transType} - {querries} ... ");
// queryString = $"CALL {querries}('{transType}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');";
// db.GetExtract(queryString);
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {transType} - {querries} ... ");
//}
//GetZip(date);
#region Member
foreach (var querries in scripts)
{
string queryString = string.Empty;
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {querries} ... ");
queryString = $"CALL {querries}('{transType}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');";
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {querries} ... ");
}
GetZip(date);
#endregion
#region Non-Member
string[] rcNM = storedProcedures["RC_NM"];
foreach (var querries in rcNM)
{
string queryString = string.Empty;
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {querries} ... ");
queryString = $"CALL {querries}('{transType}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');";
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {querries} ... ");
}
GetZip(date);
#endregion
}
}
else
{
foreach (var _query in scripts)
{
string queryString = string.Empty;
db = new MySQLHelper();
//if (parameter[1] != "SI" && _query == "ARStored_5")
//{
// break;
//}
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {_query} ... ");
if (item == "IP_OR")
{
queryString = $"CALL {_query}('{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}')";
}
else
{
queryString = $"CALL {_query}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{branchCodeNumber}');";
}
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {_query} ... ");
}
GetZip(date);
}
}
else
{
//if trans type generation is per reference.
for (int i = 0; i < reference.Count; i++)
{
if (parameter[1] == reference[i].Substring(0, 2))
{
foreach (var querys in scripts)
{
string queryString = string.Empty;
db = new MySQLHelper();
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {querys} - {reference[i]} ... ");
queryString = $"CALL {querys} ('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}', '{reference[i]}', '{branchCodeNumber}');";
db.GetExtract(queryString);
ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {querys} - {reference[i]} ... ");
}
GetZip(date);
}
}
}
}
//foreach (var item in query)
//{
// var parameter = item.Split('_');
// string[] scripts = storedProcedures[parameter[0]];
// if (item == "IP_SI")
// {
// for (int i = 0; i <= scripts.Length - 1; i++)
// {
// string querys = string.Empty;
// if (i == 4)
// break;
// else
// querys = $"CALL IPStored_{i + 1}('{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}')";
// db = new MySQLHelper();
// db.GetExtract(querys);
// }
// GetZip();
// }
// else if (item == "IP_OR")
// {
// for (int i = 0; i <= scripts.Length - 1; i++)
// {
// string querys = string.Empty;
// if (i == 3)
// {
// querys = $"CALL IPStored_{i + 1}('{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}')";
// db = new MySQLHelper();
// db.GetExtract(querys);
// }
// }
// GetZip();
// }
// else if (item=="AR_SI")
// {
// foreach (var _query in scripts)
// {
// string queryString = string.Empty;
// db = new MySQLHelper();
// //if (parameter[1] != "SI" && _query == "ARStored_5")
// //{
// // break;
// //}
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {_query} ... ");
// queryString = $"CALL {_query}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}');";
// db.GetExtract(queryString);
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {_query} ... ");
// }
// GetZip();
// string[] nonmember = storedProcedures["AR2"];
// foreach (var _query in nonmember)
// {
// string queryString = string.Empty;
// db = new MySQLHelper();
// //if (parameter[1] != "SI" && _query == "ARStored_5")
// //{
// // break;
// //}
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {_query} ... ");
// queryString = $"CALL {_query}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}');";
// db.GetExtract(queryString);
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {_query} ... ");
// }
// GetZip();
// }
// //else if (item != "AR_SI")
// //{
// // for (int i = 0; i <= scripts.Length - 1; i++)
// // {
// // string querys = string.Empty;
// // if (i == 4)
// // break;
// // else
// // querys = $"CALL ARStored_{i + 1}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}');";
// // db = new MySQLHelper();
// // db.GetExtract(querys);
// // val++;
// // ThreadHelper.SetValue(frm, frm.progressBar1, val, maxVal);
// // }
// // GetZip();
// //}
// else if(item == "RC_RC")
// {
// foreach (var transType in transTypeRC)
// {
// foreach (var querries in scripts)
// {
// string queryString = string.Empty;
// db = new MySQLHelper();
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {transType} - {querries} ... ");
// queryString = $"CALL {querries}('{transType}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}');";
// db.GetExtract(queryString);
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {transType} - {querries} ... ");
// }
// GetZip();
// }
// }
// else
// {
// foreach (var _query in scripts)
// {
// string queryString = string.Empty;
// db = new MySQLHelper();
// //if (parameter[1] != "SI" && _query == "ARStored_5")
// //{
// // break;
// //}
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Start generating {parameter[1]} - {_query} ... ");
// queryString = $"CALL {_query}('{parameter[1]}', '{date}', '{Properties.Settings.Default.BRANCH_CODE}', '{Properties.Settings.Default.WAREHOUSE}');";
// db.GetExtract(queryString);
// ThreadHelper.SetLabel(frm, frm.lblStatus, $"Finished generating {parameter[1]} - {_query} ... ");
// }
// GetZip();
// }
//}
}
catch
{
throw;
}
}