using DBUtility;
using JAnCallCenter.Model;
using System;
using System.Collections.Generic;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Text;

namespace JAnCallCenter.DAL
{
	public class GG_JSGN
	{
		public bool Exists(decimal JSID, string GNID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) from GG_JSGN");
			strSql.Append(" where JSID=:JSID and GNID=:GNID ");
			OracleParameter[] parameters = new OracleParameter[]
			{
				new OracleParameter(":JSID", OracleDbType.Int32, 4),
				new OracleParameter(":GNID", OracleDbType.Varchar2, 60)
			};
			parameters[0].Value = JSID;
			parameters[1].Value = GNID;
			return DbHelperOra.Exists(strSql.ToString(), parameters);
		}

		public int AddTran(int jsid, string[] arrgnid, string[] arrymgnid)
		{
			int r = -1;
			List<string> sqlList = new List<string>(10);
			try
			{
				sqlList.Add("delete from GG_JSGN where JSID=" + jsid.ToString());
				for (int i = 0; i < arrgnid.Length; i++)
				{
					string gnid = arrgnid[i];
					sqlList.Add(string.Concat(new string[]
					{
						"insert into GG_JSGN(JSID,GNID)values (",
						jsid.ToString(),
						",'",
						gnid.Trim(),
						"')"
					}));
				}
				sqlList.Add("delete from GG_JSYMCZGN where JSID=" + jsid.ToString());
				for (int i = 0; i < arrymgnid.Length; i++)
				{
					string ymgnid = arrymgnid[i];
					if (ymgnid != "")
					{
						sqlList.Add(string.Concat(new string[]
						{
							"insert into GG_JSYMCZGN(JSID,YMCZid)values (",
							jsid.ToString(),
							",'",
							ymgnid.Trim(),
							"')"
						}));
					}
				}
				r = DbHelperOra.ExecuteSqlTran(sqlList);
			}
			catch
			{
				r = -1;
			}
			finally
			{
				sqlList.Clear();
			}
			return r;
		}

		public bool Add(Model.GG_JSGN model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into GG_JSGN(");
			strSql.Append("JSID,GNID)");
			strSql.Append(" values (");
			strSql.Append(":JSID,:GNID)");
			OracleParameter[] parameters = new OracleParameter[]
			{
				new OracleParameter(":JSID", OracleDbType.Int32, 4),
				new OracleParameter(":GNID", OracleDbType.Varchar2, 60)
			};
			parameters[0].Value = model.JSID;
			parameters[1].Value = model.GNID;
			int rows = DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
			return rows > 0;
		}

		public bool Update(Model.GG_JSGN model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update GG_JSGN set ");
			strSql.Append("JSID=:JSID,");
			strSql.Append("GNID=:GNID");
			strSql.Append(" where JSID=:JSID and GNID=:GNID ");
			OracleParameter[] parameters = new OracleParameter[]
			{
				new OracleParameter(":JSID", OracleDbType.Int32, 4),
				new OracleParameter(":GNID", OracleDbType.Varchar2, 60)
			};
			parameters[0].Value = model.JSID;
			parameters[1].Value = model.GNID;
			int rows = DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
			return rows > 0;
		}

		public bool Delete(decimal JSID, string GNID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("delete from GG_JSGN ");
			strSql.Append(" where JSID=:JSID and GNID=:GNID ");
			OracleParameter[] parameters = new OracleParameter[]
			{
				new OracleParameter(":JSID", OracleDbType.Int32, 4),
				new OracleParameter(":GNID", OracleDbType.Varchar2, 60)
			};
			parameters[0].Value = JSID;
			parameters[1].Value = GNID;
			int rows = DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
			return rows > 0;
		}

		public Model.GG_JSGN GetModel(decimal JSID, string GNID)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select JSID,GNID from GG_JSGN ");
			strSql.Append(" where JSID=:JSID and GNID=:GNID ");
			OracleParameter[] parameters = new OracleParameter[]
			{
				new OracleParameter(":JSID", OracleDbType.Int32, 4),
				new OracleParameter(":GNID", OracleDbType.Varchar2, 60)
			};
			parameters[0].Value = JSID;
			parameters[1].Value = GNID;
            Model.GG_JSGN model = new Model.GG_JSGN();
			DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            Model.GG_JSGN result;
			if (ds.Tables[0].Rows.Count > 0)
			{
				if (ds.Tables[0].Rows[0]["JSID"] != null && ds.Tables[0].Rows[0]["JSID"].ToString() != "")
				{
					model.JSID = decimal.Parse(ds.Tables[0].Rows[0]["JSID"].ToString());
				}
				if (ds.Tables[0].Rows[0]["GNID"] != null && ds.Tables[0].Rows[0]["GNID"].ToString() != "")
				{
					model.GNID = ds.Tables[0].Rows[0]["GNID"].ToString();
				}
				result = model;
			}
			else
			{
				result = null;
			}
			return result;
		}

		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select JSID,GNID ");
			strSql.Append(" FROM GG_JSGN ");
			if (strWhere.Trim() != "")
			{
				strSql.Append(" where " + strWhere);
			}
			return DbHelperOra.Query(strSql.ToString());
		}

		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) FROM GG_JSGN ");
			if (strWhere.Trim() != "")
			{
				strSql.Append(" where " + strWhere);
			}
			object obj = DbHelperSQL.GetSingle(strSql.ToString());
			int result;
			if (obj == null)
			{
				result = 0;
			}
			else
			{
				result = Convert.ToInt32(obj);
			}
			return result;
		}

		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby);
			}
			else
			{
				strSql.Append("order by T.GNID desc");
			}
			strSql.Append(")AS Row, T.*  from GG_JSGN T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperOra.Query(strSql.ToString());
		}
	}
}