public dynamic GetAllCities()
{
get_Cities = "SELECT [id],[name] FROM [Cities] WHERE [isDeleted] = 0 ORDER BY [name]";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(get_Cities));
return(query);
}
public List <CatalogesTemp> GetTopCataloges()
{
sql = "GetTopCataloges";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query <CatalogesTemp>(sql, commandType: CommandType.StoredProcedure)).ToList();
return(query);
}
public List <int> GetChart7_14Day()
{
sql = "GetChart7_14Day";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query <int>(sql, commandType: CommandType.StoredProcedure)).ToList();
return(query);
}
public dynamic GetAllProducts()
{
sql = "GetAllProducts";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(sql, commandType: CommandType.StoredProcedure));
return(query);
}
public dynamic GetTopCataloges7Days()
{
sql = "GetTopCataloges7Days";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(sql, commandType: CommandType.StoredProcedure)).ToList();
return(query);
}
public dynamic GetAllProductsByCataloges(int CatalogesId)
{
sql = "GetAllProductsByCataloges";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(sql, new { CatalogesId }, commandType: CommandType.StoredProcedure));
return(query);
}
public dynamic GetAllTables(int?shopsId)
{
get_Tables = "SELECT t.[id], t.[name] FROM [Tables] t JOIN [Floors] f ON t.FloorsId = f.id " +
"WHERE f.ShopsId = @ShopsId AND t.[isDeleted] = 0 AND f.[isDeleted] = 0 ORDER BY t.[name]";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(get_Tables, new { ShopsId = shopsId })).ToList();
return(query);
}
public void UpdateTables(int id, Tables Tables)
{
update_Tables = "UPDATE [Tables] SET [status] = @status, [updated_at] = GETDATE(), " +
"[updated_by] = @updated_by WHERE [id] = @id AND [isDeleted] = 0";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Tables>(update_Tables,
new { Tables.status, Tables.updated_by, id });
});
}
public dynamic GetAllCataloges()
{
get_Cataloges = "SELECT c.[id], c.[name], c.[ShopsId] AS shopsId, s.[name] AS shopsName " +
"FROM [Cataloges] c JOIN [Shops] s ON c.[ShopsId] = s.[id] " +
"WHERE c.[isDeleted] = 0 AND s.[isDeleted] = 0 ORDER BY c.[name]";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(get_Cataloges));
return(query);
}
public void UpdateFloor(int id, Floors floor)
{
update_Floor = "UPDATE [Floors] SET [name] = @name, [permalink] = @permalink, [updated_at] = GETDATE(), " +
"[updated_by] = @updated_by WHERE [id] = @id AND [isDeleted] = 0";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Floors>(update_Floor,
new { name = floor.name, permalink = floor.permalink, updated_by = floor.updated_by, id = id });
});
}
public void RemoveProducts(int id, string username)
{
remove_Product = "UPDATE [Products] SET [isDeleted] = @isDeleted, " +
"[deleted_at] = GETDATE(), [deleted_by] = @deleted_by WHERE [id] = @id";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Shops>(remove_Product,
new { isDeleted = 1, deleted_by = username, id = id });
});
}
public void CreateFloor(Floors floor)
{
create_Floor = "INSERT INTO [Floors] ([name], [permalink],[ShopsId], [isDeleted], " +
"[created_at], [created_by]) " +
"VALUES(@name, @permalink,@ShopsId, 0, GETDATE(), @created_by)";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Floors>(create_Floor,
new { name = floor.name, permalink = floor.permalink, ShopsId = floor.ShopsId, created_by = floor.created_by });
});
}
public void EditBill(decimal total_money, int id)
{
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query("EditBill",
new
{
total_money,
id
}, commandType: CommandType.StoredProcedure);
});
}
public dynamic GetBillByTable(int?TableId)
{
get_Bills = "SELECT b.[id], t.[name] as tablesName, b.[sub_total], b.[fee_service], b.[total_money], " +
"b.[created_by] FROM[Bills] b JOIN [Tables] t ON b.[TablesId] = t.[id] " +
"WHERE b.[isDeleted] = 0 AND t.[isDeleted] = 0 AND t.[status] <> 0 " +
"AND b.[status] = 0 AND b.[TablesId] = @TableId";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(get_Bills, new { TableId }));
return(query);
}
public void AddImages(int id, string images)
{
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Cataloges>("addImage",
new
{
images,
id
}, commandType: CommandType.StoredProcedure);
});
}
public dynamic GetBillDetailsByBill(int BillsId)
{
sql = "GetBillDetailsByBill";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(sql,
new
{
BillsId
}, commandType: CommandType.StoredProcedure));
return(query);
}
public void RemoveProducts(int id, string username)
{
sql = "RemoveProducts";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Cataloges>(sql,
new
{
username,
id
}, commandType: CommandType.StoredProcedure);
});
}
public List <CatalogesTemp> GetTopCatalogesByDate(string StartDate, string EndDate)
{
sql = "GetTopCatalogesByDate";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query <CatalogesTemp>(sql,
new
{
StartDate,
EndDate
}, commandType: CommandType.StoredProcedure)).ToList();
return(query);
}
public dynamic AddCataloges(Cataloges cataloges)
{
sql = "AddCataloges";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(sql,
new
{
cataloges.name,
cataloges.created_by
}, commandType: CommandType.StoredProcedure)).FirstOrDefault();
return(query);
}
public dynamic EditCataloges(int id, Cataloges cataloges)
{
sql = "EditCataloges";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(sql,
new
{
cataloges.name,
cataloges.updated_by,
id
}, commandType: CommandType.StoredProcedure)).FirstOrDefault();
return(query);
}
public void CreateBills(Bills Bills)
{
create_Bills = "INSERT INTO [Bills] ([time_enter], [status], [sub_total], " +
"[fee_service], [total_money] ,[TablesId], [isDeleted], [created_at], " +
"[created_by]) VALUES(GETDATE(), 0, 0, 0, 0, @TablesId, 0, GETDATE(), @created_by)";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Bills>(create_Bills,
new
{
Bills.TablesId,
Bills.created_by
});
});
}
public void RemoveCataloges(int id, string username)
{
remove_Cataloges = "UPDATE [Cataloges] SET [isDeleted] = @isDeleted, " +
"[deleted_at] = GETDATE(), [deleted_by] = @deleted_by WHERE [id] = @id";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Cataloges>(remove_Cataloges,
new
{
isDeleted = 1,
deleted_by = username,
id
});
});
}
public void Register(Users users)
{
create_Users = "INSERT INTO [Users] ([username], [password], [PositionsId], [ShopsId], [isDeleted], [created_at])" +
"VALUES(@username, @password, @PositionsId, @ShopsId, 0, GETDATE())";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Users>(create_Users,
new
{
users.username,
users.password,
users.PositionsId,
users.ShopsId
});
});
}
public void UpdateCataloges(int id, Cataloges Cataloges)
{
update_Cataloges = "UPDATE [Cataloges] SET [name] = @name, [permalink] = @permalink,[updated_at] = GETDATE(), " +
"[updated_by] = @updated_by WHERE [id] = @id AND [isDeleted] = 0";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Cataloges>(update_Cataloges,
new
{
Cataloges.name,
Cataloges.permalink,
Cataloges.updated_by,
id
});
});
}
public void CreateCataloges(Cataloges Cataloges)
{
create_Cataloges = "INSERT INTO [Cataloges] ([name], [permalink], [isDeleted], " +
"[created_at], [created_by]) " +
"VALUES(@name, @permalink, 0, GETDATE(), @created_by)";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Cataloges>(create_Cataloges,
new
{
Cataloges.name,
Cataloges.permalink,
Cataloges.created_by
});
});
}
public dynamic AddProducts(Products products)
{
sql = "AddProducts";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(sql,
new
{
products.name,
products.images,
products.price,
products.unit,
products.created_by,
products.CatalogesId
}, commandType: CommandType.StoredProcedure)).FirstOrDefault();
return(query);
}
public dynamic GetBillByDate(int?shopsId, string startDate, string endDate)
{
get_Bills = "SELECT b.[id], b.[time_out], t.[name], b.[status], b.[created_by], " +
"b.[sub_total], b.[fee_service], b.[total_money] FROM[Bills] b JOIN[Tables] " +
"t on b.[TablesId] = t.[id] JOIN[Floors] f on f.[id] = t.[FloorsId] " +
"WHERE b.[isDeleted] = 0 AND t.[isDeleted] = 0 AND f.[isDeleted] = 0 " +
"AND f.[ShopsId] = @shopsId AND CAST(b.[time_out] as startDate) >= @startDate " +
"AND CAST(b.[time_out] as endDate) <= @endDate";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(get_Bills, new {
ShopsId = shopsId,
startDate,
endDate
}));
return(query);
}
public void UpdateBills(int id, Bills Bills)
{
update_Bills = "UPDATE [Bills] SET [time_out] = GETDATE(), [status] = @status, " +
"[sub_total] = @sub_total, [total_money] = @total_money, [updated_at] = GETDATE(), " +
"[updated_by] = @updated_by WHERE [id] = @id AND [isDeleted] = 0";
SQLUtils.ExecuteCommand(SQLUtils._connStr, conn =>
{
var query = conn.Query <Bills>(update_Bills,
new
{
Bills.status,
Bills.sub_total,
Bills.total_money,
Bills.updated_by,
id
});
});
}
public int AddBill(string created_by, dynamic order)
{
if (order.note != "")
{
note = order.note;
}
else
{
note = null;
}
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query <int>("AddBill",
new
{
created_by,
note
}, commandType: CommandType.StoredProcedure)).FirstOrDefault();
return(query);
}
public dynamic GetUser(Users users)
{
get_Users = "SELECT u.[id], u.[name], u.[username], u.[PositionsId], u.[ShopsId], " +
"u.[remember_token] FROM [Users] u JOIN [Shops] s ON u.[ShopsId] = s.[id] " +
"WHERE u.[isDeleted] = 0 AND s.[isDeleted] = 0 AND u.[username] = @username " +
"AND u.[password] = @password AND u.[ShopsId] = @ShopsId " +
"AND CAST(s.[time_open] as [date]) <= GETDATE() AND " +
"CAST(GETDATE() as [date]) <= CAST(s.[time_close] as [date])";
var query = SQLUtils.ExecuteCommand(SQLUtils._connStr,
conn => conn.Query(get_Users,
new
{
users.username,
users.password,
users.ShopsId
})).FirstOrDefault();
return(query);
}