public static void insertDevRowGS(string processID, WsGetDataClient.WSGetData mgr, DataTable dt, string psDWBH, string psYear, string psBH)
{
psDWBH = string.IsNullOrEmpty(psDWBH) ? " " : psDWBH;
string sql = @"insert into {20} (F_DWBH, F_ID, F_GSBH, F_TIBH,
F_JS, F_FIELD,F_TEXT, F_TYPE,F_ALIGN, F_WIDTH, F_PROP, F_PREC,
F_DISP, F_REAL, F_HJBZ, F_PXBZ, F_YHBZ, F_FHBZ, F_HZBZ, F_COLOR)values
('{0}', '{1}', '{2}', '{3}',
'{4}', '{5}','{6}', '{7}','{8}', {9}, '{10}', '{11}',
'{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}');";
string sqldel = "delete from {0} WHERE F_DWBH='{1}' AND F_ID='{2}' AND F_GSBH='{3}' AND F_TIBH='{4}' ; ";
string vssql = "";
string tbname = "EATIGS" + psYear;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
if (row["F_FIELD"].ToString() == "F_DT_FIELD" || row["F_FIELD"].ToString() == "F_NUM_FIELD" ||
row["F_FIELD"].ToString() == "F_TXT_FIELD")
{
tbname = "EATIGSDEVQRY";
}
vssql += string.Format(sqldel, tbname, psDWBH, row["F_ID"].ToString(), psBH,
row["F_TIBH"].ToString());
vssql += string.Format(sql, psDWBH, row["F_ID"].ToString(), psBH,
row["F_TIBH"].ToString(), row["F_JS"].ToString(), row["F_FIELD"].ToString(),
row["F_TEXT"].ToString(), row["F_TYPE"].ToString(), row["F_ALIGN"].ToString(),
row["F_WIDTH"].ToString(), row["F_PROP"].ToString(), row["F_PREC"].ToString(),
row["F_DISP"].ToString(), row["F_REAL"].ToString(), row["F_HJBZ"].ToString(),
row["F_PXBZ"].ToString(), row["F_YHBZ"].ToString(), row["F_FHBZ"].ToString(),
row["F_HZBZ"].ToString(), row["F_COLOR"].ToString(), tbname);
// OleDbHelp.execSql(vssql);
}
vssql = "begin " + vssql + " end;";
WebSvrGetData.execsql(processID, vssql, mgr);
}
public void Save(DataTable dt, string ID)
{
JTPUBQRDEFMgr defmgr = new JTPUBQRDEFMgr(mgr, ProcessID);
JTPUBQRDEFEty ety = defmgr.getEty(ID);
string sql = "begin ";
sql += string.Format("DELETE FROM EAZBGS WHERE F_ID='{0}' and F_DWBH=' ';", ID);
sql += "INSERT INTO EAZBGS(F_DWBH,F_ID,F_GSBH,F_GSMC,F_CAPT,F_CAPH,F_SCAPH,F_BTGD,F_ROWH,F_BWGD,F_SPACE,F_SYBZ) ";
sql += string.Format("VALUES (' ','{0}','01','系统格式','{1}',30,30,20,20,0,10,'0');", ID, ety.JTPUBQRDEF_TITLE);
sql += string.Format("DELETE FROM EAOTGS WHERE F_ID='{0}' and F_DWBH=' ';", ID);
sql += "INSERT INTO EAOTGS(F_DWBH,F_ID,F_GSBH,F_OTBH,F_TEXT,F_OTBZ,F_JS,F_ALIGN)";
sql += string.Format("Values(' ','{0}','01','01','{1}','H','1','L');", ID, ety.JTPUBQRDEF_SUBTIL);
sql += string.Format("delete from EATIGS where F_ID='{0}' and F_DWBH=' ';", ID);
if (dt.Rows.Count > 0)
{
/*F_DWBH,F_ID,F_FHBZ,F_GSBH,F_TIBH,F_JS,F_FIELD,F_TEXT,F_TYPE,F_ALIGN,
* F_WIDTH,F_PREC,F_HJBZ,F_YHBZ*/
string vsinsert = @"INSERT INTO EATIGS(F_DWBH,F_ID,F_GSBH,F_TIBH,F_JS,F_FIELD,F_TEXT,F_TYPE,F_ALIGN,F_WIDTH,F_PROP,F_PREC,F_DISP,F_REAL,F_HJBZ,F_PXBZ,F_YHBZ,F_FHBZ,F_HZBZ,F_TSTEXT,F_GROUP,F_TSHID,F_ISGD,F_PSUMTYPE,F_FORMAT)
Values(' ','{0}','01','{1}','{2}','{3}','{4}','{5}','{6}',{7},' ','{8}',' ',' ','{9}','0','{10}','0','0','{11}','{12}','{13}','{14}','{15}','{16}');";
foreach (DataRow row in dt.Rows)
{
sql += string.Format(vsinsert, row["F_ID"].ToString(), row["F_TIBH"].ToString(), row["F_JS"].ToString(),
row["F_FIELD"].ToString(), row["F_TEXT"].ToString(), row["F_TYPE"].ToString(), row["F_ALIGN"].ToString(), row["F_WIDTH"].ToString(),
row["F_PREC"].ToString(), row["F_HJBZ"].ToString(), row["F_YHBZ"].ToString(),
row["F_TSTEXT"].ToString(), row["F_GROUP"].ToString(), row["F_TSHID"].ToString(), row["F_ISGD"].ToString(), row["F_PSUMTYPE"].ToString(), row["F_FORMAT"].ToString());
}
}
sql += " end;";
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
}
public static void saveBBSubTitle(string processID, WsGetDataClient.WSGetData mgr, DataTable dt, string psDWBH, string psYear, string psType, string psBH)
{
psDWBH = string.IsNullOrEmpty(psDWBH) ? " " : psDWBH;
string sql = @"insert into EAOTGS{8} (F_DWBH, F_ID, F_GSBH, F_OTBH, F_TEXT, F_OTBZ, F_JS, F_ALIGN)values
('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');";
string sqldel = "delete from EAOTGS{0} WHERE F_DWBH='{1}' AND F_ID='{2}' AND F_GSBH='{3}' AND F_OTBH='{4}' ; ";
if (dt.Rows.Count > 0)
{
if (psType == "edit")
{
sql = @" update EAOTGS{8} set F_TEXT='{4}', F_OTBZ='{5}', F_JS='{6}', F_ALIGN='{7}'
where F_DWBH='{0}' and F_ID='{1}' and F_GSBH='{2}' and F_OTBH='{3}';";
}
string vssql = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
string vsOTBH = i.ToString().PadLeft(2, '0');
DataRow row = dt.Rows[i];
vssql += string.Format(sqldel, psYear, psDWBH, row["F_ID"].ToString(), psBH, vsOTBH);
vssql += string.Format(sql, psDWBH, row["F_ID"].ToString(), psBH,
vsOTBH, row["F_TEXT"].ToString(), row["F_OTBZ"].ToString(), row["F_JS"].ToString(), row["F_ALIGN"].ToString(), psYear);
// OleDbHelp.execSql(vssql);
}
vssql = "begin " + vssql + " end;";
WebSvrGetData.execsql(processID, vssql, mgr);
}
}
public static void saveBBTitle(string processID, WsGetDataClient.WSGetData mgr, DataTable dt, string psDWBH, string psYear, string psType, string psBH, string psMC)
{
psDWBH = string.IsNullOrEmpty(psDWBH) ? " " : psDWBH;
string sql = @"insert into EAZBGS{12} (F_DWBH, F_ID, F_GSBH, F_GSMC,
F_CAPT, F_CAPH, F_SCAPH, F_BTGD, F_ROWH, F_BWGD, F_SPACE, F_SYBZ)values
('{0}', '{1}', '{2}', '{3}','{4}', {5}, {6}, {7}, {8}, {9}, {10}, '{11}') ";
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
if (psType == "edit")
{
sql = @" update EAZBGS{12} set F_GSMC='{3}',F_CAPT='{4}', F_CAPH={5}, F_SCAPH={6},
F_BTGD={7}, F_ROWH={8}, F_BWGD={9}, F_SPACE={10}, F_SYBZ='{11}' where F_DWBH='{0}' and F_ID='{1}' and F_GSBH='{2}' ";
}
else
{
WebSvrGetData.execsql(processID, string.Format("delete from EAZBGS{0} where F_DWBH='{1}' and F_ID='{2}' and F_GSBH='{3}' ", psYear, psDWBH, row["F_ID"].ToString(), psBH), mgr);
}
sql = string.Format(sql, psDWBH, row["F_ID"].ToString(), psBH,
psMC, row["F_CAPT"].ToString(), row["F_CAPH"].ToString(), row["F_SCAPH"].ToString(),
row["F_BTGD"].ToString(), row["F_ROWH"].ToString(), row["F_BWGD"].ToString(),
row["F_SPACE"].ToString(), row["F_SYBZ"].ToString(), psYear);
WebSvrGetData.execsql(processID, sql, mgr);
}
}
public void delete(string id)
{
string sql = @"begin delete from EATIGSDEVQRY where F_ID='{0}';
delete from EAOTGS where F_ID='{0}';
delete from EAZBGS where F_ID='{0}';
delete from JTPUBQRDEF where JTPUBQRDEF_ID='{0}';
delete from JTPUBQRPARAMDEF where PARAMDEF_QRYID='{0}'; end;";
sql = string.Format(sql, id);
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
}
private void add(JTPUBQRDEFEty ety)
{
string sql = @"insert into JTPUBQRDEF
(JTPUBQRDEF_ID,JTPUBQRDEF_BH,JTPUBQRDEF_MC,JTPUBQRDEF_TITLE,JTPUBQRDEF_SUBTIL,JTPUBQRDEF_TYPE,JTPUBQRDEF_SQL,JTPUBQRDEF_DWFIELD,JTPUBQRDEF_RQFIELD,JTPUBQRDEF_WHERE,JTPUBQRDEF_DBSRC,JTPUBQRDEF_ORA)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')";
sql = string.Format(sql, ety.JTPUBQRDEF_ID, ety.JTPUBQRDEF_BH, ety.JTPUBQRDEF_MC, ety.JTPUBQRDEF_TITLE, ety.JTPUBQRDEF_SUBTIL,
ety.JTPUBQRDEF_TYPE, ety.JTPUBQRDEF_SQL.Replace("'", "''"), ety.JTPUBQRDEF_DWFIELD,
ety.JTPUBQRDEF_RQFIELD, ety.JTPUBQRDEF_WHERE, ety.JTPUBQRDEF_DBSRC, ety.JTPUBQRDEF_ORA.Replace("'", "''"));
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
}
public static void saveBBRowGS(string processID, WsGetDataClient.WSGetData mgr, DataTable dt, string psDWBH, string psYear, string psType, string psBH)
{
psDWBH = string.IsNullOrEmpty(psDWBH) ? " " : psDWBH;
/*SELECT F_DWBH, F_ID, F_GSBH, F_TIBH, F_JS, F_FIELD, F_TEXT, F_TYPE, F_ALIGN, F_WIDTH, F_PROP, F_PREC, F_DISP, F_REAL, F_HJBZ, F_PXBZ, F_YHBZ, F_FHBZ, F_HZBZ, F_COLOR
* FROM LSTIGS;*/
string sql = @"insert into {20} (F_DWBH, F_ID, F_GSBH, F_TIBH,
F_JS, F_FIELD,F_TEXT, F_TYPE,F_ALIGN, F_WIDTH, F_PROP, F_PREC,
F_DISP, F_REAL, F_HJBZ, F_PXBZ, F_YHBZ, F_FHBZ, F_HZBZ, F_COLOR,F_TSTEXT,F_GROUP,F_TSHID,F_ISGD,F_PSUMTYPE,F_FORMAT)values
('{0}', '{1}', '{2}', '{3}',
'{4}', '{5}','{6}', '{7}','{8}', {9}, '{10}', '{11}',
'{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}','{21}','{22}','{23}','{24}','{25}','{26}');";
if (dt.Rows.Count > 0)
{
/*增加修改编号功能，实现排序，所有的格式先删除，后增加，update语句不用了
*
* if (psType=="edit")
* {
* sql = @" update {20} set
* F_JS='{4}', F_FIELD='{5}',F_TEXT='{6}', F_TYPE='{7}',F_ALIGN='{8}', F_WIDTH={9}, F_PROP='{10}', F_PREC='{11}',
* F_DISP='{12}', F_REAL='{13}', F_HJBZ='{14}', F_PXBZ='{15}', F_YHBZ='{16}', F_FHBZ='{17}', F_HZBZ='{18}', F_COLOR='{19}'
* where F_DWBH='{0}' and F_ID='{1}' and F_GSBH='{2}' and F_TIBH='{3}' ;";
* } */
string vssql = " ";
string tbname = "EATIGS" + psYear;
vssql += string.Format("delete from {0} where F_DWBH='{1}' and F_ID='{2}' and F_GSBH='{3}'; ", tbname, psDWBH, dt.Rows[0]["F_ID"].ToString(), psBH);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
if (row["F_FIELD"].ToString() == "F_DT_FIELD" || row["F_FIELD"].ToString() == "F_NUM_FIELD" ||
row["F_FIELD"].ToString() == "F_TXT_FIELD")
{
tbname = "EATIGSDEVQRY";
}
vssql += string.Format(sql, psDWBH, row["F_ID"].ToString(), psBH,
row["F_TIBH"].ToString(), row["F_JS"].ToString(), row["F_FIELD"].ToString(),
row["F_TEXT"].ToString(), row["F_TYPE"].ToString(), row["F_ALIGN"].ToString(),
row["F_WIDTH"].ToString(), row["F_PROP"].ToString(), row["F_PREC"].ToString(),
row["F_DISP"].ToString(), row["F_REAL"].ToString(), row["F_HJBZ"].ToString(),
row["F_PXBZ"].ToString(), row["F_YHBZ"].ToString(), row["F_FHBZ"].ToString(),
row["F_HZBZ"].ToString(), row["F_COLOR"].ToString(), tbname,
row["F_TSTEXT"].ToString(), row["F_GROUP"].ToString(), row["F_TSHID"].ToString(),
row["F_ISGD"].ToString(), row["F_PSUMTYPE"].ToString(), row["F_FORMAT"].ToString());
// OleDbHelp.execSql(vssql);
}
vssql = "begin " + vssql + " end;";
WebSvrGetData.execsql(processID, vssql, mgr);
}
}
private void Update(JTPUBQRDEFEty ety)
{
string sql = @"begin
update JTPUBQRDEF set JTPUBQRDEF_BH='{1}',JTPUBQRDEF_MC='{2}',JTPUBQRDEF_TITLE='{3}',JTPUBQRDEF_SUBTIL='{4}',JTPUBQRDEF_TYPE='{5}',
JTPUBQRDEF_SQL='{6}',JTPUBQRDEF_DWFIELD='{7}',JTPUBQRDEF_RQFIELD ='{8}',JTPUBQRDEF_WHERE='{9}',JTPUBQRDEF_DBSRC='{10}',JTPUBQRDEF_ORA='{11}'
where JTPUBQRDEF_ID='{0}' ;";
sql = string.Format(sql, ety.JTPUBQRDEF_ID, ety.JTPUBQRDEF_BH, ety.JTPUBQRDEF_MC, ety.JTPUBQRDEF_TITLE, ety.JTPUBQRDEF_SUBTIL,
ety.JTPUBQRDEF_TYPE, ety.JTPUBQRDEF_SQL.Replace("'", "''"), ety.JTPUBQRDEF_DWFIELD, ety.JTPUBQRDEF_RQFIELD,
ety.JTPUBQRDEF_WHERE, ety.JTPUBQRDEF_DBSRC, ety.JTPUBQRDEF_ORA.Replace("'", "''"));
sql += string.Format("DELETE FROM EAOTGS WHERE F_ID='{0}';", ety.JTPUBQRDEF_ID);
sql += "INSERT INTO EAOTGS(F_ID,F_GSBH,F_OTBH,F_TEXT,F_OTBZ,F_JS,F_ALIGN)";
sql += string.Format("Values('{0}','01','01','{1}','H','1','L');", ety.JTPUBQRDEF_ID, ety.JTPUBQRDEF_SUBTIL);
sql += " end; ";
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
}
private void btnParam_Click(object sender, EventArgs e)
{
string sql = insertDefineParam();
if (!string.IsNullOrEmpty(sql))
{
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
frmDefIndex frmind = new frmDefIndex();
frmind.ID = ety.JTPUBQRDEF_ID;
frmind.WSMgr = mgr;
frmind.ShowDialog();
}
else
{
MessageBox.Show("没有定义参数！");
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
DataRow row = getCtrlValue();
string sql = string.Format(@"UPDATE EACUSTOMFIELDS set DISPLAYNAME='{2}',INPUTTYPE='{3}',ISDISPLAY='{4}',
GETINFOFROM='{5}',GETINFOWHERE='{6}',ISREQUIRED='{7}',DEFAULTVALUE='{8}',DISPLAYORDER='{9}'
where CLASSSETCODE='{0}' and FIELDNAME='{1}' ", this.ID, row["FIELDNAME"].ToString(), row["DISPLAYNAME"].ToString(),
row["INPUTTYPE"].ToString(), row["ISDISPLAY"].ToString(),
row["GETINFOFROM"].ToString(), row["GETINFOWHERE"].ToString(),
row["ISREQUIRED"].ToString(), row["DEFAULTVALUE"].ToString(), row["DISPLAYORDER"].ToString());
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void updateWhereData(string qryID, DataTable dtwhere)
{
string sql = string.Format("begin delete from JTPUBQRPARAMDEF where PARAMDEF_QRYID='{0}'; ", qryID);
for (int i = 0; i < dtwhere.Rows.Count; i++)
{
if (dtwhere.Rows[i]["PARAMDEF_NAME"].ToString().Trim() != "")
{
string id = System.Guid.NewGuid().ToString().Replace("-", "");
sql += string.Format(@"insert into JTPUBQRPARAMDEF (PARAMDEF_ID,PARAMDEF_QRYID,PARAMDEF_ORD ,
PARAMDEF_NAME,PARAMDEF_TYPE,PARAMDEF_CMP,PARAMDEF_ISUSR,PARAMDEF_CMPSTR)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", id, qryID, dtwhere.Rows[i]["PARAMDEF_ORD"].ToString(), dtwhere.Rows[i]["PARAMDEF_NAME"].ToString(),
dtwhere.Rows[i]["PARAMDEF_TYPE"].ToString(), dtwhere.Rows[i]["PARAMDEF_CMP"].ToString(),
dtwhere.Rows[i]["PARAMDEF_ISUSR"].ToString() == "是"?"1":"0", dtwhere.Rows[i]["PARAMDEF_CMPSTR"].ToString());
}
}
sql += " end;";
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
}
private DataTable getQryData()
{
JTPUBQRDEFMgr defmgr = new JTPUBQRDEFMgr(mgr, ProcessID);
JTPUBQRDEFEty ety = defmgr.getEty(this.ID);
DataTable dt = null;
if (ety.JTPUBQRDEF_TYPE.ToUpper() == "SQL")
{
string sql = ety.JTPUBQRDEF_SQL + " " + ety.JTPUBQRDEF_WHERE;
if (mgr.getDBType(ety.JTPUBQRDEF_DBSRC) == "ORA")
{
sql = ety.JTPUBQRDEF_ORA + " " + ety.JTPUBQRDEF_WHERE;
}
dt = getDataTable(sql, ety.JTPUBQRDEF_DBSRC);
}
else
{
string[] paramArr = null;
string[] valueArr = null;
DataTable dtwhere = defmgr.getWhereDt(this.ID);
if (dtwhere.Rows.Count > 0)
{
paramArr = new string[dtwhere.Rows.Count];
valueArr = new string[dtwhere.Rows.Count];
for (int i = 0; i < dtwhere.Rows.Count; i++)
{
paramArr[i] = dtwhere.Rows[i]["PARAMDEF_NAME"].ToString();
valueArr[i] = "";
}
}
string proc = ety.JTPUBQRDEF_SQL;
if (mgr.getDBType(ety.JTPUBQRDEF_DBSRC) == "ORA")
{
proc = ety.JTPUBQRDEF_ORA;
}
dt = WebSvrGetData.getDataTable(ety.JTPUBQRDEF_DBSRC, ety.JTPUBQRDEF_SQL, paramArr, valueArr, mgr);
}
return(dt);
}
public void delete(string ID)
{
string sql = string.Format("delete from EATIGS where F_ID='{0}'", ID);
WebSvrGetData.execsql(this.ProcessID, sql, mgr);
}