public int of_SetMySysSet(string as_type, string as_item, string as_value)
{
MyConfig.DbName = "config.db";
int li_row = 0;
string ls_sql = "select count(*) from mysysset where itemtype='" + as_type + "' and itemname='" + as_item + "'";
li_row = MyConfig.ExecuteScalarNum(ls_sql);
n_create_sql lnv_sql = new n_create_sql("mysysset", MyConfig.DbName);
lnv_sql.of_AddCol("itemtype", as_type);
lnv_sql.of_AddCol("itemname", as_item);
lnv_sql.of_AddCol("itemvalue", as_value);
int li_rc = 0;
if (li_row <= 0)
li_rc = lnv_sql.of_execute();
if (li_row > 0)
li_rc = lnv_sql.of_execute("itemtype=@itemtype and itemname=@itemname", "@itemtype=" + as_type, "@itemname=" + as_item);
return li_rc;
}
/// <summary>
/// 上传同步新增客户数据
/// </summary>
private void of_SyncAddCustomerData()
{
SysVisitor.CreateServerDB();
while (true)
{
System.Threading.Thread.Sleep(5000);
string ls_sql_update = "select cus_code from customer where cus_code like 'tmp%'";
DataTable ldt_update = new DataTable();
ldt_update = SqliteHelper.ExecuteDataTable(ls_sql_update);
if (ldt_update == null)
continue;
if (ldt_update.Rows.Count <= 0)
continue;
for (int i = 0; i < ldt_update.Rows.Count; i++)
{
string ls_sql_items = "select * from customer where cus_code='" + ldt_update.Rows[i]["cus_code"] + "'";
DataTable ldt_items = new DataTable();
ldt_items = SqliteHelper.ExecuteDataTable(ls_sql_items);
string ls_sql_values = "";
ls_sql_values += "'" + ldt_items.Rows[0]["cus_code"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["cus_name"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["customercreatetime"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["QQNo"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["Phone"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["address"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["linkman"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["trafficeAddr"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["cus_prov"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["Cus_City"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["mobile"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["modifydate"] + "',";
ls_sql_values += "'" + ldt_items.Rows[0]["e_mail"] + "'";
//DataTable ldt_tb = SqlHelper.of_downBasedata("cus_code,fax,cus_name,customercreatetime,QQNo,Phone,address,linkman,trafficeAddr,cus_prov,Cus_City,mobile,modifydate,e_mail", "synccustomer", ls_sql_values);
string json = Json.DataTableToJson(ldt_items);
DataTable ldt_rt = SqlHelper.of_downBasedata("", "synccustomer", "", json);
string json_rt = JsonHelper.DatatableToJson(ldt_rt);
if (ldt_rt.Rows.Count > 0)
{
string ls_sql_item = "select count(*) from customer where cus_code='" + ldt_rt.Rows[0]["cus_code"].ToString().Trim() + "'";
int row = 0;
row = SqliteHelper.ExecuteNum(ls_sql_item);
if (row <= 0)
{
//更新本地数据
n_create_sql lnv_sql = new n_create_sql();
lnv_sql.of_SetTable("customer");
lnv_sql.of_AddCol("cus_code", ldt_rt.Rows[0]["cus_code"].ToString().Trim());
lnv_sql.of_execute("cus_code='" + ldt_items.Rows[0]["cus_code"] + "'");
n_create_sql lnv_sql_getmain = new n_create_sql();
lnv_sql_getmain.of_SetTable("getmain");
lnv_sql_getmain.of_AddCol("cus_code", ldt_rt.Rows[0]["cus_code"].ToString().Trim());
lnv_sql_getmain.of_execute("cus_code='" + ldt_items.Rows[0]["cus_code"] + "'");
n_create_sql lnv_sql_getdetail = new n_create_sql();
lnv_sql_getdetail.of_SetTable("getdetail");
lnv_sql_getdetail.of_AddCol("cus_code", ldt_rt.Rows[0]["cus_code"].ToString().Trim());
lnv_sql_getdetail.of_execute("cus_code='" + ldt_items.Rows[0]["cus_code"] + "'");
//更新服务器数据
baseclass.n_create_sql lnv_sql_update = new baseclass.n_create_sql("customer");
lnv_sql_update.of_AddCol("customercreatetime", ldt_items.Rows[0]["customercreatetime"].ToString());
lnv_sql_update.of_AddCol("QQNo", ldt_items.Rows[0]["QQNo"].ToString());
lnv_sql_update.of_AddCol("Phone", ldt_items.Rows[0]["Phone"].ToString());
lnv_sql_update.of_AddCol("address", ldt_items.Rows[0]["address"].ToString());
lnv_sql_update.of_AddCol("linkman", ldt_items.Rows[0]["linkman"].ToString());
lnv_sql_update.of_AddCol("Cus_City", ldt_items.Rows[0]["Cus_City"].ToString());
lnv_sql_update.of_AddCol("mobile", ldt_items.Rows[0]["mobile"].ToString());
lnv_sql_update.of_AddCol("trafficeAddr", ldt_items.Rows[0]["trafficeAddr"].ToString());
lnv_sql_update.of_AddCol("modifydate", DateTime.Now.ToString());
lnv_sql_update.of_AddCol("e_mail", ldt_items.Rows[0]["e_mail"].ToString());
lnv_sql_update.of_execute("cus_code=@cus_code=",
"@cus_code=" + ldt_rt.Rows[0]["cus_code"].ToString().Trim());
}
}
}
System.Threading.Thread.Sleep(5000);
}
}
/// <summary>
/// 把datatable某行的值，生成insert的SQL语句
/// </summary>
/// <param name="dt"></param>
/// <param name="as_tablename"></param>
/// <param name="ai_row"></param>
/// <returns></returns>
public static string of_InsertToTable(DataTable dt, string as_tablename, DataRow dr,out GysoftParameter[] ap_value)
{
ErrStr = "";
if (dt == null)
{
ap_value = new GysoftParameter[1];
return "";
}
ap_value = new GysoftParameter[dt.Columns.Count];
n_create_sql lnv_sql = new n_create_sql();
lnv_sql.of_SetTable(as_tablename);
string ls_step = "1";
string ls_rc="";
try
{
ls_step = "2";
for (int li_col = 0; li_col < dt.Columns.Count; li_col++)
{
string ls_colName;
ls_colName = dt.Columns[li_col].ColumnName.ToString();
lnv_sql.of_AddCol(ls_colName, "@" + ls_colName);
ap_value[li_col] = new GysoftParameter();
ap_value[li_col].PaName = ls_colName;
ap_value[li_col].Value = dr[li_col].ToString();
}
ls_step = "3";
ls_rc = lnv_sql.of_GetInsertSQL();
ls_step = "4";
}
catch (Exception ex)
{
ErrStr =ls_step+" "+ ex.ToString();
}
return ls_rc;
}
public static string of_InsertToTable(DataTable dt, string as_tablename, DataRow dr)
{
ErrStr = "";
if (dt == null)
{
return "";
}
n_create_sql lnv_sql = new n_create_sql();
lnv_sql.of_SetTable(as_tablename);
string ls_step = "1";
string ls_rc = "";
try
{
ls_step = "2";
for (int li_col = 0; li_col < dt.Columns.Count; li_col++)
{
string ls_colName;
ls_colName = dt.Columns[li_col].ColumnName.ToString();
string ls_value;
ls_value = dr[li_col].ToString();
ls_value = ls_value.Replace("'", "''");
lnv_sql.of_AddCol(ls_colName, ls_value);
}
ls_step = "3";
ls_rc = lnv_sql.of_GetInsertSQL();
ls_step = "4";
}
catch (Exception ex)
{
ErrStr = ls_step + " " + ex.ToString();
}
return ls_rc;
}
/// <summary>
/// 把datatable某行的值，生成insert的SQL语句
/// </summary>
/// <param name="dt"></param>
/// <param name="as_tablename"></param>
/// <param name="ai_row"></param>
/// <returns></returns>
public static string of_InsertToTable(DataTable dt, string as_tablename, int ai_row, out GysoftParameter[] ap_value)
{
ErrStr = "";
if (dt == null)
{
ap_value = new GysoftParameter[1];
return "";
}
if (ai_row>=dt.Rows.Count)
{
ap_value = new GysoftParameter[1];
return "";
}
ap_value = new GysoftParameter[dt.Columns.Count];
n_create_sql lnv_sql = new n_create_sql();
lnv_sql.of_SetTable(as_tablename);
for (int li_col = 0; li_col < dt.Columns.Count; li_col++)
{
string ls_colName;
ls_colName = dt.Columns[li_col].ColumnName.ToString();
lnv_sql.of_AddCol(ls_colName, "@" + ls_colName);
ap_value[li_col].PaName = ls_colName;
ap_value[li_col].Value = dt.Rows[ai_row][li_col].ToString();
}
string ls_rc;
ls_rc = lnv_sql.of_GetInsertSQL();
return ls_rc;
}