public static DataTable GetTaskAssignTable(string deptType, string deptId)
{
string sql = string.Empty;
switch (deptType)
{
case "0":
sql = string.Format("SELECT itemId,itemName,t.task FROM t_det_item_produce i left join t_task_assign_produce t on i.itemId = t.iid and t.did = '{0}' where openFlag = '1'", deptId);
break;
case "1":
sql = string.Format("SELECT itemId,itemName,t.task FROM t_det_item_fishery i left join t_task_assign_fishery t on i.itemId = t.iid and t.did = '{0}'where openFlag = '1' ", deptId);
break;
case "2":
sql = string.Format("SELECT itemId,itemName,t.task FROM t_det_item_animal i left join t_task_assign_animal t on i.itemId = t.iid and t.did = '{0}'where openFlag = '1'", deptId);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetTaskAssignTable异常");
throw new Exception(e.Message);
}
}
public static bool UpdateTCompany(string companyName, string phone, string areaId, string contacter, string companyId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update t_company set ");
strSql.Append("companyName =@itemName,");
strSql.Append("phone = @phone,");
strSql.Append("areaId = @areaId,");
strSql.Append("contacter = @contacter ");
strSql.Append(" where companyId=@companyId");
MySqlParameter[] parameters =
{
new MySqlParameter("@companyName", MySqlDbType.VarChar, 50),
new MySqlParameter("@phone", MySqlDbType.VarChar, 50),
new MySqlParameter("@areaId", MySqlDbType.VarChar, 50),
new MySqlParameter("@contacter", MySqlDbType.VarChar, 50),
new MySqlParameter("@companyId", MySqlDbType.VarChar, 50)
};
parameters[0].Value = companyName;
parameters[1].Value = phone;
parameters[2].Value = areaId;
parameters[3].Value = contacter;
parameters[4].Value = companyId;
int rows = DbHelperMySQL.CreateDbHelper().ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public static string GetTaskParameter(string deptId, string gradeId)
{
string sql = string.Empty;
string sql2 = string.Empty;
switch (gradeId)
{
case "2":
sql = string.Format("select parameterDown from t_dept_grade where deptId = '{0}' and gradeId = '{1}'", deptId, "1");
sql2 = string.Format("select parameterUp from t_dept_grade where deptId = '{0}' and gradeId = '{1}'", deptId, "3");
break;
case "3":
sql = string.Format("select parameterDown from t_dept_grade where deptId = '{0}' and gradeId = '{1}'", deptId, "2");
sql2 = string.Format("select parameterUp from t_dept_grade where deptId = '{0}' and gradeId = '{1}'", deptId, "4");
break;
case "4":
sql = string.Format("select parameterDown from t_dept_grade where deptId = '{0}' and gradeId = '{1}'", deptId, "3");
sql2 = string.Format("select parameterUp from t_dept_grade where deptId = '{0}' and gradeId = '{1}'", deptId, "5");
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetSingle(sql).ToString() + "_" +
DbHelperMySQL.CreateDbHelper().GetSingle(sql2).ToString());
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetTaskParameter异常");
throw new Exception(e.Message);
}
}
//category:农产品p 水产品f 畜产品a 根据不同类别分别执行农、水、畜自定义报详情存储过程
public static DataTable ExecuteProYearReportDetails(string category, string startDate, string endDate, string DeptId,
string itemId, string resultId, string objectId, int rowN, int tRowN)
{
string sql = string.Empty;
switch (category)
{
case "p":
sql = string.Format("call p_report_year_details_produce('{0}','{1}','{2}','{3}','{4}','{5}',{6},{7})", startDate, endDate, DeptId, itemId, resultId, objectId, rowN, tRowN);
break;
case "f":
sql = string.Format("call p_report_year_details_fishery('{0}','{1}','{2}','{3}','{4}','{5}',{6},{7})", startDate, endDate, DeptId, itemId, resultId, objectId, rowN, tRowN);
break;
case "a":
sql = string.Format("call p_report_year_details_animal('{0}','{1}','{2}','{3}','{4}','{5}',{6},{7})", startDate, endDate, DeptId, itemId, resultId, objectId, rowN, tRowN);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProYearReportDetails异常");
throw new Exception(e.Message);
}
}
public static DataTable GetComboSampleNo(string category, string userId)
{
string strSql = string.Empty;
switch (category)
{
case "p":
strSql = string.Format("call p_sampleno_produce('{0}')", userId);
break;
case "f":
strSql = string.Format("call p_sampleno_fishery('{0}')", userId);
break;
case "a":
strSql = string.Format("call p_sampleno_animal('{0}')", userId);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(strSql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetComboSampleNo异常");
throw new Exception(e.Message);
}
}
public static DataTable GetTaskTable(string deptType, string deptId)
{
string sql = string.Empty;
switch (deptType)
{
case "0":
sql = string.Format("select t_det_item_produce.itemName,task from t_task_assign_produce left JOIN t_det_item_produce ON t_task_assign_produce.iid = t_det_item_produce.itemId where t_task_assign_produce.did ='{0}'", deptId);
break;
case "1":
sql = string.Format("select t_det_item_fishery.itemName,task from t_task_assign_fishery left JOIN t_det_item_fishery ON t_task_assign_fishery.iid = t_det_item_fishery.itemId where t_task_assign_fishery.did ='{0}'", deptId);
break;
case "2":
sql = string.Format("select t_det_item_animal.itemName,task from t_task_assign_animal left JOIN t_det_item_animal ON t_task_assign_animal.iid = t_det_item_animal.itemId where t_task_assign_animal.did = '{0}'", deptId);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetTaskTable异常");
throw new Exception(e.Message);
}
}
//category 根据农、水、畜执行不同存储过程 预警复核
public static DataTable ExecuteProReviewInfo(string category, string userId, DateTime startDate, DateTime endDate, string deptItem, string item, int rowN, int tRowN)
{
string sql = string.Empty;
switch (category)
{
case "p":
sql = string.Format("call p_review_details_produce('{0}','{1}','{2}','{3}','{4}',{5},{6})", userId, startDate, endDate, deptItem, item, rowN, tRowN);
break;
case "f":
sql = string.Format("call p_review_details_fishery('{0}','{1}','{2}','{3}','{4}',{5},{6})", userId, startDate, endDate, deptItem, item, rowN, tRowN);
break;
case "a":
sql = string.Format("call p_review_details_animal('{0}','{1}','{2}','{3}','{4}',{5},{6})", userId, startDate, endDate, deptItem, item, rowN, tRowN);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProReviewInfo异常");
throw new Exception(e.Message);
}
}
//depttype 根据不同类别执行不同存储过程
public static DataTable ExecuteProTrendAnalysis(string deptType, string userId)
{
string sql = string.Empty;
switch (deptType)
{
case "0":
sql = string.Format("call p_year_count_produce({0})", userId);
break;
case "1":
sql = string.Format("call p_year_count_fishery({0})", userId);
break;
case "2":
sql = string.Format("call p_year_count_animal({0})", userId);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProTrendAnalysis异常");
throw new Exception(e.Message);
}
}
public static DataTable GetDetItem(string category, string itemName)
{
string sql = string.Empty;
switch (category)
{
case "p":
sql =
string.Format(
"select itemId,itemName,case when openFlag = '1' then ' 启用' else '禁用' end as openFlag from t_det_item_produce where itemName like '{0}%'", itemName);
break;
case "f":
sql = string.Format("select itemId,itemName,case when openFlag = '1' then ' 启用' else '禁用' end as openFlag from t_det_item_fishery where itemName like '{0}%'", itemName);
break;
case "a":
sql = string.Format("select itemId,itemName,case when openFlag = '1' then ' 启用' else '禁用' end as openFlag from t_det_item_animal where itemName like '{0}%'", itemName);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetDetItem异常");
throw new Exception(e.Message);
}
}
public detectDetailsReview(IDBOperation dbOperation, int id)
{
InitializeComponent();
this.dbOperation = dbOperation;
dbHelper = DbHelperMySQL.CreateDbHelper();
orderid = id;
DataTable table = dbOperation.GetDbHelper().GetDataSet(string.Format("call p_detect_details('{0}')", id)).Tables[0];
//给画面上的控件赋值
_areaName.Text = table.Rows[0][10].ToString();
_companyName.Text = table.Rows[0][11].ToString();
_cardId.Text = table.Rows[0][12].ToString();
_itemName.Text = table.Rows[0][3].ToString();
_objectName.Text = table.Rows[0][4].ToString();
_sampleName.Text = table.Rows[0][5].ToString();
_reagentName.Text = table.Rows[0][7].ToString();
_sensitivityName.Text = table.Rows[0][6].ToString();
_resultName.Text = table.Rows[0][8].ToString();
_deptName.Text = table.Rows[0][2].ToString();
_detectDate.Text = table.Rows[0][1].ToString();
_detectUserName.Text = table.Rows[0][9].ToString();
_detectTypeName.Text = table.Rows[0][0].ToString();
_reviewBz.Text = table.Rows[0][19].ToString();
_reviewUserid.Text = table.Rows[0][14].ToString();
_reviewReagent_text.Text = table.Rows[0][15].ToString();
_reviewResult_text.Text = table.Rows[0][16].ToString();
_reviewDate.Text = table.Rows[0][17].ToString();
}
public static DataTable ExecuteProWarningReportDetails(string category, string kssj, string jssj, string deptId, string itemId, string reviewFlag, int rowN, int tRowN)
{
string sql = string.Empty;
switch (category)
{
case "p":
sql = string.Format("call p_warning_report_details_produce('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", kssj, jssj, deptId, itemId, reviewFlag, rowN, tRowN);
break;
case "f":
sql = string.Format("call p_warning_report_details_fishery('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", kssj, jssj, deptId, itemId, reviewFlag, rowN, tRowN);
break;
case "a":
sql = string.Format("call p_warning_report_details_animal('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", kssj, jssj, deptId, itemId, reviewFlag, rowN, tRowN);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProWarningReportDetails异常");
throw new Exception(e.Message);
}
}
public static string GetReviewFlag(string category, int detectId)
{
string sql = string.Empty;
switch (category)
{
case "p":
sql = string.Format("select reviewFlag from t_detect_report_produce where detectId = '{0}'", detectId);
break;
case "f":
sql = string.Format("select reviewFlag from t_detect_report_fishery where detectId = '{0}'", detectId);
break;
case "a":
sql = string.Format("select reviewFlag from t_detect_report_animal where detectId = '{0}'", detectId);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetSingle(sql).ToString());
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetReviewFlag异常");
throw new Exception(e.Message);
}
}
public static DataTable ExecuteProDetect(string category, int id)
{
string sql = string.Empty;
switch (category)
{
case "p":
sql = string.Format("call p_detect_details_produce('{0}')", id);
break;
case "f":
sql = string.Format("call p_detect_details_fishery('{0}')", id);
break;
case "a":
sql = string.Format("call p_detect_details_animal('{0}')", id);
break;
}
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProDetect异常");
throw new Exception(e.Message);
}
}
void SysPersonManager_Loaded(object sender, RoutedEventArgs e)
{
dbHelper = DbHelperMySQL.CreateDbHelper();
this.txtPersonCode.Text = CreateNewNumber();
InitComboboxs();
BindData();
Clear();
}
public AddReviewDetails(IDBOperation dbOperation, int id, SysReviewInfo sysreviewinfo)
{
InitializeComponent();
this.dbOperation = dbOperation;
this.sysreviewinfo = sysreviewinfo;
dbHelper = DbHelperMySQL.CreateDbHelper();
orderid = id;
string reviewflag = dbHelper.GetSingle(string.Format("select ReviewFlag from t_detect_report where ORDERID = '{0}'", id)).ToString();
DataTable table = dbOperation.GetDbHelper().GetDataSet(string.Format("call p_detect_details('{0}')", id)).Tables[0];
//给画面上的控件赋值
_areaName.Text = table.Rows[0][10].ToString();
_companyName.Text = table.Rows[0][11].ToString();
_cardId.Text = table.Rows[0][12].ToString();
_itemName.Text = table.Rows[0][3].ToString();
_objectName.Text = table.Rows[0][4].ToString();
_sampleName.Text = table.Rows[0][5].ToString();
_reagentName.Text = table.Rows[0][7].ToString();
_sensitivityName.Text = table.Rows[0][6].ToString();
_resultName.Text = table.Rows[0][8].ToString();
_deptName.Text = table.Rows[0][2].ToString();
_detectDate.Text = table.Rows[0][1].ToString();
_detectUserName.Text = table.Rows[0][9].ToString();
_detectTypeName.Text = table.Rows[0][0].ToString();
if (reviewflag == "1")
{
_reviewUserid.Text = table.Rows[0][14].ToString();
_reviewReagent_text.Text = table.Rows[0][15].ToString();
_reviewResult_text.Text = table.Rows[0][16].ToString();
_reviewDate.Text = table.Rows[0][17].ToString();
_reviewBz.Text = table.Rows[0][19].ToString();
btnSave.Visibility = Visibility.Hidden;
_reviewReagent.Visibility = Visibility.Hidden;
_reviewResult.Visibility = Visibility.Hidden;
_reviewReagent_text.Visibility = Visibility.Visible;
_reviewResult_text.Visibility = Visibility.Visible;
_reviewBz.IsEnabled = false;
}
else
{
_reviewUserid.Text = (Application.Current.Resources["User"] as UserInfo).ShowName;
_reviewDate.Text = DateTime.Now.ToString();
ComboboxTool.InitComboboxSource(_reviewResult, "SELECT resultId,resultName FROM t_det_result where openFlag = '1' ORDER BY id", "lr");
ComboboxTool.InitComboboxSource(_reviewReagent, "select reagentId,reagentName from t_det_reagent where openFlag = '1' and reagentId <> '1'", "lr");
btnSave.Visibility = Visibility.Visible;
_reviewReagent.Visibility = Visibility.Visible;
_reviewResult.Visibility = Visibility.Visible;
_reviewReagent_text.Visibility = Visibility.Hidden;
_reviewResult_text.Visibility = Visibility.Hidden;
_reviewBz.IsEnabled = true;
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public static bool Add(FoodSafety.Model.Sys_client_sysdept model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into sys_client_sysdept(");
strSql.Append("deptId,deptName,deptLevel,fkDeptId,province,city,country,address,contacter,contacterphone,supplierId,maintypes,town,principal,principalphone,depttype)");
strSql.Append(" values (");
strSql.Append("@deptId,@deptName,@deptLevel,@fkDeptId,@province,@city,@country,@address,@contacter,@contacterphone,@supplierId,@maintypes,@town,@principal,@principalphone,@depttype)");
MySqlParameter[] parameters =
{
new MySqlParameter("@deptId", MySqlDbType.VarChar, 50),
new MySqlParameter("@deptName", MySqlDbType.VarChar, 50),
new MySqlParameter("@deptLevel", MySqlDbType.VarChar, 1),
new MySqlParameter("@fkDeptId", MySqlDbType.VarChar, 50),
new MySqlParameter("@province", MySqlDbType.VarChar, 50),
new MySqlParameter("@city", MySqlDbType.VarChar, 50),
new MySqlParameter("@country", MySqlDbType.VarChar, 50),
new MySqlParameter("@address", MySqlDbType.VarChar, 50),
new MySqlParameter("@contacter", MySqlDbType.VarChar, 50),
new MySqlParameter("@contacterphone", MySqlDbType.VarChar, 50),
new MySqlParameter("@supplierId", MySqlDbType.VarChar, 50),
new MySqlParameter("@maintypes", MySqlDbType.VarChar, 50),
new MySqlParameter("@town", MySqlDbType.VarChar, 50),
new MySqlParameter("@principal", MySqlDbType.VarChar, 50),
new MySqlParameter("@principalphone", MySqlDbType.VarChar, 50),
new MySqlParameter("@depttype", MySqlDbType.VarChar, 50)
};
parameters[0].Value = model.deptId;
parameters[1].Value = model.deptName;
parameters[2].Value = model.deptLevel;
parameters[3].Value = model.fkDeptId;
parameters[4].Value = model.province;
parameters[5].Value = model.city;
parameters[6].Value = model.country;
parameters[7].Value = model.address;
parameters[8].Value = model.contacter;
parameters[9].Value = model.contacterphone;
parameters[10].Value = model.supplierId;
parameters[11].Value = model.maintypes;
parameters[12].Value = model.town;
parameters[13].Value = model.principal;
parameters[14].Value = model.principalphone;
parameters[15].Value = model.depttype;
int rows = DbHelperMySQL.CreateDbHelper().ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public static DataTable GetClientLog(string strSql)
{
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(strSql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetClientUser异常");
throw new Exception(e.Message);
}
}
public static int LoadPicture(string deptId, byte[] img)
{
try
{
return(DbHelperMySQL.CreateDbHelper().Load_Picture(deptId, img));
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetRoleSub异常");
throw new Exception(e.Message);
}
}
public static bool AddUpdateRole(string strSql)
{
int rows = DbHelperMySQL.CreateDbHelper().ExecuteSql(strSql.ToString());
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
public static bool AddUpdateDetectReport(string strSql)
{
int rows = DbHelperMySQL.CreateDbHelper().ExecuteSql(strSql.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public static string ExecuteFunObjectTable(string deptId)
{
try
{
string sql = string.Format("select f_create_objectlable('{0}')", deptId);
return(DbHelperMySQL.CreateDbHelper().GetSingle(sql).ToString());
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteFunObjectTable异常");
throw new Exception(e.Message);
}
}
public static DataTable GetCompany(string userId)
{
try
{
string sql = string.Format("call p_user_company_details('{0}')", userId);
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetCompany异常");
throw new Exception(e.Message);
}
}
/// <summary>
/// 获取软件的最新版本
/// </summary>
/// <param name="supplierid"></param>
/// <returns></returns>
public static string GetNewVersion(string supplierid)
{
try
{
string sql = string.Format("select version from t_version where id = (select max(id) from t_version where supplierId ='{0}')", supplierid);
return(DbHelperMySQL.CreateDbHelper().GetSingle(sql).ToString());
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetNewVersion异常");
throw new Exception(e.Message);
}
}
public static bool AddSjhc(string strSql)
{
int rows = DbHelperMySQL.CreateDbHelper().ExecuteSql(strSql);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
public static DataTable ExecuteProQueryDevice(string userId, string deviceId, string deptType)
{
string sql = string.Format("call p_query_device('{0}','{1}','{2}')", userId, deviceId, deptType);
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProQueryDevice异常");
throw new Exception(e.Message);
}
}
public static DataTable ExecuteProReviewLog(string function, string userId, DateTime kssj, DateTime jssj, string deptItem, string item, int rowN, int tRowN)
{
string sql = string.Format("call {0}('{1}','{2}','{3}','{4}','{5}',{6},{7})", function, userId, kssj, jssj, deptItem, item, rowN, tRowN);
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProReviewLog异常");
throw new Exception(e.Message);
}
}
public static DataTable ExecuteProReviewDetails(string kssj, string jssj, string deptId, string itemId, string reviewId, int rowN, int tRowN)
{
string sql = string.Format("call p_review_details('{0}','{1}','{2}','{3}','{4}',{5},{6})", kssj, jssj, deptId, itemId, reviewId, rowN, tRowN);
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProReviewDetails异常");
throw new Exception(e.Message);
}
}
public static DataTable ExecuteProWarningReportCountry(string function, string kssj, string jssj, string deptId, string itemId, string reviewFlag, string deptType)
{
string sql = string.Format("call {0}('{1}','{2}','{3}','{4}','{5}')", function, kssj, jssj, deptId, itemId, reviewFlag, deptType);
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProWarningReportCountry异常");
throw new Exception(e.Message);
}
}
public static DataTable GetComboDetItemProduce()
{
string strSql = string.Format("SELECT itemId,itemName FROM t_det_item_produce where openFlag = '1'");
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(strSql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("GetComboDetItemAnimal异常");
throw new Exception(e.Message);
}
}
public static DataTable ExecuteProWarningReport(string function, string userId, DateTime startDate, DateTime endDate, string item, string itemId, string reviewId)
{
string sql = string.Format("call {0}('{1}','{2}','{3}','{4}','{5}','{6}')", function, userId, startDate, endDate, item, itemId, reviewId);
try
{
return(DbHelperMySQL.CreateDbHelper().GetDataSet(sql).Tables[0]);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("ExecuteProWarningReport异常");
throw new Exception(e.Message);
}
}