public int GetCountNotifications(
ENotificationStatus status,
NotificationPriorityM priority,
NotificationTypeM type,
string filterText,
int?countInPage = 100,
int?pageNum = null)
{
using (ISQLCommand sqlCmd = _app.SqlWork())
{
sqlCmd.sql = @"SELECT count(*) as cnt FROM notification.view_user_notifications";
sqlCmd.sql += " WHERE 1=1";
string where = GenerateWhereSQL(status, priority, type, filterText);
sqlCmd.sql += where;
sqlCmd.sql += ";";
try
{
return(sqlCmd.ExecuteScalar <int>());
}
catch (Exception ex)
{
throw ex;
}
}
}
public NotificationM GetNotification(int gid)
{
using (ISQLCommand sqlCmd = _app.SqlWork())
{
sqlCmd.sql = @"SELECT gid, type_gid, type_name, priority_type_gid, priority_name, weight, subject,
message, created, seen_datetime, table_gid, row_gid, alias_text, user_gid, favorite
FROM notification.view_user_notifications WHERE gid=" + gid.ToString();
sqlCmd.ExecuteReader();
if (sqlCmd.CanRead())
{
return(new NotificationM(
sqlCmd.GetValue <int>("gid"),
sqlCmd.GetValue <int>("type_gid"),
sqlCmd.GetValue <int>("priority_type_gid"),
sqlCmd.GetValue <int>("weight"),
sqlCmd.GetValue <string>("subject"),
sqlCmd.GetValue <string>("message"),
sqlCmd.GetValue <DateTime?>("created"),
sqlCmd.GetValue <DateTime?>("seen_datetime"),
sqlCmd.GetValue <int>("user_gid"),
sqlCmd.GetValue <bool>("favorite"),
new RefObjM()
{
IdObj = sqlCmd.GetValue <int?>("row_gid"), IdTable = sqlCmd.GetValue <int?>("table_gid"), Name = sqlCmd.GetValue <string>("alias_text")
}
));
}
}
return(null);
}
/// <summary>
/// Загрузка справочника видов топлива
/// </summary>
/// <returns></returns>
public static ObservableCollection <wb_Fuel_mark> Load_Fuel_marks()
{
ObservableCollection <wb_Fuel_mark> x = new ObservableCollection <wb_Fuel_mark>();
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = "SELECT gid, name FROM autobase.waybills_fuel_marks";
sqlqry.ExecuteReader();
while (sqlqry.CanRead())
{
x.Add(new wb_Fuel_mark(
sqlqry.GetValue <int>("gid"),
sqlqry.GetValue <string>("name"),
sqlqry.GetValue <string>("name").ToUpper().Trim().Equals("НЕТ") ? true : false
));
}
sqlqry.Close();
}
catch (Exception z)
{
sqlqry.Close();
throw z;
}
}
return(x);
}
/// <summary>
/// Загрузка справочника зоны (работ)
/// </summary>
/// <returns></returns>
public static ObservableCollection <wb_Road_type> Load_Road_types()
{
ObservableCollection <wb_Road_type> x = new ObservableCollection <wb_Road_type>();
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = "SELECT gid, name FROM autobase.waybills_road_types";
sqlqry.ExecuteReader();
while (sqlqry.CanRead())
{
x.Add(new wb_Road_type(
sqlqry.GetValue <int>("gid"),
sqlqry.GetValue <string>("name")
));
}
sqlqry.Close();
}
catch (Exception z)
{
sqlqry.Close();
throw z;
}
}
return(x);
}
/// <summary>
/// Загрузка справочника организаций
/// </summary>
/// <returns></returns>
public static ObservableCollection <wb_Organisation> Load_Organisations()
{
ObservableCollection <wb_Organisation> x = new ObservableCollection <wb_Organisation>();
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = @"SELECT gid, name, waybill_name_seq, waybill_med_checks FROM autobase.orgs WHERE gid in ( SELECT autobase.get_access_orgs() ) and waybill_name_seq is not null;";
sqlqry.ExecuteReader();
if (sqlqry.CanRead())
{
x.Add(new wb_Organisation(
sqlqry.GetValue <int>("gid"),
sqlqry.GetValue <string>("name"),
sqlqry.GetValue <string>("waybill_name_seq"),
sqlqry.GetValue <bool>("waybill_med_checks")
));
}
sqlqry.Close();
}
catch (Exception z)
{
sqlqry.Close();
throw z;
}
}
return(x);
}
/// <summary>
/// Загрузка справочника маршрутов
/// </summary>
/// <returns></returns>
public static ObservableCollection <wb_Route> Load_Route(int org_id)
{
ObservableCollection <wb_Route> x = new ObservableCollection <wb_Route>();
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = String.Format("SELECT gid, name FROM autobase.waybills_routes WHERE org_id={0} ORDER BY name", org_id);
sqlqry.ExecuteReader();
while (sqlqry.CanRead())
{
x.Add(new wb_Route(
sqlqry.GetValue <int>("gid"),
sqlqry.GetValue <string>("name")
));
}
sqlqry.Close();
}
catch (Exception z)
{
sqlqry.Close();
throw z;
}
}
return(x);
}
public override int Execute()
{
CommandStatus ret = CommandStatus.E_OK;
if (_sql.Trim().ToUpper().StartsWith("SELECT"))
{
ret = CommandStatus.E_FAIL_INVALID_SQL;
}
else
{
IConnection conn = null;
try
{
conn = CreateConnection(_prv, _connStr);
conn.Open();
}
catch (OSGeo.FDO.Common.Exception ex)
{
WriteException(ex);
ret = CommandStatus.E_FAIL_CONNECT;
}
if (conn.ConnectionState == ConnectionState.ConnectionState_Open)
{
try
{
var caps = conn.CommandCapabilities;
if (Array.IndexOf <int>(caps.Commands, (int)CommandType.CommandType_SQLCommand) < 0)
{
ret = CommandStatus.E_FAIL_SQL_COMMAND_NOT_SUPPORTED;
}
else
{
using (ISQLCommand cmd = (ISQLCommand)conn.CreateCommand(CommandType.CommandType_SQLCommand))
{
cmd.SQLStatement = _sql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
WriteException(ex);
ret = CommandStatus.E_FAIL_SQL_EXECUTION_ERROR;
}
}
}
}
finally
{
conn.Close();
}
}
}
return((int)ret);
}
public List <NotificationM> GetNotifications(
ENotificationStatus status,
NotificationPriorityM priority,
NotificationTypeM type,
string filterText,
int?countInPage = 100,
int?pageNum = null)
{
List <NotificationM> outList = new List <NotificationM>();
using (ISQLCommand sqlCmd = _app.SqlWork())
{
sqlCmd.sql = @"SELECT gid, type_gid, type_name, priority_type_gid, priority_name, weight, subject,
message, created, seen_datetime, table_gid, row_gid, alias_text, user_gid, favorite
FROM notification.view_user_notifications";
sqlCmd.sql += " WHERE 1=1";
string where = GenerateWhereSQL(status, priority, type, filterText);
sqlCmd.sql += where;
sqlCmd.sql += " ORDER BY created DESC";
if (pageNum != null)
{
int begin = (pageNum.Value - 1) * 100;
countInPage = countInPage.HasValue ? countInPage.Value : 100;
sqlCmd.sql += string.Format(" LIMIT {0} OFFSET {1};", countInPage, begin);
}
try
{
sqlCmd.ExecuteReader();
while (sqlCmd.CanRead())
{
outList.Add(new NotificationM(
sqlCmd.GetValue <int>("gid"),
sqlCmd.GetValue <int>("type_gid"),
sqlCmd.GetValue <int>("priority_type_gid"),
sqlCmd.GetValue <int>("weight"),
sqlCmd.GetValue <string>("subject"),
sqlCmd.GetValue <string>("message"),
sqlCmd.GetValue <DateTime?>("created"),
sqlCmd.GetValue <DateTime?>("seen_datetime"),
sqlCmd.GetValue <int>("user_gid"),
sqlCmd.GetValue <bool>("favorite"),
new RefObjM()
{
IdObj = sqlCmd.GetValue <int?>("row_gid"), IdTable = sqlCmd.GetValue <int?>("table_gid"), Name = sqlCmd.GetValue <string>("alias_text")
}
));
}
}
catch (Exception ex)
{
throw ex;
}
}
return(outList);
}
public int GetUnreadCount()
{
int z = 0;
using (ISQLCommand sqlCmd = _app.SqlWork(true))
{
sqlCmd.sql = "SELECT cnt_unread FROM notification.notification_counter WHERE user_gid = (SELECT notification.get_current_user_id())";
sqlCmd.ExecuteReader();
return(sqlCmd.CanRead() ? sqlCmd.GetValue <int>("cnt_unread") : 0);
}
}
/// <summary>
/// Загрузка справочника автомобилей
/// </summary>
/// <param name="org_id">Организация</param>
/// <param name="gid">Автомобиль</param>
/// <returns></returns>
public static ObservableCollection <wb_Car> Load_Cars(int org_id, int?gid)
{
ObservableCollection <wb_Car> x = new ObservableCollection <wb_Car>();
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = String.Format("SELECT c.gid, c.gos_no, c.gar_no, ct.name type_name, cm.name mark_name, cmd.name model_name, c.model_id" +
" FROM autobase.cars c" +
" LEFT JOIN autobase.cars_types ct ON c.type_id = ct.gid" +
" LEFT JOIN autobase.cars_marks cm ON c.mark_id = cm.gid" +
" LEFT JOIN autobase.cars_models cmd ON c.model_id = cmd.gid" +
" WHERE c.org_id = {0} and c.type_id not in (2,39)", org_id);
if (gid != null)
{
sqlqry.sql += String.Format(" UNION SELECT c.gid, c.gos_no, c.gar_no, ct.name type_name, cm.name mark_name, cmd.name model_name, c.model_id" +
" FROM autobase.cars c" +
" LEFT JOIN autobase.cars_types ct ON c.type_id = ct.gid" +
" LEFT JOIN autobase.cars_marks cm ON c.mark_id = cm.gid" +
" LEFT JOIN autobase.cars_models cmd ON c.model_id = cmd.gid" +
" WHERE c.gid = {0} ORDER BY gos_no", gid);
}
else
{
sqlqry.sql += " ORDER BY gos_no";
}
sqlqry.ExecuteReader();
while (sqlqry.CanRead())
{
x.Add(new wb_Car(
sqlqry.GetValue <int>("gid"),
sqlqry.GetValue <string>("gos_no"),
sqlqry.GetValue <string>("gar_no"),
sqlqry.GetValue <string>("type_name"),
sqlqry.GetValue <string>("model_name"),
sqlqry.GetValue <string>("mark_name"),
sqlqry.GetValue <int>("model_id")
));
}
sqlqry.Close();
}
catch (Exception z)
{
sqlqry.Close();
throw z;
}
}
return(x);
}
public List <NotificationPriorityM> GetPriorityTypes()
{
List <NotificationPriorityM> result = new List <NotificationPriorityM>();
using (ISQLCommand sqlCmd = _app.SqlWork())
{
sqlCmd.sql = "SELECT gid, typename, weight, knowncolor FROM notification.notification_priority_types ORDER BY typename";
sqlCmd.ExecuteReader();
while (sqlCmd.CanRead())
{
result.Add(new NotificationPriorityM(sqlCmd.GetValue <int>("gid"),
sqlCmd.GetValue <int>("weight"),
sqlCmd.GetValue <string>("typename"), (System.Drawing.KnownColor)sqlCmd.GetValue <int>("knowncolor")));
}
return(result);
}
}
/// <summary>
/// Загрузка справочника водителей
/// </summary>
/// <returns></returns>
public static ObservableCollection <wb_Driver> Load_Driver(int org_id, int?gid)
{
ObservableCollection <wb_Driver> x = new ObservableCollection <wb_Driver>();
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = String.Format("SELECT e.gid, e.firstname, e.lastname, e.middlename, e.tab_no" +
" FROM autobase.employees e, autobase.employees_positions p, autobase.employees_positions_groups pg" +
" WHERE e.position_id = p.gid and p.group_id = pg.gid and pg.gid = 3 and e.org_id={0}", org_id);
if (gid != null)
{
sqlqry.sql += String.Format(" UNION SELECT e.gid, e.firstname, e.lastname, e.middlename, e.tab_no" +
" FROM autobase.employees e, autobase.employees_positions p, autobase.employees_positions_groups pg" +
" WHERE e.position_id = p.gid and p.group_id = pg.gid and e.gid={0}", gid);
}
else
{
sqlqry.sql += " ORDER BY lastname";
}
sqlqry.ExecuteReader();
while (sqlqry.CanRead())
{
x.Add(new wb_Driver(
sqlqry.GetValue <int>("gid"),
sqlqry.GetValue <string>("firstname"),
sqlqry.GetValue <string>("lastname"),
sqlqry.GetValue <string>("middlename"),
sqlqry.GetValue <string>("tab_no")
));
}
sqlqry.Close();
}
catch (Exception z)
{
sqlqry.Close();
throw z;
}
}
return(x);
}
/// <summary>
/// Может ли текущий пользователь изменять значения путевого листа, после его выписки
/// </summary>
/// <returns></returns>
public static bool isAdminWaybill()
{
bool rez = false;
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = "SELECT autobase.is_admin_waybills() AS adminQ";
if (sqlqry.ExecuteReader() && sqlqry.CanRead())
{
rez = sqlqry.GetValue <bool>("adminQ");
}
sqlqry.Close();
}
catch (Exception x)
{
sqlqry.Close();
throw x;
}
}
return(rez);
}
/// <summary>
/// Загрузка справочника топливных карт
/// </summary>
/// <returns></returns>
public static ObservableCollection <wb_Fuel_card> Load_Fuel_cards(int org_id, int?gid_one = null, int?gid_two = null)
{
ObservableCollection <wb_Fuel_card> x = new ObservableCollection <wb_Fuel_card>();
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = String.Format("SELECT gid, card_no, fuel_mark_id FROM autobase.waybills_fuel_cards WHERE org_id = {0} ", org_id);
if (gid_one != null)
{
sqlqry.sql += String.Format(" UNION SELECT gid, card_no, fuel_mark_id FROM autobase.waybills_fuel_cards where gid = {0}", gid_one);
}
if (gid_two != null)
{
sqlqry.sql += String.Format(" UNION SELECT gid, card_no, fuel_mark_id FROM autobase.waybills_fuel_cards where gid = {0}", gid_two);
}
sqlqry.ExecuteReader();
while (sqlqry.CanRead())
{
x.Add(new wb_Fuel_card(
sqlqry.GetValue <int>("gid"),
sqlqry.GetValue <string>("card_no"),
sqlqry.GetValue <int>("fuel_mark_id")
));
}
sqlqry.Close();
}
catch (Exception z)
{
sqlqry.Close();
throw z;
}
}
return(x);
}
public static void Load_Waybill_Item(ref Waybill_M modelka)
{
//Waybill_M modelka = new Waybill_M();
if (modelka.Gid == null)
{
return;
}
int id_object = (int)modelka.Gid;
using (ISQLCommand sqlqry = MainPluginClass.App.SqlWork())
{
try
{
sqlqry.sql = String.Format("SELECT * FROM autobase.waybills WHERE gid = {0}", id_object);
if (sqlqry.ExecuteReader() && sqlqry.CanRead())
{
modelka.Gid = sqlqry.GetValue <int>("gid");
modelka.Doc_date = sqlqry.GetValue("doc_date") == null ? DateTime.MinValue : sqlqry.GetValue <DateTime>("doc_date");
modelka.Doc_no = sqlqry.GetValue("doc_no") == null ? "" : sqlqry.GetValue <String>("doc_no");
modelka.Car_id = sqlqry.GetValue <int>("car_id");
modelka.Trailer_id = sqlqry.GetValue <int?>("trailer_id");
modelka.Automaster_id = sqlqry.GetValue <int?>("automaster_id");
modelka.Date_out_plan = sqlqry.GetValue("date_out_plan") == null ? DateTime.MinValue : sqlqry.GetValue <DateTime>("date_out_plan");
modelka.Date_out_fact = sqlqry.GetValue("date_out_fact") == null ? DateTime.MinValue : sqlqry.GetValue <DateTime>("date_out_fact");
modelka.Date_in_plan = sqlqry.GetValue("date_in_plan") == null ? DateTime.MinValue : sqlqry.GetValue <DateTime>("date_in_plan");
modelka.Date_in_fact = sqlqry.GetValue("date_in_fact") == null ? DateTime.MinValue : sqlqry.GetValue <DateTime>("date_in_fact");
modelka.Driver_id = sqlqry.GetValue <int?>("driver_id");
modelka.Work_regime_id = sqlqry.GetValue <int?>("work_regime_id");
modelka.Motorcade = sqlqry.GetValue <int?>("motorcade");
modelka.Brigade = sqlqry.GetValue <int?>("brigade");
modelka.Route_id = sqlqry.GetValue <int?>("route_id");
modelka.Work_type_id = sqlqry.GetValue <int?>("work_type_id");
modelka.Cargo_type_id = sqlqry.GetValue <int?>("cargo_type_id");
modelka.Ttd_count = sqlqry.GetValue <int?>("ttd_count");
modelka.Trip_count = sqlqry.GetValue <int?>("trip_count");
modelka.Road_type_id = sqlqry.GetValue <int?>("road_type_id");
modelka.Km_begin = sqlqry.GetValue <decimal?>("km_begin");
modelka.Km_end = sqlqry.GetValue <decimal?>("km_end");
modelka.Km_run = sqlqry.GetValue <decimal?>("km_run");
modelka.Km_run_glonass = sqlqry.GetValue <decimal?>("km_run_glonass");
modelka.Mh_begin = sqlqry.GetValue <decimal?>("mh_begin");
modelka.Mh_end = sqlqry.GetValue <decimal?>("mh_end");
modelka.Mh_run = sqlqry.GetValue <decimal?>("mh_run");
modelka.Mh_run_glonass = sqlqry.GetValue <decimal?>("mh_run_glonass");
modelka.Mh_ob_begin = sqlqry.GetValue <decimal?>("mh_ob_begin");
modelka.Mh_ob_end = sqlqry.GetValue <decimal?>("mh_ob_end");
modelka.Mh_ob_run = sqlqry.GetValue <decimal?>("mh_ob_run");
modelka.Mh_ob_run_glonass = sqlqry.GetValue <decimal?>("mh_ob_run_glonass");
modelka.Fuel_mark_id = sqlqry.GetValue <int?>("fuel_mark_id");
modelka.Fuel_plan = sqlqry.GetValue <decimal?>("fuel_plan");
modelka.Fuel_fact = sqlqry.GetValue <decimal?>("fuel_fact");
modelka.Fuel_fact_glonass = sqlqry.GetValue <decimal?>("fuel_fact_glonass");
modelka.Fuel_begin = sqlqry.GetValue <decimal?>("fuel_begin");
modelka.Fuel_end = sqlqry.GetValue <decimal?>("fuel_end");
modelka.Fuel_begin_glonass = sqlqry.GetValue <decimal?>("fuel_begin_glonass");
modelka.Fuel_end_glonass = sqlqry.GetValue <decimal?>("fuel_end_glonass");
modelka.Fuel_mark2_id = sqlqry.GetValue <int?>("fuel_mark2_id");
modelka.Fuel_plan2 = sqlqry.GetValue <decimal?>("fuel_plan2");
modelka.Fuel_fact2 = sqlqry.GetValue <decimal?>("fuel_fact2");
modelka.Fuel_fact2_glonass = sqlqry.GetValue <decimal?>("fuel_fact2_glonass");
modelka.Fuel_begin2 = sqlqry.GetValue <decimal?>("fuel_begin2");
modelka.Fuel_end2 = sqlqry.GetValue <decimal?>("fuel_end2");
modelka.Fuel_begin2_glonass = sqlqry.GetValue <decimal?>("fuel_begin2_glonass");
modelka.Fuel_end2_glonass = sqlqry.GetValue <decimal?>("fuel_end2_glonass");
modelka.Fuel_card_id = sqlqry.GetValue <int?>("fuel_card_id");
modelka.Fuel_card2_id = sqlqry.GetValue <int?>("fuel_card2_id");
modelka.Calc_fuel_norm = sqlqry.GetValue <decimal?>("calc_fuel_norm");
modelka.Calc_fuel_fact = sqlqry.GetValue <decimal?>("calc_fuel_fact");
modelka.Calc_fuel_delta = sqlqry.GetValue <decimal?>("calc_fuel_delta");
modelka.Calc_fuel_drain = sqlqry.GetValue <int?>("calc_fuel_drain");
modelka.Calc_km_run_delta = sqlqry.GetValue <decimal?>("calc_km_run_delta");
modelka.Calc_mh_run_delta = sqlqry.GetValue <decimal?>("calc_mh_run_delta");
modelka.Pay_work_h = sqlqry.GetValue <int?>("pay_work_h");
modelka.Pay_lunch_h = sqlqry.GetValue <int?>("pay_lunch_h");
modelka.Pay_duty_h = sqlqry.GetValue <int?>("pay_duty_h");
modelka.Pay_repair_h = sqlqry.GetValue <int?>("pay_repair_h");
modelka.Pay_day_h = sqlqry.GetValue <int?>("pay_day_h");
modelka.Pay_night_h = sqlqry.GetValue <int?>("pay_night_h");
modelka.Pay_rate_rh = sqlqry.GetValue <decimal?>("pay_rate_rh");
modelka.Pay_total_r = sqlqry.GetValue <decimal?>("pay_total_r");
modelka.Support_persons = sqlqry.GetValue("support_persons") == null ? "" : sqlqry.GetValue <String>("support_persons");
modelka.Special_note_id = sqlqry.GetValue <int?>("special_note_id");
modelka.Notes = sqlqry.GetValue("notes") == null ? "" : sqlqry.GetValue <String>("notes");
modelka.Createdate = sqlqry.GetValue("createdate") == null ? DateTime.MinValue : sqlqry.GetValue <DateTime>("createdate");
modelka.Secondsave = sqlqry.GetValue <int?>("secondsave");
modelka.User_id = sqlqry.GetValue <int?>("user_id");
modelka.Org_id = sqlqry.GetValue <int?>("org_id");
}
sqlqry.Close();
}
catch (Exception x)
{
sqlqry.Close();
throw x;
}
}
}
