using System;
using System.Collections.Generic;
using System.Text;
using TSMCISMobile.DataFactory;
using System.Data;
using System.IO;
using System.Data.SqlServerCe;
using System.Collections;
namespace TSMCISMobile.DataAccess
{
///<summary>
/// 模块编号：
/// 作用：
/// 创建者：豹子
/// 创建日期：2008-4-14
/// 最后修改者:
/// 最后修改日
/// 最后修改描述：
///</summary>
class DA_LocalTable
{
#region 构造函数
public DA_LocalTable()
{
}
#endregion
#region 私有变量
private string strSQL = string.Empty;
private string Sql = string.Empty;
//private StringBuilder sbSQL = new StringBuilder();
#endregion
#region 公有变量
#endregion
#region 公开方法
/// <summary>
/// 创建收支明细表
/// </summary>
public void CreatDictTable()
{
try
{
//创建P$DEPT_DICT表
strSQL = @"CREATE TABLE P_MoneyDetailed(s_ID NVARCHAR(10),S_DataTime NVARCHAR(12),S_GETOUTFLAG NVARCHAR(8),S_GETOUTTYPE NVARCHAR(20),I_MONEYSUM NUMERIC,S_ACCOUNTS NVARCHAR(40),S_NOTE NVARCHAR(100))";
StaticAccess.CreatDictTable("P_MoneyDetailed", strSQL);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 创建礼尚往来表
/// </summary>
public void CreatInteTable()
{
try
{
//创建P$DEPT_DICT表
string SQL = @"CREATE TABLE P_Intercourse(s_ID NVARCHAR(10),S_DataTime NVARCHAR(14),S_GETOUTFLAG NVARCHAR(8),S_USERNAME NVARCHAR(12),I_REASON NVARCHAR(20),GOODSNAME NVARCHAR(20), I_MONEY NUMERIC,S_NOTE NVARCHAR(100))";
StaticAccess.CreatDictTable("P_Intercourse", SQL);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 创建用户表
/// </summary>
public void CreatUserTable()
{
try
{
//创建P$DEPT_DICT表
string SQL = @"CREATE TABLE P_UserTable(s_ID NVARCHAR(10),S_UserName NVARCHAR(14),S_PassWord NVARCHAR(8))";
StaticAccess.CreatDictTable("P_UserTable", SQL);
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 添加用户记录
/// </summary>
/// <param name="P_id">编号</param>
/// <param name="p_UserName">用户名</param>
/// <param name="p_PassWord">密码</param>
public void AddUser(string P_id, string p_UserName, string p_PassWord)
{
try
{
string SQL = "INSERT INTO P_UserTable(s_ID,S_UserName,S_PassWord) VALUES ('" + P_id + "','" + p_UserName + "','" + p_PassWord + "')";
StaticAccess.ExecuteNonQuery(SQL);
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 获得用户记录
/// </summary>
/// <returns></returns>
public DataTable SelectUser()
{
try
{
string sql = "Select * from P_UserTable";
DataTable dt = StaticAccess.ReadTable(sql);
return dt;
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 添加收支明细记录
/// </summary>
public bool AddMoneyDetil(string p_Id,
string p_DataTime,
string p_GetOutFlag,
string p_GetOutType,
string p_MoneySum,
string p_Accounte,
string p_Note)
{
try
{
Sql = @"INSERT INTO P_MoneyDetailed(s_ID,S_DataTime,s_GetOutFlag,s_GetOutType,i_MoneySum,s_Accounts,s_Note) VALUES ('" + p_Id + "','"
+ p_DataTime + "','"
+ p_GetOutFlag + "','"
+ p_GetOutType + "','"
+ p_MoneySum + "','"
+ p_Accounte + "','"
+ p_Note + "' )";
StaticAccess.ReadTable(Sql);
return true;
}
catch (Exception ex)
{
throw(ex);
//return false;
}
}
/// <summary>
/// 修改收支明细表
/// </summary>
/// <param name="p_id"></param>
/// <param name="p_DataTime"></param>
/// <param name="p_GetOutFlag"></param>
/// <param name="p_GetType"></param>
/// <param name="p_MoneySum"></param>
/// <param name="p_Accounts"></param>
/// <param name="p_Noet"></param>
/// <returns></returns>
public bool UpDataList(string p_id,
string p_DataTime,
string p_GetOutFlag,
string p_GetType,
string p_MoneySum,
string p_Accounts,
string p_Noet)
{
try
{
string sql = @"UPDATE P_MoneyDetailed Set s_ID = '" + p_id +
"',S_DataTime = '" + p_DataTime +
"',s_GetOutFlag = '" + p_GetOutFlag +
"',s_GetOutType = '" + p_GetType +
"',i_MoneySum = '" + p_MoneySum +
"',s_Accounts = '" + p_Accounts +
"',s_Note = '" + p_Noet +
"' where s_ID = '" + p_id + "'";
StaticAccess.ReadTable(sql);
return true;
}
catch (Exception ex)
{
throw(ex);
//return false;
}
}
/// <summary>
/// 删除收支明细
/// </summary>
/// <param name="p_Id"></param>
/// <returns></returns>
public bool DeleteMoneyLost(string p_Id)
{
try
{
string sql = @"DELETE FROM P_MoneyDetailed WHERE s_ID = '" + p_Id + "'";
StaticAccess.ReadTable(sql);
return true;
}
catch (Exception ex)
{
throw(ex);
}
}
/// <summary>
/// 得到收支明细表数据
/// </summary>
/// <returns></returns>
public DataTable GetMoneyList()
{
try
{
strSQL = @"SELECT * FROM P_MoneyDetailed ORDER BY S_ID";
DataTable dtbl = new DataTable();
dtbl = StaticAccess.ReadTable(strSQL);
return dtbl;
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 得到礼尚往来表数据
/// </summary>
/// <returns></returns>
public DataTable GetMoneyInteList()
{
try
{
strSQL = @"SELECT * FROM P_Intercourse ORDER BY S_ID";
DataTable dtbl = new DataTable();
dtbl = StaticAccess.ReadTable(strSQL);
return dtbl;
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 添加礼尚往来记录
/// </summary>
public bool AddMoneyInte(string p_Id,
string p_DataTime,
string p_GetOutFlag,
string p_UserName,
string p_Reason,
string p_GoodsName,
string p_MONEY,
string p_Note)
{
try
{
Sql = @"INSERT INTO P_Intercourse(s_ID,S_DataTime,s_GetOutFlag,S_USERNAME,I_REASON,GOODSNAME,I_MONEY，s_Note) VALUES ('" + p_Id + "','"
+ p_DataTime + "','"
+ p_GetOutFlag + "','"
+ p_UserName + "','"
+ p_Reason + "','"
+ p_GoodsName + "','"
+ p_MONEY + "','"
+ p_Note + "' )";
StaticAccess.ReadTable(Sql);
return true;
}
catch (Exception ex)
{
throw (ex);
//return false;
}
}
/// <summary>
/// 统计本月收支情况
/// </summary>
/// <returns></returns>
public ArrayList GetStatData()
{
try
{
//获取服务器时间
CommonFunc Fun = new CommonFunc();
ArrayList Ar = new ArrayList();
//DateTime DateTimeNow = Fun.GetSysDateTime();
DateTime DateTimeNow = DateTime.Now;
string DataTimeStr = DateTimeNow.ToString("yyyy-MM-dd");
string Year = DateTimeNow.Year.ToString();
string Month = DateTimeNow.Month.ToString();
int month = Convert.ToInt32(Month);
if (month < 10)
{
Month = "0" + Month;
}
string TimeStar = Year + "-" + Month + "-" + "01";
string TimeEnd = Year + "-" + Month + "-" + "31";
//查询当天的收入或支出之和
string sql1 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime = '" + DataTimeStr + "' AND s_GetOutFlag = '收入'";
//查询当天的收入或支出之和
string sql2 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime = '" + DataTimeStr + "' AND s_GetOutFlag = '支出'";
//查询当月的收入或支出之和
string sql3 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime >= '" + TimeStar + "' AND S_DataTime <='" + TimeEnd + "' AND s_GetOutFlag = '收入'";
//查询当月的收入或支出之和
string sql4 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime >= '" + TimeStar + "' AND S_DataTime <='" + TimeEnd + "' AND s_GetOutFlag = '支出'";
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
dt1 = StaticAccess.ReadTable(sql1);
dt2 = StaticAccess.ReadTable(sql2);
dt3 = StaticAccess.ReadTable(sql3);
dt4 = StaticAccess.ReadTable(sql4);
string Sum1 = dt1.Rows[0][0].ToString();
string Sum2 = dt2.Rows[0][0].ToString();
string Sum3 = dt3.Rows[0][0].ToString();
string Sum4 = dt4.Rows[0][0].ToString();
Ar.Add(Sum1);
Ar.Add(Sum2);
Ar.Add(Sum3);
Ar.Add(Sum4);
return Ar;
}
catch (Exception ex)
{
throw(ex);
}
}
/// <summary>
/// 得到按条件收支明细表数据
/// </summary>
/// <returns></returns>
public DataTable GetSelectMoney(string p_DataStar,
string p_DataEnd,
string p_GetOut,
string p_Typet,
string p_Accounte)
{
try
{
strSQL = @"SELECT * FROM P_MoneyDetailed WHERE 1 = 1 ";
if (p_DataStar != string.Empty)
{
strSQL += "AND S_DataTime >= '" + p_DataStar + "' AND S_DataTime <='" + p_DataEnd + "'";
}
if (p_GetOut != string.Empty)
{
strSQL += " AND s_GetOutFlag = '" + p_GetOut + "'";
}
if (p_Typet != string.Empty)
{
strSQL += " AND s_GetOutType = '" + p_Typet + "'";
}
if (p_Accounte != string.Empty)
{
strSQL += " AND s_Accounts = '" + p_Accounte + "'";
}
DataTable dtbl = new DataTable();
dtbl = StaticAccess.ReadTable(strSQL);
return dtbl;
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 与Oracle数据库同步，更新本地字典表
/// </summary>
public void SyncWithOracle(DataSet p_ds)
{
//插入P$DEPT_DICT表
int i;
ClearTable("P$DEPT_DICT");
try
{
//反序列化二进制流
//DataSet ds = new DataSet();
//byte[] bDictTable = ClientGlobal.GetWebService().AddDictTable();
//MemoryStream ms = new MemoryStream(bDictTable);
//IFormatter bf = new BinaryFormatter();
//object obj = bf.Deserialize(ms);
//ds = (DataSet)obj;
//ms.Close();
DataRow myRow = null;
for (i = 0; i < p_ds.Tables[0].Rows.Count; i++)
{
strSQL = @"INSERT INTO P$DEPT_DICT
(
S_DEPTCODE,
C_DEPTTYPECODE,
S_DEPTNAME,
S_BRANCHNUM,
S_DEPTABBR,
S_FATHERDEPTCODE,
C_ABLEFLAG,
I_SORTID
)
VALUES (
?,
?,
?,
?,
?,
?,
?,
?
)";
myRow = p_ds.Tables[0].Rows[i];
SqlCeCommand mySqlCeCommand = new SqlCeCommand();
mySqlCeCommand.CommandText = strSQL;
//mySqlCeCommand.Parameters.Add("S_DEPTCODE",SqlDbType.VarChar);
if (myRow["S_DEPTCODE"] == null || myRow["S_DEPTCODE"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_DEPTCODE", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_DEPTCODE", myRow["S_DEPTCODE"].ToString());
}
if (myRow["C_DEPTTYPECODE"] == null || myRow["C_DEPTTYPECODE"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("C_DEPTTYPECODE", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("C_DEPTTYPECODE", myRow["C_DEPTTYPECODE"].ToString());
}
if (myRow["S_DEPTNAME"] == null || myRow["S_DEPTNAME"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_DEPTNAME", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_DEPTNAME", myRow["S_DEPTNAME"].ToString());
}
if (myRow["S_BRANCHNUM"] == null || myRow["S_BRANCHNUM"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_BRANCHNUM", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_BRANCHNUM", int.Parse(myRow["S_BRANCHNUM"].ToString()));
}
if (myRow["S_DEPTABBR"] == null || myRow["S_DEPTABBR"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_DEPTABBR", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_DEPTABBR", myRow["S_DEPTABBR"].ToString());
}
if (myRow["S_FATHERDEPTCODE"] == null || myRow["S_FATHERDEPTCODE"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_FATHERDEPTCODE", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_FATHERDEPTCODE", myRow["S_FATHERDEPTCODE"].ToString());
}
if (myRow["C_ABLEFLAG"] == null || myRow["C_ABLEFLAG"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("C_ABLEFLAG", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("C_ABLEFLAG", myRow["C_ABLEFLAG"].ToString());
}
if (myRow["I_SORTID"] == null || myRow["I_SORTID"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("I_SORTID", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("I_SORTID", int.Parse(myRow["I_SORTID"].ToString()));
}
StaticAccess.ExecuteNonQuery(mySqlCeCommand);
}
//插入P$DEPT_BRANCH_DICT表
ClearTable("P$DEPT_BRANCH_DICT");
for (i = 0; i < p_ds.Tables[1].Rows.Count; i++)
{
string strSQL = @"INSERT INTO P$DEPT_BRANCH_DICT
(
S_DEPTCODE,
S_BRANCHCODE,
S_BRANCHNAME,
S_BRANCHABBR,
S_BRANCHDESC,
C_ABLEFLAG,
I_SORTID
)
VALUES (
?,
?,
?,
?,
?,
?,
?
)";
myRow = p_ds.Tables[1].Rows[i];
SqlCeCommand mySqlCeCommand = new SqlCeCommand();
mySqlCeCommand.CommandText = strSQL;
if (myRow["S_DEPTCODE"] == null || myRow["S_DEPTCODE"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_DEPTCODE", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_DEPTCODE", myRow["S_DEPTCODE"].ToString());
}
if (myRow["S_BRANCHCODE"] == null || myRow["S_BRANCHCODE"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_BRANCHCODE", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_BRANCHCODE", myRow["S_BRANCHCODE"].ToString());
}
if (myRow["S_BRANCHNAME"] == null || myRow["S_BRANCHNAME"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_BRANCHNAME", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_BRANCHNAME", myRow["S_BRANCHNAME"].ToString());
}
if (myRow["S_BRANCHABBR"] == null || myRow["S_BRANCHABBR"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_BRANCHABBR", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_BRANCHABBR", myRow["S_BRANCHABBR"].ToString());
}
if (myRow["S_BRANCHDESC"] == null || myRow["S_BRANCHDESC"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("S_BRANCHDESC", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("S_BRANCHDESC", myRow["S_BRANCHDESC"].ToString());
}
if (myRow["C_ABLEFLAG"] == null || myRow["C_ABLEFLAG"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("C_ABLEFLAG", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("C_ABLEFLAG", myRow["C_ABLEFLAG"].ToString());
}
if (myRow["I_SORTID"] == null || myRow["I_SORTID"].ToString() == "")
{
mySqlCeCommand.Parameters.Add("I_SORTID", DBNull.Value);
}
else
{
mySqlCeCommand.Parameters.Add("I_SORTID", int.Parse(myRow["I_SORTID"].ToString()));
}
StaticAccess.ExecuteNonQuery(mySqlCeCommand);
}
}
catch (System.Exception E)
{
throw (E);
}
}
/// <summary>
/// 删除表
/// </summary>
private void ClearTable(string p_TableName)
{
strSQL = "DELETE FROM " + p_TableName;
StaticAccess.ExecuteNonQuery(strSQL);
}
/// <summary>
/// 添加班
/// </summary>
/// <returns></returns>
public DataTable AddShift()
{
try
{
strSQL = @"SELECT * FROM P$DEPT_DICT ORDER BY S_DEPTCODE";
DataTable dtbl = new DataTable();
dtbl = StaticAccess.ReadTable(strSQL);
return dtbl;
}
catch (Exception ex)
{
throw (ex);
}
}
/// <summary>
/// 添加组
/// </summary>
/// <returns></returns>
public DataTable AddGroup(string p_ShiftNo)
{
try
{
if (p_ShiftNo == string.Empty)
{
strSQL = @"SELECT * FROM P$DEPT_BRANCH_DICT ORDER BY S_DEPTCODE";
}
else
{
strSQL = @"SELECT * FROM P$DEPT_BRANCH_DICT WHERE S_DEPTCODE='" + p_ShiftNo + "' ORDER BY S_DEPTCODE";
}
DataTable dtbl = new DataTable();
dtbl = StaticAccess.ReadTable(strSQL);
return dtbl;
}
catch (Exception ex)
{
throw (ex);
}
}
#endregion
}
}