//查询base_year是否有重复HS代码库（新增）
public DataSet check_base_year(JObject json)
{
string sql = @"select * from base_year where name = '{0}' and kind = '{1}'";
sql = string.Format(sql, json.Value <string>("NAME"), (int)Base_YearKindEnum.Decl_HS);
return(DBMgrBase.GetDataSet(sql));
}
private void getclass()
{
string table = Request["table"];
string sql = String.Empty;
string json = String.Empty;
switch (table)
{
case "chapter":
sql = @"select t1.*, t2.name as createmanname
from base_declhstype t1
left join sys_user t2
on t1.createman = t2.id ";
break;
case "smallclass":
sql = @"select t1.*, t2.code as typecode, t2.name as typename,t1.code || '('|| t1.name ||')' as codeaddname,t2.code || '|' || t2.name as typecodeaddname
from base_declhschapter t1
left join base_declhstype t2
on t1.typecode = t2.code where t1.code ='" + Request["code"] + "' ";
break;
}
if (!string.IsNullOrEmpty(sql))
{
DataTable dt = DBMgrBase.GetDataTable(sql);
json = JsonConvert.SerializeObject(dt, iso);
}
else
{
json = "[]";
}
Response.Write(json);
Response.End();
}
//获取国家名称
private void getcountryname()
{
string table = Request["table"];
string sql = String.Empty;
string json = String.Empty;
switch (table)
{
case "insp_portout":
sql = @"select t1.*, t2.name as createmanname, t3.name as stopmanname
from base_inspcountry t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id";
break;
case "base_harbour":
sql = @"select t1.*, t2.name as createmanname, t3.name as stopmanname
from base_country t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id
where t1.enabled = '1'";
break;
case "base_booksdata":
sql = @"select t1.*, t2.name as createmanname, t3.name as stopmanname
from base_decltradeway t1
left join sys_user t2
on t1.createman = t2.id
left join sys_user t3
on t1.stopman = t3.id";
break;
case "sys_woodpacking":
sql = @"select hscode, hsname, inspectionregulatory, customregulatory
from base_insphs
where enabled = 1";
break;
case "sys_declarationcar":
sql = @"select * from base_motorcade where enabled = '1'";
break;
}
if (!string.IsNullOrEmpty(sql))
{
DataTable dt = DBMgrBase.GetDataTable(sql);
json = JsonConvert.SerializeObject(dt, iso);
}
else
{
json = "[]";
}
Response.Write(json);
Response.End();
}
public DataTable export_rela_harbor(string strWhere)
{
string sql = @"select t1.*,t2.name as DeclPortName,t3.name as inspportname,t4.name as createmanname,t5.name as stopmanname from rela_port t1 left join base_harbour
t2 on t1.declport = t2.code left join base_port t3 on t1.inspport = t3.code left join sys_user t4 on t1.createman=t4.id left join sys_user t5 on t1.stopman=t5.id {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
public DataTable export_rela_hs_ciq(string strWhere)
{
string sql = @"select t1.*,t2.hsname as hsname,t3.ciqname,t4.name as createmanname,t5.name as stopmanname from rela_hsciq t1 left join base_insphs t2 on
t1.hscode=(t2.hscode||t2.extracode) left join base_ciqcode t3 on t1.ciqcode=t3.ciq left join sys_user t4 on t1.createman=t4.id left join sys_user t5 on t1.stopman=t5.id {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
public DataTable export_rela_currency(string strWhere)
{
string sql = @"select t1.*,t2.name as declcurrencyname,t3.name as inspcurrencyname,t4.name as createmanname,t5.name as stopmanname from rela_currency t1 left join base_declcurrency
t2 on t1.declcurrency=t2.code left join base_currency t3 on t1.inspcurrency=t3.code left join sys_user t4 on t1.createman=t4.id left join sys_user t5 on t1.stopman=t5.id {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
public DataTable export_rela_container(string strWhere)
{
string sql = @"select t1.*,t2.name as createmanname,t3.name as stopmanname,t4.name as containersizename,t5.name as containertypename from rela_container t1 left join
sys_user t2 on t1.createman=t2.id left join sys_user t3 on t1.stopman=t3.id left join base_containersize t4 on t4.code=t1.containersize left join base_containertype t5 on
t1.containertype=t5.code " + strWhere;
return(DBMgrBase.GetDataTable(sql));
}
public DataTable export_rela_declstatus(string strWhere)
{
string sql = @"select t1.*,decode(t1.type,'1','报关','2','报检') as typeclass,t2.name as createmanname,t3.name as stopmanname,t4.name as busistatusname from base_statusConfig t1 left join sys_user t2 on t1.createman=t2.id
left join sys_user t3 on t1.stopman=t3.id left join base_declstatus t4 on t1.busistatus=t4.code {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
//获取修改之前的数据
public DataTable getBeforeChangeData(string table, JObject json)
{
string BeforChange = "BeforChange";
string sql = sc.get_base_sql("BeforChange", table, json, "");
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//修改
public int updataTable(string table, JObject json)
{
string update = "update";
string sql = sc.get_base_sql(update, table, json, "");
int i = DBMgrBase.ExecuteNonQuery(sql);
return(i);
}
//修改提交时检查是否重复
public int update_check_repeat(string table, JObject json)
{
string updateCheckrepeat = "updateCheckrepeat";
string sql = sc.get_base_sql(updateCheckrepeat, table, json, "");
int count = DBMgrBase.GetDataTable(sql).Rows.Count;
return(count);
}
//修改按钮所需sql
public int update_base_year(JObject json, string stopman)
{
string sql = @"update base_year set name ='{0}',startdate=to_date('{1}','yyyy/mm/dd '),enddate=to_date('{2}','yyyy/mm/dd '),customarea='{3}',
remark='{4}',enabled='{6}',stopman='" + stopman + "' where id='{5}'";
sql = String.Format(sql, json.Value <string>("NAME"), json.Value <string>("STARTDATE"), json.Value <string>("ENDDATE"), "", json.Value <string>("REMARK"), json.Value <string>("ID"), json.Value <string>("ENABLED"));
return(DBMgrBase.ExecuteNonQuery(sql));
}
//新增
public int insertTable(string table, JObject json)
{
string insert = "insert";
string sql = sc.get_base_sql(insert, table, json, "");
int i = DBMgrBase.ExecuteNonQuery(sql);
return(i);
}
public string LoadSupplier()
{
string sql = "select t.* from sys_customer t where t.enabled=1 and t.isshipper=1";
DataTable dt = DBMgrBase.GetDataTable(sql);
string json = JsonConvert.SerializeObject(dt);
return("{success:true,rows:" + json + "}");
}
public DataTable export_rela_region(string strWhere)
{
string sql = @"select t1.*,t2.name as DeclRegionName,t3.name as inspregionname,t4.name as createmanname,t5.name as stopmanname from rela_withinregion t1 left join base_shipping_destination t2 on
t1.declregion = t2.code left join base_withinregion t3 on t1.inspregion = t3.code left join sys_user t4 on t1.createman=t4.id left join sys_user t5 on t1.stopman=t5.id {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
public DataTable export_rela_trade(string strWhere)
{
string sql = @"select t1.*,t2.name as decltradename,t3.name as insptradename,t4.name as createmanname,t5.name as stopmanname from rela_package t1 left join base_packing t2 on
t1.declpackage=t2.code left join base_insppackage t3 on t1.insppackage=t3.code left join sys_user t4 on t1.createman=t4.id left join sys_user t5 on t1.stopman=t5.id {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
public DataTable export_rela_companynature(string strWhere)
{
string sql = @"select t1.*,t2.name as DeclCompanyNatureName,t3.name as InspCompanyNatureName,t4.name as createmanname,t5.name as stopmanname from rela_companynature t1 left join sys_companynature t2 on
t1.declcompanynature = t2.code left join base_inspcompanynature t3 on t1.inspcompanynature = t3.code left join sys_user t4 on t1.createman=t4.id left join sys_user t5 on t1.stopman=t5.id {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
public DataTable export_rela_hs_ciq(string strWhere)
{
string sql = @"select t1.*,t2.name as createmanname,t3.name as stopmanname from base_new_hsciq t1
left join sys_user t2 on t1.createman=t2.id left join sys_user t3 on t1.stopman=t3.id {0}";
sql = string.Format(sql, strWhere);
return(DBMgrBase.GetDataTable(sql));
}
//更新时检查是否存在该CIQ代码
public DataTable update_check_repeat_base_ciqcode(JObject json)
{
string sql = @"select * from base_ciqcode where ciq = '{0}' and id not in ('{1}')";
sql = String.Format(sql, json.Value <string>("CIQ"), json.Value <string>("ID"));
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//更新时检查是否重复
public DataTable check_update_repeat(JObject json)
{
string sql = @"select * from sys_unitconvert where unitcode1 = '{0}' and unitcode2 = '{1}' and convertrate = '{2}' and id not in ('{3}')";
sql = String.Format(sql, json.Value <string>("UNITCODE1"), json.Value <string>("UNITCODE2"), json.Value <string>("CONVERTRATE"), json.Value <string>("ID"));
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//更新时验证HS编码重复
public DataTable check_repeat_base_insphs_update(JObject json, string yearid)
{
string sql = @"select * from base_insphs where hscode = '{0}' and id not in ('{1}') and yearid = '{2}'";
sql = String.Format(sql, json.Value <string>("HSCODE"), json.Value <string>("ID"), yearid);
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
public DataTable check_hscode_repeat(string hscode, string ciqcode, string strWhere)
{
string sql = "select * from base_new_hsciq t1 where t1.enabled=1 and t1.hscode='{0}' and t1.ciqcode='{1}'";
sql = string.Format(sql, hscode, ciqcode) + strWhere;
DataTable check_table = DBMgrBase.GetDataTable(sql);
return(check_table);
}
private DataTable check_hscode_repeat(string hscode, string strWhere)
{
string sql = "select * from base_containerstandard where enabled = '1' and hscode = '{0}' {1}";
sql = string.Format(sql, hscode, strWhere);
DataTable check_table = DBMgrBase.GetDataTable(sql);
return(check_table);
}
//导出
public DataTable ExportTable(string table, string strWhere)
{
string export = "export";
JObject json = null;
string sql = sc.get_base_sql(export, table, json, "") + strWhere;
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
public DataTable Before_Change(JObject json)
{
string sql = @"select * from sys_recordinfo where id = '{0}'";
sql = String.Format(sql, json.Value <string>("ID"));
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//根据id来找出
public DataTable befor_change(JObject json)
{
string sql = @"select * from sys_unitconvert where id = '{0}'";
sql = String.Format(sql, json.Value <string>("ID"));
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
public DataTable update_check_sys_recordinfo(JObject json)
{
string sql = @"select * from sys_recordinfo where code = '{0}' and id not in ('{1}')";
sql = String.Format(sql, json.Value <string>("CODE"), json.Value <string>("ID"));
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//更新是否重复
public DataTable check_update_repeat(JObject json)
{
string sql = @"select * from base_specialhsconvert where code = '{0}' and id not in ('{1}')";
sql = String.Format(sql, json.Value <string>("CODE"), json.Value <string>("ID"));
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//重复CIQ代码验证
public DataTable check_repeat_base_ciqcode(JObject json, string yearid)
{
string sql = "select * from base_ciqcode where ciq = '{0}'";
sql = String.Format(sql, json.Value <string>("CIQ"), yearid);
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}
//根据名字获取单位code
public DataTable getUnitName(string unitname)
{
string sql = @"select * from base_declproductunit where name = '{0}'";
sql = String.Format(sql, unitname.Trim());
DataTable dt = DBMgrBase.GetDataTable(sql);
return(dt);
}