public bool QueryMI(string[] xMIInfo)
{
GV_Main.Columns.Clear();
String JsonStr = "";
try
{ // Create an instance of StreamReader to read from a file.
// The using statement also closes the StreamReader.
using (SqlConnection conn = new SqlConnection(fc.makeConnectString(fc.FDBInfo)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT MI003 FROM ADMMI WHERE MI001=@MI001 AND MI002=@MI002", conn))
{
cmd.Parameters.AddWithValue("@MI001", xMIInfo[0]);
cmd.Parameters.AddWithValue("@MI002", xMIInfo[1]);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
JsonStr = dr["MI003"].ToString();
}
}
}
}
/*using (StreamReader sr = new StreamReader(xPath.Trim())) //小寫TXT
* {
* JsonStr = sr.ReadToEnd();
* }*/
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
fc.ErrorLog(ex.Message);
return(false);
}
if (JsonStr == "")
{
return(false);
}
F2 = JsonConvert.DeserializeObject <F2Object.Rootobject>(JsonStr.Trim());
ResutnValus = new String[F2.RETURN.Length];
using (SqlConnection conn = new SqlConnection(fc.makeConnectString(fc.FDBInfo)))
{
try
{
conn.Open();
string OrderStr = "";
foreach (string s in F2.ORDER)
{
OrderStr += s + ",";
}
OrderStr = OrderStr.Substring(0, OrderStr.Length - 1);
if (F2.PARAM[0] != "0")
{
for (int i = 0; i < F2.PARAM.Count(); i++)
{
F2.MAINSELECT = F2.MAINSELECT.Replace(F2.PARAM[i], "'" + MIParam[i] + "'");
}
}
if (FMIField != null)
{
string mFilter = "";
DevExpress.XtraGrid.Views.Grid.GridView gv = FRF.GetGV(RootForm.GCNum.GCN_Body);
if (gv != null)
{
for (int i = 0; i < gv.RowCount; i++)
{
if (i == gv.FocusedRowHandle)
{
continue;
}
object mvalue = gv.GetRowCellValue(i, FMIField[0]);
if (mvalue != null && mvalue.ToString() != "")
{
if (i == 0)
{
if (F2.MAINSELECT.ToUpper().Contains("WHERE"))
{
mFilter = " AND ";
}
else
{
mFilter = " WHERE ";
}
}
else
{
mFilter += " AND ";
}
mFilter += " " + FMIField[1] + " <> '" + mvalue.ToString() + "' ";
}
}
F2.MAINSELECT += mFilter;
}
}
if (F2.GROUP != null)
{
if (F2.GROUP.Count() > 0)
{
F2.MAINSELECT += " GROUP BY ";
for (int i = 0; i < F2.GROUP.Count(); i++)
{
F2.MAINSELECT += F2.GROUP[i] + ",";
}
if (F2.MAINSELECT.EndsWith(","))
{
F2.MAINSELECT = F2.MAINSELECT.Substring(0, F2.MAINSELECT.Length - 1);
}
}
}
using (SqlCommand cmd = new SqlCommand(F2.MAINSELECT + " ORDER BY " + OrderStr, conn))
{
//myCommand = new SqlCommand(F2.MAINSELECT + " ORDER BY " + OrderStr, conn);
using (SqlDataAdapter sqldataadpt = new SqlDataAdapter(cmd))
{
//SqlDataAdapter sqldataadpt = new SqlDataAdapter(myCommand);
DataTable dt1 = new DataTable();
sqldataadpt.Fill(dt1);
if (dt1.Rows.Count == 0)
{
return(false);
}
else
{
GC_Main.DataSource = dt1;
for (int i = 0; i < GV_Main.Columns.Count; i++)
{
GV_Main.Columns[i].AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;
GV_Main.Columns[i].Caption = F2.DISPLAY[i];
}
tbName.Properties.Items.Clear();
for (int i = 0; i < F2.DISPLAY.Count(); i++)
{
tbName.Properties.Items.Add(GV_Main.Columns[i].FieldName + " " + F2.DISPLAY[i]);
}
if (tbName.Properties.Items.Count > 0)
{
tbName.SelectedIndex = 0;
}
GV_Main.BestFitColumns();
return(true);
}
}
}
//conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
fc.ErrorLog(ex.Message);
return(false);
}
}
}
public bool QueryMIMuity(string[] xMIInfo)
{
GV_Main.Columns.Clear();
String JsonStr = "";
try
{ // Create an instance of StreamReader to read from a file.
// The using statement also closes the StreamReader.
using (SqlConnection conn = new SqlConnection(fc.makeConnectString(fc.FDBInfo)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT MI003 FROM ADMMI WHERE MI001=@MI001 AND MI002=@MI002", conn))
{
cmd.Parameters.AddWithValue("@MI001", xMIInfo[0]);
cmd.Parameters.AddWithValue("@MI002", xMIInfo[1]);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
JsonStr = dr["MI003"].ToString();
}
}
}
}
/*using (StreamReader sr = new StreamReader(xPath.Trim())) //小寫TXT
* {
* JsonStr = sr.ReadToEnd();
* }*/
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
fc.ErrorLog(ex.Message);
return(false);
}
if (JsonStr == "")
{
return(false);
}
F2 = JsonConvert.DeserializeObject <F2Object.Rootobject>(JsonStr.Trim());
ResutnValus = new String[F2.RETURN.Length];
if (F2.PARAM[0] != "0")
{
for (int i = 0; i < F2.PARAM.Count(); i++)
{
F2.MAINSELECT = F2.MAINSELECT.Replace(F2.PARAM[i], "'" + MIParam[i] + "'");
}
}
using (SqlConnection conn = new SqlConnection(fc.makeConnectString(fc.FDBInfo)))
{
try
{
conn.Open();
string OrderStr = "";
foreach (string s in F2.ORDER)
{
OrderStr += s + ",";
}
OrderStr = OrderStr.Substring(0, OrderStr.Length - 1);
using (SqlCommand cmd = new SqlCommand(F2.MAINSELECT + " ORDER BY " + OrderStr, conn))
{
//myCommand = new SqlCommand(F2.MAINSELECT + " ORDER BY " + OrderStr, conn);
using (SqlDataAdapter sqldataadpt = new SqlDataAdapter(cmd))
{
//SqlDataAdapter sqldataadpt = new SqlDataAdapter(myCommand);
DataTable dt1 = new DataTable();
sqldataadpt.Fill(dt1);
if (dt1.Rows.Count == 0)
{
return(false);
}
else
{
dt1.Columns.Add("Select", typeof(string));
GC_Main.DataSource = dt1;
GV_Main.PopulateColumns();
GV_Main.Columns["Select"].VisibleIndex = 0;
GV_Main.Columns["Select"].Caption = "選擇";
DevExpress.XtraEditors.Repository.RepositoryItemCheckEdit selectnew = new DevExpress.XtraEditors.Repository.RepositoryItemCheckEdit();
GV_Main.Columns["Select"].ColumnEdit = selectnew;
selectnew.NullText = "";
selectnew.ValueChecked = "Y";
selectnew.ValueUnchecked = "N";
selectnew.ValueGrayed = "-";
selectnew.QueryCheckStateByValue += new DevExpress.XtraEditors.Controls.QueryCheckStateByValueEventHandler(QueryCheckStateByValue);
GV_Main.Columns["Select"].OptionsColumn.ReadOnly = false;
GV_Main.OptionsBehavior.Editable = true;
GV_Main.OptionsBehavior.ReadOnly = false;
GV_Main.OptionsSelection.MultiSelect = true;
for (int i = 0; i < GV_Main.Columns.Count - 1; i++)
{
GV_Main.Columns[i].Caption = F2.DISPLAY[i];
}
tbName.Properties.Items.Clear();
for (int i = 0; i < F2.DISPLAY.Count(); i++)
{
tbName.Properties.Items.Add(GV_Main.Columns[i].FieldName + " " + F2.DISPLAY[i]);
}
if (tbName.Properties.Items.Count > 0)
{
tbName.SelectedIndex = 0;
}
GV_Main.BestFitColumns();
return(true);
}
}
}
//conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
fc.ErrorLog(ex.Message);
return(false);
}
}
}
public bool QueryMJ(string[] xMJInfo)
{
String JsonStr = "";
try
{ // Create an instance of StreamReader to read from a file.
// The using statement also closes the StreamReader.
using (SqlConnection conn = new SqlConnection(fc.makeConnectString(fc.FDBInfo)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT MJ003 FROM ADMMJ WHERE MJ001=@MJ001 AND MJ002=@MJ002", conn))
{
cmd.Parameters.AddWithValue("@MJ001", xMJInfo[0]);
cmd.Parameters.AddWithValue("@MJ002", xMJInfo[1]);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
JsonStr = dr["MJ003"].ToString();
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
fc.ErrorLog(ex.Message);
return(false);
}
F2 = JsonConvert.DeserializeObject <F2Object.Rootobject>(JsonStr.Trim());
ResutnValus = new String[F2.RETURN.Length];
using (SqlConnection conn = new SqlConnection(fc.makeConnectString(fc.FDBInfo)))
{
try
{
conn.Open();
for (int i = 0; i < F2.PARAM.Count(); i++)
{
F2.MAINSELECT = F2.MAINSELECT.Replace(F2.PARAM[i], "'" + MJParam[i] + "'");
}
using (SqlCommand cmd = new SqlCommand(F2.MAINSELECT, conn))
{
using (SqlDataAdapter sqldataadpt = new SqlDataAdapter(cmd))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
for (int i = 0; i < F2.RETURN.Length; i++)
{
ResutnValus[i] = dr[i].ToString();
}
//JsonStr = dr[0].ToString();
}
else
{
//JsonStr = "";
return(false);
}
}
}
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
fc.ErrorLog(ex.Message);
return(false);
}
}
}