public static System.Data.DataTable mCashs_SumBrand(DateTime NgayBatDau, DateTime NgayKetThuc)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Cashs_SumBrand;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayBatDau;
Parameter.Value = NgayBatDau;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayKetThuc;
Parameter.Value = NgayKetThuc;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
public static DataTable mSales_OneStoreChart(Guid KeyChiNhanh, DateTime NgayBatDau, DateTime NgayKetThuc)
{
using (DbCommand DbCommand = clsConnect.CreateCommand())
{
DbCommand.CommandText = SpMobile_Sales_OneCharts;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PKeyChiNhanh;
DbParameter.Value = KeyChiNhanh;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PNgayBatDau;
DbParameter.Value = NgayBatDau;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PNgayKetThuc;
DbParameter.Value = NgayKetThuc;
DbParameters.Add(DbParameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch (Exception ex) { DataTable = null; }
return(DataTable);
}
}
public static System.Data.DataTable mGoods_FilterGoods(decimal TileHangDuoi, decimal TileHangTren)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Goods_FiltLists;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PTileHangDuoi;
Parameter.Value = TileHangDuoi;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PTileHangTren;
Parameter.Value = TileHangTren;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
public static decimal mCashs_SumSales(Guid KeyChiNhanh, Guid KeyThuNgan, Guid KeyNguoiDung,
DateTime NgayBatDau, DateTime NgayKetThuc)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Cashs_SumSales;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyChiNhanh;
Parameter.Value = KeyChiNhanh;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyThuNgan;
Parameter.Value = KeyThuNgan;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyNguoiDung;
Parameter.Value = KeyNguoiDung;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayBatDau;
Parameter.Value = NgayBatDau;
Parameters.Add(Parameter);
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PNgayKetThuc;
Parameter.Value = NgayKetThuc;
Parameters.Add(Parameter);
decimal nSumBan = 0;
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{
if (DbDataReader.Read())
{
nSumBan = DbDataReader.GetDecimal(DbDataReader.GetOrdinal(PBanTienGiao));
DbDataReader.Close();
}
}
}
catch { nSumBan = 0; }
return(nSumBan);
}
}
public static System.Data.DataTable mCashs_OneShift(Guid KeyTienGiao)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Cashs_OneShift;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyTienGiao;
Parameter.Value = KeyTienGiao;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
public static System.Data.DataTable mClient_FilterClient(string TenCardClient)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Client_FiltClient;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PCodCardClient;
Parameter.Value = TenCardClient;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch { DataTable = null; }
return(DataTable);
}
}
/// <summary>
/// Lay danh sach don hang thuoc cua khach hang
/// </summary>
/// Tra ve: Danh sach don hang duoc dang ky
public static System.Data.DataTable m2018_GetallOrders(Guid KeyCardClient)
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_2018_GetallOrders;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters;
System.Data.Common.DbParameter Parameter;
Parameter = DbCommand.CreateParameter();
Parameter.ParameterName = PKeyCardClient;
Parameter.Value = KeyCardClient;
Parameters.Add(Parameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch (Exception ex) {
DataTable = null;
}
return(DataTable);
}
}
public static bool Login(string UserName, string Password, out string TenNguoiDung)
{
bool HasRows = false; TenNguoiDung = string.Empty;
if (clsConnect.DB_OpenConnection("", "", "", ""))
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Users_Logins;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PMobNguoiDung;
DbParameter.Value = UserName;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PPasNguoiDung;
string EnPass = clsConnect.Encrypt(Password);
DbParameter.Value = EnPass;
DbParameters.Add(DbParameter);
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{
HasRows = DbDataReader.HasRows;
if (DbDataReader.Read())
{
TenNguoiDung = DbDataReader.GetString(DbDataReader.GetOrdinal(PTenNguoiDung));
}
else
{
TenNguoiDung = "Chưa xác định";
}
}
}
catch { }
}
}
return(HasRows);
}
private void AttachParameters(DbParameterCollection dbParameters, object parameters)
{
var dic = parameters as IDictionary<string, object>;
if (dic != null)
{
foreach (var parameter in dic)
{
dbParameters.Add(CreateParameter(parameter.Key, parameter.Value));
}
}
else
{
var properties = parameters.GetType().GetRuntimeProperties();
foreach (var property in properties)
{
if (property.CanRead)
{
dbParameters.Add(CreateParameter(property.Name, property.GetValue(parameters)));
}
}
}
}
/// <summary>
/// Get or set command parameter. If a command parameter not found,
/// a new parameter with the given field identifier is created and returned.
/// </summary>
/// <param name="fieldIdentifier">Field identifier without '@' as prefix.</param>
/// <returns>Returns a command parameter associated with the field identifier. If not found,
/// a new parameter with the given field identifier is created and returned.</returns>
public DbParameter this[string fieldIdentifier]
{
get
{
if (_parameters.Contains("@" + fieldIdentifier))
{
return(_parameters["@" + fieldIdentifier]);
}
else
{
DbParameter parameter = _command.CreateParameter();
parameter.ParameterName = "@" + fieldIdentifier;
_parameters.Add(parameter);
return(parameter);
}
}
set
{
_parameters["@" + fieldIdentifier] = value;
}
}
/// <summary>
/// Kiem tra xem so dien thoai khach hang da co tren he thong chua
/// </summary>
/// Tra ve: True - Da ton tại; False - Chua ton tai
public static bool m2018_CheckClient(string CodCardClient)
{
using (System.Data.Common.DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_2018_CheckClient;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PCodCardClient;
DbParameter.Value = CodCardClient;
DbParameters.Add(DbParameter);
bool HasRows = false;
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{ HasRows = DbDataReader.HasRows; }
}
catch { }
return(HasRows);
}
}
protected override void AddParameter(DbParameterCollection collection, string name, object value)
{
collection.Add(new SqlParameter(name, value));
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="parameters">SqlParameterCollection的参数对象</param>
/// <param name="parmsCollection">参数值</param>
private DbParameterCollection AddParms(DbParameterCollection parmsCollection, DbParameter[] parameters)
{
parmsCollection.Clear();
if (parameters != null)
{
foreach (var parms in parameters)
{
parmsCollection.Add(parms);
}
}
return parmsCollection;
}
private static string FillLikeCommand(DbParameterCollection dbParams, string columnName, string paraName, object data, bool not, bool or)
{
if (data is string)
{
string s = data.ToString();
if (IsAcurateMode(s))
{
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, s.Substring(0, s.Length - 1)));
return (or ? " OR " : "") + columnName + (not ? "!=" : " = ") + paraName;
}
else
{
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, "%" + data + "%"));
return (or ? " OR " : "") + columnName + (not ? " NOT LIKE " : " LIKE ") + paraName;
}
}
else
{
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, data));
return (or ? " OR " : "") + columnName + (not ? "!=" : " = ") + paraName;
}
}
internal static string FillDbCommand(ISearchExpression se, DbParameterCollection dbParams, bool isHaving, ref int paramCnt, Dictionary<string, string> selectAsColumns)
{
if (se == null)
return null;
if (se is QueryExpression)
return null;
if (se is LogicalExpression)
{
LogicalExpression le = se as LogicalExpression;
switch (le.LogicOperator)
{
case LogicalOperator.And:
{
string b1 = FillDbCommand(le.LeftHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns);
string b2 = FillDbCommand(le.RightHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns);
if (!string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2))
{
return "(" + b1 + " AND " + b2 + ")";
}
else if (!string.IsNullOrEmpty(b1) && string.IsNullOrEmpty(b2))
{
return b1;
}
else if (string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2))
{
return b2;
}
else
{
return null;
}
}
case LogicalOperator.Or:
{
string b1 = FillDbCommand(le.LeftHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns);
string b2 = FillDbCommand(le.RightHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns);
if (!string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2))
{
return "(" + b1 + " OR " + b2 + ")";
}
else if (!string.IsNullOrEmpty(b1) && string.IsNullOrEmpty(b2))
{
return b1;
}
else if (string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2))
{
return b2;
}
else
{
return null;
}
}
case LogicalOperator.Not:
{
string b1 = FillDbCommand(le.LeftHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns);
if (!string.IsNullOrEmpty(b1))
{
return "(NOT " + b1 + ")";
}
else
{
return null;
}
}
default:
throw new ArgumentException("Invalid LogicalOperator!", "se");
}
}
else if (se is SimpleExpression)
{
SimpleExpression cse = se as SimpleExpression;
if (cse.FullPropertyName.Contains(":"))
{
throw new ArgumentException("Not supported in Sql SearchManager with ':' pattern");
}
bool isInHaving = IsInHaving(se, selectAsColumns);
if ((isHaving && isInHaving) || (!isHaving && !isInHaving))
{
string columnName = cse.FullPropertyName;
if (isHaving)
{
columnName = selectAsColumns[columnName];
}
//string paraName = "@" + GetParameterName(cmd, cse.FullPropertyName + cse.Operator.ToString());
string paraName = CreateParamName(cse, dbParams);
IList arrayData = cse.Values as IList;
if (arrayData != null)
{
paramCnt += arrayData.Count;
}
else
{
paramCnt++;
}
switch (cse.Operator)
{
case SimpleOperator.Eq:
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values));
return columnName + " = " + paraName;
case SimpleOperator.NotEq:
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values));
return columnName + " <> " + paraName;
case SimpleOperator.EqProperty:
return columnName + " = " + cse.Values;
case SimpleOperator.NotEqProperty:
return columnName + " <> " + cse.Values;
case SimpleOperator.Like:
return FillLikeCommand(dbParams, columnName, paraName, cse.Values, false, false);
case SimpleOperator.InG:
{
StringBuilder sb = new StringBuilder();
sb.Append(columnName + " IN (");
IList data = cse.Values as IList;
System.Diagnostics.Debug.Assert(data != null, "SimpleOperator.InG's Values should be ArrayList!");
for (int i = 0; i < data.Count; ++i)
{
string paraNameI = paraName + i.ToString();
sb.Append(paraNameI);
if (i != data.Count - 1)
{
sb.Append(",");
}
dbParams.Add(DbHelper.Instance.CreateParameter(paraNameI, data[i]));
}
sb.Append(')');
return sb.ToString();
}
case SimpleOperator.IsNotNull:
return columnName + " IS NOT NULL";
case SimpleOperator.IsNull:
return columnName + " IS NULL";
case SimpleOperator.Gt:
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values));
return columnName + " > " + paraName;
case SimpleOperator.Lt:
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values));
return columnName + " > " + paraName;
case SimpleOperator.Ge:
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values));
return columnName + " >= " + paraName;
case SimpleOperator.Le:
dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values));
return columnName + " <= " + paraName;
case SimpleOperator.GInG:
{
StringBuilder sb = new StringBuilder();
IList data = cse.Values as IList;
System.Diagnostics.Debug.Assert(data != null, "SimpleOperator.InG's Values should be ArrayList!");
//if (data.Count <= 1)
//{
sb.Append("( ");
sb.Append(FillLikeCommand(dbParams, columnName, paraName, data[0], false, false));
for (int i = 1; i < data.Count; ++i)
{
string paraNameI = paraName + i.ToString();
sb.Append(FillLikeCommand(dbParams, columnName, paraNameI, data[i], false, true));
}
sb.Append(" )");
//}
//else
//{
// sb.Append(columnName + " IN (");
// for (int i = 0; i < data.Count; ++i)
// {
// string paraNameI = paraName + i.ToString();
// sb.Append(paraNameI);
// if (i != data.Count - 1)
// {
// sb.Append(",");
// }
// dbParams.Add(DbHelper.Instance.CreateParameter(paraNameI, data[i]));
// }
// sb.Append(')');
//}
return sb.ToString();
}
case SimpleOperator.Any:
return null;
case SimpleOperator.Sql:
{
return cse.FullPropertyName;
}
default:
throw new NotSupportedException("Not supported SimpleOperator");
}
}
else
{
return null;
}
}
else
{
throw new NotSupportedException("Invalid SearchExpression of " + se.ToString());
}
}
