/// <summary>Завершення заміни</summary>
private void finishingReplaceLamps()
{
object[] result = null;
using (
SqlCeCommand query =
dbWorker.NewQuery(
@"SELECT Map, Register, Position, Status, SyncRef FROM Cases WHERE RTRIM(BarCode)=RTRIM(@Old)"))
{
query.AddParameter("Old", ExistLampBarCode);
result = query.SelectArray();
}
if (result != null)
{
int map = Convert.ToInt32(result[0]);
int register = Convert.ToInt32(result[1]);
int position = Convert.ToInt32(result[2]);
Cases.ChangeLighterState(NewLampBarCode, TypesOfLampsStatus.IsWorking, false, map, register,
position);
Cases.ChangeLighterState(ExistLampBarCode, TypesOfLampsStatus.Storage, true);
//Внесение записи в "Перемещение"
string newLampRef = BarcodeWorker.GetRefByBarcode(typeof(Cases), NewLampBarCode);
Movement.RegisterLighter(ExistLampBarCode, result[4].ToString(), OperationsWithLighters.Removing,
map, register, position);
Movement.RegisterLighter(NewLampBarCode, newLampRef, OperationsWithLighters.Installing,
map, register, position);
}
}
/// <summary>Позиция светильника</summary>
/// <returns>Карта, Register, Position</returns>
private object[] getLightPositionInfo()
{
using (SqlCeCommand query = dbWorker.NewQuery(@"SELECT m.Description, c.Map MapId, c.Register, c.Position
FROM Cases c
LEFT JOIN Maps m ON m.Id=c.Map
WHERE RTRIM(c.Barcode)=@Barcode"))
{
query.AddParameter("Barcode", LightBarcode);
return(query.SelectArray());
}
}
private object[] getMapInfo(long id)
{
using (SqlCeCommand query = dbWorker.NewQuery(@"SELECT m.Id,m.Description,m.RegisterFrom,m.RegisterTo
FROM Maps m
WHERE Id=@Id"))
{
query.AddParameter("Id", id);
return(query.SelectArray());
}
}
/// <summary>Інформація по світильнику на гектарі</summary>
/// <param name="barcode">Штрихкод світильника</param>
/// <returns>Інформація</returns>
private object[] LuminaireOnHectareInfo(string barcode)
{
using (SqlCeCommand command = dbWorker.NewQuery(@"SELECT CaseModel, CaseParty, CaseWarrantly
FROM (
SELECT
0 Type
, t.Description CaseModel
, p.Description CaseParty
, c.DateOfWarrantyEnd CaseWarrantly
FROM Cases c
LEFT JOIN Models t ON t.Id=c.Model
LEFT JOIN Party p ON p.Id=c.Party
WHERE RTRIM(c.BarCode) like @BarCode
UNION
SELECT
1 Type
, t.Description CaseModel
, p.Description CaseParty
, l.DateOfWarrantyEnd CaseWarrantly
FROM Cases c
LEFT JOIN Lamps l ON l.Id=c.Lamp
LEFT JOIN Models t ON t.Id=l.Model
LEFT JOIN Party p ON p.Id=l.Party
WHERE RTRIM(c.BarCode) like @BarCode
UNION
SELECT
2 Type
, '' CaseModel
, p.Description CaseParty
, u.DateOfWarrantyEnd CaseWarrantly
FROM Cases c
LEFT JOIN ElectronicUnits u ON u.Id=c.ElectronicUnit
LEFT JOIN Models t ON t.Id=u.Model
LEFT JOIN Party p ON p.Id=u.Party
WHERE RTRIM(c.BarCode) like @BarCode) t
ORDER BY Type"))
{
command.AddParameter("@BarCode", barcode);
return
(command.SelectArray(new Dictionary <string, Enum>
{
{ BaseFormatName.DateTime, DateTimeFormat.OnlyDate }
}));
}
}
/// <summary>Инфо о блоке</summary>
private object[] getData()
{
using (SqlCeCommand query = dbWorker.NewQuery(@"SELECT
t.Description Model
, p.Description Party
, e.DateOfWarrantyEnd Warrantly
FROM ElectronicUnits e
LEFT JOIN Models t ON t.Id=e.Model
LEFT JOIN Party p ON p.Id=e.Party
WHERE RTRIM(e.BarCode)=RTRIM(@BarCode)"))
{
query.AddParameter("BarCode", UnitBarcode);
return
(query.SelectArray(new Dictionary <string, Enum> {
{ BaseFormatName.DateTime, DateTimeFormat.OnlyDate }
}));
}
}
/// <summary>Информация по светильнику</summary>
/// <returns>Model, Party, DateOfWarrantyEnd, Contractor</returns>
public static object[] GetLightInfo(string lightBarcode)
{
using (SqlCeCommand query = dbWorker.NewQuery(@"SELECT
m.Description Model
, p.Description Party
, c.DateOfWarrantyEnd
, cc.Description Contractor
FROM Cases c
LEFT JOIN Models m ON m.Id=c.Model
LEFT JOIN Party p ON p.Id=c.Party
LEFT JOIN Contractors cc ON cc.Id=p.Contractor
WHERE RTRIM(c.Barcode)=RTRIM(@BarCode)"))
{
query.AddParameter("Barcode", lightBarcode);
return
query.SelectArray(new Dictionary<string, Enum> { { BaseFormatName.DateTime, DateTimeFormat.OnlyDate } });
}
}
/// <summary>Отримати інформації по блоку</summary>
private object[] getUnitInfo()
{
using (SqlCeCommand query = dbWorker.NewQuery(@"SELECT
m.Description Model
, p.Description Party
, e.DateOfWarrantyEnd
, c.Description Contractor
FROM ElectronicUnits e
LEFT JOIN Models m ON m.Id=e.Model
LEFT JOIN Party p ON p.Id=e.Party
LEFT JOIN Contractors c ON c.Id=p.Contractor
WHERE RTRIM(e.Barcode)=RTRIM(@Barcode)"))
{
query.AddParameter("Barcode", UnitBarcode);
return
(query.SelectArray(new Dictionary <string, Enum> {
{ BaseFormatName.DateTime, DateTimeFormat.OnlyDate }
}));
}
}
//private void installMovement(string caseBarCode)
//{
// List<object> data = getPositionInfo(caseBarCode);
// string newBarcode = NewAccessoryBarcode.ToString();
// string syncRef = BarcodeWorker.GetRefByBarcode(accessoryTable, newBarcode);
// Movement.RegisterLighter(newBarcode, syncRef, OperationsWithLighters.Installing,
// (int) data[0], (int) data[1], (int) data[2]);
//}
//private void removeMovement(string oldBarcode, string caseBarCode)
//{
// List<object> data = getPositionInfo(caseBarCode);
// string syncRef = BarcodeWorker.GetRefByBarcode(accessoryTable, oldBarcode);
// Movement.RegisterLighter(oldBarcode, syncRef, OperationsWithLighters.Removing,
// (int) data[0], (int) data[1], (int) data[2]);
//}
//private List<object> getPositionInfo(string caseBarCode)
//{
// string query = string.Format("SELECT Map,Register,Position From {0} WHERE RTRIM({1})=RTRIM(@{1})",
// typeof (Cases).Name, dbObject.BARCODE_NAME);
// SqlCeCommand command = dbWorker.NewQuery(query);
// command.AddParameter(dbObject.BARCODE_NAME, caseBarCode);
// return command.SelectToList();
//}
#endregion
#region Query
private object[] getInfo(out bool underWarranty)
{
string table = type == TypeOfAccessories.Lamp ? "Lamps" : "ElectronicUnits";
using (SqlCeCommand query = dbWorker.NewQuery(string.Format(@"SELECT
CASE WHEN l.DateOfWarrantyEnd>=@EndOfDay THEN 1 ELSE 0 END UnderWarranty
, t.Description CaseModel
, p.Description CaseParty
, l.DateOfWarrantyEnd CaseWarrantly
FROM {0} l
LEFT JOIN Cases c ON l.Id=c.Lamp
LEFT JOIN Models t ON t.Id=l.Model
LEFT JOIN Party p ON p.Id=l.Party
WHERE RTRIM(l.BarCode) like @BarCode", table)))
{
query.AddParameter("BarCode", NewAccessoryBarcode);
query.AddParameter("EndOfDay", DateTime.Now.Date.AddDays(1));
object[] result = query.SelectArray();
underWarranty = result != null && Convert.ToBoolean(result[0]);
return(result);
}
}
/// <summary>Інформація про новий світильник</summary>
private object[] GetNewIlluminatorInfo()
{
using (SqlCeCommand query = dbWorker.NewQuery(@"
SELECT CaseModel,CaseParty,CaseWarrantly
FROM(
SELECT
0 Type
, t.Description CaseModel
, p.Description CaseParty
, c.DateOfWarrantyEnd CaseWarrantly
FROM Cases c
LEFT JOIN Models t ON t.Id=c.Model
LEFT JOIN Party p ON p.Id=c.Party
WHERE c.BarCode=@BarCode
UNION
SELECT
1 Type
, '' CaseModel
, p.Description CaseParty
, u.DateOfWarrantyEnd CaseWarrantly
FROM Cases c
LEFT JOIN ElectronicUnits u ON u.Id=c.ElectronicUnit
LEFT JOIN Models t ON t.Id=u.Model
LEFT JOIN Party p ON p.Id=u.Party
WHERE c.BarCode=@BarCode)t
ORDER BY Type"))
{
query.AddParameter("BarCode", NewLampBarCode);
return
(query.SelectArray(new Dictionary <string, Enum> {
{ BaseFormatName.DateTime, DateTimeFormat.OnlyDate }
}));
}
}
private void finish(bool isForExchange)
{
object[] result = null;
//Штрихкод установленной лампы
using (SqlCeCommand query = dbWorker.NewQuery(string.Format(@"
SELECT a.Barcode, a.SyncRef
FROM Cases c
LEFT JOIN {0} a ON a.Id=c.{1}
WHERE RTRIM(c.BarCode)=@BarCode", accessoryTable, accessoryTable.Substring(0, accessoryTable.Length - 1))))
{
query.AddParameter("BarCode", CaseBarcode);
result = query.SelectArray();
}
if (result != null && result.Length == 2)
{
object oldLampBarcode = result[0];
object accessoryRef = result[1];
object caseId = BarcodeWorker.GetIdByBarcode(CaseBarcode);
object newAccessoryId = BarcodeWorker.GetIdByBarcode(NewAccessoryBarcode);
bool isLamp = accessoryTable == typeof(db.Lamps).Name;
string partOfCommand = string.Format(
"UPDATE {0} SET Status=@Status{1},[Case]=@Case,{2}=0,DateOfActuality=@Date{3} WHERE ",
accessoryTable,
type != TypeOfAccessories.ElectronicUnit
? ", Barcode=@NewBarcode "
: string.Empty,
dbObject.IS_SYNCED,
"{0}");
//Статус комплектующего, которое меняем
TypesOfLampsStatus status = Accessory.GetState(TypeOfAccessories.Lamp, oldLampBarcode.ToString());
//Старое комплектующее
using (SqlCeCommand query = dbWorker.NewQuery(string.Concat(
string.Format(partOfCommand, ",DrawdownDate=@DrawdownDate"),
" RTRIM(SyncRef)=RTRIM(@SyncRef)")))
{
query.AddParameter("Status",
isForExchange ? TypesOfLampsStatus.ToRepair : TypesOfLampsStatus.Storage);
query.AddParameter("Case", 0);
query.AddParameter("LampBarcode", oldLampBarcode);
query.AddParameter("NewBarcode", isLamp ? NewAccessoryBarcode : oldLampBarcode);
query.AddParameter(dbObject.SYNCREF_NAME, accessoryRef);
query.AddParameter("Date", DateTime.Now);
query.AddParameter("DrawdownDate", DateTime.Now);
query.ExecuteNonQuery();
}
//Новое комплектующее
using (
SqlCeCommand query =
dbWorker.NewQuery(string.Concat(string.Format(partOfCommand, string.Empty), " Id=@Id")))
{
query.AddParameter("Status", status);
query.AddParameter("Case", caseId);
query.AddParameter("Id", newAccessoryId);
query.AddParameter("NewBarcode", isLamp ? string.Empty : oldLampBarcode);
query.AddParameter("Date", DateTime.Now);
query.ExecuteNonQuery();
}
//Замена комплектующего в светильнике
using (SqlCeCommand query = dbWorker.NewQuery(string.Format(
"UPDATE CASES SET {0}=@NewAccessoryId,{1}=0,DateOfActuality=@Date WHERE RTRIM(BarCode)=RTRIM(@CaseBarcode)",
accessoryField,
dbObject.IS_SYNCED)))
{
query.AddParameter("CaseBarcode", CaseBarcode);
query.AddParameter("NewAccessoryId", newAccessoryId);
query.AddParameter("Date", DateTime.Now);
query.ExecuteNonQuery();
}
//Завершение
//string caseBarcode = CaseBarcode.ToString();
//installMovement(caseBarcode);
//removeMovement(oldLampBarcode.ToString(), caseBarcode);
OnHotKey(KeyAction.Esc);
}
}