/// <summary>
/// Возвращает строку для фильтрации, с id всех компаний до 4-ого уровня.
/// </summary>
/// <returns></returns>
public string GetListOfDepartments()
{
DBWeb web = this.Site.GetWeb("/");
if (web == null)
{
throw new Exception("Узел не найден");
}
DBList departmentsList = web.GetList(Consts.DepartmentsList);
if (departmentsList == null)
{
throw new Exception($"Не найден список по имени '{Consts.DepartmentsList}'");
}
string selectCondition = null;
string result = null;
List <DBItem> lvlOne = new List <DBItem>();
selectCondition = "[Название] = " + Consts.DepartmentLevelQuery;
lvlOne.Add(departmentsList.GetItem(selectCondition));
foreach (DBItem item in lvlOne)
{
result += item.ID + ",";
}
List <DBItem> lvlTwo = new List <DBItem>();
lvlTwo = this.GetChildren(lvlOne, departmentsList);
foreach (DBItem item in lvlTwo)
{
result += item.ID + ",";
}
List <DBItem> lvlThree = new List <DBItem>();
lvlThree = this.GetChildren(lvlTwo, departmentsList);
foreach (DBItem item in lvlThree)
{
result += item.ID + ",";
}
List <DBItem> lvlFour = new List <DBItem>();
lvlFour = this.GetChildren(lvlThree, departmentsList);
foreach (DBItem item in lvlFour)
{
result += item.ID + ",";
}
return(result.TrimEnd(','));
}
/// <summary>
/// Возвращает имя подразделения по его id
/// </summary>
/// <param name="departmentID"></param>
/// <returns></returns>
private string GetDepartmentNameByID(string departmentID)
{
bool b = int.TryParse(departmentID, out int id);
if (!b)
{
throw new FormatException("Передан неверный id подразделения");
}
DBWeb web = this.Site.GetWeb("/");
DBList listDeps = web.GetList("Departments");
DBItem department = listDeps.GetItem(id);
return(department.GetStringValue("Название"));
}
/// <summary>
/// Метод поиска среди "Родительских подразделений" по условию.
/// Результат поиска в свойстве Result.
/// </summary>
/// <param name="depid"></param>
private void Scan(string departmentID)
{
if (string.IsNullOrEmpty(departmentID))
{
throw new ArgumentException("message", nameof(departmentID));
}
DBWeb web = this.Site.GetWeb("/");
DBList Deps = web.GetList(Consts.GetDBList);
string newNode = this.GetParent(departmentID, Deps);
while (Result == null)
{
newNode = this.GetParent(newNode, Deps);
}
}
void BakiyeGuncelle()
{
DataTable dtUzak = DBWeb.GetData_Web("select * from Firmalar with(nolock)");// where isnull(Aktarildi,0)=0");
if (dtUzak.Rows.Count == 0)
{
return;
}
foreach (DataRow dr in dtUzak.Rows)
{
string pkFirma = dr["pkFirma"].ToString();
string Devir = dr["Devir"].ToString();
DB.ExecuteSQL("update Firmalar set Devir=" + Devir.Replace(",", ".") + " where pkFirma=" + pkFirma);
}
//listBoxControl1.Items.Add("Bakiyeler Güncellendi");
formislemleri.Mesajform("Bakiyeler Güncellendi", "S", 200);
}
/// <summary>
/// Вычисляет доступные группы рассылки для текущего пользователя
/// </summary>
/// <returns>SQL-запрос</returns>
public string GetGroups()
{
string result = string.Empty;
//Список группы рассылки
DBList deviringList = this.Site.RootWeb.GetList(Consts.DeliveringGroups.ListName, true);
//список «WSSC_Доступ к группам рассылки»
DBWeb web = this.Site.GetWeb("dms", true);
DBList accessList = web.GetList(Consts.Lists.AccessOfDelivering.ListName, true);
//поля в которых нужно проверять текущего юзера
DBFieldLookupMulti fieldGroups = accessList.GetField <DBFieldLookupMulti>(Consts.Lists.AccessOfDelivering.Fields.UsersGroups, true);
DBFieldLookupMulti fieldUsers = accessList.GetField <DBFieldLookupMulti>(Consts.Lists.AccessOfDelivering.Fields.Users, true);
DBFieldLookupMulti fieldDevGroups = accessList.GetField <DBFieldLookupMulti>(Consts.Lists.AccessOfDelivering.Fields.DeliverinGroups, true);
//текущий юзер
DBUser currentUser = this.Context.CurrentUser;
//группы текущего юзера
IEnumerable <int> currentUsersGroups = currentUser.AllGroups.Select(group => group.ID);
List <string> devGroupsID = new List <string>();
foreach (DBItem accessItem in accessList.GetItems($"{fieldUsers.GetSelectCondition(currentUser.ID)} OR {fieldGroups.GetSelectCondition(currentUsersGroups)}"))
{
devGroupsID.AddRange(accessItem.GetLookupValues(Consts.Lists.AccessOfDelivering.Fields.DeliverinGroups).Select(lv => lv.LookupID.ToString()));
}
string devGroupsIDStr = string.Join(",", devGroupsID.Distinct().ToArray());
string condition = devGroupsIDStr.Length > 0 ? $"OR[ID] IN({ devGroupsIDStr})" : string.Empty;
result = $@"
[ID] IN (
SELECT [ID] FROM {deviringList.TableName} WITH(NOLOCK)
WHERE ([{Consts.DeliveringGroups.AccessRestriction}] = 0 OR [{Consts.DeliveringGroups.AccessRestriction}] IS NULL)
{condition}
)
";
//throw new Exception(result);
return(result);
}
/// <summary>
/// Возвращает id по названию зоны ответственности.
/// </summary>
/// <param name="zoneName"></param>
/// <returns>id для поля "Зона ответственности"</returns>
private int ConvertResponsibilityZoneNameToID(string zoneName)
{
if (zoneName == null)
{
throw new ArgumentNullException(nameof(zoneName));
}
DBWeb web = this.Site.GetWeb("/dms/InvestmentInitiatives");
DBList listRespZones = web.GetList("ResponsibilityZones");
string selectCondition = $"[Название] = N'{zoneName}'";
DBItem responsibilityZone = listRespZones.GetItem(selectCondition);
if (responsibilityZone == null)
{
throw new Exception("Зона ответственности не найдена");
}
return(responsibilityZone.ID);
}
/// <summary>
/// Возвращает id по названию подразделения.
/// </summary>
/// <param name="zoneName"></param>
/// <returns>id для поля "Подразделение-заказчик"</returns>
private int ConvertDepartmentNameToID(string departmentName)
{
if (departmentName == null)
{
throw new ArgumentNullException(nameof(departmentName));
}
DBWeb web = this.Site.GetWeb("/");
DBList listDeps = web.GetList("Departments");
string selectCondition = $"[Название] = N'{departmentName}'";
DBItem department = listDeps.GetItem(selectCondition);
if (department == null)
{
throw new Exception("Подразделение не найдено");
}
return(department.ID);
}
private void SatislariWebeGonder()
{
//-- null ise insert 0 ise güncelle 1 ise gönderildi
DataTable dt = DB.GetData("select top 100 * from Satislar with(nolock) where GonderildiWS is null ");
for (int i = 0; i < dt.Rows.Count; i++)
{
ArrayList list = new ArrayList();
list.Add(new SqlParameter("@Tarih", Convert.ToDateTime(dt.Rows[i]["Tarih"].ToString())));
list.Add(new SqlParameter("@fkFirma", dt.Rows[i]["fkFirma"].ToString()));
list.Add(new SqlParameter("@fkSatisDurumu", dt.Rows[i]["fkSatisDurumu"].ToString()));
list.Add(new SqlParameter("@Aciklama", dt.Rows[i]["Aciklama"].ToString()));
string sql = @"insert into Satislar (Tarih,fkFirma,fkSatisDurumu,Aciklama)
values(@Tarih,@fkFirma,@fkSatisDurumu,@Aciklama)";
string sonuc = DBWeb.ExecuteSQL_Web(sql, list);
if (sonuc == "0")
{
DB.ExecuteSQL("update Satislar set GonderildiWS=1 where pkSatislar=" + dt.Rows[i]["pkSatislar"].ToString());
}
}
}
private void SatisDetayWebeGonder()
{
//-- null ise insert 0 ise güncelle 1 ise gönderildi
DataTable dt = DB.GetData("select top 100 * from SatisDetay with(nolock) where GonderildiWS is null ");
for (int i = 0; i < dt.Rows.Count; i++)
{
ArrayList list = new ArrayList();
list.Add(new SqlParameter("@fkSatislar", dt.Rows[i]["fkSatislar"].ToString()));
list.Add(new SqlParameter("@Tarih", Convert.ToDateTime(dt.Rows[i]["Tarih"].ToString())));
list.Add(new SqlParameter("@fkStokKarti", dt.Rows[i]["fkStokKarti"].ToString()));
list.Add(new SqlParameter("@Adet", dt.Rows[i]["Adet"].ToString()));
list.Add(new SqlParameter("@SatisFiyati", dt.Rows[i]["SatisFiyati"].ToString().Replace(",", ".")));
string sql = @"insert into SatisDetay (Tarih,fkSatislar,fkStokKarti,Adet,SatisFiyati)
values(@Tarih,@fkSatislar,@fkStokKarti,@Adet,@SatisFiyati)";
string sonuc = DBWeb.ExecuteSQL_Web(sql, list);
if (sonuc == "0")
{
DB.ExecuteSQL("update SatisDetay set GonderildiWS=1 where pkSatisDetay=" + dt.Rows[i]["pkSatisDetay"].ToString());
}
}
}
